ORA-19909

MRP Recovery Failed With ORA-19909 [ID 554358.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3 - Release: 10.2 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 22-Mar-2011***

Symptoms

Physical Standby Database Setup. 
The Standby database has been activated (open read/write, resetlogs done), then using Flashback to recover it back to Physical Standby Status. During flashback, it could not recover due to a corrupted archive log.

After Restore backup past that corrupted Point, MRP will get this Error:

ERROR
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/oracle/oradata/PROD/oradg07/PROD_system01_4096m'

Changes

The Standby Database has been activated, then flashback to be Physical Standby again.

Cause

This is caused by Bug 6035495 ORA-19909 during MRP / RMAN-600 on resync
Fixed-Releases: 10.2.0.4, 11.1.0.0
Details:
ORA-19909 during MRP or RMAN-600 (8201) during RMAN resync can occur.
MRP encounter same error and standby has resetlogs done

The Problem here is the Primary Database has only 1 incarnation:

    RMAN> list incarnation of database;
      
    List of Database Incarnations
    DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
    ------- ------- -------- ---------------- --- ---------- ----------
    1       1       PROD     554405896        CURRENT 1          03-MAY-2007 15:52:40

While the Standby Database has 2 Incarnations due to Activation:

    rman > list incarnation of database;
  
    List of Database Incarnations
    DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
    ------- ------- -------- ---------------- --- ---------- ----------
    1       1       LIVE     554405896        PARENT  1          03-MAY-07
    2       2       PROD     554405896        CURRENT 1858780073 14-FEB-08

The CURRENT Incarnation on Standby Database is 2 which is different from Primary Database Incarnation 1. That is why ORA-19909 reported when recovery is done.

The incarnation is recorded in Datafile Header, so simply recreating the Standby Controlfile will not help.

Solution

The Solution is to find out the CURRENT Incarnation from the Primary Database, reset the Standby Database Incarnation to match the Primary Database using rman command:
        reset database to incarnation <key>
then perform Recovery.

In this sample:
Primary Database Incarnation is 1
Standby Database Incarnation is 2

On the Standby Site:

$ rman target=/
rman > reset database to incarnation 1
rman > exit

$sqlplus / as sysdba
SQL> recover standby database;
- supply archive log manually if the sequence prompted is not the right one
- type "CANCEL" after few logs applied
- start managed recovery
SQL> recover managed standby database disconnect; 

Check alert log, it should continue Managed Recovery now.

To avoid such situation in future please apply at least 10.2.0.4 Server Patch Set which includes
the fix for this.

see also : http://oracledb-admin.blogspot.com/2011/10/ora-19909-datafile-1-belongs-to-orphan.html