暂无图片
暂无图片
3
暂无图片
暂无图片
暂无图片
首页 / 写了一个 Oracle DG 监控脚本，这下可以安心睡觉了

写了一个 Oracle DG 监控脚本，这下可以安心睡觉了

原创 三笠丶 2026-09-27
71

前言

昨晚有一套 Oracle DG 数据库一直发告警邮件：

ADG error：SCN not change，please check。

连上数据库主机看了一下同步：

SELECT process,
       thread#,
       sequence#,
       status
FROM v$managed_standby;

PROCESS   THREAD#   SEQUENCE#   STATUS
-------   -------   ---------   ------------
MRP0            1      169889   APPLYING_LOG
RFS             1      169889   IDLE

再看延迟：

SELECT name,
       value
FROM v$dataguard_stats
WHERE name IN ('transport lag','apply lag');

transport lag   +00 00:00:00
apply lag       +00 00:00:00

发现是 DG 同步是完全正常的。

检查发现服务器上原来有一个 DG 监控脚本，逻辑也比较简单：

查询 CURRENT_SCN
      ↓
等待 5 秒
      ↓
再次查询 CURRENT_SCN
      ↓
SCN 没变化
      ↓
发送 DG 异常邮件

核心 SQL 就这一句：

SELECT current_scn
FROM v$database;

实际检查时发现，两次查询的 SCN 确实一样：

86803034214
86803034214

也就是说：DG 本身完全正常，只是这几秒 SCN 没有变化。

所以用：SCN 5 秒没变化 = DG 异常 作为监控条件，比较容易产生误报。

重新写了一个 DG 监控脚本

为了后续不再误报，我重新写了一个 DG 检查脚本，主要检查内容：

Database Role
Open Mode
MRP 状态
RFS 状态
Transport Lag
Apply Lag
Received / Applied Sequence
最近的 DG Error/Fatal

其中真正用于判断异常的主要是：

MRP 是否正常运行
Transport Lag 是否超过阈值
Apply Lag 是否超过阈值
最近是否存在 DG Error/Fatal

Sequence 更多用于辅助判断，不直接拿 Received - Applied 作为告警条件。

因为开启 Real-Time Apply 后，MRP 可能已经在应用当前 Standby Redo Log，而 V$ARCHIVED_LOG 中显示的 Sequence 还慢一条，这是正常现象。

实际运行效果

在一套 Oracle 19c ADG 上执行：

./check_dg_status.sh

输出：

======================================================================
 Oracle Data Guard Monitor V4.3
======================================================================
Database          : ORCL
Oracle Version    : 19.0.0.0.0
Host / Check Time : ssthlmesdbdg01 / 2026-09-26 20:24:40
Role / Open Mode  : PHYSICAL STANDBY / READ ONLY WITH APPLY
Protection Mode   : MAXIMUM PERFORMANCE
Switchover Status : NOT ALLOWED

[Instances]
  Inst=1 Name=orcldg Host=ssthlmesdbdg01 Status=OPEN Thread=1

[Recovery]
  MRP Count=1 Inst=1 Status=APPLYING_LOG Thread=2 Seq=9415
  RFS Count=10
  RFS Inst=1 Status=IDLE Thread=1 Seq=9389
  RFS Inst=1 Status=IDLE Thread=2 Seq=9415

[Lag]
  Transport Lag : +00 00:00:00
  Apply Lag     : +00 00:00:00

[Archive by Thread]
  Thread 1: Received=9388 Applied=9388 Diff=0
            LastReceive=2026-09-26 19:55:52 LastApply=2026-09-26 19:55:52
  Thread 2: Received=9414 Applied=9414 Diff=0
            LastReceive=2026-09-26 20:06:27 LastApply=2026-09-26 20:06:27

[Recent DG Errors]
  NONE

Status            : HEALTHY
Health Basis      : MRP running; Transport Lag=+00 00:00:00; Apply Lag=+00 00:00:00; no recent DG Error/Fatal
======================================================================

目前已经上实际跑过的版本：

  1. Oracle 11.2.0.4 Physical Standby
  2. Oracle 19c Active Data Guard

同时支持多 Redo Thread，所以 RAC 主库的 Thread 1、Thread 2 会分开统计。

告警也做了简单控制

