Understanding database Incarnation and steps to recover to previous incarnation

To perform an incomplete recovery to previous incarnation, first of all we need to understand what database incarnation means.  Actually, incarnation is a new version of the database.  By opening the database with the resetlogs option, Oracle archives the current redo log file and clears them all by resetting the log sequence number to 1.  This option is most often used after an incomplete recovery where information from archived redo log files is applied partly but not  completely; therefore, the online redo log files need to be cleared.

If the database was opened with the resetlogs option, it is not possible to use backups that were taken before the newly created incarnation.

Take a look at the following scenario to better understand the process.

Note: we have taken the backup of the database

Lets perform the below steps

steps 1: Backup database
=======

step 2: lets create a table with some records and find the current SCN
======
SQL> create table emp1 as select * from emp1;
SQL> Insert into emp1 select * from emp1;
SQL> /
68096 rows created.

SQL>  select current_scn from v$database;
 CURRENT_SCN
-----------
     547181


Step 3: lets delete the rows of  the table emp1;
======

SQL> delete from emp1;
136192 rows deleted.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
     547891

Steps 4: lets drop the table
=======
SQL> drop table emp1;
Table dropped.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
     547917
SQL> alter system switch logfile;
System altered.



Step5: lets list the incarnations
======
select incarnation#, resetlogs_change#  from v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE#
------------ -----------------
           1                 1
           2            534907
 

Lets  perform an incomplete recovery to the state of the database before dropping the table, thus until scn 547891 as follows:

SQL>
shutdown immediate
SQL> startup mount

RMAN> run
2> {
3> set until scn=547891;
4> restore database;
5> recover database;
6> }
RMAN> alter database open resetlogs;

Now the database is opened using the resetlogs option, a new incarnation has been created. 
SQL>
select incarnation#, resetlogs_change# from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE#
------------ -----------------
           1                 1
           2            534907
           3            547896

SQL>  select * from emp1;
no rows selected
SQL>

The new incarnation has been created and the table has been recovered successfully. After this operation,  lets recover the database up to the delete command issued as follows:
RMAN> run
2> {
3> set until scn=547181;
4> restore database;
5> recover database;
6> }

executing command: set until clause
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== error message stack follows ===============
RMAN-00571: ===========================================================


Lets list the incarnations of the database
RMAN> list incarnation of database;
List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- --------------
1       1       DB1      1295402827       PARENT  1          30-AUG-05
2       2       DB1      1295402827       PARENT  534907     29-OCT-09
3       3       DB1      1295402827       CURRENT 547896     29-OCT-09

RMAN> shutdown immediate
RMAN> startup mount
RMAN> reset database to incarnation 2;
database reset to incarnation 2

RMAN> run
2> {
3> set until scn=547181;
4> restore database;
5> recover database;
6> }

RMAN> alter database open resetlogs;
database opened

RMAN> list incarnation of database;
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       DB1      1295402827       PARENT  1          30-AUG-17
2       2       DB1      1295402827       PARENT  534907     29-OCT-17
4       4       DB1      1295402827       CURRENT 547183     29-OCT-17
3       3       DB1      1295402827       ORPHAN  547896     29-OCT-17