Flashback in Oracle Database
Flashback technology is a set of features in Oracle database that make your work easier to view past states of data or to move your database objects to a previous state without using point in time media recovery.
View past states of data or move database objects to previous state means you have performed some operations like DML + COMMIT and now you want to rollback that operation, this can be done easily through FLASHBACK technology without using point in time media recovery.
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 -- Change ARCHIVE mode of database
2. Flash Recovery Area has to be configured. To configure PFB steps :-
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- - -----------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
Currently flashback is disabled. To enable :-
A. Set db_recovery_file_dest_size initialization parameter.
SQL> alter system set db_recovery_file_dest_size=2g;
System altered.
B. After db_recovery_file_dest_size parameeter has been set, create a location in OS where your FLASHBACK logs will be stored.
bash-3.2$ cd /u01
bash-3.2$ mkdir FLASHBACK
bash-3.2$ pwd
/u01/FLASHBACK
C. Now set db_recovery_file_dest initialization parameter.
SQL> alter system set db_recovery_file_dest='/u01/FLASHBACK'; ======For Standalone database========
System altered.
SQL> alter system set db_recovery_file_dest='/u01/FLASHBACK' sid='*'; =======For RAC database=======
System altered.
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/FLASHBACK
db_recovery_file_dest_size big integer 2G
3. Create an Undo Tablespace with enough space to keep data for flashback operations. More often users update the database more space is required.
4. By default automatic Undo Management is enabled, if not enable it. In 10g release 2 or later default value of UNDO management is AUTO. If you are using lower release then PFB to enable it:-
SQL> alter system set undo_management=auto scope=spfile;
System altered
5. Shut Down your database
SQL> shut 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.
How to disable FLASHBACK in Oracle Database 11G R1 and below versions
1. Shut Down your database
SQL> shut 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 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. To configure PFA steps.
3. TO enable or disable flashback , we can change this while database is in open mode. PFB
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.
