Check the last incarnation restore time of oracle database

Check the last incarnation restore time when the database is started with resetlogs command with following command.

Incarnation is the time when the database is started with resetlogs command. If you start the database is the incarnation, then all available archivelog and backup become invalid. If you want to restore old incarnation, then you need to set the incarnation level in RMAN to restore or recover old SCN.

Always take a fresh backup of the database when you start the database in resetlog. Because when the database is open with resetlogs all redo Sequence no is reset and start from one.

It will show you the last incarnation time when the database is up with resetlogs commands.

SELECT incarnation#,

resetlogs_change#,

TO_CHAR(resetlogs_time, 'dd-mon-yyyy hh24:mi:ss') db_restored_time ,

scn_to_timestamp(resetlogs_change#) db_recovery_till_time

FROM v$database_incarnation

WHERE resetlogs_change# !=

(SELECT MIN(resetlogs_change#) FROM v$database_incarnation

);

List of incarnation:

select incarnation#, resetlogs_change# from v$database_incarnation;

List incarnation from RMAN prompt:

RMAN> list incarnation of database;

When you open the database in resetlogs then a new incarnation is created at the database level. If you tried to restore the database before incarnation state, then you got the error:

RMAN-20208: until change is before resetlogs change

Then you need to check the SCN from incarnation commands:

Example:

RMAN> list incarnation of database;

 DB Key Inc Key DB Name DB ID         STATUS  Reset SCN Reset Time

------ ------- ------- ------------- ------- --------- -----------

1      1       ORCL     1235491512   PARENT  1         03-AUG-17

2      2       ORCL     1235491512   PARENT  543000    10-OCT-17

3      3       ORCL     1235491512   CURRENT 548000    10-OCT-17

If I want to restore the SCN no 54700 previous then the current incarnation number, then I will get the rman error:

RMAN-20208: until change is before resetlogs change

run{

set until scn=547000;

restore database;

recover database;

}

executing command: set until clause

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== error message stack follows ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of set command at 10/10/2017 9:10:11

RMAN-20208: until change is before resetlogs change

Solution:

to overcome this problem, we need to set the database to old incarnation:

RMAN> shutdown immediate

RMAN> startup mount

RMAN> reset database to incarnation 2;

database reset to incarnation 2

Then after restore, you can check incarnation status again:

RMAN> run

2> {

3> set until scn=547000;

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       ORCL     1235491512   PARENT  1         03-AUG-17

2      2       ORCL     1235491512   PARENT  543000    10-OCT-17

4      4       ORCL     1235491512   CURRENT 547000    10-OCT-17

3      3       ORCL     1235491512   ORPHAN  548000    10-OCT-17

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!