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

Had an ODG configuration which contains 1 primary and 1 physical standby databases running with 19c on Oracle Linux 7. We moved them to Oracle Linux 8 with the forklift method.

However, because we didn’t copy the online redo logs over from old OL7 servers to the new OL8 servers, an incomplete recover was done and an open resetlogs operation was performed to open the primary database on the new sever.

Later for the standby server, data files, spfiles, control files, password files etc were copied to from old OL7 to OL8.

Dataguard broker was reporting the following error for the standby server after enabled the configuration:

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

  Database Warning(s):
    ORA-16854: apply lag could not be determined
    ORA-16856: transport lag could not be determined

And from the alert log of the standby server:

PR00 (PID:49270): MRP0: Background Media Recovery terminated with error 38760
ORA-38760: This database instance failed to turn on flashback database

For the ORA-16700 error, it means incarnation of primary and standby becomes different due to resetlogs operation after an incomplete recovery on the primary server.

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       PROD    3531317972       PARENT  1          18-JUN-18
2       2       PROD    3531317972       PARENT  79735363311 07-NOV-20
3       3       PROD    3531317972       CURRENT 88569091625 09-APR-22

To fix this, the standby database needs to be flashed back to 88569091623 (88569091625 -2) where the primary and standby database were still on the same incarnation. For example: a manual flashback of the standby:

flashback database to scn 88569091623;

The prerequisites for the flashback operation is the flashback is on for the database and flashback logs are still available.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

For 19c, a good thing is you don’t need to do a manually flashback. It will take care of it automatically.

So we just copied flashback logs over from OL7 to OL8 and started redo apply. Everything has been good since then. The alert log of standby showed the incarnation forwarding:

022-04-11T08:23:27.166054-07:00
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
2022-04-11T08:23:27.189440-07:00
Attempt to start background Managed Standby Recovery process (PROD)
Starting background process MRP0
.
.
Background Managed Standby Recovery process started (PROD)
2022-04-11T08:23:29.407917-07:00
 rfs (PID:55553): Selected LNO:7 for T-1.S-36 dbid 3531317972 branch 1101543804
.
.
2022-04-11T08:23:34.629870-07:00
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 88569097863) is orphaned on incarnation#=2
PR00 (PID:55567): MRP0: Detected orphaned datafiles!
2022-04-11T08:23:34.835188-07:00
Errors in file /opt/oracle/diag/rdbms/PROD2/PROD/trace/PROD_pr00_55567.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/dbase/system/PROD/system1.dbf'
.
.
.
2022-04-11T08:23:55.157442-07:00
MRP0 (PID:55557): Recovery coordinator performing automatic flashback of database to SCN:0x000000149f211627 (88569091623)
Flashback Restore Start
2022-04-11T08:24:07.526126-07:00
Flashback Restore Complete
Flashback Media Recovery Start
2022-04-11T08:24:07.575702-07:00
Setting recovery target incarnation to 2
.
.
2022-04-11T08:24:08.877250-07:00
Media Recovery Log /backup/PROD2/archivelog/2022_04_11/o1_mf_1_14667_k58gnh4c_.arc
2022-04-11T08:24:17.855947-07:00
Incomplete Recovery applied until change 88569091623 time 04/09/2022 08:09:30
2022-04-11T08:24:17.893128-07:00
Flashback Media Recovery Complete
.
.
2022-04-11T08:24:18.655922-07:00
Setting recovery target incarnation to 3
.
.
2022-04-11T08:24:20.086589-07:00
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 88569091624

Things to consider for this type for forklift upgrade:

do a complete recovery (copying online redo logs over) if possible.
make sure flashback is on for both primary & standby servers before the upgrade.
copy flashback logs over for the standby server if an incomplete recovery (openreset option) is done.
copy broker data files over if ODG broker is used. Or the broker configuration needs to be recreated.