Flashback Database To The Right of Open Resetlogs

In some cases, you may need to return the database to a point in time in the parent incarnation, later than the SCN of the OPEN RESETLOGS at which the current incarnation path branched from the old incarnation.

The process is described below.

1)Verify that the flashback logs contain enough information to flash back.

SQL> select oldest_flashback_scn from v$flashback_database_log;

2)Determine the target incarnation number for the flashback, that is, the incarnation key for the parent incarnation.

SQL> select prior_incarnation# from v$database_incarnation where status ='CURRENT';

3)Start the database in mount stage.

RMAN>SHUTDOWN IMMEDIATE;
RMAN>START THE MOUNT;

4)Issue List Incarnation to see the SCN.

RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DATA1 2547250380 PARENT 1 17-MAR-06
2 2 DATA1 2547250380 PARENT 526290 23-APR-08
3 3 DATA1 2547250380 ORPHAN 789429 27-APR-08
4 4 DATA1 2547250380 PARENT 789429 28-APR-08
5 5 DATA1 2547250380 ORPHAN 792942 28-APR-08
6 6 DATA1 2547250380 PARENT 792942 28-APR-08
7 7 DATA1 2547250380 CURRENT 793335 28-APR-08

5)Re-check the OLDEST_FLASHBACK_SCN again.

SQL> select oldest_flashback_scn from v$flashback_database_log;
OLDEST_FLASHBACK_SCN
--------------------
786801

6)Set the database incarnation to the parent incarnation:

RMAN> RESET DATABASE TO INCARNATION 6;

7)Run the FLASHBACK DATABASE command:

RMAN> flashback database to scn 792942;

8)Open the database read only and see the contents. Either OPEN it with RESETLOGS option or export/import data and RECOVER DATABASE.