Check last Incarnation restore time of oracle database

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

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

Always take fresh backup of database when you start the database in resetlog. Because when database is open with resetlogs all redo Sequence no is reset and start from one.
 
It will show you the last incarnation time when 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 database in resetlogs then a new incarnation is created at database level. If you tried to restore the database before incarnation state then you got error:
RMAN-20208: until change is before resetlogs change

Then you need to check the SCN from incarantion 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 incarantion 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s