Oracle Data Guard Health Check

Purpose

Checks the health of an Oracle Data Guard physical standby:

1. Database role and protection mode
2. MRP0 and RFS process status
3. Transport and apply lag
4. Archive log gap detection
5. Standby redo log status

Supported Platforms and Versions

Oracle Database 12c Release 2 through 23ai. Physical standby only — logical standby monitoring differs.

Run on the standby database unless specified otherwise.

Required Permissions

DBA role or SELECT_CATALOG_ROLE on the standby database.

Risk Level

Low. Read-only queries against V$ and DBA_* views. No configuration changes.

Script

-- ============================================================
-- Oracle Data Guard Health Check
-- Run on STANDBY database (unless noted)
-- Permissions: DBA or SELECT_CATALOG_ROLE
-- Risk: Read-only
-- ============================================================

SET LINESIZE 200
SET PAGESIZE 100

-- 1. Database role and protection mode
SELECT
    name                AS db_name,
    db_unique_name,
    database_role,
    protection_mode,
    protection_level,
    switchover_status,
    open_mode
FROM v$database;

-- 2. Managed recovery and RFS processes
SELECT
    process,
    status,
    thread#,
    sequence#,
    block#,
    blocks
FROM v$managed_standby
WHERE process IN ('MRP0', 'RFS', 'ARCH')
ORDER BY process;

-- 3. Transport and apply lag
SELECT
    name,
    value,
    datum_time,
    time_computed
FROM v$dataguard_stats
WHERE name IN (
    'transport lag',
    'apply lag',
    'apply finish time',
    'estimated startup time'
)
ORDER BY name;

-- 4. Archive log gap detection
-- Returns rows only if a gap exists; empty = no gap
SELECT
    thread#,
    low_sequence#,
    high_sequence#
FROM v$archive_gap;

-- 5. Standby redo log status
SELECT
    l.thread#,
    l.sequence#,
    l.bytes / 1048576    AS size_mb,
    l.used               AS used_bytes,
    l.archived,
    l.status
FROM v$standby_log l
ORDER BY l.thread#, l.sequence#;

-- 6. Archive destination status (run on PRIMARY or STANDBY with access)
SELECT
    dest_id,
    status,
    target,
    archiver,
    schedule,
    destination,
    error
FROM v$archive_dest
WHERE status != 'INACTIVE'
ORDER BY dest_id;

-- 7. Recent standby alert log errors (requires access to alert log via ADRCI or V$DIAG_INFO)
-- Use ADRCI on the OS to review recent errors:
-- adrci> show alert -tail 100

Interpreting Results

Safe Execution Guidance

Run in SQL*Plus or SQLcl. Safe at any time. Schedule as a monitoring job every 5 minutes and alert on:

• MRP0 absent or not in APPLYING_LOG state
• Transport lag exceeding threshold
• Any rows in v$archive_gap

Rollback Steps

Not applicable — read-only script.