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