Tuesday, April 18, 2017

ORA-19909: datafile 1 belongs to an orphan incarnation while performing active database duplication to create new standby database


##########################
## Error
##########################

ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+SHAREDDATA04/drsid01/datafile/system.446.939940085'

### Full Error

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/18/2017 16:54:22
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
 standby start until change 5990833
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+SHAREDDATA04/drsid01/datafile/system.446.939940085'

##########################
#  Error Occurred
##########################

Error occured while creating a new standby database using active database duplication in 11.2.0.4 version

##########################
## Command Executed
##########################

run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
set cluster_database='false'
set db_unique_name='fsid01'
set db_create_online_log_dest_1='+SHAREDREDO01'
set db_create_online_log_dest_2='+SHAREDREDO02'
set db_create_online_log_dest_3='+SHAREDREDO03'
set db_create_file_dest='+SHAREDDATA04'
set db_recovery_file_dest='+SHAREDFRA04'
set control_files='+SHAREDDATA04/fsid01/standby_fsid01.ctl'
dorecover nofilenamecheck;
}

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Snippet of database logs
#########################################

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
Using previous duplicated file +SHAREDDATA04/drsid01/datafile/system.446.939940085 for datafile 1 with checkpoint SCN of 5984647
Using previous duplicated file +SHAREDDATA04/drsid01/datafile/sysaux.447.939940151 for datafile 2 with checkpoint SCN of 5984647
Using previous duplicated file +SHAREDDATA04/drsid01/datafile/undotbs1.386.939923955 for datafile 3 with checkpoint SCN of 5984647
...
...
contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for datafile  1 to
 "+SHAREDDATA04/drsid01/datafile/system.446.939940085";
   set newname for datafile  2 to
 "+SHAREDDATA04/drsid01/datafile/sysaux.447.939940151";
...
...
contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "+SHAREDFRA04/psid01/archivelog/2017_04_18/thread_1_seq_225.271.941647725" auxiliary format
 "+SHAREDFRA04"   archivelog like
 "+SHAREDFRA04/psid01/archivelog/2017_04_18/thread_3_seq_207.1182.941644927" auxiliary format
 "+SHAREDFRA04"   archivelog like
 "+SHAREDFRA04/psid01/archivelog/2017_04_18/thread_3_seq_208.1206.941647725" auxiliary format
 "+SHAREDFRA04"   archivelog like
 "+SHAREDFRA04/psid01/archivelog/2017_04_18/thread_1_seq_224.274.941644927" auxiliary format
 "+SHAREDFRA04"   archivelog like
 "+SHAREDFRA04/psid01/archivelog/2017_04_18/thread_2_seq_217.275.941647727" auxiliary format
 "+SHAREDFRA04"   ;
   catalog clone start with  "+SHAREDFRA04";
   catalog clone datafilecopy  "+SHAREDDATA04/drsid01/datafile/system.446.939940085",
 "+SHAREDDATA04/drsid01/datafile/sysaux.447.939940151",
 "+SHAREDDATA04/drsid01/datafile/undotbs1.386.939923955",
 "+SHAREDDATA04/drsid01/datafile/admin_data_ts01.455.939940317",
...
...
datafile 74 switched to datafile copy
input datafile copy RECID=76 STAMP=941648028 file name=+SHAREDDATA04/drsid01/datafile/undotbs3.390.939925417

contents of Memory Script:
{
   set until scn  5990833;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 18-APR-17

starting media recovery
media recovery failed
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/18/2017 16:54:22
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
 standby start until change 5990833
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+SHAREDDATA04/drsid01/datafile/system.446.939940085'

Recovery Manager complete.


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

#########################################
# 2) Reason for Failure
#########################################

The database we are trying to duplicate is fsid01. But from the above logs we can see that its using another database called "drsid01" which is the another standby database for this same primary.

sql statement: alter database mount standby database
Using previous duplicated file +SHAREDDATA04/drsid01/datafile/system.446.939940085 for datafile 1 with checkpoint SCN of 5984647
Using previous duplicated file +SHAREDDATA04/drsid01/datafile/sysaux.447.939940151 for datafile 2 with checkpoint SCN of 5984647

And all the datafiles are referenced to drsid01 instead of fsid01 database.

A duplicate was performed before/previously for drsid01 database, and now duplicate command is using the Datafile(s) which now are being reused, instead of restoring the datafile FROM primary database(psid01)

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

#########################################
# 3) How RMAN Duplicate Works?
#########################################

RMAN automatically optimizes a DUPLICATE command that is a repeat of a previously failed DUPLICATE command. The repeat DUPLICATE command notices which data files were successfully copied earlier and does not copy them again. This applies to all forms of duplication, whether they are backup-based (with or without a target connection) or active database duplication. The automatic optimization of the DUPLICATE command can be especially useful when a failure occurs during the duplication of very large databases.

If you do not want RMAN to automatically recover from a failed DUPLICATE operation, specify the keyword NORESUME to disable the functionality. Using the keyword NORESUME in the first invocation of DUPLICATE prevents a subsequent DUPLICATE command for the new database from using this automatic optimization.

Furthermore, using NORESUME in the first invocation of duplicate will prevent that a subsequent duplicate (in case of failure) uses the functionality. Here after using NORESUME, RMAN restored datafile 3 from the latest backup being used thus RMAN did not look for old archivelog.

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

##########################
## Solution
##########################

#### Modified the duplicate command and added NORESUME.

SQL> !more psid01_to_fsid01_dup.rcv
connect target sys/****@psid01;
connect auxiliary sys/****@dup;
spool log to 'rman_dup_fsid01.log';
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
set cluster_database='false'
set db_unique_name='fsid01'
set db_create_online_log_dest_1='+SHAREDREDO01'
set db_create_online_log_dest_2='+SHAREDREDO02'
set db_create_online_log_dest_3='+SHAREDREDO03'
set db_create_file_dest='+SHAREDDATA04'
set db_recovery_file_dest='+SHAREDFRA04'
set control_files='+SHAREDDATA04/fsid01/standby_fsid01.ctl'
dorecover nofilenamecheck noresume;
}


=====================================================================================================================
Now the duplication is working fine.
=====================================================================================================================

contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
...
...
executing command: SET NEWNAME
Starting backup at 18-APR-17
channel prmy1: starting datafile copy
input datafile file number=00003 name=+SHAREDDATA04/psid01/datafile/undotbs1.335.939312685
channel prmy2: starting datafile copy
input datafile file number=00011 name=+SHAREDDATA04/psid01/datafile/undotbs2.344.939312711
channel prmy3: starting datafile copy
input datafile file number=00012 name=+SHAREDDATA04/psid01/datafile/undotbs3.345.939312715
channel prmy4: starting datafile copy
input datafile file number=00072 name=+SHAREDDATA04/psid01/datafile/undotbs1.336.939742283
~

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================