为了避免偶尔的网络抖动就发邮件，现在的规则是：

  • 正常 → 不发邮件
  • 第一次异常 → 记录
  • 连续第二次异常 → 发告警邮件
  • 一直异常 → 每 6 小时提醒一次
  • 恢复正常 → 清除异常状态，不发邮件

我最后放到 crontab 每 5 分钟检查一次：

*/5 * * * * $HOME/jobs/check_dg_status.sh >/dev/null 2>&1

正常情况下完全静默，真正出现 DG 异常才会收到邮件。

最后

这样更符合平时 DBA 手工检查 Data Guard 的思路，也能少掉不少误报。

完整脚本如下：

#!/bin/bash
# Oracle Data Guard Monitor V4.3
# Tested scenarios:
#   Oracle 11.2.0.4 Physical Standby / MOUNTED
#   Oracle 19c Active Data Guard / READ ONLY WITH APPLY
# Supports multi redo threads. RAC standby code path is retained but not field-tested.
#
# Alert policy:
#   Healthy: no mail
#   First abnormal check: record only
#   Second consecutive abnormal check: send alert
#   Persistent abnormal: reminder every 6 hours
#   Recovery: clear state silently, no recovery mail

source ~/.bash_profile
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

MAIL_TO="pc1107750981@163.com"
TRANSPORT_LAG_THRESHOLD=300
APPLY_LAG_THRESHOLD=300
FAIL_THRESHOLD=2
REMINDER_INTERVAL=21600
DG_ERROR_LOOKBACK_MIN=10

HOST=$(hostname)
CHECK_TIME=$(date '+%Y-%m-%d %H:%M:%S')
NOW_EPOCH=$(date +%s)

TMP_FILE="/tmp/dg_monitor_$$.out"
MAIL_FILE="/tmp/dg_monitor_mail_$$.out"
STATE_DIR="$HOME/jobs/.dg_monitor"
FAIL_FILE="$STATE_DIR/fail_count"
ALERT_FILE="$STATE_DIR/alert_sent"
LAST_MAIL_FILE="$STATE_DIR/last_mail_epoch"

mkdir -p "$STATE_DIR"
trap 'rm -f "$TMP_FILE" "$MAIL_FILE"' EXIT

read_int() {
    local file="$1"
    local default_value="$2"
    local value
    if [ ! -f "$file" ]; then
        echo "$default_value"
        return
    fi
    value=$(cat "$file" 2>/dev/null)
    case "$value" in
        ''|*[!0-9]*) echo "$default_value" ;;
        *) echo "$value" ;;
    esac
}

