RMAN: Restoring ARCHIVELOG For Specific Database Incarnation

This description is only valid for Oracle DBs from version 11.2.x and above.

After an Oracle database has performed a failover, the former primary DB needs some archivelogs in order to become a synchronized standby DB. Sometimes test archive logs have been deleted from the DB host by the RMAN backup.

For cases, that it is not possible to simply restore the missing archivelogs for the actual DB incarnation, RMAN with Oracle 11.2.x and above offers the new syntax "RESTORE ARCHIVELOG ... INCARNATION ...".

Normally you can simply use the number of the previous incarnation. But for some "unclear" cases, this description shows how to find the correct incarnation for a needed specific archivelog.

In case the DB misses an archivelog, the DB alertlog should contain the following message after the restart of the standby DB:

. . .

2018-04-25T15:31:59.503251+02:00

Media Recovery start incarnation depth : 1, target inc# : 8, irscn : 3815571

Media Recovery Waiting for thread 1 sequence 32 branch(resetlogs_id) 974369770

. . .

Find the DB incarnation based in the resetlogs_id:

SQL>

set lines 400

select INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, STATUS, RESETLOGS_ID

  from V$DATABASE_INCARNATION

 where RESETLOGS_ID = 974369770;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      STATUS                       RESETLOGS_ID

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

           7           3674168 2018-04-25 10:16:10 PARENT                          974369770

Here the DB incarnations and their correspondent SCN:

RMAN>

list incarnation;

List of Database Incarnations

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

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

1       1       X0300Z01 2998214488       PARENT  1          2018-04-20 08:00:24

2       2       X0300Z01 2998214488       PARENT  2449245    2018-04-23 07:56:18

3       3       X0300Z01 2998214488       PARENT  2450480    2018-04-23 08:03:30

4       4       X0300Z01 2998214488       PARENT  2451567    2018-04-23 08:07:43

5       5       X0300Z01 2998214488       PARENT  2798708    2018-04-24 08:31:27

6       6       X0300Z01 2998214488       PARENT  3360141    2018-04-25 09:05:27

7       7       X0300Z01 2998214488       PARENT  3674168    2018-04-25 10:16:10

8       8       X0300Z01 2998214488       CURRENT 3815572    2018-04-25 14:44:33

Here the confirmation, that the archivelog sequence number 32 shown in the alertlog is linked to the resetlogs_id 974369770 :

RMAN> LIST ARCHIVELOG SCN BETWEEN 3674168 AND 3815572;

List of Archived Log Copies for database with db_unique_name C0200Z01_HSB2

=====================================================================

Key     Thrd Seq     S Low Time

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

1217    1    32      A 2018-04-25 14:34:32

        Name: +U02/X0300Z01_SIT2/ARCHIVELOG/2018_04_26/thread_1_seq_32.783.974446349 (TERMINAL)

Examples how to restore the archive logs for DB incarnation 7

RUN {

  ALLOCATE CHANNEL ch1 TYPE 'sbt_tape' PARMS="XXXXXXXXXXXXXXXX";

  # ALLOCATE CHANNEL ch1 TYPE disk;

  #

  RESTORE ARCHIVELOG SEQUENCE 32 INCARNATION 7;

  # RESTORE ARCHIVELOG FROM SEQUENCE 32 INCARNATION ALL;

  # RESTORE ARCHIVELOG FROM SEQUENCE 32 INCARNATION 7;

  # RESTORE ARCHIVELOG SEQUENCE BETWEEN 32 AND 35 INCARNATION 7;

  # RESTORE ARCHIVELOG FROM SCN 3674168 INCARNATION 7;

  # RESTORE ARCHIVELOG SCN BETWEEN 3674168 AND 3815572 INCARNATION 7;

  #

  # Syntax OK, but does not restore from other DB incarnations (non current DB incarnation)

  # The syntax diagram shows, that it is not possible to restore by TAG from a

  # non current DB incarnation.

  #   RESTORE ARCHIVELOG ALL FROM TAG 'TAG20180425T145639';  

}