oracle logminer

Oracle LogMiner 日志挖掘【一、LogMiner 核心概念】LogMiner 是 Oracle 内置的日志分析工具通过解析 redo log / 归档日志提取其中的 SQL 变更记录用于• 数据审计谁改了什么、什么时候改的• 数据恢复误删数据后提取原始值• 数据同步CDC 场景提取变更事件• 故障排查追踪特定事务或表的变更历史核心视图V$LOGMNR_CONTENTS启动 LogMiner 后才有数据【二、前置条件开启补充日志Supplemental Log】LogMiner 要精确识别行数据必须开启补充日志-- 2.1 查看当前补充日志状态SELECT supplemental_log_data_min, supplemental_log_data_pk,supplemental_log_data_ui, supplemental_log_data_fk,supplemental_log_data_allFROM v$database;-- 2.2 开启最小补充日志必须ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;-- 2.3 开启主键/唯一键补充日志推荐用于精确定位行ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;-- 2.4 开启全列补充日志最完整但日志量大ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;-- 2.5 关闭补充日志ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;-- 2.6 表级补充日志更精细控制ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;【三、LogMiner 标准使用流程】流程添加日志文件 → 启动 LogMiner → 查询 V$LOGMNR_CONTENTS → 结束 LogMiner-- 3.1 方式一使用在线 redo log当前正在写入的日志BEGINDBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME /u01/app/oracle/oradata/ORCL/redo01.log,OPTIONS DBMS_LOGMNR.NEW -- NEW 新建日志列表);END;/-- 3.2 方式二添加多个归档日志BEGINDBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME /u01/app/oracle/arch/1_123_1234567890.arc,OPTIONS DBMS_LOGMNR.NEW);DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME /u01/app/oracle/arch/1_124_1234567890.arc,OPTIONS DBMS_LOGMNR.ADDFILE -- ADDFILE 追加到列表);END;/-- 3.3 方式三自动添加所有归档日志按时间/SCN 范围BEGINDBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME /u01/app/oracle/arch/1_125_1234567890.arc,OPTIONS DBMS_LOGMNR.NEW);END;/-- 3.4 启动 LogMiner按时间范围BEGINDBMS_LOGMNR.START_LOGMNR(STARTTIME TO_DATE(2026-05-20 10:00:00, YYYY-MM-DD HH24:MI:SS),ENDTIME TO_DATE(2026-05-20 12:00:00, YYYY-MM-DD HH24:MI:SS),OPTIONS DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG DBMS_LOGMNR.COMMITTED_DATA_ONLY DBMS_LOGMNR.PRINT_PRETTY_SQL);END;/-- 3.5 启动 LogMiner按 SCN 范围BEGINDBMS_LOGMNR.START_LOGMNR(STARTSCN 123456789,ENDSCN 123500000,OPTIONS DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG DBMS_LOGMNR.COMMITTED_DATA_ONLY);END;/-- 3.6 启动 LogMiner无范围限制分析全部添加的日志BEGINDBMS_LOGMNR.START_LOGMNR(OPTIONS DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);END;/-- 3.7 结束 LogMiner必须执行释放资源BEGINDBMS_LOGMNR.END_LOGMNR();END;/【四、START_LOGMNR 常用 OPTIONS 参数】┌─────────────────────────────────┬──────────────────────────────────────────┐│ 参数 │ 说明 │├─────────────────────────────────┼──────────────────────────────────────────┤│ DICT_FROM_ONLINE_CATALOG │ 使用在线数据字典最常用无需额外文件 ││ DICT_FROM_REDO_LOGS │ 从 redo 日志中提取数据字典 ││ DICT_FROM_UTL_FILE │ 使用外部平面文件字典需提前生成 ││ COMMITTED_DATA_ONLY │ 只显示已提交事务过滤未提交/回滚操作 ││ NO_ROWID_IN_STMT │ 生成的 SQL 中不包含 ROWID ││ PRINT_PRETTY_SQL │ 格式化输出 SQL更易读 ││ CONTINUOUS_MINE │ 持续挖掘在线 redo11g 后推荐用 ADD_LOGFILE││ SKIP_CORRUPTION │ 跳过损坏的日志块 │└─────────────────────────────────┴──────────────────────────────────────────┘组合示例DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG DBMS_LOGMNR.COMMITTED_DATA_ONLY DBMS_LOGMNR.PRINT_PRETTY_SQL【五、V$LOGMNR_CONTENTS 核心字段详解】┌─────────────────────┬──────────────────────────────────────────────────────┐│ 字段 │ 说明 │├─────────────────────┼──────────────────────────────────────────────────────┤│ SCN │ 系统变更号精确时间点标识 ││ TIMESTAMP │ 操作发生时间 ││ COMMIT_TIMESTAMP │ 事务提交时间 ││ THREAD# │ RAC 线程号 ││ LOG_ID │ 日志序列号 ││ XIDUSN / XIDSLT │ 事务 ID 组成部分 ││ XID │ 完整事务 ID ││ OPERATION │ 操作类型INSERT / UPDATE / DELETE / DDL / START / COMMIT ││ OPERATION_CODE │ 操作数字代码1INSERT, 2DELETE, 3UPDATE, 5DDL ││ SEG_TYPE_NAME │ 段类型TABLE / INDEX / CLUSTER 等 ││ TABLE_SPACE │ 表空间名 ││ SEG_OWNER │ 对象所有者Schema ││ TABLE_NAME │ 表名 ││ SEG_NAME │ 段名通常同表名 ││ USERNAME │ 执行操作的数据库用户 ││ SESSION_INFO │ 会话信息含 OS 用户、机器名、客户端程序 ││ SQL_REDO │ 重做 SQL正向操作可用于重做 ││ SQL_UNDO │ 回滚 SQL逆向操作可用于撤销/恢复 ││ RS_ID │ 记录集 ID ││ SSN │ SQL 序列号 ││ CSF │ 跨片段标志长 SQL 会分段 ││ REDO_VALUE │ Redo 向量 ││ UNDO_VALUE │ Undo 向量 ││ ROW_ID │ 行 ROWID ││ RBASQN / RBABLK │ Redo Block Address ││ STATUS │ 状态 │└─────────────────────┴──────────────────────────────────────────────────────┘【六、常用查询 SQL】-- 6.1 查看所有 DML 操作按时间排序SELECTscn,timestamp,username,operation,seg_owner,table_name,sql_redo,sql_undoFROM v$logmnr_contentsWHERE operation IN (INSERT, UPDATE, DELETE)ORDER BY scn;-- 6.2 追踪特定表的变更历史SELECTscn,timestamp,username,operation,sql_redo,sql_undoFROM v$logmnr_contentsWHERE seg_owner SCOTTAND table_name EMPAND operation IN (INSERT, UPDATE, DELETE)ORDER BY scn;-- 6.3 追踪特定用户的所有操作SELECTscn,timestamp,operation,seg_owner,table_name,sql_redoFROM v$logmnr_contentsWHERE username SCOTTORDER BY scn;-- 6.4 查看特定时间段的变更SELECTscn,timestamp,username,operation,seg_owner,table_name,sql_redoFROM v$logmnr_contentsWHERE timestamp BETWEENTO_DATE(2026-05-20 10:00:00, YYYY-MM-DD HH24:MI:SS)AND TO_DATE(2026-05-20 12:00:00, YYYY-MM-DD HH24:MI:SS)ORDER BY timestamp;-- 6.5 查看特定 SCN 范围的变更SELECT * FROM v$logmnr_contentsWHERE scn BETWEEN 123456789 AND 123500000ORDER BY scn;-- 6.6 查看 DDL 操作建表、删表、改表结构SELECTscn,timestamp,username,operation,sql_redoFROM v$logmnr_contentsWHERE operation DDLORDER BY scn;-- 6.7 查看事务提交记录SELECTxid,commit_timestamp,username,operation,seg_owner,table_nameFROM v$logmnr_contentsWHERE operation COMMITORDER BY commit_timestamp;-- 6.8 按事务聚合查看完整操作序列SELECTxid,COUNT(*) op_count,LISTAGG(operation || : || table_name, , ) WITHIN GROUP (ORDER BY scn) opsFROM v$logmnr_contentsWHERE operation IN (INSERT, UPDATE, DELETE)GROUP BY xidORDER BY op_count DESC;-- 6.9 提取误删数据的恢复 SQL核心用途SELECT sql_undoFROM v$logmnr_contentsWHERE seg_owner SCOTTAND table_name EMPAND operation DELETEAND timestamp SYSDATE - 1ORDER BY scn;-- 6.10 查看会话详细信息追踪客户端来源SELECTscn,timestamp,username,operation,seg_owner,table_name,session_info,sql_redoFROM v$logmnr_contentsWHERE table_name EMPORDER BY scn;-- session_info 示例输出-- login_usernameSCOTT client_info OS_usernameoracle Machine_namedbserver1 OS_terminalpts/0 OS_program_namesqlplusdbserver1-- 6.11 处理长 SQL 分段CSF 1 表示继续SELECTscn,operation,CASE WHEN csf 0 THEN sql_redoELSE sql_redo || ... (continued)END sql_redo_completeFROM v$logmnr_contentsWHERE seg_owner SCOTTORDER BY scn, ssn;【七、数据字典选项详解】LogMiner 需要数据字典来解析对象名和列名有三种方式7.1 在线数据字典最常用最简单BEGINDBMS_LOGMNR.START_LOGMNR(OPTIONS DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);END;/-- 优点无需准备直接使用当前数据库字典-- 缺点如果对象已被删除/修改历史日志中的对象可能无法正确解析7.2 提取字典到 redo 日志用于异机分析或历史对象BEGINDBMS_LOGMNR_D.BUILD(OPTIONS DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);END;/-- 然后在目标库用 DICT_FROM_REDO_LOGS 启动7.3 提取字典到平面文件用于无字典环境BEGINDBMS_LOGMNR_D.BUILD(DICTIONARY_FILENAME /tmp/dictionary.ora,DICTIONARY_LOCATION /tmp,OPTIONS DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);END;/-- 启动时指定-- OPTIONS DBMS_LOGMNR.DICT_FROM_UTL_FILE-- DICTFILENAME /tmp/dictionary.ora-- DICTLOCATION /tmp【八、归档日志自动挖掘简化版】-- 8.1 查询需要分析的归档日志范围SELECT name, first_time, next_time, first_change#, next_change#FROM v$archived_logWHERE first_time BETWEENTO_DATE(2026-05-20 08:00:00, YYYY-MM-DD HH24:MI:SS)AND TO_DATE(2026-05-20 18:00:00, YYYY-MM-DD HH24:MI:SS)ORDER BY first_time;-- 8.2 批量添加归档日志并启动PL/SQL 自动化SET SERVEROUTPUT ONDECLARECURSOR c_logs ISSELECT nameFROM v$archived_logWHERE first_time BETWEENTO_DATE(2026-05-20 08:00:00, YYYY-MM-DD HH24:MI:SS)AND TO_DATE(2026-05-20 18:00:00, YYYY-MM-DD HH24:MI:SS)AND name IS NOT NULL -- 排除空路径记录AND deleted NOAND status AORDER BY first_time;v_first BOOLEAN : TRUE;BEGINFOR r IN c_logs LOOPBEGINIF v_first THENDBMS_LOGMNR.ADD_LOGFILE(r.name, DBMS_LOGMNR.NEW);v_first : FALSE;ELSEDBMS_LOGMNR.ADD_LOGFILE(r.name, DBMS_LOGMNR.ADDFILE);END IF;DBMS_OUTPUT.PUT_LINE(✓ 已添加: || r.name);EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(✗ 跳过损坏/缺失: || r.name || | || SQLERRM);CONTINUE;END;END LOOP;DBMS_LOGMNR.START_LOGMNR(OPTIONS DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG DBMS_LOGMNR.COMMITTED_DATA_ONLY);DBMS_OUTPUT.PUT_LINE(LogMiner 启动成功);END;/-- 查看这段时间所有 DML 操作SELECT scn, timestamp, username, operation, seg_owner, table_name, sql_redo, sql_undoFROM v$logmnr_contentsWHERE operation IN (INSERT, UPDATE, DELETE)ORDER BY scn;-- 或追踪特定表SELECT * FROM v$logmnr_contentsWHERE seg_owner YOUR_SCHEMA AND table_name YOUR_TABLE;【九、典型应用场景】9.1 误删数据恢复-- 步骤1确定删除时间范围-- 步骤2添加该时间段归档日志-- 步骤3启动 LogMiner-- 步骤4提取 SQL_UNDO 并执行SELECT sql_undo FROM v$logmnr_contentsWHERE operation DELETEAND seg_owner SCOTTAND table_name EMPAND timestamp BETWEEN TO_DATE(...) AND TO_DATE(...);9.2 数据变更审计-- 追踪谁在什么时候改了什么SELECT username, timestamp, operation, sql_redoFROM v$logmnr_contentsWHERE seg_owner HR AND table_name SALARY;9.3 CDC 数据同步-- 提取变更事件用于下游同步SELECT scn, xid, operation, sql_redoFROM v$logmnr_contentsWHERE operation IN (INSERT, UPDATE, DELETE)AND seg_owner APP;【十、注意事项与常见问题】┌─────────────────────────────────┬──────────────────────────────────────────┐│ 问题 │ 解决 │├─────────────────────────────────┼──────────────────────────────────────────┤│ 表名显示为 OBJ#12345无法解析 │ 未开启补充日志或对象已删除用平面文件字典 ││ SQL_REDO 为 NULL │ 补充日志级别不够开启 ALL COLUMNS ││ SQL_UNDO 为 NULL │ 未开启足够补充日志无法生成回滚 SQL ││ 查询 V$LOGMNR_CONTENTS 很慢 │ 日志量大加过滤条件seg_owner/table_name││ 看不到 DDL 详细内容 │ DDL 的 SQL_REDO 可能分段需合并 CSF1 行 ││ RAC 环境日志分散 │ 需添加所有实例的归档日志 ││ 权限不足 │ 需 SYSDBA 或 EXECUTE_CATALOG_ROLE 权限 ││ 在线日志被覆盖 │ 及时归档分析归档日志而非在线日志 │└─────────────────────────────────┴──────────────────────────────────────────┘【十一、权限要求】执行 LogMiner 需要• 角色EXECUTE_CATALOG_ROLE或 DBA• 系统权限SYSDBA 可直接执行• 必须能访问要分析的日志文件操作系统权限授权GRANT EXECUTE_CATALOG_ROLE TO analyst_user;【十二、快速命令速查】-- 开启补充日志ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;-- 添加日志EXEC DBMS_LOGMNR.ADD_LOGFILE(/path/to/arch.log, DBMS_LOGMNR.NEW);-- 启动在线字典 仅已提交 格式化BEGINDBMS_LOGMNR.START_LOGMNR(OPTIONS DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG DBMS_LOGMNR.COMMITTED_DATA_ONLY DBMS_LOGMNR.PRINT_PRETTY_SQL);END;/-- 查询SELECT * FROM v$logmnr_contents WHERE table_name YOUR_TABLE;-- 结束EXEC DBMS_LOGMNR.END_LOGMNR();