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

 https://subhanuddinkhadri.blogspot.com/2015/10/flashback-standby-after-resetlogs-on.html

http://anuj-singh.blogspot.com/2018/06/oracle-flashback-for-standby-database.html

Error code: ORA-16700
Description: the standby database has diverged from the primary database
Cause: The primary database may have been flashed back or restored from a backup set and then reopened with the RESETLOGS option.
Action: Re-create the standby database from the primary database or flash back the standby database to the same point the primary database had been flashed back to.

The primary database may has been flashed back or restored from a backup set and then re-opened with the RESETLOGS option.

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.

This error will appear in the alertlog and DGMGRL> show database  mtproddr

How to deal with ORA-16700:

Reinstantiate the standby database from the primary database.

Flashback the Standby database

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.

Simulate  and resolve ORA-16700

On Primary

RMAN> backup database;

pri> create restore point before_update;

PRI>select salary from hr.employees where employee_id=100;

PRI> update hr.employees set salary = 1000 where employee_id = 100;

commit;

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

-- On primary Restore from backup and recover till before update
PRI>shutdown immediate;

startup mount;

$ rman target sys/oracle@orcl

RMAN> restore database;

RMAN> recover database until restore point before_update;

RMAN> alter database open resetlogs;

-- check that recovery was successful i.e. salary on primary database is the same as before update

PRI> select salary from hr.employees where employee_id=100;

-- check configuration --
Note that there is error as standby database has diverged from primary

DGMGRL> show configuration;

Configuration - orcl_stdy
  Protection Mode: MaxPerformance
  Databases:
    orcl - Primary database
    stdy - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

DGMGRL> show database stdy;

Database - stdy
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   (unknown)
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    stdy

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

Database Status:
ERROR

-- check resetlogs scn from primary database

PRI>select RESETLOGS_CHANGE# from v$database;

RESETLOGS_CHANGE#
-----------------
           880352

-- check current scn# from standby database

-- note that standby database is ahead of reset logs change# and hence needs to be flashed back

SBY>select current_scn from v$database;

CURRENT_SCN
-----------
     883003

--Flashback standby database to 2 SCN earlier than resetlogs_scn--
i.e. 880352 -2 = 880350

SBY>shut immediate;

startup mount;

flashback database to scn 970350 ;

alter database open;

-- check that standby database is ready to receive and apply logs from primary;

DGMGRL> show configuration;

-- check that salary on standby database is the same as before update

SBY>select salary from hr.employees where employee_id=100;

DGMGRL> show configuration;

Configuration - orcl_stdy
  Protection Mode: MaxPerformance
  Databases:
    orcl - Primary database
    stdy - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Using Flashback standby after resetlogs on primary.

• Flashback Database provides the following in a Data Guard configuration:
– An alternative to restoring and recovering the primary database
– A way to reinstate the primary database that was disabled as part of a failover to any standby database operation
– An alternative to delaying the application of redo to protect against user errors or logical corruptions
 

• Flashback Database is used by the following features in a Data Guard configuration:
– Fast-start failover
– Snapshot standby

Flashback Database provides the following advantages in a Data Guard configuration:

• Provides an alternative to delaying the application of redo to protect against user errors or logical corruptions. By using Flashback Database in this context, standby databases are more closely synchronized with the primary database, thereby reducing failover and switchover times.
• Eliminates the need to completely re-create the original primary database after a failover. The failed primary database can be flashed back to a point in time before the failover and converted to be a standby database for the new primary database.
 

Flashback Database is used in a Data Guard configuration for the following features:

 
• Fast-start failover: You must enable Flashback Database and set up a fast recovery area on the primary database and the target standby database before enabling fast-start failover. 

• Snapshot standby: To convert a physical standby database to a snapshot standby database, you must configure the Fast Recovery Area and size. If Flashback Database is not enabled, it will be enabled when the primary database is converted to a snapshot standby database.