About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Friday, July 24, 2015

ORA-16700: the standby database has diverged from the primary database

Yesterday I faced below error in my standby database :-

Problem :-

DGMGRL> show database amit_dr

Database - amit_dr

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   (unknown)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      148.00 KByte/s
  Real Time Query: OFF
  Instance(s):
   amit_dr

  Database Error(s):
    ORA-16700: the standby database has diverged from the primary database
    ORA-16766: Redo Apply is stopped

Database Status:
ERROR

Cause :- 

This happened because when due to an incomplete recovery in primary database, it has to be opened using resetlogs option and incarnation of primary and standby becomes different due to resetlogs option.

we will get below error in our standby database alert logfile

MRP0: Incarnation has changed! Retry recovery...
Errors in file /opt/oracle/diag/rdbms/amit/amit1/trace/amit_pr00_17847.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Fri Jul 24 05:58:48 2015
Archived Log entry 113 added for thread 1 sequence 1 ID 0xf0d327b6 dest 1:
Recovered data files to a consistent state at change 15883769
Fri Jul 24 05:58:48 2015
 started logmerger process
Fri Jul 24 05:58:48 2015
Managed Standby Recovery starting Real Time Apply
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 15883769) is orphaned on incarnation#=5
MRP0: Detected orphaned datafiles!

Solution :-

We do not have to rebuild our standby, just move standby database using flashback to a previous SCN value when the incarnation of primary and standby was same. Start the MRP manually or bounce the configuration in DG broker.

SQL> flashback database to scn 15883324;

Flashback complete.

Details from alert log on standby :-

flashback database to scn 15883324
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Fri Jul 24 06:06:06 2015
Setting recovery target incarnation to 5
 started logmerger process
Parallel Media Recovery started with 12 slaves
Fri Jul 24 06:06:06 2015
Media Recovery Log +AMIT_DG/amit/archivelog/2015_07_24/thread_1_seq_7.704.885880789
Incomplete Recovery applied until change 15883325 time 07/24/2015 05:52:43
Flashback Media Recovery Complete
Setting recovery target incarnation to 6
Completed: flashback database to scn 15883324

Attempt to start background Managed Standby Recovery process (dr3fia)
Fri Jul 24 06:10:35 2015
MRP0 started with pid=49, OS id=14805
MRP0: Background Managed Standby Recovery process started (dr3fia)
 started logmerger process
Fri Jul 24 06:10:40 2015
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 12 slaves
Media Recovery start incarnation depth : 1, target inc# : 6, irscn : 15883360
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +AMIT_DG/amit/archivelog/2015_07_24/thread_1_seq_7.704.885880789
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

DGMGRL> show configuration

Configuration - amit_dr

  Protection Mode: MaxPerformance
  Databases:
    amit  - Primary database
    amit_dr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

I hope this article helped you.

Thanks
Amit Rath

2 comments:

  1. Replies
    1. Hi Suy,

      Thanks for writing in , SCN= 15883324 is a previous SCN value when the incarnation of primary and standby was same.

      We can check in alert log for the timestamp when primary and standby are in sync condition and then can have the scn value from the timestamp and do the above mentioned process.

      Hoping I resolved your query.

      Thanks
      Amit Rath

      Delete