How To Enable And Disable Flashback In Oracle Database

By using flashback technology we can restore the database to a particular point in past. It’s like time machine.

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


Enabling Flashback Database:
===================================

To revert the entire database to a prior point in time, you can either revert the entire database to a prior point in time by restoring a backup and doing point-in-time recovery, or you can enable Flashback Database. When you enable Flashback Database, the database generates flashback logs in the fast recovery area. These logs are used to flash back the database to a specified time. During usual operation, the database occasionally logs images of data blocks to the flashback logs. 

The database automatically creates, deletes, and resizes flashback logs.

Use the following command to check if Flashback Database is enabled for your target database:
SELECT FLASHBACK_ON FROM V$DATABASE;

• Ensure that you configure a fast recovery area and that the database is running in ARCHIVELOG mode.

• Optionally, specify the length of the desired flashback window (in minutes) by setting the DB_FLASHBACK_RETENTION_TARGET initialization parameter.

• The default value for this parameter is 1440 minutes, which is one day.The following command specifies that the flashback window must be 3 days.

     ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;

• Enable the Flashback Database feature for the whole database using the following command:

     ALTER DATABASE FLASHBACK ON;





How to Enable Flashback in oracle 11g database:

SQL> select name,open_mode,log_mode,flashback_on from v$database;

NAME      OPEN_MODE            LOG_MODE     FLASHBACK_ON
--------- -------------------- ------------ ------------------
DUCAT     READ WRITE           ARCHIVELOG   NO

SQL>
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440

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

SQL> alter database flashback on;
Database altered.
SQL> select name,open_mode,log_mode,flashback_on from v$database;
NAME      OPEN_MODE            LOG_MODE     FLASHBACK_ON
--------- -------------------- ------------ ------------------
DUCAT     READ WRITE           ARCHIVELOG   YES

SQL>





How to disable Flashback in oracle 11g database:

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

SQL> alter database flashback on;

Database altered.

SQL> select name,open_mode,log_mode,flashback_on from v$database;

NAME      OPEN_MODE            LOG_MODE     FLASHBACK_ON
--------- -------------------- ------------ ------------------

DUCAT     READ WRITE           ARCHIVELOG   YES

SQL>
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440

SQL>
SQL>
SQL> alter database flashback off;

Database altered.

SQL> select name,open_mode,log_mode,flashback_on from v$database;

NAME      OPEN_MODE            LOG_MODE     FLASHBACK_ON
--------- -------------------- ------------ ------------------
DUCAT     READ WRITE           ARCHIVELOG   NO

SQL>


NOTE: If you are in 10g, then we need to enable/disable the flashback mode in mount stage.

Enable Flashback:

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


Disable Flashback:

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