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
=====================================================================================================================

ORA-38788: More standby database recovery is needed

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

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

### Full Error

ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38788: More standby database recovery is needed

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

Error occured while turning flashback database on in 11.2.0.4.0 version of a standby database.

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

Alter database flashback on;

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

#########################################
# 1) Check Flashback Status in STandby
#########################################

Select name,DB_UNIQUE_NAME,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 FLASHBACK_ON
--------- ------------------------------ ------------------
DB01    FDB01                         NO

#### Turn on Flashback

SQL> Alter database flashback on;
Alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38788: More standby database recovery is needed

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

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

This is a new standby database and it was created using active database duplication method. During duplication at the end of recovery, errors occurred which is why we are receiving the errors.

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

#########################################
# 3) Duplication Log
#########################################

executing command: SET until clause
Starting recover at 17-MAR-16
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 03/17/2016 18:33:17
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 2227661
ORA-00283: recovery session canceled due to errorsORA-19909: datafile 1 belongs to an orphan incarnationORA-01110: data file 1: '+SHAREDDATA02/fDB01/datafile/system.453.906748087'
Recovery Manager complete.

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

#########################################
# 4) Enable Mrp and see if it helps
#########################################

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> 

#########################################
# Alert Log
#########################################

Fri Mar 18 09:57:40 2016
ALTER DATABASE RECOVER  managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (FDB011)
Fri Mar 18 09:57:40 2016
MRP0 started with pid=41, OS id=6839
MRP0: Background Managed Standby Recovery process started (FDB011)
 started logmerger process
Fri Mar 18 09:57:45 2016
Managed Standby Recovery starting Real Time Apply
Fri Mar 18 09:57:45 2016
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 2227001) is orphaned on incarnation#=1
MRP0: Detected orphaned datafiles!
Recovery will possibly be retried after flashback...
Errors in file /ofa/u001/app/oracle/product/admin/FDB01/diag/rdbms/fDB01/FDB011/trace/FDB011_pr00_6891.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+SHAREDDATA02/fDB01/datafile/system.453.906748087'
Managed Standby Recovery not using Real Time Apply
Recovery Slave PR00 previously exited with exception 19909
Completed: ALTER DATABASE RECOVER  managed standby database using current logfile disconnect from session
Fri Mar 18 09:58:06 2016
MRP0: Background Media Recovery process shutdown (FDB011)

=====================================================================================================================
If we can see from above, Datafile 1 is showing as orphan incarnation.
=====================================================================================================================

#########################################
# 5) Check Database Incarnation in Primary & Standby
#########################################

#### Primary Database

[oracle@host01 bin]$ . oraenv_DB01
The Oracle base remains unchanged with value /ofa/u001/app/oracle/product
[oracle@host01 bin]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 18 09:59:56 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB01 (DBID=2325795052)

RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB01  2325795052       CURRENT 1          02-MAR-16
RMAN>

#### Standby Database

[oracle@host02 bin]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 18 10:00:11 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB01 (DBID=2325795052, not open)

RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB01  2325795052       PARENT  1          02-MAR-16
2       2       DB01  2325795052       CURRENT 2216947    17-MAR-16
RMAN>

=====================================================================================================================
From Above output, we can see standby database is 2 incarnations. This actually happened during the time of duplication, it tries to catalog the files in SHAREDFRA02 as below,
=====================================================================================================================

searching for all files that match the pattern +SHAREDFRA02
List of Files Unknown to the Database
=====================================
File Name: +sharedfra02/snapcf_psoa03.f
File Name: +sharedfra02/FDB01/ARCHIVELOG/2016_03_17/thread_1_seq_77.2069.906748295
File Name: +sharedfra02/FDB01/ARCHIVELOG/2016_03_17/thread_3_seq_81.2070.906748295
File Name: +sharedfra02/FDB01/ARCHIVELOG/2016_03_17/thread_2_seq_75.2071.906748295
File Name: +sharedfra02/FDB01/ARCHIVELOG/2016_03_17/thread_1_seq_78.2072.906748295
File Name: +sharedfra02/FDB01/ARCHIVELOG/2016_03_17/thread_3_seq_82.2073.906748297
File Name: +sharedfra02/DRDB01/AUTOBACKUP/2016_03_17/s_906744772.2054.906744773
File Name: +sharedfra02/DRDB01/AUTOBACKUP/2016_03_15/s_906488122.826.906565615
File Name: +sharedfra02/DRDB01/FLASHBACK/log_1.1725.906565083
File Name: +sharedfra02/DRDB01/FLASHBACK/log_2.1484.906565087
File Name: +sharedfra02/DRDB01/FLASHBACK/log_3.1722.906565089
File Name: +sharedfra02/DRDB01/FLASHBACK/log_4.1720.906565093
File Name: +sharedfra02/DRDB01/FLASHBACK/log_5.2023.906733525
File Name: +sharedfra02/DRDB01/ARCHIVELOG/2016_03_17/thread_3_seq_64.1908.906681665
File Name: +sharedfra02/DRDB01/ARCHIVELOG/2016_03_17/thread_1_seq_60.1911.906681667
File Name: +sharedfra02/DRDB01/ARCHIVELOG/2016_03_17/thread_2_seq_58.1914.906681669

As it tries to catalog all the files in +SHAREDFRA02, the other database files will be skipped because it will have different DBID.

In our case, we have one more standby database in the same place called "DRDB01", which is also having same DBID which is of its primary.

So this is the reason, the datafile shows multiple incarnation as "DRDB01" database files are cataloged to "FDB01".

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

As the standby database shows multiple incarnation, reset the incarnation of the database to the old one.

Reset database to incarnation 1;

RMAN> Reset database to incarnation 1;
database reset to incarnation 1
RMAN> list incarnation of database;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB01  2325795052       CURRENT 1          02-MAR-16
2       2       DB01  2325795052       ORPHAN  2216947    17-MAR-16
RMAN>

=====================================================================================================================
After database incarnation is reset, wait for around 5 minutes to get the database catch up. Now if you turn on the flashback it works.
=====================================================================================================================

[oracle@host03 bin]$ . oraenv_FDB01
The Oracle base remains unchanged with value /ofa/u001/app/oracle/product
[oracle@host03 bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 18 10:16:05 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> Select name,DB_UNIQUE_NAME,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 FLASHBACK_ON
--------- ------------------------------ ------------------
DB01   FDB01                        NO

SQL> select distinct process from gv$managed_standby;

PROCESS
---------
ARCH
MRP0

SQL> Recover managed standby database cancel;
Media recovery complete.

SQL> Alter database flashback on;
Database altered.
SQL> Select name,DB_UNIQUE_NAME,flashback_on from v$database;
NAME      DB_UNIQUE_NAME                 FLASHBACK_ON
--------- ------------------------------ ------------------
DB01   FDB01                        YES

SQL> Recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> select distinct process from gv$managed_standby;

PROCESS
---------
ARCH
MRP0

SQL>

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