How to Rewind an OPEN RESETLOGS Operation with Flashback Database?

Hi,

Steps for using Flashback Database to reverse an unwanted “ALTER DATABASE OPEN RESETLOGS” statement is similar to “FLASHBACK DATABASE”. You will use “FLASHBACK DATABASE TO BEFORE RESETLOGS” statement. Of course you must enable flashback database before performing “ALTER DATABASE OPEN RESETLOGS” operation.

To undo an OPEN RESETLOGS operation:

1. Connect SQL*Plus to the target database and verify that the beginning of the flashback window is earlier than the time of the most recent OPEN RESETLOGS. Run the following queries:

SELECT RESETLOGS_CHANGE#
FROM V$DATABASE;

SELECT OLDEST_FLASHBACK_SCN
FROM V$FLASHBACK_DATABASE_LOG;

If RESETLOGS_CHANGE# is greater than OLDEST_FLASHBACK_SCN, then you can use Flashback Database to reverse the OPEN RESETLOGS operation

2. Shut down the database,

SHUTDOWN IMMEDIATE;

3. mount it

STARTUP MOUNT;

4. Recheck the flashback window. If the resetlogs SCN is still within the flashback window, then proceed to the next step.
Connect RMAN to the target database.
rman target /

5. Perform a flashback to the SCN immediately before the RESETLOGS. You can use the following FLASHBACK DATABASE command:

FLASHBACK DATABASE TO BEFORE RESETLOGS;

As with other uses of FLASHBACK DATABASE (until SCN, until TIME, etc.), if the target SCN is before the beginning of the flashback database window, an error is returned and the database is not modified. If the command completes successfully, then the database is left mounted and recovered to the most recent SCN before the OPEN RESETLOGS operation in the previous incarnation.

6. Open the database read-only in SQL*Plus and perform queries to ensure that the effects of the logical corruption have been reversed.