Using the FLASHBACK database, we can return the database to a time or a SCN number in the past.
You can think of FLASHBACK Database as an alternative to incomplete database recovery.
Of course, you will get faster results than restore-recovery.
The following conditions are required in order to return the database to a moment in the past with “flashback database” .
- You need to have the right to sysdba.
- The “flash recovery area” must be prepared for the database .
- The database must be in mount mode.
- FLASHBACK must be enabled in the database with the command “ALTER DATABASE FLASHBACK ON”.
With the following script, you can query how far back you can return the database using “flashback database”.
Select * FROM V$FLASHBACK_DATABASE_LOG
With the command below we can see whether Flashback is enabled or disabled in the database.
Select flashback_on from v$database;
With the help of the following script, you can set the number of minutes you want to “flasback” the database.
With the below script, we set this parameter to enable flashed back 1000 minutes before.
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1000;
You can run Flashback database with rman or sql.
Below you will find examples of the Flasback database.
With the help of the following commands, we activate the flashback feature in the database and open the database in mount mode.
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
With the following command, we can return the database to the day ahead.
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1;
To return to the “t” time in the database, we can use the flashback database to return to the “t” time later.
You must create a restore point at “t” time to be able to return to “t” time.
To create a restore point you can read the article “How To Create a Restore Point On Oracle“.
You can use the following script to return a previously created restore point using a flashback database.
FLASHBACK DATABASE TO RESTORE POINT 'restore_point_name';
With the help of the following script, you can return the database to a specific scn number using flashback database.
FLASHBACK DATABASE TO SCN 152635;