lag_to_seconds() {
    local lag="$1"
    local clean days tm hh mm ss
    if [ -z "$lag" ]; then
        echo -1
        return
    fi
    clean=$(echo "$lag" | sed 's/^+//' | xargs)
    days=$(echo "$clean" | awk '{print $1}')
    tm=$(echo "$clean" | awk '{print $2}')
    hh=$(echo "$tm" | cut -d: -f1)
    mm=$(echo "$tm" | cut -d: -f2)
    ss=$(echo "$tm" | cut -d: -f3 | cut -d. -f1)
    if [ -z "$days" ] || [ -z "$hh" ] || [ -z "$mm" ] || [ -z "$ss" ]; then
        echo -1
        return
    fi
    echo $((10#$days * 86400 + 10#$hh * 3600 + 10#$mm * 60 + 10#$ss))
}

sqlplus -s / as sysdba > "$TMP_FILE" 2>&1 <<EOF
whenever sqlerror exit 10
whenever oserror exit 11
set heading off feedback off pagesize 0 linesize 1000 trimspool on verify off echo off tab off

SELECT 'DB|'||name||'|'||database_role||'|'||open_mode||'|'||
       protection_mode||'|'||switchover_status
FROM v\$database;

SELECT 'VER|'||version
FROM v\$instance;

SELECT 'INST|'||inst_id||'|'||instance_name||'|'||host_name||'|'||
       status||'|'||thread#
FROM gv\$instance
ORDER BY inst_id;

SELECT 'MRP|'||inst_id||'|'||status||'|'||thread#||'|'||sequence#
FROM gv\$managed_standby
WHERE process='MRP0'
ORDER BY inst_id;

SELECT 'RFS|'||inst_id||'|'||status||'|'||thread#||'|'||sequence#
FROM gv\$managed_standby
WHERE process='RFS'
ORDER BY inst_id,thread#,sequence#;

SELECT 'TLAG|'||value
FROM v\$dataguard_stats
WHERE name='transport lag';

SELECT 'ALAG|'||value
FROM v\$dataguard_stats
WHERE name='apply lag';

SELECT 'ERR|'||TO_CHAR(timestamp,'YYYY-MM-DD HH24:MI:SS')||'|'||
       severity||'|'||NVL(TO_CHAR(error_code),'0')||'|'||
       REPLACE(REPLACE(REPLACE(message,'|','/'),CHR(10),' '),CHR(13),' ')
FROM v\$dataguard_status
WHERE severity IN ('Error','Fatal')
AND timestamp > SYSDATE - (${DG_ERROR_LOOKBACK_MIN}/1440)
ORDER BY timestamp;

SELECT 'REC|'||thread#||'|'||MAX(sequence#)||'|'||
       TO_CHAR(MAX(completion_time),'YYYY-MM-DD HH24:MI:SS')
FROM v\$archived_log
WHERE registrar='RFS'
AND resetlogs_change#=(SELECT resetlogs_change# FROM v\$database)
GROUP BY thread#
ORDER BY thread#;

SELECT 'APP|'||thread#||'|'||MAX(sequence#)||'|'||
       TO_CHAR(MAX(completion_time),'YYYY-MM-DD HH24:MI:SS')
FROM v\$archived_log
WHERE applied='YES'
AND resetlogs_change#=(SELECT resetlogs_change# FROM v\$database)
GROUP BY thread#
ORDER BY thread#;

exit
EOF

SQL_RC=$?

if [ "$SQL_RC" -ne 0 ]; then
    FAIL_COUNT=$(read_int "$FAIL_FILE" 0)
    FAIL_COUNT=$((FAIL_COUNT + 1))
    echo "$FAIL_COUNT" > "$FAIL_FILE"

    {
        echo "======================================================================"
        echo " Oracle Data Guard Monitor - CHECK FAILED"
        echo "======================================================================"
        echo "Host          : $HOST"
        echo "Check Time    : $CHECK_TIME"
        echo "SQLPlus RC    : $SQL_RC"
        echo "Failure Count : $FAIL_COUNT/$FAIL_THRESHOLD"
        echo
        cat "$TMP_FILE"
        echo "======================================================================"
    } > "$MAIL_FILE"

    cat "$MAIL_FILE"

    if [ "$FAIL_COUNT" -ge "$FAIL_THRESHOLD" ]; then
        LAST_MAIL=$(read_int "$LAST_MAIL_FILE" 0)
        if [ ! -f "$ALERT_FILE" ] || [ $((NOW_EPOCH - LAST_MAIL)) -ge "$REMINDER_INTERVAL" ]; then
            mailx -s "[DG CHECK FAILED] $HOST" "$MAIL_TO" < "$MAIL_FILE"
            if [ $? -eq 0 ]; then
                touch "$ALERT_FILE"
                echo "$NOW_EPOCH" > "$LAST_MAIL_FILE"
            fi
        fi
    fi
    exit 2
fi

DB_LINE=$(grep '^DB|' "$TMP_FILE" | head -1)
if [ -z "$DB_LINE" ]; then
    echo "[ERROR] Cannot obtain V\$DATABASE information."
    exit 2
fi

DB_NAME=$(echo "$DB_LINE" | cut -d'|' -f2)
DB_ROLE=$(echo "$DB_LINE" | cut -d'|' -f3)
OPEN_MODE=$(echo "$DB_LINE" | cut -d'|' -f4)
PROTECTION_MODE=$(echo "$DB_LINE" | cut -d'|' -f5)
SWITCHOVER_STATUS=$(echo "$DB_LINE" | cut -d'|' -f6)
ORACLE_VERSION=$(grep '^VER|' "$TMP_FILE" | head -1 | cut -d'|' -f2)

MRP_COUNT=$(grep -c '^MRP|' "$TMP_FILE")
if [ "$MRP_COUNT" -gt 0 ]; then
    MRP_LINE=$(grep '^MRP|' "$TMP_FILE" | head -1)
    MRP_INST=$(echo "$MRP_LINE" | cut -d'|' -f2)
    MRP_STATUS=$(echo "$MRP_LINE" | cut -d'|' -f3)
    MRP_THREAD=$(echo "$MRP_LINE" | cut -d'|' -f4)
    MRP_SEQ=$(echo "$MRP_LINE" | cut -d'|' -f5)
else
    MRP_INST="-"
    MRP_STATUS="NOT_RUNNING"
    MRP_THREAD="-"
    MRP_SEQ="-"
fi

RFS_COUNT=$(grep -c '^RFS|' "$TMP_FILE")
TRANSPORT_LAG=$(grep '^TLAG|' "$TMP_FILE" | head -1 | cut -d'|' -f2)
APPLY_LAG=$(grep '^ALAG|' "$TMP_FILE" | head -1 | cut -d'|' -f2)
TRANSPORT_SEC=$(lag_to_seconds "$TRANSPORT_LAG")
APPLY_SEC=$(lag_to_seconds "$APPLY_LAG")
DG_ERROR_COUNT=$(grep -c '^ERR|' "$TMP_FILE")

ABNORMAL=0
PROBLEM=""

add_problem() {
    ABNORMAL=1
    PROBLEM="${PROBLEM}
$1"
}

if [ "$DB_ROLE" != "PHYSICAL STANDBY" ]; then
    add_problem "[CRITICAL] Database role is $DB_ROLE; expected PHYSICAL STANDBY."
fi

case "$OPEN_MODE" in
    "MOUNTED"|"READ ONLY WITH APPLY") ;;
    *) add_problem "[CRITICAL] Unexpected standby open mode: $OPEN_MODE." ;;
