Enabling / Disabling Flashback Database in 11gR2 without recycling database

Flashback database offer a simple way for performing a point in time recovery. This feature was introduced in oracle 10g.

Let’s take a scenario, where we have generated huge amount of flashback logs. Now to reclaim the space, we can reduce the db_flashback_retention_target parameter to a very small value, which will make the logs obsolete after some time & will delete them in case of space pressure in FRA (Flash / Fast Recovery Area).

But if we want to reclaim the space immediately, we can trun off the flashback.

In 10g we’ll have to

shutdown immediate 
startup mount 
alter database flashback off; 
alter database open;

But with 11gR2, Oracle introduced a new feature. We can now turn flashback on / off, when database is OPEN

SQL> select * from v$version;
BANNER
------------------------------------------------------
Oracle Database 11g Enterprise Edition 
Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select flashback_on, status from v$database, v$instance;
FLASHBACK_ON STATUS
------------------ ------------
YES OPEN

SQL> alter database flashback off;
Database altered.

SQL> select flashback_on, status from v$database, v$instance;
FLASHBACK_ON STATUS
------------------ ------------
NO OPEN

SQL> alter database flashback on;
Database altered.

SQL> select flashback_on, status from v$database, v$instance;
FLASHBACK_ON STATUS
------------------ ------------
YES OPEN

 

 

Advertisements
This entry was posted in Oracle 11.2.0.x, Oracle 11g Enhancements and tagged . Bookmark the permalink.

5 Responses to Enabling / Disabling Flashback Database in 11gR2 without recycling database

  1. Johnluchy says:

    Hello, this is a really nice article. Thanks for great posting.

  2. Henry says:

    Hi, Thanks for the guide, so does that mean that disabling and enabling the flashback without restart would help clear out the flashback recovery area in 11gR2?

  3. nrsun says:

    However , the database should be in archive log mode

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s