Flashback Database - Procedure

Step 1: Check how far back you can flash back.

SQL> Select Oldest_flashback_SCN, to_char(oldest_flashback_time, ‘MM/DD/YY HH24:MI:SS’) from v$flashback_database_log;

Step 2: Connect to RMAN

$ rman target /

Step 3: Shutdown the database

RMAN> shutdown immediate;

Step 4: Start the database in mount mode

RMAN> Startup Mount;

Step 5: Flashing back to a specific SCN

RMAN> Flashback database to SCN 10956234;

Step 6: Open the database in Read-Only mode to check whether you have flashed back to the exact point in time.

RMAN> Alter database open read only;

If the database is not flashed back to the desired SCN repeat the steps with the new SCN from steps 3 to 6.

Step 7: Open the database

RMAN> shutdown immediate;
RMAN> Startup Mount;
RMAN> Alter database open resetlogs;

To Flashback to a specific time instead of SCN, change the step 5.

Step 5: Flashing back to a specific time.

RMAN> Flashback database to time ‘sysdate-2/60/24’;
Or
RMAN> Flashback database to time “to_date(‘01/23/09 13:00:00’, ‘mm/dd/yy hh24:mi:ss’)”;

To Flashback to a restore point, change the step 5.

Step 5: Flashing back to a restore point.

RMAN> Flashback database to restore point rp1;

To Flashback before the last resetlogs operation, change the step 5.

Step 5: Flashing back to before the last reset logs.

RMAN> Flashback database to before resetlogs;

Note: Database records the SCN when the database was opened with resetlogs. So flashback to the last resetlogs is merely same as issuing the flashback to that SCN.

Thanks