How to detect gaps in your Data Guard environments

In the OTN Data Guard Forum, a user was trying to understand if there's a gap between the Primary and the Standby database so he executed the following query:

select count(*) from v$archived_log where applied = 'NO'

The output of the query was higher than 3000, my response was that the fact that his query returns more than 3000  doesn't necessarily tell that there's any problem becuase when you query v$archived_log it will always report "NO" for the local archive log destinations, therefore he should check for APPLIED = 'NO' and also standby_dest = 'YES'.

Link to the Discussion:

https://community.oracle.com/thread/3781242?sr=stream

I decided to enhance the query even more so it will dipslay per each standby archive log destinaion the following information:

• INST_ID - The Instance ID, relevant for RAC with Data Guard configurations
• DEST_ID - The ID of the Archive Log Destination
• Status - Will indicate whether the standby destination status is valid or not
• Destination - The name of the Archive Log Destination
• Applied_Gap - The applied gap between the Primary and the Standby
• Received_Gap - The received gap between the Primary and the Standby
• Last_Received_Seq - Last archive log received in the standby destination
• Last_Applied_Seq - Last archive log applied in the standby destination

The following query should always be executed on the Primary database:

select ar.inst_id "inst_id",
       ar.dest_id "dest_id",
       ar.status "dest_status",
       ar.destination "destination",
         (select MAX (sequence#) highiest_seq
            from v$archived_log val, v$database vdb
           where     val.resetlogs_change# = vdb.resetlogs_change#
                 and thread# = ar.inst_id
                 and dest_id = ar.dest_id)
       - NVL (
            (select MAX (sequence#)
               from v$archived_log val, v$database vdb
              where     val.resetlogs_change# = vdb.resetlogs_change#
                    and thread# = ar.inst_id
                    and dest_id = ar.dest_id
                    and standby_dest = 'YES'
                    and applied = 'YES'),
            0)
          "applied_gap",
         (SELECT MAX (sequence#) highiest_seq
            from v$archived_log val, v$database vdb
           where     val.resetlogs_change# = vdb.resetlogs_change#
                 AND thread# = ar.inst_id)
       - NVL (
            (SELECT MAX (sequence#)
               from v$archived_log val, v$database vdb
              where     val.resetlogs_change# = vdb.resetlogs_change#
                    and thread# = ar.inst_id
                    and dest_id = ar.dest_id
                    and standby_dest = 'YES'),
            0)
          "received_gap",
       NVL (
          (SELECT MAX (sequence#)
             from v$archived_log val, v$database vdb
            where     val.resetlogs_change# = vdb.resetlogs_change#
                  and thread# = ar.inst_id
                  and dest_id = ar.dest_id
                  and standby_dest = 'YES'),
          0)
          "last_received_seq",
       NVL (
          (SELECT MAX (sequence#)
             from v$archived_log val, v$database vdb
            where     val.resetlogs_change# = vdb.resetlogs_change#
                  and thread# = ar.inst_id
                  and dest_id = ar.dest_id
                  and standby_dest = 'YES'
                  and applied = 'YES'),
          0)
          "last_applied_seq"
  from (SELECT DISTINCT dest_id,
                        inst_id,
                        status,
                        target,
                        destination,
                        error
          from sys.gv_$archive_dest
         where target = 'STANDBY' and STATUS <> 'DEFERRED') ar

Example of the SQL output:

I executed the query in one of our data guard environments.
As you can see from the example output, the sequence# of the last archive log that was sent to the standby destination is 9699 and the last one that was applied is 9698, the applied gap is 1 which is definitely OK because I'm working with a standby redo log therefore the last sequence# should always be the standby redolog that Oracle is currently writing to.

Bottom line:
As long as the applied and the received gap are lower than 2 the it means that there are no gaps in your Data Guard environment.