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 archive log 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 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