How to Check the Archive Log Gap Between Primary and Standby Database

Hiya Buddies!!!

We would like share here "how to check the archive log gap between the primary and standby database" and also how to take action.

Please find the below steps:

$cat archive_gap.sql

set lines 180

col host_name for a25

prompt SQL>1 output

select thread#,max(sequence#) from gv$archived_log group by thread#;

prompt SQL>2 output

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

prompt SQL>3 output

select name,instance_name,host_name,status,to_char(startup_time , 'dd-mon-rr hh24:mi:ss') Start_Time from v$database,gv$instance;

prompt SQL>4 output <Main>

SELECT PROCESS, STATUS, SEQUENCE# FROM gv$MANAGED_STANDBY;

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM gv$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN

(SELECT THREAD#,MAX(FIRST_TIME) FROM gv$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM gv$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN

(SELECT THREAD#,MAX(FIRST_TIME) FROM gv$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

SQL> @archive_gap.sql

OUTPUT:

SQL>1 output

   THREAD# MAX(SEQUENCE#)

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

         2         410266

         1         579475

   THREAD# MAX(SEQUENCE#)

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

         2         410257

         1         579462

SQL>2 output

NAME      INSTANCE_NAME    HOST_NAME                 STATUS       START_TIME

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

PRD1  PRD21        ora-data                  OPEN         03-jul-18 04:04:03

PRD1  PRD22        ora-data2                  MOUNTED      03-jul-18 04:04:00

SQL>3 output

PROCESS   STATUS        SEQUENCE#

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

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CLOSING          579449

ARCH      CONNECTED             0

RFS       RECEIVING        579476

RFS       RECEIVING        579477

RFS       IDLE                  0

RFS       IDLE             410267

ARCH      CLOSING          410158

ARCH      CLOSING          410159

ARCH      CONNECTED             0

PROCESS   STATUS        SEQUENCE#

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

ARCH      CONNECTED             0

RFS       IDLE                  0

RFS       IDLE                  0

RFS       IDLE                  0

RFS       IDLE                  0

16 rows selected.

SQL>4 output

    Thread Last Sequence Received Last Sequence Applied Difference

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

         1                 579475                579462         13

         1                 579475                579462         13

         1                 579475                579462         13

         1                 579475                579462         13

         2                 410266                410258          8

         2                 410266                410258          8

         2                 410266                410258          8

         2                 410266                410258          8

8 rows selected.

SQL>

Now, As we see the above result the differences / gaps are 13 and 8 as respective nodes...

So, here we have to put database in MRP mode as shown below command.

So, archive sync will start...

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL>

SQL> @archive_gap.sql

Output:

   THREAD# MAX(SEQUENCE#)

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

         1         579507

         2         410282

   THREAD# MAX(SEQUENCE#)

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

         1         579507

         2         410281

NAME      INSTANCE_NAME    HOST_NAME                 STATUS       START_TIME

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

PRD1  PRD21        ora-data                  MOUNTED      04-jul-18 06:01:02

PRD1  PRD22        ora-data2                  MOUNTED      04-jul-18 06:00:57

SQL> SELECT PROCESS, STATUS, SEQUENCE# FROM gv$MANAGED_STANDBY

PROCESS   STATUS        SEQUENCE#

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

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

RFS       IDLE             410283

RFS       IDLE                  0

MRP0      WAIT_FOR_LOG     579508

ARCH      CLOSING          410270

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

PROCESS   STATUS        SEQUENCE#

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

RFS       IDLE                  0

RFS       IDLE                  0

RFS       IDLE                  0

RFS       RECEIVING             0

RFS       IDLE                  0

RFS       RECEIVING        579508

17 rows selected.

    Thread Last Sequence Received Last Sequence Applied Difference

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

         1                 579507                579507          0

         1                 579507                579507          0

         1                 579507                579507          0

         1                 579507                579507          0

         2                 410282                410282          0

         2                 410282                410282          0

         2                 410282                410282          0

         2                 410282                410282          0

8 rows selected.

SQL>

Hope this may useful and helpful here.

Thanks...