Query executed by the dataguard_gap oracle probe checkpoint

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

What is the query executed by the dataguard_gap oracle probe checkpoint and what does it monitor exactly? 

Environment

• DX UIM 23.4
• oracle probe any version

 

Cause

Guidance

Resolution

This is the query executed by the probe for dataguard_gap: 

 

/*044*/ "dataguard_gap

           SELECT /*+rule*/ 
           AD.DB_UNIQUE_NAME db_name, 
           ARCH.DEST_ID, 
           ARCH.THREAD# Thread, 
           ((PRIM.SEQUENCE#) - APPL.SEQUENCE#) gap_to_primary, 
           (ARCH.SEQUENCE# - APPL.SEQUENCE#) gap_applied, 
           ((PRIM.SEQUENCE#) - ARCH.SEQUENCE#) gap_received, 
           ((ARCH.SEQUENCE# - APPL.SEQUENCE#) + ((PRIM.SEQUENCE#) - ARCH.SEQUENCE#)) gap 
           FROM 
           ( SELECT DEST_ID, THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE STANDBY_DEST = 'YES' AND (DEST_ID, THREAD#, FIRST_TIME) IN ( SELECT AL.DEST_ID, THREAD#, MAX(FIRST_TIME) 
           FROM V$ARCHIVED_LOG AL, V$ARCHIVE_DEST AD 
           WHERE AL.RESETLOGS_CHANGE# = ( SELECT RESETLOGS_CHANGE# FROM v$database) AND 
           AL.DEST_ID = AD.DEST_ID AND 
           AD.TARGET = 'STANDBY' AND 
           AD.STATUS <> 'INACTIVE' 
           GROUP BY AL.DEST_ID, AL.THREAD#)) ARCH, 
           ( SELECT DEST_ID, THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE STANDBY_DEST = 'YES' AND 
           APPLIED = 'YES' AND (DEST_ID, THREAD#, FIRST_TIME) IN 
           ( SELECT AL.DEST_ID, THREAD#, MAX(FIRST_TIME) 
           FROM V$ARCHIVED_LOG AL, V$ARCHIVE_DEST AD 
           WHERE AL.RESETLOGS_CHANGE# = ( SELECT RESETLOGS_CHANGE# FROM v$database) AND 
           AL.APPLIED = 'YES' AND 
           AL.DEST_ID = AD.DEST_ID AND 
           AD.TARGET = 'STANDBY' AND 
           AD.STATUS <> 'INACTIVE' 
           GROUP BY AL.DEST_ID, AL.THREAD#)) APPL, 
           (SELECT THREAD#, SEQUENCE# - 1 sequence# FROM V$LOG WHERE STATUS like '%CURRENT') PRIM, 
           v$archive_dest AD 
           WHERE ARCH.THREAD# = APPL.THREAD# 
           AND PRIM.THREAD# = APPL.THREAD# 
           AND ARCH.DEST_ID = APPL.DEST_ID 
           AND APPL.DEST_ID = AD.DEST_ID 
           AND AD.TARGET = 'STANDBY' 
           AND AD.STATUS <> 'INACTIVE' 
           ORDER BY 2,3 

 

 

This query tracks archive log gaps between a primary Oracle database and its standby using Oracle Data Guard. It does so by querying V$ARCHIVED_LOG, V$ARCHIVE_DEST, V$LOG, and related views.

It monitors the following:

1. gap_to_primary = PRIM.SEQUENCE# - APPL.SEQUENCE#
   Difference between current primary redo log and the last applied archived log on the standby.
   Indicates how far the standby is behind the primary in terms of applied logs.

2. gap_applied = ARCH.SEQUENCE# - APPL.SEQUENCE#
   Difference between the last archived log and the last applied log on the standby.
   indicates what’s archived but not yet applied.

3. gap_received = PRIM.SEQUENCE# - ARCH.SEQUENCE#
   Difference between the current redo sequence on the primary and the last received (archived) sequence at standby.
   Indicates whether the logs are being shipped to the standby at all.

4. gap = gap_applied + gap_received
   Total gap from primary to applied log.

 

It effectively shows how far behind the standby is — both in terms of:

• logs received but not applied, and

• logs not yet shipped at all.