esac

if [ "$MRP_COUNT" -eq 0 ]; then
    add_problem "[CRITICAL] MRP0 is NOT running."
fi

if [ "$TRANSPORT_SEC" -lt 0 ]; then
    add_problem "[WARNING] Cannot obtain Transport Lag."
elif [ "$TRANSPORT_SEC" -gt "$TRANSPORT_LAG_THRESHOLD" ]; then
    add_problem "[WARNING] Transport Lag $TRANSPORT_LAG exceeds ${TRANSPORT_LAG_THRESHOLD}s."
fi

if [ "$APPLY_SEC" -lt 0 ]; then
    add_problem "[WARNING] Cannot obtain Apply Lag."
elif [ "$APPLY_SEC" -gt "$APPLY_LAG_THRESHOLD" ]; then
    add_problem "[WARNING] Apply Lag $APPLY_LAG exceeds ${APPLY_LAG_THRESHOLD}s."
fi

if [ "$DG_ERROR_COUNT" -gt 0 ]; then
    add_problem "[CRITICAL] $DG_ERROR_COUNT Data Guard Error/Fatal message(s) detected in last ${DG_ERROR_LOOKBACK_MIN} minutes."
fi

print_report() {
    echo "======================================================================"
    echo " Oracle Data Guard Monitor V4.3"
    echo "======================================================================"
    echo "Database          : $DB_NAME"
    echo "Oracle Version    : $ORACLE_VERSION"
    echo "Host / Check Time : $HOST / $CHECK_TIME"
    echo "Role / Open Mode  : $DB_ROLE / $OPEN_MODE"
    echo "Protection Mode   : $PROTECTION_MODE"
    echo "Switchover Status : $SWITCHOVER_STATUS"

    echo
    echo "[Instances]"
    grep '^INST|' "$TMP_FILE" |
    while IFS='|' read -r TAG INST_ID INST_NAME INST_HOST INST_STATUS INST_THREAD
    do
        echo "  Inst=$INST_ID Name=$INST_NAME Host=$INST_HOST Status=$INST_STATUS Thread=$INST_THREAD"
    done

    echo
    echo "[Recovery]"
    echo "  MRP Count=$MRP_COUNT Inst=$MRP_INST Status=$MRP_STATUS Thread=$MRP_THREAD Seq=$MRP_SEQ"
    echo "  RFS Count=$RFS_COUNT"
    grep '^RFS|' "$TMP_FILE" |
    while IFS='|' read -r TAG R_INST R_STATUS R_THREAD R_SEQ
    do
        if [ "$R_THREAD" -gt 0 ] 2>/dev/null && [ "$R_SEQ" -gt 0 ] 2>/dev/null; then
            echo "  RFS Inst=$R_INST Status=$R_STATUS Thread=$R_THREAD Seq=$R_SEQ"
        fi
    done

    echo
    echo "[Lag]"
    echo "  Transport Lag : ${TRANSPORT_LAG:--}"
    echo "  Apply Lag     : ${APPLY_LAG:--}"

    echo
    echo "[Archive by Thread]"
    THREADS=$(
        {
            grep '^REC|' "$TMP_FILE" | cut -d'|' -f2
            grep '^APP|' "$TMP_FILE" | cut -d'|' -f2
        } | grep '^[0-9][0-9]*$' | sort -n -u
    )

    if [ -z "$THREADS" ]; then
        echo "  No archive thread information."
    else
        for THREAD in $THREADS
        do
            REC_LINE=$(grep "^REC|${THREAD}|" "$TMP_FILE" | tail -1)
            APP_LINE=$(grep "^APP|${THREAD}|" "$TMP_FILE" | tail -1)

            REC_SEQ=$(echo "$REC_LINE" | cut -d'|' -f3)
            REC_TIME=$(echo "$REC_LINE" | cut -d'|' -f4)
            APP_SEQ=$(echo "$APP_LINE" | cut -d'|' -f3)
            APP_TIME=$(echo "$APP_LINE" | cut -d'|' -f4)

            [ -n "$REC_SEQ" ] || REC_SEQ=0
            [ -n "$APP_SEQ" ] || APP_SEQ=0
            [ -n "$REC_TIME" ] || REC_TIME="-"
            [ -n "$APP_TIME" ] || APP_TIME="-"

            if echo "$REC_SEQ" | grep -q '^[0-9][0-9]*$' &&
               echo "$APP_SEQ" | grep -q '^[0-9][0-9]*$'; then
                ARCHIVE_DIFF=$((REC_SEQ - APP_SEQ))
            else
                ARCHIVE_DIFF="-"
            fi

            echo "  Thread $THREAD: Received=$REC_SEQ Applied=$APP_SEQ Diff=$ARCHIVE_DIFF"
            echo "            LastReceive=$REC_TIME LastApply=$APP_TIME"
        done
    fi

    echo
    echo "[Recent DG Errors]"
    if [ "$DG_ERROR_COUNT" -eq 0 ]; then
        echo "  NONE"
    else
        grep '^ERR|' "$TMP_FILE" | sed 's/^ERR|/  /'
    fi
}

