Skip to content

Oracle 11gr2 Dataguard – Resolving ORA 19909

Just wanted to write quick blog about an error we got on Standby Database and steps I performed to resolved it.  Saved me from re-building the dataguard database. 

Configuration:  All databases were on 11gR2 (11.2.0.4) single node using Oracle Restart. We had one Primary database (prmy) and two Standby Database (stdby1 and stdby2).  Primary was shipping logs to two standby database.  In other words it wasn’t cascade configuration.  For testing purposes we changed stdby1 to primary.  The log_archive_dest_2 and log_archive_dest_3 were set to ship logs to other two databases.  So as soon as stdby1 switched to Primary role, it start shipping logs to stdby2.

Now stdby2 was getting redo logs from two different primary databases (prmy and stdby1) and getting very confused .  It started reporting following errors in alert logs:

ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+DG_DATA_01/stdby2/datafile/system.278.901783129'
Managed Standby Recovery not using Real Time Apply
Recovery Slave PR00 previously exited with exception 19909
Wed Apr 13 12:02:53 2016
MRP0: Background Media Recovery process shutdown (stdby2)
Wed Apr 13 12:02:53 2016
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT

First thing I did is set log_archive_dest_state_2 and log_archive_dest_state_3 parameters to defer on stdby1.

On stdby2, I removed all the archive logs since stdby1 became primary.

ASMCMD> rm -r 2016_04_13 2016_04_12

You may delete multiple files and/or directories.

Are you sure? (y/n) y

Performed crosscheck to update RMAN catalog and controlfile. But got ORA 1580 error. To resolve this error you have to set the snapshot controlfile to temp file and set it back to original location.

RMAN> crosscheck archivelog all;
ORA-01580: error creating control backup file /oracle/product/11.2.0/dbhome_2/dbs/snapcf_prmy.f
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
RMAN-08132: WARNING: cannot update recovery area reclaimable file list

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/tmp/snapcf_prmy.f';

old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/11.2.0/dbhome_2/dbs/snapcf_prmy.f';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/tmp/snapcf_prmy.f';
new RMAN configuration parameters are successfully stored

RMAN> crosscheck archivelog all;

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/11.2.0/dbhome_2/dbs/snapcf_prmy.f';

old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/tmp/snapcf_prmy.f';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/11.2.0/dbhome_2/dbs/snapcf_prmy.f';
new RMAN configuration parameters are successfully stored

Now stdby2 doesn’t have archive logs from stdby1. So, if I start Managed Recovery Process (MRP) on stdby2, it should start requesting logs from prmy database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+DG_DATA_01/stdby2/datafile/system.278.901783129'

hmmm….got the same error as before. stdby2 database always been in mounted state, so what is the deal with incarnation. So I took a look at the incarnation.

RMAN> list incarnation of database;

List of Database Incarnations

DB Key  Inc Key  DB Name  DB ID        STATUS   Reset SCN    Reset Time
------- -------- -------- -----------  -------- ----------   ----------
476830  476831   PRMY     934143187    PARENT   1            28-january  -2014 12:14:11 pm
476830  28649947 PRMY     934143187    CURRENT  186709296840 11-april    -2016 11:23:26 am

A new incarnation was created around the same time stdby1 role was change to Primary. Oracle implicitly created a new incarnation after receiving redo log from stdby1. This is easy to fix:

RMAN> reset database to incarnation 476831;

database reset to incarnation 476831

RMAN> exit

After reseting the database to correct incarnation, MRP started successfully and stdby2 started to synchronized with prmy database.

A simple understanding of system and dataguard saved me from rebuilding dataguard instance.

Published inOracle Database 11gR2

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *