Oracle RAC与 ADG巡检脚本分析
Oracle RAC 与 ADG 的巡检建议按四层依次进行：集群层 → ASM 存储层 → 数据库实例层 → Data Guard 同步层。如果 RAC 主库和 RAC 备库都需要巡检，就应在主库和备库分别执行一遍。
一、集群层（Grid 用户）
# 1. 集群守护进程状态（CRS/CSS/OHASD）
crsctl check crs
# 2. 所有集群资源状态，重点看 ONLINE/OFFLINE/UNKNOWN
crsctl stat res -t
crsctl stat res -t -init
# 3. 节点成员与角色
olsnodes -n -s -t
# 4. 私网（心跳）网卡配置，确认 interconnect 绑定正确
oifcfg getif
# 5. 表决盘与 OCR
crsctl query css votedisk
ocrcheck
# 6. 数据库/服务/监听资源
srvctl status database -d <db_unique_name> -v
srvctl status service -d <db_unique_name>
srvctl config database -d <db_unique_name>
srvctl status asm -n <节点名>
srvctl status listener
lsnrctl status
二、ASM 存储层（+ASM 实例）
asmcmd lsdg
asmcmd lsdsk --statistics
-- 磁盘组容量与冗余余量（ASM 实例执行）
SELECT name, type, state, total_mb, free_mb,
required_mirror_free_mb, usable_file_mb, offline_disks
FROM v$asm_diskgroup_stat
ORDER BY name;
-- 磁盘挂载/成员状态，检查是否有 MISSING/FORCING/OFFLINE 盘
SELECT group_number, disk_number, name, path,
mount_status, header_status, mode_status, state
FROM v$asm_disk
ORDER BY group_number, disk_number;
三、RAC 实例层（任一实例执行）
-- 1. 各实例状态、角色、启动时间
SELECT inst_id, instance_number, instance_name, host_name, version,
status, database_status, active_state, startup_time, thread#
FROM gv$instance
ORDER BY inst_id;
-- 2. 集群私网互连地址
SELECT inst_id, name, ip_address, is_public, source
FROM gv$cluster_interconnects
ORDER BY inst_id;
-- 3. 服务在节点间的分布（防止单节点承载全部连接）
SELECT inst_id, name, network_name, goal, clb_goal
FROM gv$active_services
ORDER BY inst_id, name;
-- 4. 在线日志与线程对应关系（RAC 每个实例一个线程）
SELECT group#, thread#, sequence#, bytes, status, archived
FROM v$log
ORDER BY thread#, group#;
-- 5. 会话与等待概况（按实例汇总）
SELECT inst_id, COUNT(*) AS session_cnt,
SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active_cnt
FROM gv$session
GROUP BY inst_id
ORDER BY inst_id;
四、ADG 同步层（核心，必须在备库执行）
1. 数据库角色与保护模式
SELECT db_unique_name, database_role, protection_mode, open_mode,
switchover_status, force_logging, flashback_on, current_scn
FROM v$database;
期望：备库
DATABASE_ROLE = PHYSICAL STANDBY、OPEN_MODE = READ ONLY WITH APPLY；PROTECTION_MODE与规划一致。
2. 传输延迟与应用延迟
SELECT name, value, unit, time_computed, datum_time
FROM v$dataguard_stats
WHERE LOWER(name) IN ('apply lag', 'transport lag')
ORDER BY name;
注意：在主库执行该视图返回空集，不能据此判断同步正常。
value是字符串，必须连同unit一起读；连续两次采样datum_time不前进，说明没有收到新日志，此时延迟显示很小也不能视为正常。
3. 归档目标状态（11gR2 及以上）
SELECT dest_id, status, type, database_mode, recovery_mode, destination,
error, gap_status, archived_thread#, archived_seq#,
applied_thread#, applied_seq#
FROM v$archive_dest_status
WHERE status <> 'INACTIVE'
ORDER BY dest_id;
期望：
status = VALID、error为空、gap_status = VALID。
4. 归档日志缺口
SELECT thread#, low_sequence#, high_sequence#
FROM v$archive_gap
ORDER BY thread#;
返回空表示当前没有阻断恢复的缺口；但空结果不等于全程没有丢过日志。
5. Data Guard 进程（RAC 备库按实例看）
SELECT inst_id, name, pid, type, role, action, client_role, client_pid,
thread#, sequence#, block#, block_count, delay_mins, dest_id
FROM gv$managed_standby
ORDER BY inst_id, name;
关键看
MRP0是否为APPLYING_LOG。RAC 备库若启用多实例 Redo Apply，MRP 会分布在多个实例；单实例模式则只在一个实例上有 MRP0。
6. 备库恢复/应用进度
SELECT start_time, type, item, units, sofar, total, timestamp
FROM v$recovery_progress
WHERE type = 'MEDIA RECOVERY'
ORDER BY start_time DESC, item;
非协调实例在 Multi-Instance Redo Apply 下可能无行；
sofar/total含义随item变化，不要统一换算百分比。
7. 备用日志组（Standby Redo Log）
SELECT group#, thread#, sequence#, bytes, used, archived, status
FROM v$standby_log
ORDER BY thread#, group#;
容量与在线日志一致，且每个线程的 SRL 组数 ≥ 该线程在线日志组数 + 1；
status不应长期为UNASSIGNED。
8. 同步位点对比（主备分别执行）
-- 主库：各线程当前最大序列
SELECT thread#, MAX(sequence#) AS last_seq
FROM v$log
GROUP BY thread#
ORDER BY thread#;
-- 备库：各线程已应用到的最大序列
SELECT thread#, MAX(sequence#) AS applied_seq
FROM v$archived_log
WHERE applied = 'YES'
GROUP BY thread#
ORDER BY thread#;
两侧差值持续增大，说明应用跟不上或传输中断。
9. 最近 24 小时 Data Guard 告警
SELECT *
FROM (
SELECT timestamp, facility, severity, dest_id, message_num, error_code, message
FROM v$dataguard_status
WHERE severity IN ('Warning', 'Error', 'Fatal')
AND timestamp >= SYSDATE - 1
ORDER BY timestamp DESC, message_num DESC
) WHERE ROWNUM <= 100;
10. Broker 配置巡检（若使用 Broker 管理）
dgmgrl sys/<密码>@<主库TNS> "show configuration verbose"
dgmgrl sys/<密码>@<主库TNS> "show database verbose <db_unique_name>"
dgmgrl sys/<密码>@<主库TNS> "validate database <db_unique_name>"
重点看
Configuration Status、各库Status、Transport Lag、Apply Lag、Protection Mode，以及validate是否报错。
五、主机与日志层
# 资源使用
top -b -n1 | head -20
vmstat 1 5
iostat -x 1 5
free -g
df -h
# 进程检查（备库看 MRP，主库看 LNS/ARCn）
ps -ef | grep -iE "mrp|lns|arc"
# 告警日志
adrci -exec "show alert -tail 200"
六、结果判定要点
| 检查项 | 正常判定 |
|---|---|
| 集群资源 | 所有资源 ONLINE，无 UNKNOWN/INTERMEDIATE |
| ASM | state = MOUNTED，offline_disks = 0，usable_file_mb 有合理余量 |
| 实例 | 所有节点 status = OPEN，database_status = ACTIVE |
| 备库角色 | PHYSICAL STANDBY，READ ONLY WITH APPLY |
| 传输/应用延迟 | 接近 0，且 datum_time 持续推进 |
| 归档目标 | VALID、无 error、gap_status = VALID |
| 日志缺口 | v$archive_gap 为空 |
| MRP 进程 | MRP0 处于 APPLYING_LOG |
| SRL | 数量满足“每线程 ≥ 在线组数 + 1”，无长期 UNASSIGNED |
下面是一份可直接保存执行的 Oracle RAC + ADG 一键巡检脚本（Bash + SQL*Plus）。脚本只读，不改动任何配置；默认以 root 运行时可自动切换到 grid / oracle 用户执行对应命令，也可分别以对应用户运行。
使用方式
chmod +x rac_adg_inspect.sh
./rac_adg_inspect.sh -d <db_unique_name> [-s <ORACLE_SID>] [-o <输出目录>]
# 主库、备库各跑一次；Broker 巡检需额外提供连接串：
DG_BROKER_CONNECT='sys/<密码>@<主库TNS>' ./rac_adg_inspect.sh -d <db_unique_name>
脚本内容
#!/usr/bin/env bash
# =============================================================================
# rac_adg_inspect.sh — Oracle RAC + ADG 一键巡检（只读）
# 自动探测：DB_UNIQUE_NAME / 本地 ORACLE_SID / ORACLE_HOME / 集群节点 / RAC 实例
# 适用：Oracle 11gR2 / 12c / 18c / 19c / 21c+
# 用法：./rac_adg_inspect.sh [-d <db_unique_name>] [-s <ORACLE_SID>] [-o <目录>] [-n]
# -d DB_UNIQUE_NAME，可选，未指定时自动探测
# -s 本地 ORACLE_SID，可选，未指定时自动探测
# -o 输出目录，默认 /tmp/rac_adg_inspect_<时间戳>
# -n 不尝试 SSH 到其他节点（默认尝试）
# 环境变量：DG_BROKER_CONNECT='sys/<密码>@<主库TNS>'
# =============================================================================
set -uo pipefail
DB_UNIQUE_NAME=""
ORACLE_SID="${ORACLE_SID:-}"
ORACLE_HOME="${ORACLE_HOME:-}"
OUT_DIR=""
NO_SSH=0
GRID_USER="grid"
DB_USER="oracle"
usage() {
cat <<EOF
用法: $0 [-d <db_unique_name>] [-s <ORACLE_SID>] [-o <输出目录>] [-n]
-d DB_UNIQUE_NAME，可选，自动探测
-s 本地 ORACLE_SID，可选，自动探测
-o 输出目录，默认 /tmp/rac_adg_inspect_<时间戳>
-n 不尝试 SSH 到其他节点
-h 显示帮助
可选环境变量:
DG_BROKER_CONNECT Data Guard Broker 连接串，如 'sys/pass@ORCL_DG'
EOF
exit 0
}
while [ $# -gt 0 ]; do
case "$1" in
-d) DB_UNIQUE_NAME="${2:-}"; shift 2 ;;
-s) ORACLE_SID="${2:-}"; shift 2 ;;
-o) OUT_DIR="${2:-}"; shift 2 ;;
-n) NO_SSH=1; shift ;;
-h|--help) usage ;;
*) echo "未知参数: $1"; usage ;;
esac
done
TS="$(date +%Y%m%d_%H%M%S)"
OUT_DIR="${OUT_DIR:-/tmp/rac_adg_inspect_${TS}}"
mkdir -p "$OUT_DIR/sql" || exit 1
chmod 755 "$OUT_DIR" "$OUT_DIR/sql"
LOG="$OUT_DIR/inspect.log"
: > "$LOG"
chmod 644 "$LOG"
CUR_USER="$(id -un)"
log() { echo "$*" | tee -a "$LOG"; }
sec() { log ""; log "==================== $* ===================="; }
is_root() { [ "$(id -u)" -eq 0 ]; }
have_user() { id "$1" >/dev/null 2>&1; }
is_local_node() {
local n="$1" h
h="$(hostname)"
[ "$n" = "$h" ] && return 0
[ "${n%%.*}" = "${h%%.*}" ] && return 0
return 1
}
# 以指定用户执行命令；非 root 且用户不匹配时输出到 stderr 并返回失败
run_as() {
local u="$1" c="$2"
if [ "$CUR_USER" = "$u" ]; then
bash -lc "$c"
return
fi
if is_root && have_user "$u"; then
su - "$u" -c "$c"
return
fi
echo "[SKIP] 需要 ${u} 用户权限执行: ${c}" >&2
return 1
}
run_grid() { run_as "$GRID_USER" "$1"; }
run_oracle() { run_as "$DB_USER" "$1"; }
run_db() {
local env="export ORACLE_SID=${ORACLE_SID};"
[ -n "${ORACLE_HOME:-}" ] && env="${env} export ORACLE_HOME=${ORACLE_HOME};"
run_oracle "${env} sqlplus -S -L / as sysdba @$1"
}
# 单条 SQL 查询，返回纯文本结果
db_query() {
local f="$OUT_DIR/sql/_q_$.sql"
printf "SET HEADING OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 400 TRIMSPOOL ON\n%s\nEXIT\n" "$1" > "$f"
chmod 644 "$f"
run_db "$f" 2>/dev/null
rm -f "$f"
}
# ---------------------------------------------------------------------------
# 探测函数
# ---------------------------------------------------------------------------
detect_local_sid() {
ps -ef 2>/dev/null | grep '[o]ra_pmon_' | awk '{print $NF}' \
| sed 's/^ora_pmon_//' | grep -v '^+ASM' | head -1
}
detect_oracle_home_by_sid() {
local sid="$1" h=""
if [ -n "$sid" ] && [ -r /etc/oratab ]; then
h="$(awk -F: -v s="$sid" '$1==s {print $2; exit}' /etc/oratab)"
fi
[ -z "$h" ] && h="${ORACLE_HOME:-}"
printf '%s' "$h"
}
detect_db_unique_name() {
local list cnt sid du
list="$(run_grid "srvctl config database" 2>/dev/null | sed 's/[[:space:]]*$//' | sed '/^$/d')"
cnt="$(printf '%s\n' "$list" | grep -c .)"
if [ "$cnt" -eq 1 ]; then
printf '%s' "$list"
return
fi
sid="$(detect_local_sid)"
if [ -n "$sid" ]; then
ORACLE_SID="$sid"
du="$(db_query "SELECT db_unique_name FROM v\$database;" | tr -d '[:space:]')"
[ -n "$du" ] && { printf '%s' "$du"; return; }
fi
printf '%s' ""
}
detect_nodes() {
local out
out="$(run_grid "olsnodes -n -s" 2>/dev/null | awk '{print $1}' | sed '/^$/d')"
[ -z "$out" ] && out="$(run_grid "olsnodes" 2>/dev/null | sed '/^$/d')"
if [ -z "$out" ]; then
out="$(db_query "SELECT DISTINCT host_name FROM gv\$instance ORDER BY 1;" | sed '/^$/d')"
fi
[ -z "$out" ] && out="$(hostname -s)"
printf '%s\n' "$out"
}
detect_instances() {
local out
out="$(run_grid "srvctl config database -d ${DB_UNIQUE_NAME} -a" 2>/dev/null \
| awk -F': *' 'tolower($1) ~ /database instance/ {print $2; exit}')"
if [ -n "$out" ]; then
printf '%s\n' "$out" | tr ',' '\n' | tr -d '[:space:]' | sed '/^$/d'
return
fi
db_query "SELECT instance_name FROM gv\$instance ORDER BY inst_id;" | sed '/^$/d'
}
detect_instance_map() {
db_query "SELECT instance_name||'|'||host_name||'|'||thread#||'|'||status FROM gv\$instance ORDER BY inst_id;" | sed '/^$/d'
}
inst_on_node() {
local n="$1"
printf '%s\n' "$INST_MAP" | awk -F'|' -v n="$n" '
{ h=$2; sub(/\..*/,"",h); if (h==n || $2==n) {print $1; exit} }'
}
# ---------------------------------------------------------------------------
# 初始化探测
# ---------------------------------------------------------------------------
sec "0. 环境探测"
[ -z "$ORACLE_SID" ] && ORACLE_SID="$(detect_local_sid)"
ORACLE_HOME="$(detect_oracle_home_by_sid "$ORACLE_SID")"
if [ -z "$DB_UNIQUE_NAME" ]; then
DB_UNIQUE_NAME="$(detect_db_unique_name)"
fi
if [ -z "$DB_UNIQUE_NAME" ]; then
log "错误：无法自动探测 DB_UNIQUE_NAME。"
log "候选数据库：$(run_grid 'srvctl config database' 2>/dev/null | tr '\n' ' ')"
log "请使用 -d 指定后重试。"
exit 1
fi
if [ -z "$ORACLE_SID" ]; then
ORACLE_SID="$(run_grid "srvctl config database -d ${DB_UNIQUE_NAME} -a" 2>/dev/null \
| awk -F': *' 'tolower($1) ~ /database instance/ {print $2; exit}' \
| cut -d, -f1 | tr -d '[:space:]')"
fi
if [ -z "$ORACLE_HOME" ]; then
ORACLE_HOME="$(run_grid "srvctl config database -d ${DB_UNIQUE_NAME} -a" 2>/dev/null \
| awk -F': *' 'tolower($1) ~ /oracle home/ {print $2; exit}')"
fi
NODES="$(detect_nodes)"
INSTANCES="$(detect_instances)"
INST_MAP="$(detect_instance_map)"
DB_VERSION="$(db_query "SELECT version FROM v\$instance;" | head -1 | tr -d '[:space:]')"
MAJOR="$(printf '%s' "$DB_VERSION" | sed 's/^\([0-9][0-9]*\).*/\1/')"
[ -z "$MAJOR" ] && MAJOR=0
# SSH 免密检测
SSH_OK=0
if [ "$NO_SSH" -eq 0 ]; then
SSH_OK=1
for n in $NODES; do
is_local_node "$n" && continue
if ! ssh -o BatchMode=yes -o ConnectTimeout=5 "${GRID_USER}@${n}" "true" 2>/dev/null; then
SSH_OK=0
break
fi
done
fi
NODE_CNT="$(printf '%s\n' "$NODES" | grep -c .)"
INST_CNT_CFG="$(printf '%s\n' "$INSTANCES" | grep -c .)"
log "巡检时间 : $(date '+%Y-%m-%d %H:%M:%S')"
log "执行用户 : ${CUR_USER} @ $(hostname)"
log "DB_UNIQUE_NAME : ${DB_UNIQUE_NAME}"
log "本地 ORACLE_SID : ${ORACLE_SID:-未探测到}"
log "ORACLE_HOME : ${ORACLE_HOME:-未探测到}"
log "数据库版本 : ${DB_VERSION:-未探测到}（主版本 ${MAJOR}）"
log "集群节点(${NODE_CNT}) : $(printf '%s' "$NODES" | tr '\n' ' ')"
log "RAC 实例(${INST_CNT_CFG}) : $(printf '%s' "$INSTANCES" | tr '\n' ' ')"
log "跨节点 SSH : $([ "$SSH_OK" -eq 1 ] && echo '可用' || echo '不可用，远程节点需本地执行')"
log ""
log "实例-节点映射:"
printf '%s\n' "$INST_MAP" | awk -F'|' '{printf " %-12s -> %-20s thread=%s status=%s\n",$1,$2,$3,$4}' | tee -a "$LOG"
if [ -z "$ORACLE_SID" ]; then
log "警告：未能探测到本地 ORACLE_SID，SQL 检查将跳过。"
fi
# ---------------------------------------------------------------------------
# 1. 集群层（集群级，执行一次）
# ---------------------------------------------------------------------------
sec "1. 集群层 (Grid Infrastructure)"
for c in \
"crsctl check crs" \
"crsctl stat res -t" \
"crsctl stat res -t -init" \
"olsnodes -n -s -t" \
"oifcfg getif" \
"crsctl query css votedisk" \
"ocrcheck" \
"srvctl status database -d ${DB_UNIQUE_NAME} -v" \
"srvctl status service -d ${DB_UNIQUE_NAME}" \
"srvctl config database -d ${DB_UNIQUE_NAME} -a"
do
log "--- ${c}"
run_grid "$c" 2>&1 | tee -a "$LOG"
done
log "--- asmcmd lsdg"
run_grid "asmcmd lsdg" 2>&1 | tee -a "$LOG"
log "--- asmcmd lsdsk --statistics"
run_grid "asmcmd lsdsk --statistics" 2>&1 | tee -a "$LOG"
# ---------------------------------------------------------------------------
# 2. 按节点检查
# ---------------------------------------------------------------------------
sec "2. 按节点检查"
for n in $NODES; do
log ""
log "########## 节点: ${n} ##########"
log "--- srvctl status asm -n ${n}"
run_grid "srvctl status asm -n ${n}" 2>&1 | tee -a "$LOG"
log "--- srvctl status listener -n ${n}"
run_grid "srvctl status listener -n ${n}" 2>&1 | tee -a "$LOG"
log "--- 实例与关键进程"
run_on_node() {
local u="$1" node="$2" cmd="$3"
if is_local_node "$node"; then
run_as "$u" "$cmd"
elif [ "$SSH_OK" -eq 1 ]; then
ssh -o BatchMode=yes -o ConnectTimeout=8 "${u}@${node}" "$cmd" 2>&1
else
echo "[SKIP] ${node}：无 SSH 免密，请在该节点本地执行"
fi
}
run_on_node "$GRID_USER" "$n" \
"ps -ef | grep -E '[o]ra_pmon_|[o]ra_asm_pmon_' | awk '{print \$NF}'" 2>&1 | tee -a "$LOG"
log "--- 主机资源"
run_on_node "$GRID_USER" "$n" "top -b -n1 | head -15" 2>&1 | tee -a "$LOG"
run_on_node "$GRID_USER" "$n" "free -g" 2>&1 | tee -a "$LOG"
run_on_node "$GRID_USER" "$n" "df -h" 2>&1 | tee -a "$LOG"
run_on_node "$GRID_USER" "$n" "iostat -x 1 3 2>/dev/null" 2>&1 | tee -a "$LOG"
inst="$(inst_on_node "$n")"
if [ -n "$inst" ]; then
log "--- 节点 ${n} 对应实例 ${inst} 的告警日志尾部"
run_on_node "$DB_USER" "$n" \
"export ORACLE_SID=${inst}; adrci -exec 'show alert -tail 100'" 2>&1 | tee -a "$LOG"
else
log "[INFO] 节点 ${n} 未映射到运行中的实例，跳过告警日志"
fi
done
# ---------------------------------------------------------------------------
# 3. 数据库 + ADG 巡检 SQL（gv$ 全局，执行一次）
# ---------------------------------------------------------------------------
sec "3. 数据库实例与 ADG 同步状态"
if [ "$MAJOR" -ge 11 ] 2>/dev/null; then
GAP_EXTRA=", gap_status"
DATUM_EXTRA=", datum_time"
else
GAP_EXTRA=""
DATUM_EXTRA=""
fi
cat > "$OUT_DIR/sql/db.sql" <<'SQL'
SET PAGESIZE 300 LINESIZE 320 FEEDBACK OFF TRIMSPOOL ON
SET UNDERLINE =
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
PROMPT ===== 实例状态（RAC 全局） =====
SELECT inst_id, instance_number, instance_name, host_name, version,
status, database_status, startup_time, thread#
FROM gv$instance
ORDER BY inst_id;
PROMPT ===== 集群互连 =====
SELECT inst_id, name, ip_address, is_public, source
FROM gv$cluster_interconnects
ORDER BY inst_id;
PROMPT ===== 服务分布 =====
SELECT inst_id, name, network_name, goal, clb_goal
FROM gv$active_services
ORDER BY inst_id, name;
PROMPT ===== 在线日志 =====
SELECT thread#, group#, sequence#, bytes, status, archived
FROM v$log
ORDER BY thread#, group#;
PROMPT ===== 会话概况 =====
SELECT inst_id, COUNT(*) AS session_cnt,
SUM(CASE WHEN status='ACTIVE' THEN 1 ELSE 0 END) AS active_cnt
FROM gv$session
GROUP BY inst_id
ORDER BY inst_id;
PROMPT ===== 数据库角色与保护模式 =====
SELECT db_unique_name, database_role, protection_mode, open_mode,
switchover_status, force_logging, flashback_on, current_scn
FROM v$database;
PROMPT ===== 传输/应用延迟（仅备库有数据） =====
SELECT name, value, unit, time_computed__DATUM_EXTRA__
FROM v$dataguard_stats
WHERE LOWER(name) IN ('apply lag','transport lag')
ORDER BY name;
PROMPT ===== 归档目标状态 =====
SELECT dest_id, status, type, database_mode, recovery_mode, destination,
error, archived_thread#, archived_seq#,
applied_thread#, applied_seq#__GAP_EXTRA__
FROM v$archive_dest_status
WHERE status <> 'INACTIVE'
ORDER BY dest_id;
PROMPT ===== 归档缺口 =====
SELECT thread#, low_sequence#, high_sequence#
FROM v$archive_gap
ORDER BY thread#;
PROMPT ===== Data Guard 进程（RAC 按实例） =====
SELECT inst_id, process, pid, status, client_process, client_pid,
thread#, sequence#, block#, delay_mins
FROM gv$managed_standby
ORDER BY inst_id, process;
PROMPT ===== 备库恢复/应用进度 =====
SELECT start_time, type, item, units, sofar, total, timestamp
FROM v$recovery_progress
WHERE type = 'MEDIA RECOVERY'
ORDER BY start_time DESC, item;
PROMPT ===== 备用日志组 =====
SELECT group#, thread#, sequence#, bytes, used, archived, status
FROM v$standby_log
ORDER BY thread#, group#;
PROMPT ===== 备库已应用序列 =====
SELECT thread#, MAX(sequence#) AS applied_seq
FROM v$archived_log
WHERE applied = 'YES'
GROUP BY thread#
ORDER BY thread#;
PROMPT ===== 主库当前日志序列 =====
SELECT thread#, MAX(sequence#) AS last_seq
FROM v$log
GROUP BY thread#
ORDER BY thread#;
PROMPT ===== 最近24小时 DG 告警 =====
SELECT *
FROM (
SELECT timestamp, facility, severity, dest_id, message_num, error_code, message
FROM v$dataguard_status
WHERE severity IN ('Warning','Error','Fatal')
AND timestamp >= SYSDATE - 1
ORDER BY timestamp DESC, message_num DESC
) WHERE ROWNUM <= 100;
PROMPT ===== ASM 磁盘组 =====
SELECT name, type, state, total_mb, free_mb,
required_mirror_free_mb, usable_file_mb, offline_disks
FROM v$asm_diskgroup_stat
ORDER BY name;
PROMPT ===== ASM 磁盘状态 =====
SELECT group_number, disk_number, name, path,
mount_status, header_status, mode_status, state
FROM v$asm_disk
ORDER BY group_number, disk_number;
EXIT
SQL
sed -i "s/__GAP_EXTRA__/${GAP_EXTRA}/g; s/__DATUM_EXTRA__/${DATUM_EXTRA}/g" "$OUT_DIR/sql/db.sql"
chmod 644 "$OUT_DIR/sql/db.sql"
if [ -n "$ORACLE_SID" ]; then
run_db "$OUT_DIR/sql/db.sql" 2>&1 | tee -a "$LOG"
else
log "[SKIP] 无 ORACLE_SID，跳过数据库 SQL 巡检"
fi
# ---------------------------------------------------------------------------
# 4. 自动判定
# ---------------------------------------------------------------------------
sec "4. 巡检结论汇总"
if [ "$MAJOR" -ge 11 ] 2>/dev/null; then
DATUM_CHK="SELECT 'CHK_DATUM_TIME='||NVL(MAX(CASE WHEN LOWER(NAME)='apply lag' THEN DATUM_TIME END),'NA') FROM V\$DATAGUARD_STATS;"
else
DATUM_CHK="SELECT 'CHK_DATUM_TIME=NA' FROM DUAL;"
fi
cat > "$OUT_DIR/sql/check.sql" <<'SQL'
SET HEADING OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 400 TRIMSPOOL ON
SELECT 'CHK_ROLE='||DATABASE_ROLE FROM V$DATABASE;
SELECT 'CHK_PROTECTION='||PROTECTION_MODE FROM V$DATABASE;
SELECT 'CHK_OPEN='||OPEN_MODE FROM V$DATABASE;
SELECT 'CHK_FORCE_LOGGING='||FORCE_LOGGING FROM V$DATABASE;
SELECT 'CHK_ASM_OFFLINE='||NVL(SUM(offline_disks),0) FROM V$ASM_DISKGROUP_STAT;
SELECT 'CHK_ARCHIVE_GAP='||COUNT(*) FROM V$ARCHIVE_GAP;
SELECT 'CHK_MRP='||NVL(MAX(CASE WHEN PROCESS='MRP0' THEN STATUS END),'NONE') FROM GV$MANAGED_STANDBY;
SELECT 'CHK_APPLY_LAG='||NVL(MAX(CASE WHEN LOWER(NAME)='apply lag' THEN VALUE END),'NA') FROM V$DATAGUARD_STATS;
SELECT 'CHK_TRANSPORT_LAG='||NVL(MAX(CASE WHEN LOWER(NAME)='transport lag' THEN VALUE END),'NA') FROM V$DATAGUARD_STATS;
SELECT 'CHK_NODES_IN_DB='||COUNT(DISTINCT host_name) FROM GV$INSTANCE;
SELECT 'CHK_INST_CNT='||COUNT(*) FROM GV$INSTANCE;
SELECT 'CHK_INST_NOT_OPEN='||COUNT(*) FROM GV$INSTANCE WHERE STATUS<>'OPEN';
SELECT 'CHK_INST_NOT_ACTIVE='||COUNT(*) FROM GV$INSTANCE WHERE DATABASE_STATUS<>'ACTIVE';
SELECT 'CHK_SRL_SHORT='||COUNT(*) FROM (
SELECT l.thread#
FROM v$standby_log s, v$log l
WHERE s.thread#=l.thread#
GROUP BY l.thread#
HAVING COUNT(DISTINCT s.group#) < COUNT(DISTINCT l.group#)+1
);
__DATUM_CHK__
SELECT 'CHK_CDB='||CASE WHEN (SELECT COUNT(*) FROM V$CONTAINERS)>0 THEN 'YES' ELSE 'NO' END FROM DUAL;
EXIT
SQL
sed -i "s#__DATUM_CHK__#${DATUM_CHK}#" "$OUT_DIR/sql/check.sql"
chmod 644 "$OUT_DIR/sql/check.sql"
CHKOUT="$OUT_DIR/check.out"
if [ -n "$ORACLE_SID" ]; then
run_db "$OUT_DIR/sql/check.sql" > "$CHKOUT" 2>&1
fi
get_chk() { grep -m1 "^CHK_$1=" "$CHKOUT" 2>/dev/null | cut -d= -f2-; }
RC=0
ok() { log " [PASS] $*"; }
warn() { log " [WARN] $*"; [ "$RC" -lt 1 ] && RC=1; }
bad() { log " [FAIL] $*"; RC=2; }
ROLE="$(get_chk ROLE)"
PROT="$(get_chk PROTECTION)"
OPEN="$(get_chk OPEN)"
FORCELOG="$(get_chk FORCE_LOGGING)"
ASMOFF="$(get_chk ASM_OFFLINE)"
GAP="$(get_chk ARCHIVE_GAP)"
MRP="$(get_chk MRP)"
ALAG="$(get_chk APPLY_LAG)"
TLAG="$(get_chk TRANSPORT_LAG)"
DBNODES="$(get_chk NODES_IN_DB)"
DBINST="$(get_chk INST_CNT)"
NOTOPEN="$(get_chk INST_NOT_OPEN)"
NOTACT="$(get_chk INST_NOT_ACTIVE)"
SRLSHORT="$(get_chk SRL_SHORT)"
DATUMTIME="$(get_chk DATUM_TIME)"
ISCDB="$(get_chk CDB)"
log " 识别角色: ${ROLE:-未知} / ${OPEN:-未知} / ${PROT:-未知}"
log " 数据库类型: $([ "$ISCDB" = "YES" ] && echo CDB || echo 'NON-CDB/未识别')"
# 节点与实例覆盖
if [ -n "$DBNODES" ] && [ "$DBNODES" -lt "$NODE_CNT" ] 2>/dev/null; then
bad "集群节点 ${NODE_CNT} 个，但仅 ${DBNODES} 个节点运行实例"
elif [ -n "$DBNODES" ]; then
ok "节点覆盖正常：${DBNODES}/${NODE_CNT}"
fi
if [ -n "$DBINST" ] && [ "$DBINST" -lt "$INST_CNT_CFG" ] 2>/dev/null; then
bad "配置实例 ${INST_CNT_CFG} 个，实际运行 ${DBINST} 个"
elif [ -n "$DBINST" ]; then
ok "实例数量正常：${DBINST}/${INST_CNT_CFG}"
fi
[ "${NOTOPEN:-0}" = "0" ] && ok "所有实例状态为 OPEN" || bad "存在非 OPEN 实例：${NOTOPEN} 个"
[ "${NOTACT:-0}" = "0" ] && ok "所有实例 database_status 为 ACTIVE" || bad "存在非 ACTIVE 实例：${NOTACT} 个"
[ "$FORCELOG" = "YES" ] && ok "FORCE_LOGGING = YES" || warn "FORCE_LOGGING = ${FORCELOG}，建议开启"
if [ -n "$ASMOFF" ] && [ "$ASMOFF" != "0" ]; then
bad "ASM 存在离线磁盘：offline_disks=${ASMOFF}"
else
ok "ASM 无离线磁盘"
fi
case "$ROLE" in
PRIMARY)
log " [INFO] 当前为主库，跳过备库专用判定"
;;
*STANDBY*)
log " [INFO] 当前为备库，执行同步层判定"
if [ "${GAP:-1}" = "0" ]; then ok "无归档缺口"; else bad "存在归档缺口：${GAP} 条"; fi
case "$MRP" in
APPLYING_LOG) ok "MRP0 状态 APPLYING_LOG";;
WAIT_FOR_LOG|IDLE) warn "MRP0 状态 ${MRP}，需确认是否有新日志";;
WAIT_FOR_GAP) bad "MRP0 状态 WAIT_FOR_GAP，应用被缺口阻断";;
NONE) bad "未发现 MRP0 进程，备库可能未应用日志";;
*) warn "MRP0 状态异常：${MRP}";;
esac
case "$ALAG" in
NA|"") warn "未能读取 apply lag";;
*00:00:00*) ok "apply lag = ${ALAG}";;
*) warn "apply lag = ${ALAG}，请持续观察";;
esac
case "$TLAG" in
NA|"") warn "未能读取 transport lag";;
*00:00:00*) ok "transport lag = ${TLAG}";;
*) warn "transport lag = ${TLAG}，请持续观察";;
esac
if [ -n "$DATUMTIME" ] && [ "$DATUMTIME" != "NA" ]; then
log " [INFO] apply lag 数据时间 DATUM_TIME = ${DATUMTIME}（需与上次采样比较是否推进）"
fi
if [ "${SRLSHORT:-0}" = "0" ]; then
ok "备用日志组数量满足每线程 >= 在线组数 + 1"
else
warn "存在线程备用日志组数量不足：${SRLSHORT} 个线程"
fi
;;
*)
warn "未能识别数据库角色"
;;
esac
# ---------------------------------------------------------------------------
# 5. Data Guard Broker（可选）
# ---------------------------------------------------------------------------
if [ -n "${DG_BROKER_CONNECT:-}" ]; then
sec "5. Data Guard Broker"
log "--- show configuration verbose"
run_oracle "dgmgrl -silent '${DG_BROKER_CONNECT}' 'show configuration verbose'" 2>&1 | tee -a "$LOG"
log "--- show database verbose ${DB_UNIQUE_NAME}"
run_oracle "dgmgrl -silent '${DG_BROKER_CONNECT}' 'show database verbose ${DB_UNIQUE_NAME}'" 2>&1 | tee -a "$LOG"
log "--- validate database ${DB_UNIQUE_NAME}"
run_oracle "dgmgrl -silent '${DG_BROKER_CONNECT}' 'validate database ${DB_UNIQUE_NAME}'" 2>&1 | tee -a "$LOG"
else
log ""
log "[INFO] 未设置 DG_BROKER_CONNECT，跳过 Broker 巡检"
fi
# ---------------------------------------------------------------------------
# 6. 结果
# ---------------------------------------------------------------------------
sec "6. 巡检结束"
case "$RC" in
0) log " 总体结论: 正常（PASS）";;
1) log " 总体结论: 存在告警项，建议人工复核（WARN）";;
2) log " 总体结论: 存在异常项，需立即处理（FAIL）";;
esac
log " 完整日志: ${LOG}"
log " 结束时间: $(date '+%Y-%m-%d %H:%M:%S')"
exit "$RC"
浙公网安备 33010602011771号