if [ "$ABNORMAL" -eq 1 ]; then
    FAIL_COUNT=$(read_int "$FAIL_FILE" 0)
    FAIL_COUNT=$((FAIL_COUNT + 1))
    echo "$FAIL_COUNT" > "$FAIL_FILE"

    print_report
    echo
    echo "Status            : ABNORMAL"
    echo "Failure Count     : $FAIL_COUNT/$FAIL_THRESHOLD"
    echo "Problems:"
    printf "%b\n" "$PROBLEM"
    echo "======================================================================"

    if [ "$FAIL_COUNT" -ge "$FAIL_THRESHOLD" ]; then
        LAST_MAIL=$(read_int "$LAST_MAIL_FILE" 0)
        SEND_MAIL=0
        SUBJECT="[DG ALERT] $DB_NAME@$HOST"

        if [ ! -f "$ALERT_FILE" ]; then
            SEND_MAIL=1
        elif [ $((NOW_EPOCH - LAST_MAIL)) -ge "$REMINDER_INTERVAL" ]; then
            SEND_MAIL=1
            SUBJECT="[DG REMINDER] $DB_NAME@$HOST"
        fi

        if [ "$SEND_MAIL" -eq 1 ]; then
            {
                print_report
                echo
                echo "Status            : ABNORMAL"
                echo "Failure Count     : $FAIL_COUNT/$FAIL_THRESHOLD"
                echo "Problems:"
                printf "%b\n" "$PROBLEM"
                echo "======================================================================"
            } > "$MAIL_FILE"

            mailx -s "$SUBJECT" "$MAIL_TO" < "$MAIL_FILE"
            if [ $? -eq 0 ]; then
                touch "$ALERT_FILE"
                echo "$NOW_EPOCH" > "$LAST_MAIL_FILE"
            fi
        fi
    fi
    exit 1
fi

echo 0 > "$FAIL_FILE"
rm -f "$ALERT_FILE" "$LAST_MAIL_FILE"

print_report
echo
echo "Status            : HEALTHY"
echo "Health Basis      : MRP running; Transport Lag=${TRANSPORT_LAG:--}; Apply Lag=${APPLY_LAG:--}; no recent DG Error/Fatal"
echo "======================================================================"
exit 0

需要的话直接拿去改一下邮箱和阈值就能用。

更多数据库相关脚本，我都在放在：https://ora100.com/script-library 可自行获取！

oracledg墨力计划
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论