Soumya's Database Blog : Rolling Forward Standby Database When Archive log is missing in Primary DB

Problem statement :- standby database goes out of sync as the archive logs are missing from primary site.

Environment:

Primary:

Primary database node : primary.soumya.com

Database name : orclcdb

Primary DB UNIQUE NAME : orclcdb

Primary database instances : orclcdb

DB Version: 19.0.0

Standby:

Standby database node : standby.soumya.com

Database name : orclcdb

Standby DB UNIQUE NAME : orclstd

Standby database instances : orclcdb

DB Version: 19.0.0

Primary:

SQL>select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

Standby:

SQL>select open_mode from v$database;

OPEN_MODE

--------------------

MOUNTED

DataGuard Broker Status:

[oracle@standby 2020_04_15]$ dgmgrl sys/admin123#@orclcdb

DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Apr 15 19:47:10 2020

Version 19.3.0.0.0

Welcome to DGMGRL, type "help" for information.

Connected to "orclcdb"

Connected as SYSDBA.

DGMGRL> show configuration;

Configuration - my_dg_config

Protection Mode: MaxPerformance

Members:

orclcdb - Primary database

orclstd - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 53 seconds ago)

Now we will try to simulate a "out of sync" situation between the primary and standby database.

To achive this , on Primary database we disabled the redo log transport towards the standby database.

DGMGRL> edit database 'orclcdb' SET STATE="LOG-TRANSPORT-OFF";

Succeeded.

Now we performed a few log switch on primary database to forward the log sequence number.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

Now check the sequence number on Primary:-

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD# MAX(SEQUENCE#)

---------- --------------

1 58

On Standby :-

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)

---------- --------------

1 50

Now we will move the archives generated on primary database.

[oracle@primary 2020_04_15]$ mv *.arc /u01/

Now we enable the log apply on primary database to put the standby database out of sync.

on Primary:-

DGMGRL> edit database 'orclcdb' SET STATE=TRANSPORT-ON;

Succeeded.

DGMGRL> exit

On Standby:-

Check the mrp process status. it shows waiting for WAIT_FOR_GAP

SQL> select process,status,sequence#,thread# from v$managed_standby;

PROCESS STATUS SEQUENCE# THREAD#

--------- ------------ ---------- ----------

DGRD ALLOCATED 0 0|}

/.....

ARCH CLOSING 49 1

DGRD ALLOCATED 0 0

ARCH CLOSING 50 1

ARCH CONNECTED 0 0

ARCH CLOSING 61 1

ARCH CLOSING 62 1

ARCH CONNECTED 0 0

PROCESS STATUS SEQUENCE# THREAD#

--------- ------------ ---------- ----------

ARCH CONNECTED 0 0

PROCESS STATUS SEQUENCE# THREAD#

--------- ------------ ---------- ----------

ARCH CONNECTED 0 0

RFS IDLE 0 1

PROCESS STATUS SEQUENCE# THREAD#

--------- ------------ ---------- ----------

RFS IDLE 0 0

MRP0 WAIT_FOR_GAP 51 1

RFS IDLE 0 0

RFS IDLE 63 1

37 rows selected.

Now, Let us identify the datafiles on standby database which are out of sync with respect to primary.

On primary:-

SQL>select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh

SQL> col substr(HXFNM,1,40) for a40

SQL> /

FILE_NUM SUBSTR(HXFNM,1,40) FHSCN

---------- ---------------------------------------- --------------------

1 /u01/app/oracle/oradata/ORCLCDB/system01 3195846

3 /u01/app/oracle/oradata/ORCLCDB/sysaux01 3195846

4 /u01/app/oracle/oradata/ORCLCDB/undotbs0 3195846

5 /u01/app/oracle/oradata/ORCLCDB/pdbseed/ 2136332

6 /u01/app/oracle/oradata/ORCLCDB/pdbseed/ 2136332

7 /u01/app/oracle/oradata/ORCLCDB/users01. 3195846

8 /u01/app/oracle/oradata/ORCLCDB/pdbseed/ 2136332

9 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3195846

10 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3195846

11 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3195846

12 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3195846

11 rows selected.

on Standby :-

SQL> set lines 200

SQL> col SUBSTR(HXFNM,1,40) for a40

SQL> /

FILE_NUM SUBSTR(HXFNM,1,40) FHSCN

---------- ---------------------------------------- --------------------

1 /u01/app/oracle/oradata/orclstd/system01 3188343

3 /u01/app/oracle/oradata/orclstd/sysaux01 3188343

4 /u01/app/oracle/oradata/orclstd/undotbs0 3188343

5 /u01/app/oracle/oradata/orclstd/pdbseed/ 2136332

6 /u01/app/oracle/oradata/orclstd/pdbseed/ 2136332

7 /u01/app/oracle/oradata/orclstd/users01. 3188343

8 /u01/app/oracle/oradata/orclstd/pdbseed/ 2136332

9 /u01/app/oracle/oradata/orclstd/orclpdb1 3188343

10 /u01/app/oracle/oradata/orclstd/orclpdb1 3188343

11 /u01/app/oracle/oradata/orclstd/orclpdb1 3188343

12 /u01/app/oracle/oradata/orclstd/orclpdb1 3188343

11 rows selected.

From the above example, when comparing the SCN of the datafiles' header on the primary (orclcdb) and standby (orclstd),

we see that whereas the SCN of datafiles 5,6 and 8 match that of primary, the rest of the datafiles (1,2,3,4,7,9,10,11,12) of

the standby are lagging behind the primary database.

Note the current SCN of the physical standby database. This is required to determine, in a later step, if new data files were

added to the primary database.

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN

-----------

3200264

The RECOVER DATABASE FROM SERVICE command refreshes the standby data files and rolls them forward to the same

point-in-time as the primary.

First cancel the recovery on standby database.

On Standby:-

SQL>alter database recover managed standby database cancel;

Database altered.

Now,Connect the standby database through RMAN as "target" and issue the "recover database from service"command.

on Standby :-

[oracle@standby ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 15 21:20:28 2020

Version 19.3.0.0.0

connected to target database: ORCLCDB (DBID=2807809769, not open)

RMAN> recover database from service orclcdb noredo using compressed backupset;

Starting recover at 15-APR-20

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=75 device type=DISK

skipping datafile 5; already restored to SCN 2136332

skipping datafile 6; already restored to SCN 2136332

skipping datafile 8; already restored to SCN 2136332

channel ORA_DISK_1: starting incremental datafile backup set restore