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.