FLASHBACK | Oracle Database | ENABLE or DISABLE | step by step

Flashback in Oracle Database

Someone tells that some changes are made in database and those changes are to be reverted back as of some particular time. says 1 hour before the database was good after that some script ran and all the data got changed. I want the database to be as of 1 hour back it was.

How to enable FLASHBACK in Oracle Database 11G R1 and below versions

1. Database has to be in ARCHIVELOG mode.
     To change ARCHIVE mode refer to --How to Enable and Disable Archive log mode. | Oracle database

2. Flash Recovery Area has to be configured :-

SQL> show parameter db_recovery_file_dest

NAME                                  TYPE           VALUE
------------------------------------       ----------- -       ----------------------------
db_recovery_file_dest             string                                                           
db_recovery_file_dest_size     big integer     0                                            


 Set the db_recovery_file_dest_size initialization parameter.

SQL> alter system set db_recovery_file_dest_size=2g;

System altered.

create a OS level folder to store flaskback logs -- not mandatory.

bash-3.2$ cd /app/11gr2/
bash-3.2$ mkdir flashback
bash-3.2$ pwd
/app/11gr2/flashback

Now set db_recovery_file_dest initialization parameter.

STANDALONE DATABASE
SQL> alter system set db_recovery_file_dest='/orcl_db/FLASHBACK'; 
System altered.

RAC DATABASE
SQL> alter system set db_recovery_file_dest='/orcl_db/FLASHBACK' sid='*'; 
System altered.

SQL> show parameter db_recovery

NAME                                 TYPE           VALUE
------------------------------------      -----------         ------------------------------
db_recovery_file_dest            string          /app/11gr2/flashback
db_recovery_file_dest_size     big integer    2G

3.Plan how much time your data should  be in undo and alter the undo_retention. TO SET UNDO RETENTION
Auto extend is recommended.

4. By default automatic Undo Management is AUTO, if not change it.  To change :-

SQL> alter system set undo_management=auto scope=spfile;

System altered

5. Shut Down your database

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down

6. Startup your database in MOUNT mode

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.

7. Change the Flashback mode of the database

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL>alter database flashback ON;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter database open;

Database altered.

FLASHBACK mode of the database has been enabled.

 To disable FLASHBACK in Oracle Database. version 11gR1 and lower.

1. Shut Down your database

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down

2. Startup your database in MOUNT mode

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>alter database flashback OFF;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO


SQL> alter database open;

Database altered.

FLASHBACK mode of the database has been disabled.


How to enable/disable FLASHBACK in Oracle Database 11G R2 and above versions.

From 11GR2 we do not have to bounce/restart the database to alter flashback.


1. Database has to be in ARCHIVELOG mode.
     To change ARCHIVE mode refer to -- Change ARCHIVE mode of database

2. Flash Recovery Area has to be configured, do as done above.

3.  TO enable or disable flashback , we can change this while database is in open mode.


SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter database flashback on;

Database altered.

SQL> alter database flashback off;

Database altered.