Check last Incarnation restore time of Oracle database

Leave a reply

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#,
       resetlogs_time,
       status
FROM   v$database_incarnation
ORDER BY resetlogs_time DESC;

OR 

SELECT dbinc_key,
       incarnation#,
       resetlogs_change#,
       resetlogs_time,
       status
FROM   rc_database_incarnation
ORDER BY resetlogs_time DESC;

List of incarnation:

RMAN> LIST INCARNATION OF DATABASE;

Check the Database 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

This error appears when you try to restore or recover UNTIL SCN / UNTIL TIME / UNTIL SEQUENCE that belongs to a previous database incarnation.

After an OPEN RESETLOGS, Oracle creates a new incarnation, and all SCNs before the resetlogs SCN belong to the old incarnation.

So RMAN refuses to use an SCN that is older than the resetlogs SCN of the current incarnation.

How to Fix RMAN‑20208

Step 1 — Check the database incarnations

RMAN> LIST INCARNATION OF DATABASE;

DB Key  Inc Key  DB Name  Incarnation#  Reset SCN  Reset Time     Status
------  -------  -------  ------------  ---------  -------------  --------
1       2        PROD      1             123456     01-JAN-2024    OLD
1       3        PROD      2             987654     10-FEB-2024    CURRENT

Step 2 — Identify the incarnation you want to use

Example: You want to recover to a date before 10‑FEB‑2024.

RMAN> RESET DATABASE TO INCARNATION <incarnation#>;

Example:
RMAN> RESET DATABASE TO INCARNATION 1;


Step 4 — Run your RESTORE/RECOVER again

RUN {
  SET UNTIL TIME "TO_DATE('2024-01-15 10:00:00','YYYY-MM-DD HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
}


Step 5: Open database with resetlogs

ALTER DATABASE OPEN RESETLOGS;


Example: With SCN

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

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

RMAN> RESET DATABASE TO INCARNATION 3;

Then after restore you can check incarnation status again:

RMAN> run
{
set until scn=547000;
restore database;
recover database;
}

RMAN> alter database open resetlogs;
database opened

RMAN> list incarnation of database;

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

Leave a Reply