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