This article shows us the procedure to activate and de-activate falshback option in oracle database.
You can recover the DB with flash recovery which is very fast if you activate it and it is very useful when we are performing changes in the database
You can recover the DB with flash recovery which is very fast if you activate it and it is very useful when we are performing changes in the database
Preparations
- · Please ensure the following preparations and pre-requisites are met before you enable or disable flashback.
- · The flashback destination which is to be used should have enough space for flashback operations. You specify this using oracle parameter DB_RECOVERY_FILE_DEST.
- · Ensure the database to be in Archivelog mode to activate flashback
Check the status of database
SQL> select log_mode, flashback_on from v$database;
SQL> select log_mode, flashback_on from v$database;
Flash back parameters checks and activation
There are three parameters which needs to be set online.
SQL> show parameter DB_RECOVERY_FILE_DEST;
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET;
SQL> alter system set DB_RECOVERY_FILE_DEST = '/oracle//sapdata1/flashback';
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = ;
SQL> alter system set DB_FLASHBACK_RETENTION_TARGET = 1440;
SQL> show parameter DB_RECOVERY_FILE_DEST;
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET;
SQL> alter system set DB_RECOVERY_FILE_DEST = '/oracle//sapdata1/flashback';
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = ;
SQL> alter system set DB_FLASHBACK_RETENTION_TARGET = 1440;
Enabling flashback for the database and
creating restore point
SQL> alter database flashback on;
SQL> create restore point ;
Issue below sql statement to check if already the Flashback process is running
SQL> select * from v$bgprocess where name='RVWR';
# ps –ef | grep rvwr
You can use the following command to check the restore points created and its size
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;
SQL> create restore point ;
Issue below sql statement to check if already the Flashback process is running
SQL> select * from v$bgprocess where name='RVWR';
# ps –ef | grep rvwr
You can use the following command to check the restore points created and its size
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;
DISABLE Oracle Flashback
SQL> alter database flashback off;
At now if there are any guaranteed restore point, flashback status will show as Restore Point Only unless you delete the restore point manually.
IMP: Always drop the restore points when there's a confirmation that the flashback restore point is not any more needed for the project cutover
SQL> drop restore point TOP_CUTOVER;
At now if there are any guaranteed restore point, flashback status will show as Restore Point Only unless you delete the restore point manually.
IMP: Always drop the restore points when there's a confirmation that the flashback restore point is not any more needed for the project cutover
SQL> drop restore point TOP_CUTOVER;
Perform Flashback of database
Before performing Flashback please make sure that you've got all the redo logs generated after enabling the flashback as these are needed to perform Flashback.
Stop database
SQL> shutdown immediate;
Start DB in no mount mode
SQL> startup mount exclusive;
Go back to guaranteed restore point:
SQL> FLASHBACK DATABASE TO RESTORE POINT ‘top_cutover;
Go back to different point in time:
SQL> FLASHBACK DATABASE TO SCN ;
SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('YYYY-MM-DD HH24:MI:SS');
Flashback recovery is like incomplete media recovery, so open DB with
SQL> alter database open resetlog
Stop database
SQL> shutdown immediate;
Start DB in no mount mode
SQL> startup mount exclusive;
Go back to guaranteed restore point:
SQL> FLASHBACK DATABASE TO RESTORE POINT ‘top_cutover;
Go back to different point in time:
SQL> FLASHBACK DATABASE TO SCN ;
SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('YYYY-MM-DD HH24:MI:SS');
Flashback recovery is like incomplete media recovery, so open DB with
SQL> alter database open resetlog