Flashback Database to Undo an OPEN RESETLOGS

Suppose you have opened your database with OPEN RESETLOGS option after performing flashback feature. And after that you have made unwanted changes in your database.

So, now you want to get your database prior to RESETLOGS option. Then you have to do following.

1)Verify the flashback window:

Before performing the flashback, verify that the beginning of the flashback window is earlier than the time of the most recent OPEN RESETLOGS.

sql> select resetlogs_change# from v$database;
sql> select oldest_flashback_scn from v$flashback_database_log;

If V$DATABASE.RESETLOGS_CHANGE# is greater than V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN, then you can use Flashback Database to reverse the OPEN RESETLOGS.

2)If step 1 is ok then shutdown the database and mount it. Now check the step 1 again. If the target SCN is prior to the beginning of the flashback database window, an error is returned and the database is not modified.

3)Perform the Operation:

SQL> FLASHBACK DATABASE TO BEFORE RESETLOGS;


4)Open the database with read only and check your desired obejcts.

SQL> alter database open read only;

5)Follow step 6 of Step 6