How to find Archivelog gap in oracle standby

To find Archivelog gap in standby 


I am mentioning 2 ways to find gap:

1.
simplest way to find archivelog gap is using below single commands from primary database itself.
you can use anyone of the below queries as per your wish.

Query 1:

set lines 200
col DESTINATION for a30
col ERROR for a50
select DESTINATION,TYPE,ARCHIVED_THREAD#,APPLIED_SEQ#,ARCHIVED_SEQ#,GAP_STATUS,error from v$archive_dest_status where DEST_ID=2;

Sample output:

DESTINATION                    TYPE             ARCHIVED_THREAD# APPLIED_SEQ# ARCHIVED_SEQ# GAP_STATUS               ERROR
------------------------------ ---------------- ---------------- ------------ ------------- ------------------------ --------------------------------------------------
ORCL                           PHYSICAL                        1        29711         29712 NO GAP

Query 2:
select(select name from v$database) name,
(select max(sequence#) from v$archived_log where dest_id=1
) current_primary_seq,
(select max(sequence#)
from v$archived_log
where trunc(next_time)> sysdate-1
and dest_id=2
) max_stby,
(select nvl(
(select max(sequence#)-min(sequence#)
from v$archived_log
where trunc(next_time)> sysdate-1
and dest_id=2
and applied='NO'
),0)
from dual
) "To be Applied",
((select max(sequence#) from v$archived_log where dest_id=1)-
(select max(sequence#) from v$archived_log where dest_id=2)) "To be shipped"
from dual;

Sample output:

NAME      CURRENT_PRIMARY_SEQ   MAX_STBY To be Applied To be shipped
--------- ------------------- ---------- ------------- -------------
ORCL                    29713      29713             0             0

2:
In Primary:

To find database status and max log sequence for each thread.
select name,database_role,open_mode from v$database;
select thread#,max(sequence#) from gv$archived_log group by thread#;

To find if there is any error for the standby apply, here ERROR colomun should be null value.
set lines 200
col dest_name for a40
col destination for a30
select dest_id,dest_name,target,destination,status,error from v$archive_dest where dest_id=2;



In Syandby side:

To find database status;
select name,database_role,open_mode from v$database;

To find the received sequence and applied sequence.

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
          FROM
         (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
         (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
         WHERE
         ARCH.THREAD# = APPL.THREAD#
          ORDER BY 1;

Remember, difference value is 0 it does not mean that standby is in sync.
Received sequence and max sequence in primary(which is taken above) should be same to confirm archives are reaching to standby from primary. if the received sequence in above query is less than max sequence in primary then there is gap in receiving archives from primary, need to find the reason according to error in alert log in primary.
If there is difference between received seq and applied seq , Then there is apply gap in standby, then we need to check standby is in recovery more or any other issue in standby based on alert log in standby.

To Check MRP is running in database level.

select process,status from v$managed_standby;

Hope it will help