DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, August 30, 2017

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

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

Problem :-

DGMGRL> show database STDBY

Database - STDBY

  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):
   STDBY

  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/stdby/STDBY/trace/stdby_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 2017
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 2017
 started logmerger process
Fri Jul 24 05:58:48 2017
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 2017
Setting recovery target incarnation to 5
 started logmerger process
Parallel Media Recovery started with 12 slaves
Fri Jul 24 06:06:06 2017
Media Recovery Log /u01/apps/oracle/fast_recovery_area/stdby/archivelog/2017_07_24/thread_1_seq_7.704.885880789
Incomplete Recovery applied until change 15883325 time 07/24/2017 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 2017
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 2017
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 /u01/apps/oracle/fast_recovery_area/stdby/archivelog/2017_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 - STDBY

  Protection Mode: MaxPerformance
  Databases:
    PRIME - Primary database
    STDBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS