DMDPC 集群异常项检测 SQL

1. 节点状态异常
– 检查节点是否非 OPEN 状态或无效
SELECT NAME, DPC_MODE, SYS_STATUS, STATUS
FROM DPC_INSTANCE
WHERE SYS_STATUS != 4 OR STATUS != 1;

2. 无效 BP
– 检查是否存在不可用的 BP
SELECT NAME, IS_VALID
FROM DPC_BP_RAFT
WHERE DPC_MODE = ‘BP’ AND IS_VALID = 0;

3. 多副本无主库
– 检查是否有 RAFT 组缺少 Leader
SELECT DISTINCT RAFT_ID
FROM V$GLOBAL_RAFT_INFO
WHERE RAFT_STAT = ‘LEADER’
GROUP BY RAFT_ID
HAVING COUNT(*) = 0;

4. 备库归档失效
– 检查归档状态为 Invalid 的库
SELECT INSTANCE_NAME, RAFT_ID, ARCH_STATUS
FROM V$GLOBAL_RAFT_INFO
WHERE ARCH_STATUS != ‘VALID’;

5. 会话数异常偏高
SELECT SF_GET_EP_SEQNO(ROWID) AS RAFT_ID, COUNT() AS SESS_CNT
FROM V$SESSIONS
GROUP BY SF_GET_EP_SEQNO(ROWID)
HAVING COUNT() > 500;

6. 主备同步差距过大
SELECT
RAFT_ID,
MAX(C_LSN) - MIN(C_LSN) AS C_LSN_GAP,
MAX(F_LSN) - MIN(F_LSN) AS F_LSN_GAP
FROM V$GLOBAL_RAFT_INFO
GROUP BY RAFT_ID
HAVING MAX(C_LSN) - MIN(C_LSN) > 1000;