Oracle LogMiner 核心 SQL 解析

Yunjie Ge

发布于 2025-04-27 11:03:39

15700

代码可运行

运行总次数：0

代码可运行

在Oracle数据库变更捕获（CDC）体系中，LogMiner是极其重要的工具，它通过解析重做日志，捕捉DML、DDL变更并还原成结构化事件。LogMiner在工作过程中大量依赖底层系统视图（如VLOGMNR_CONTENTS、SYSTEM.LOGMNR系列表）以及一系列精心设计的SQL查询。本文将带你逐条深入了解LogMiner背后的关键SQL，揭开高效日志解析的秘密。

1. 获取LogMiner会话UID

SELECT MAX(LOGMNR_UID) AS LOGMNR_UID FROM SYSTEM.LOGMNR_UID$;

目的：查询当前最近一次启动LogMiner时生成的唯一标识（LOGMNR_UID）。
意义：后续所有访问对象表、列表（如LOGMNR_OBJ、LOGMNR_COL）时，都要限定在同一个LOGMNR_UID之下，以确保一致性。

2. 查询所有涉及表

SELECT DISTINCT(p.TABLE_NAME), p.OWNER_NAME, p.OWNER_ID, p.TABLE_ID, p.LOGMNR_UID, p.OWNER_NAME || '.' || p.TABLE_NAME AS KEYID
FROM (
    SELECT b.NAME OWNER_NAME, a.NAME TABLE_NAME, a.LOGMNR_UID, a.OWNER# OWNER_ID, a.OBJ# TABLE_ID, a.STIME, a.SUBNAME
    FROM SYSTEM.LOGMNR_OBJ$ a
    JOIN SYSTEM.LOGMNR_USER$ b ON a.OWNER# = b.USER# AND a.LOGMNR_UID = b.LOGMNR_UID
) p
JOIN (
    SELECT DISTINCT(OBJ#), LOGMNR_UID FROM SYSTEM.LOGMNR_COL$
) q
ON p.TABLE_ID = q.OBJ# AND p.LOGMNR_UID = q.LOGMNR_UID
WHERE p.SUBNAME IS NULL
  AND p.LOGMNR_UID = ?
  AND (p.OWNER_NAME LIKE ? AND p.TABLE_NAME LIKE ?);

目的：列出所有当前会话（LOGMNR_UID）中解析到的有效表。
筛选逻辑：
- 连接对象表（LOGMNR_OBJ）和用户表（LOGMNR_USER）；
- 只选取主对象（排除子对象如分区表子表，SUBNAME IS NULL）；
- 只列出有实际列定义（关联LOGMNR_COL$）的对象。
用途：根据配置筛选需要捕获变更的表，生成待监听表清单。

3. 查询排除表（反选模式）

select DISTINCT(p.TABLE_NAME),p.OWNER_NAME,p.OWNER_ID,p.TABLE_ID,p.LOGMNR_UID 
from (
  select b.NAME OWNER_NAME,a.NAME TABLE_NAME,a.LOGMNR_UID LOGMNR_UID,a.OWNER# OWNER_ID,a.OBJ# TABLE_ID,a.STIME TIME,a.SUBNAME SUBNAME 
  from SYSTEM.LOGMNR_OBJ$ a 
  join SYSTEM.LOGMNR_USER$ b on a.OWNER# = b.USER# and a.LOGMNR_UID = b.LOGMNR_UID
  ) p 
  join (
    select DISTINCT(OBJ#),LOGMNR_UID from SYSTEM.LOGMNR_COL$
  ) q 
  on p.TABLE_ID = q.OBJ# and p.LOGMNR_UID = q.LOGMNR_UID 
  where p.SUBNAME is NULL 
    and p.LOGMNR_UID = ? 
    and (( p.OWNER_NAME like ? and p.TABLE_NAME like ? )

目的：逻辑上与上一条查询类似，但用于排除模式，即设定哪些表不捕获，剩下的都捕获。
应用场景：灵活配置同步范围，比如排除系统表、临时表等。

4. 查询表字段信息

SELECT x.COL#, x.NAME, x.TYPE#, x.LENGTH, x.NULL$, x.PRECISION#, x.SCALE, x.PROPERTY, 
       x.OBJ#, y.NAME TABLE_NAME, y.OWNER# OWNER_ID, z.NAME OWNER_NAME
FROM SYSTEM.LOGMNR_COL$ x
JOIN SYSTEM.LOGMNR_OBJ$ y ON x.OBJ# = y.OBJ# AND x.LOGMNR_UID = y.LOGMNR_UID
JOIN SYSTEM.LOGMNR_USER$ z ON y.OWNER# = z.USER# AND y.LOGMNR_UID = z.LOGMNR_UID
WHERE y.SUBNAME IS NULL
  AND x.LOGMNR_UID = ?
  AND (z.NAME LIKE ? AND y.NAME LIKE ?);

目的：基于对象ID，提取每张表的列结构信息（列名、数据类型、长度、精度等）。
重要性：解析DML操作（INSERT/UPDATE）时，需要精确还原列的定义，尤其是处理LOB、NUMBER、TIMESTAMP等特殊类型字段。

5. 获取数据库当前SCN

SELECT current_scn FROM gv$database;

目的：查询Oracle实例当前的系统变更号（SCN）。
用途：用于确定从哪个位置开始解析日志（比如初次启动、断点恢复时）。

6. 查询合适的日志文件

SELECT a.first_change#, a.next_change#, a.group#, b.member
FROM v$log a, v$logfile b
WHERE a.next_change# > (SELECT MAX(first_change#) FROM v$log WHERE first_change# <= ?)
  AND b.group# = a.group#;

目的：基于指定SCN，找出之后可能涉及的归档日志/在线日志文件。
意义：LogMiner解析需要提前加载所有相关日志，否则会因缺日志导致启动失败。

7. 启动LogMiner

BEGIN
  DBMS_LOGMNR.START_LOGMNR(
    STARTSCN => ?,
    OPTIONS  => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE
  );
END;

目的：开启一段新的日志解析会话。
常用选项：
- DICT_FROM_ONLINE_CATALOG：直接从当前数据字典读取对象信息；
- CONTINUOUS_MINE：连续挖掘后续生成的日志（注意新版本已弃用）。
说明：也可以加上 SKIP_CORRUPTION、NO_ROWID_IN_STMT等参数，增强容错性。

8. 停止LogMiner

BEGIN
  SYS.DBMS_LOGMNR.END_LOGMNR;
END;

目的：正式关闭LogMiner会话，释放系统资源。

9. 解析日志内容（通用查询模板）

SELECT thread#, scn, start_scn, commit_scn, timestamp, commit_timestamp, 
       (xidusn || '.' || xidslt || '.' || xidsqn) AS xid, operation_code, status, SEG_TYPE_NAME,
       info, seg_owner, table_name, SSN, username, sql_redo, row_id, csf, rollback, 
       TABLE_SPACE, SESSION_INFO, RS_ID, RBASQN, RBABLK, SEQUENCE#, TX_NAME, SEG_NAME, 
       SEG_TYPE_NAME, (PXIDUSN || '.' || PXIDSLT || '.' || PXIDSQN) AS pid, 
       AUDIT_SESSIONID, SESSION#, SERIAL#
FROM v$logmnr_contents
WHERE ...

解释：
- 每一行代表一个Redo日志中的变更事件；
- 包括事务ID（XID）、变更类型（operation_code）、实际SQL（SQL_REDO）、表信息等；
- DML/ DDL/ 事务边界（START/COMMIT/ROLLBACK） 都通过这里统一读取。
变种：
- LOG_MINER_SELECT_DDL：过滤只保留DDL记录（忽略SYS/SYSTEM/MDSYS的DDL）。
- LOG_MINER_SELECT_WITHSCHEMA：针对支持PDB多租户环境，返回额外字段SRC_CON_NAME。