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

Here we will see , how to enable and disable flashback in oracle.

 

ENABLE FLASHBACK:

 

Make sure the database is in archive log mode :

Refer : How to enable and disable archive log 
To enable flashback we need to set two parameters: DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE

SQL> alter system set db_recovery_file_dest='/home/oracle/app/oracle';

System altered.

SQL> alter system set db_recovery_file_dest_size=10g;

System altered.

SQL> show parameter db_recovery_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /home/oracle/app/oracle
db_recovery_file_dest_size big integer 10G

Turn on flashback:
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

 

DISABLE FLASHBACK:

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

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

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

 

 

SEE ALSO – COMPLETE COLLECTION OF DATABASE SCRIPTS