尧图网站建设 
Oracle LogMiner日志挖掘操作总结一、工具介绍1.1 什么是LogMinerOracle LogMiner 是 Oracle 从 8i 版本开始提供的一个极其强大且免费的内置分析工具。它通过一组 PL/SQL 包DBMS_LOGMNR和DBMS_LOGMNR_D和动态视图如V$LOGMNR_CONTENTS将 Oracle 重做日志Online Redo Logs和归档日志Archived Logs中晦涩难懂的底层二进制数据转化为人类可读的 SQL 语句。LogMiner工具既可以用来分析在线日志也可以分析离线日志文件既可以分析自身数据库的重做日志文件也可以用来分析其他数据库的重做日志文件。1.2 LogMiner的作用LogMiner的四大作用场景说明数据变更审计精准追踪DML操作INSERT/UPDATE/DELETE的执行时间、用户及影响范围误操作数据恢复当发生错误的DELETE或UPDATE且超出了 Flashback Query 的保留窗口时通过提取SQL_UNDO语句进行精确回滚。DDL 变更回溯找回被意外删除或修改的存储过程、表结构等元数据。性能与容量分析分析哪些表在特定时间段内被频繁修改为数据库调优和扩容提供历史依据1.3 LogMiner的主要组件LogMiner分析工具实际上是由一组PL/SQL包和动态视图组成。核心PL/SQL包包名用途DBMS_LOGMNR核心分析包用于添加日志文件、启动/停止LogMiner分析DBMS_LOGMNR_D辅助包用于创建和管理数据字典文件这两个包分别由以下脚本创建-- 以SYS用户执行$ORACLE_HOME/rdbms/admin/dbmslm.sql-- 创建DBMS_LOGMNR包$ORACLE_HOME/rdbms/admin/dbmslmd.sql-- 创建DBMS_LOGMNR_D包核心动态视图视图说明V$LOGMNR_DICTIONARY显示用于决定对象ID名称的字典文件信息V$LOGMNR_PARAMETERS查询当前LogMiner设定的参数V$LOGMNR_LOGS在LogMiner启动时显示待分析的日志列表V$LOGMNR_CONTENTS最重要的视图包含解析后的日志内容注意事项在 19c 多租户CDB架构下LogMiner 必须在CDB 根容器下执行在 PDB 中执行会报错。所有操作必须在同一个数据库会话Session中连续执行。如果断开重连V$LOGMNR_CONTENTS视图中的结果将丢失。1.4 数据字典选项当LogMiner分析重做数据时需要一个数据字典将日志中的对象ID转换为可读的表名和列名。如果没有数据字典LogMiner解释出来的语句中关于数据字典的部分如表名、列名等都将是16进制的形式无法直接理解。LogMiner提供了三种使用数据字典的方式方式说明命令在线目录Online Catalog使用当前数据库的在线数据字典必须在源数据库执行DBMS_LOGMNR.START_LOGMNR(optionsDBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)提取到重做日志将字典提取到重做日志流中要求数据库处于ARCHIVELOG模式且为OPEN状态DBMS_LOGMNR_D.BUILD(optionsDBMS_LOGMNR_D.STORE_IN_REDO_LOGS)提取到操作系统文件将字典导出为文本文件需设置UTL_FILE_DIR参数DBMS_LOGMNR_D.BUILD(filename,directory,DBMS_LOGMNR_D.STORE_IN_FLAT_FILE)方式对比在线目录最简单无需额外步骤但只能分析当前数据库的日志提取到重做日志适合长期归档分析字典与日志一起归档提取到文件适合跨数据库分析但需要配置UTL_FILE_DIR并重启数据库1.5关键前置条件补充日志 (Supplemental Logging)在使用 LogMiner 进行数据恢复前强烈建议开启最小补充日志。未开启时Oracle 仅记录恢复数据块所需的最小信息。对于UPDATE操作LogMiner 可能无法获取修改前的旧值SQL_UNDO中的旧值可能为NULL且WHERE条件可能依赖物理地址ROWID导致生成的回滚 SQL 无法在其他环境执行。开启后Oracle 会在重做日志中记录足够的列信息如主键或修改前的值确保生成的SQL_REDO和SQL_UNDO逻辑完整、准确可用。-- 启用最小补充日志 ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; -- 更全面的补充日志推荐 ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; -- 验证是否启用 SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all FROM v$database; SUPPLEMENTAL_LOG_DATA_MIN应返回 YES 或 IMPLICIT二、简单使用案例1、补充日志开启确认 SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all FROM v$database; SUPPLEME SUP SUP SUP SUP -------- --- --- --- --- YES YES NO NO NO 2、做一些测试操作 [oraclehost51 ~]$ sqlplus user1/user1 SQL select count(*) from emp; SQL select count(*) from dept; COUNT(*) ---------- 4 SQL insert into emp select * from scott.emp; 14 rows created. SQL commit; Commit complete. SQL alter system archive log current; System altered. SQL delete from dept; 4 rows deleted. SQL commit; Commit complete. SQL alter system archive log current; System altered. SQL create table tab1 as select * from dba_users; Table created. SQL select to_char(sysdate,yyyymmdd hh24:mi:ss) from dual; TO_CHAR(SYSDATE, ----------------- 20260806 15:28:06 3、指定 LogMiner 数据字典这里我们选择直接从在线目录获取 EXEC DBMS_LOGMNR_D.BUILD(OPTIONS DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); 4、添加需要挖掘的日志文件 -- 1生成添加归档日志的执行脚本根据实际时间范围修改 SELECT DISTINCT EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME || name || , OPTIONSDBMS_LOGMNR.ADDFILE); AS add_log_sql FROM GV$ARCHIVED_LOG WHERE completion_time BETWEEN TO_DATE(2026-08-06 15:26:00, YYYY-MM-DD HH24:MI:SS) AND TO_DATE(2026-08-06 15:29:00, YYYY-MM-DD HH24:MI:SS) AND dest_id 1 ORDER BY 1; -- 2在 SQL*Plus 中执行上述生成的脚本批量添加日志 -- (如果是第一个文件请将 OPTIONS 改为 DBMS_LOGMNR.NEW) 具体如下 EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME/oradata/orcl/fast_recovery_area/ORCL/archivelog/2026_08_06/o1_mf_1_6_o78ftzoz_.arc, OPTIONSDBMS_LOGMNR.new); EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME/oradata/orcl/fast_recovery_area/ORCL/archivelog/2026_08_06/o1_mf_1_7_o78fvd9b_.arc, OPTIONSDBMS_LOGMNR.ADDFILE); 5、启动 LogMiner 会话 EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); 6、查询挖掘结果 --由于视图数据量可能极大且会话已断开就会丢失视图里面的内容建议先将结果存入临时表再进行查询分析 CREATE TABLE t_logminer_res AS SELECT scn,timestamp, operation, table_name, sql_redo, sql_undo FROM v$logmnr_contents WHERE seg_owner USER1 ORDER BY 1; 查询临时表类似如下 SQL select * from t_logminer_res; SCN TIMESTAMP OPERATION ---------- ------------ -------------------------------- TABLE_NAME -------------------------------- SQL_REDO -------------------------------------------------------------------------------- SQL_UNDO -------------------------------------------------------------------------------- 1126344 06-AUG-26 INSERT EMP insert into USER1.EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,D EPTNO) values (7369,SMITH,CLERK,7902,TO_DATE(17-DEC-80, DD-MON-RR), 800,NULL,20); delete from USER1.EMP where EMPNO 7369 and ENAME SMITH and JOB CLERK and MGR 7902 and HIREDATE TO_DATE(17-DEC-80, DD-MON-RR) and SAL 800 and COMM IS NULL and DEPTNO 20 and ROWID AAAVVUAAF AAAACDAAA; ...... 根据找到对应的 SQL_UNDO 或原始的 CREATE/REPLACE PROCEDURE 语句后交由开发人员确认无误即可恢复存储过程 7、结束 LogMiner 会话 分析完成后务必结束会话以释放 PGA/SGA 内存资源 EXEC DBMS_LOGMNR.END_LOGMNR; 至此简单测试案例完成三、使用案例详细讲解3.1 环境准备与配置步骤1确认补充日志已启用补充日志Supplemental Logging必须在生成待分析的重做日志之前启用否则LogMiner挖掘的一些信息无法正常显示。-- 查看补充日志状态SELECTsupplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,supplemental_log_data_fk,supplemental_log_data_allFROMv$database;-- 如果返回结果为NO执行以下命令启用ALTERDATABASEADDSUPPLEMENTAL LOGDATA;-- 更全面的补充日志推荐ALTERDATABASEADDSUPPLEMENTAL LOGDATA(PRIMARYKEY)COLUMNS;步骤2确认/安装LogMiner包-- 检查DBMS_LOGMNR包是否存在SELECTowner,object_name,object_typeFROMdba_objectsWHEREobject_nameLIKEDBMS_LOGMNR%;-- 如果不存在以SYS用户执行安装脚本$ORACLE_HOME/rdbms/admin/dbmslm.sql$ORACLE_HOME/rdbms/admin/dbmslmd.sql步骤3设置数据字典目录如使用文件方式-- 创建目录CREATEDIRECTORY logminer_dirAS/u01/app/oracle/logminer;-- 设置UTL_FILE_DIR参数需要重启数据库ALTERSYSTEMSETUTL_FILE_DIR/u01/app/oracle/logminerSCOPESPFILE;-- 然后重启数据库2.2 生成数据字典文件方式如果选择将数据字典提取到操作系统文件注意需要重启数据库-- 创建数据字典文件BEGINDBMS_LOGMNR_D.BUILD(dictionary_filenamedictionary.ora,dictionary_location/u01/app/oracle/logminer);END;/[oraclehost51logminer]$ ls-ltr/u01/app/oracle/logminer-rw-r--r-- 1 oracle oinstall 41111786 Aug 6 15:47 dictionary.ora --可以看到数据字典文件形式备注如果不重启数据库报错如下 ERROR at line1: ORA-01308: initialization parameter utl_file_dirisnotsetORA-06512: atSYS.DBMS_LOGMNR_INTERNAL,line6110ORA-06512: atSYS.DBMS_LOGMNR_INTERNAL,line6200ORA-06512: atSYS.DBMS_LOGMNR_D,line12ORA-06512: at line22.3 添加待分析的日志文件使用DBMS_LOGMNR.ADD_LOGFILE过程添加需要分析的日志文件-- 第一次添加使用NEW选项清空之前的日志列表BEGINDBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME/u01/app/oracle/oradata/mydb/redo01.log,OPTIONSDBMS_LOGMNR.NEW);END;/-- 继续添加其他日志文件使用ADDFILE选项BEGINDBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME/u01/app/oracle/oradata/mydb/redo02.log,OPTIONSDBMS_LOGMNR.ADDFILE);END;/-- 添加归档日志文件BEGINDBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME/u01/app/oracle/archivelog/arch_1_12345.arc,OPTIONSDBMS_LOGMNR.ADDFILE);END;/2.4 启动LogMiner使用DBMS_LOGMNR.START_LOGMNR启动LogMiner分析会话方式一使用在线目录最常用BEGINDBMS_LOGMNR.START_LOGMNR(OPTIONSDBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);END;/方式二使用字典文件BEGINDBMS_LOGMNR.START_LOGMNR(DICTFILENAME/u01/app/oracle/logminer/dictionary.ora,OPTIONSDBMS_LOGMNR.DDL_DICT_TRACKING);END;/2.5 查询分析结果启动LogMiner后通过查询V$LOGMNR_CONTENTS视图获取解析后的日志内容-- 查看所有解析结果SELECTscn,timestamp,username,seg_owner,table_name,operation,sql_redo,sql_undoFROMv$logmnr_contentsORDERBYscn;-- 查看特定用户的操作SELECTscn,timestamp,operation,sql_redo,sql_undoFROMv$logmnr_contentsWHEREseg_ownerUSER1ORDERBYscn;-- 查看特定表的DML操作SELECTscn,timestamp,operation,sql_redo,sql_undoFROMv$logmnr_contentsWHEREseg_ownerUSER1ANDtable_nameEMPANDoperationIN(INSERT,UPDATE,DELETE)ORDERBYscn;-- 查看特定时间段的操作SELECTscn,timestamp,username,sql_redoFROMv$logmnr_contentsWHEREtimestampBETWEENTO_DATE(2026-08-01 10:00:00,YYYY-MM-DD HH24:MI:SS)ANDTO_DATE(2026-08-01 12:00:00,YYYY-MM-DD HH24:MI:SS);-- 查看DDL操作SELECTscn,timestamp,username,operation,sql_redoFROMv$logmnr_contentsWHEREoperationLIKEDDL%;-- 查看已提交的事务仅显示已提交的变更SELECTscn,timestamp,operation,sql_redo,sql_undoFROMv$logmnr_contentsWHEREcommit_scnISNOTNULL;2.6 结束LogMiner会话分析完成后关闭LogMiner以释放资源EXECDBMS_LOGMNR.END_LOGMNR;2.7 完整案例演示以下是一个完整的LogMiner使用案例步骤1创建测试表并执行操作-- 创建测试表CREATETABLEtest_logminer(id NUMBER,name VARCHAR2(50),create_timeDATE);-- 插入数据INSERTINTOtest_logminerVALUES(1,Tom,SYSDATE);INSERTINTOtest_logminerVALUES(2,Mary,SYSDATE);INSERTINTOtest_logminerVALUES(3,Mike,SYSDATE);COMMIT;-- 更新数据UPDATEtest_logminerSETnameTom_UpdatedWHEREid1;COMMIT;-- 删除数据DELETEFROMtest_logminerWHEREid3;COMMIT;-- 执行DDLALTERTABLEtest_logminerADD(statusVARCHAR2(10));步骤2强制日志切换-- 确保日志已写入归档ALTERSYSTEM SWITCH LOGFILE;ALTERSYSTEM SWITCH LOGFILE;步骤3启动LogMiner分析-- 添加当前在线日志或归档日志-- 1生成添加归档日志的执行脚本根据实际时间范围修改SELECTDISTINCTEXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME||name||, OPTIONSDBMS_LOGMNR.ADDFILE);ASadd_log_sqlFROMGV$ARCHIVED_LOGWHEREcompletion_timeBETWEENTO_DATE(2026-08-06 15:26:00,YYYY-MM-DD HH24:MI:SS)ANDTO_DATE(2026-08-06 15:29:00,YYYY-MM-DD HH24:MI:SS)ANDdest_id1ORDERBY1;-- 2在 SQL*Plus 中执行上述生成的脚本批量添加日志-- (如果是第一个文件请将 OPTIONS 改为 DBMS_LOGMNR.NEW)EXECDBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME/oradata/orcl/fast_recovery_area/ORCL/archivelog/2026_08_06/o1_mf_1_8_o78fyb2g_.arc,OPTIONSDBMS_LOGMNR.new);EXECDBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME/oradata/orcl/fast_recovery_area/ORCL/archivelog/2026_08_06/o1_mf_1_9_o78fybk4_.arc,OPTIONSDBMS_LOGMNR.ADDFILE);-- 启动LogMiner使用在线数据字典BEGINDBMS_LOGMNR.START_LOGMNR(OPTIONSDBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);END;/步骤4查询分析结果-- 查看测试表的所有变更SELECTscn,TO_CHAR(timestamp,YYYY-MM-DD HH24:MI:SS)ASop_time,operation,sql_redo,sql_undoFROMv$logmnr_contentsWHEREseg_ownerUSERANDtable_nameTEST_LOGMINERORDERBYscn;输出示例SCN OP_TIME OPERATION ---------- ------------------- -------------------------------- SQL_REDO -------------------------------------------------------------------------------- SQL_UNDO -------------------------------------------------------------------------------- 1155998 2026-08-06 15:57:39 DDL CREATE TABLE test_logminer ( id NUMBER, name VARCHAR2(50), create_time DATE ); 1156010 2026-08-06 15:57:44 INSERT insert into USER1.TEST_LOGMINER(COL 1,COL 2,COL 3) values (HEXTORAW(c 102),HEXTORAW(546f6d),HEXTORAW(787e0806103a2d)); delete from USER1.TEST_LOGMINER where COL 1 HEXTORAW(c102) and COL 2 HEXTORAW(546f6d) and COL 3 HEXTORAW(787e0806103a2d) and ROWID AAAV XvAAFAAAACnAAA; ......步骤5结束会话EXECDBMS_LOGMNR.END_LOGMNR;2.8 典型应用场景场景一误操作数据恢复当用户误删除或误更新数据时可通过LogMiner找到对应的SQL_UNDO语句执行回退-- 找到误操作对应的UNDO SQLSELECTsql_undo,timestamp,usernameFROMv$logmnr_contentsWHEREseg_ownerSCOTTANDtable_nameEMPANDoperationDELETEANDtimestampSYSDATE-1/24;-- 最近1小时场景二数据库变更审计追踪特定用户或特定时间段的所有数据变更SELECTusername,operation,table_name,TO_CHAR(timestamp,YYYY-MM-DD HH24:MI:SS)ASop_time,sql_redoFROMv$logmnr_contentsWHEREusernameAPP_USERANDtimestampBETWEENTO_DATE(2025-08-01 00:00:00,YYYY-MM-DD HH24:MI:SS)ANDTO_DATE(2025-08-01 23:59:59,YYYY-MM-DD HH24:MI:SS)ORDERBYtimestamp;场景三分析数据增长模式通过分析INSERT操作了解数据增长趋势SELECTTO_CHAR(timestamp,YYYY-MM-DD)ASday,COUNT(*)ASinsert_count,seg_owner,table_nameFROMv$logmnr_contentsWHEREoperationINSERTANDtimestampSYSDATE-30GROUPBYTO_CHAR(timestamp,YYYY-MM-DD),seg_owner,table_nameORDERBYdayDESC; Oracle LogMiner日志挖掘操作总结
Oracle LogMiner日志挖掘操作总结一、工具介绍1.1 什么是LogMinerOracle LogMiner 是 Oracle 从 8i 版本开始提供的一个极其强大且免费的内置分析工具。它通过一组 PL/SQL 包DBMS_LOGMNR和DBMS_LOGMNR_D和动态视图如V$LOGMNR_CONTENTS将 Oracle 重做日志Online Redo Logs和归档日志Archived Logs中晦涩难懂的底层二进制数据转化为人类可读的 SQL 语句。LogMiner工具既可以用来分析在线日志也可以分析离线日志文件既可以分析自身数据库的重做日志文件也可以用来分析其他数据库的重做日志文件。1.2 LogMiner的作用LogMiner的四大作用场景说明数据变更审计精准追踪DML操作INSERT/UPDATE/DELETE的执行时间、用户及影响范围误操作数据恢复当发生错误的DELETE或UPDATE且超出了 Flashback Query 的保留窗口时通过提取SQL_UNDO语句进行精确回滚。DDL 变更回溯找回被意外删除或修改的存储过程、表结构等元数据。性能与容量分析分析哪些表在特定时间段内被频繁修改为数据库调优和扩容提供历史依据1.3 LogMiner的主要组件LogMiner分析工具实际上是由一组PL/SQL包和动态视图组成。核心PL/SQL包包名用途DBMS_LOGMNR核心分析包用于添加日志文件、启动/停止LogMiner分析DBMS_LOGMNR_D辅助包用于创建和管理数据字典文件这两个包分别由以下脚本创建-- 以SYS用户执行$ORACLE_HOME/rdbms/admin/dbmslm.sql-- 创建DBMS_LOGMNR包$ORACLE_HOME/rdbms/admin/dbmslmd.sql-- 创建DBMS_LOGMNR_D包核心动态视图视图说明V$LOGMNR_DICTIONARY显示用于决定对象ID名称的字典文件信息V$LOGMNR_PARAMETERS查询当前LogMiner设定的参数V$LOGMNR_LOGS在LogMiner启动时显示待分析的日志列表V$LOGMNR_CONTENTS最重要的视图包含解析后的日志内容注意事项在 19c 多租户CDB架构下LogMiner 必须在CDB 根容器下执行在 PDB 中执行会报错。所有操作必须在同一个数据库会话Session中连续执行。如果断开重连V$LOGMNR_CONTENTS视图中的结果将丢失。1.4 数据字典选项当LogMiner分析重做数据时需要一个数据字典将日志中的对象ID转换为可读的表名和列名。如果没有数据字典LogMiner解释出来的语句中关于数据字典的部分如表名、列名等都将是16进制的形式无法直接理解。LogMiner提供了三种使用数据字典的方式方式说明命令在线目录Online Catalog使用当前数据库的在线数据字典必须在源数据库执行DBMS_LOGMNR.START_LOGMNR(optionsDBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)提取到重做日志将字典提取到重做日志流中要求数据库处于ARCHIVELOG模式且为OPEN状态DBMS_LOGMNR_D.BUILD(optionsDBMS_LOGMNR_D.STORE_IN_REDO_LOGS)提取到操作系统文件将字典导出为文本文件需设置UTL_FILE_DIR参数DBMS_LOGMNR_D.BUILD(filename,directory,DBMS_LOGMNR_D.STORE_IN_FLAT_FILE)方式对比在线目录最简单无需额外步骤但只能分析当前数据库的日志提取到重做日志适合长期归档分析字典与日志一起归档提取到文件适合跨数据库分析但需要配置UTL_FILE_DIR并重启数据库1.5关键前置条件补充日志 (Supplemental Logging)在使用 LogMiner 进行数据恢复前强烈建议开启最小补充日志。未开启时Oracle 仅记录恢复数据块所需的最小信息。对于UPDATE操作LogMiner 可能无法获取修改前的旧值SQL_UNDO中的旧值可能为NULL且WHERE条件可能依赖物理地址ROWID导致生成的回滚 SQL 无法在其他环境执行。开启后Oracle 会在重做日志中记录足够的列信息如主键或修改前的值确保生成的SQL_REDO和SQL_UNDO逻辑完整、准确可用。-- 启用最小补充日志 ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; -- 更全面的补充日志推荐 ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; -- 验证是否启用 SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all FROM v$database; SUPPLEMENTAL_LOG_DATA_MIN应返回 YES 或 IMPLICIT二、简单使用案例1、补充日志开启确认 SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all FROM v$database; SUPPLEME SUP SUP SUP SUP -------- --- --- --- --- YES YES NO NO NO 2、做一些测试操作 [oraclehost51 ~]$ sqlplus user1/user1 SQL select count(*) from emp; SQL select count(*) from dept; COUNT(*) ---------- 4 SQL insert into emp select * from scott.emp; 14 rows created. SQL commit; Commit complete. SQL alter system archive log current; System altered. SQL delete from dept; 4 rows deleted. SQL commit; Commit complete. SQL alter system archive log current; System altered. SQL create table tab1 as select * from dba_users; Table created. SQL select to_char(sysdate,yyyymmdd hh24:mi:ss) from dual; TO_CHAR(SYSDATE, ----------------- 20260806 15:28:06 3、指定 LogMiner 数据字典这里我们选择直接从在线目录获取 EXEC DBMS_LOGMNR_D.BUILD(OPTIONS DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); 4、添加需要挖掘的日志文件 -- 1生成添加归档日志的执行脚本根据实际时间范围修改 SELECT DISTINCT EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME || name || , OPTIONSDBMS_LOGMNR.ADDFILE); AS add_log_sql FROM GV$ARCHIVED_LOG WHERE completion_time BETWEEN TO_DATE(2026-08-06 15:26:00, YYYY-MM-DD HH24:MI:SS) AND TO_DATE(2026-08-06 15:29:00, YYYY-MM-DD HH24:MI:SS) AND dest_id 1 ORDER BY 1; -- 2在 SQL*Plus 中执行上述生成的脚本批量添加日志 -- (如果是第一个文件请将 OPTIONS 改为 DBMS_LOGMNR.NEW) 具体如下 EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME/oradata/orcl/fast_recovery_area/ORCL/archivelog/2026_08_06/o1_mf_1_6_o78ftzoz_.arc, OPTIONSDBMS_LOGMNR.new); EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME/oradata/orcl/fast_recovery_area/ORCL/archivelog/2026_08_06/o1_mf_1_7_o78fvd9b_.arc, OPTIONSDBMS_LOGMNR.ADDFILE); 5、启动 LogMiner 会话 EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); 6、查询挖掘结果 --由于视图数据量可能极大且会话已断开就会丢失视图里面的内容建议先将结果存入临时表再进行查询分析 CREATE TABLE t_logminer_res AS SELECT scn,timestamp, operation, table_name, sql_redo, sql_undo FROM v$logmnr_contents WHERE seg_owner USER1 ORDER BY 1; 查询临时表类似如下 SQL select * from t_logminer_res; SCN TIMESTAMP OPERATION ---------- ------------ -------------------------------- TABLE_NAME -------------------------------- SQL_REDO -------------------------------------------------------------------------------- SQL_UNDO -------------------------------------------------------------------------------- 1126344 06-AUG-26 INSERT EMP insert into USER1.EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,D EPTNO) values (7369,SMITH,CLERK,7902,TO_DATE(17-DEC-80, DD-MON-RR), 800,NULL,20); delete from USER1.EMP where EMPNO 7369 and ENAME SMITH and JOB CLERK and MGR 7902 and HIREDATE TO_DATE(17-DEC-80, DD-MON-RR) and SAL 800 and COMM IS NULL and DEPTNO 20 and ROWID AAAVVUAAF AAAACDAAA; ...... 根据找到对应的 SQL_UNDO 或原始的 CREATE/REPLACE PROCEDURE 语句后交由开发人员确认无误即可恢复存储过程 7、结束 LogMiner 会话 分析完成后务必结束会话以释放 PGA/SGA 内存资源 EXEC DBMS_LOGMNR.END_LOGMNR; 至此简单测试案例完成三、使用案例详细讲解3.1 环境准备与配置步骤1确认补充日志已启用补充日志Supplemental Logging必须在生成待分析的重做日志之前启用否则LogMiner挖掘的一些信息无法正常显示。-- 查看补充日志状态SELECTsupplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,supplemental_log_data_fk,supplemental_log_data_allFROMv$database;-- 如果返回结果为NO执行以下命令启用ALTERDATABASEADDSUPPLEMENTAL LOGDATA;-- 更全面的补充日志推荐ALTERDATABASEADDSUPPLEMENTAL LOGDATA(PRIMARYKEY)COLUMNS;步骤2确认/安装LogMiner包-- 检查DBMS_LOGMNR包是否存在SELECTowner,object_name,object_typeFROMdba_objectsWHEREobject_nameLIKEDBMS_LOGMNR%;-- 如果不存在以SYS用户执行安装脚本$ORACLE_HOME/rdbms/admin/dbmslm.sql$ORACLE_HOME/rdbms/admin/dbmslmd.sql步骤3设置数据字典目录如使用文件方式-- 创建目录CREATEDIRECTORY logminer_dirAS/u01/app/oracle/logminer;-- 设置UTL_FILE_DIR参数需要重启数据库ALTERSYSTEMSETUTL_FILE_DIR/u01/app/oracle/logminerSCOPESPFILE;-- 然后重启数据库2.2 生成数据字典文件方式如果选择将数据字典提取到操作系统文件注意需要重启数据库-- 创建数据字典文件BEGINDBMS_LOGMNR_D.BUILD(dictionary_filenamedictionary.ora,dictionary_location/u01/app/oracle/logminer);END;/[oraclehost51logminer]$ ls-ltr/u01/app/oracle/logminer-rw-r--r-- 1 oracle oinstall 41111786 Aug 6 15:47 dictionary.ora --可以看到数据字典文件形式备注如果不重启数据库报错如下 ERROR at line1: ORA-01308: initialization parameter utl_file_dirisnotsetORA-06512: atSYS.DBMS_LOGMNR_INTERNAL,line6110ORA-06512: atSYS.DBMS_LOGMNR_INTERNAL,line6200ORA-06512: atSYS.DBMS_LOGMNR_D,line12ORA-06512: at line22.3 添加待分析的日志文件使用DBMS_LOGMNR.ADD_LOGFILE过程添加需要分析的日志文件-- 第一次添加使用NEW选项清空之前的日志列表BEGINDBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME/u01/app/oracle/oradata/mydb/redo01.log,OPTIONSDBMS_LOGMNR.NEW);END;/-- 继续添加其他日志文件使用ADDFILE选项BEGINDBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME/u01/app/oracle/oradata/mydb/redo02.log,OPTIONSDBMS_LOGMNR.ADDFILE);END;/-- 添加归档日志文件BEGINDBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME/u01/app/oracle/archivelog/arch_1_12345.arc,OPTIONSDBMS_LOGMNR.ADDFILE);END;/2.4 启动LogMiner使用DBMS_LOGMNR.START_LOGMNR启动LogMiner分析会话方式一使用在线目录最常用BEGINDBMS_LOGMNR.START_LOGMNR(OPTIONSDBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);END;/方式二使用字典文件BEGINDBMS_LOGMNR.START_LOGMNR(DICTFILENAME/u01/app/oracle/logminer/dictionary.ora,OPTIONSDBMS_LOGMNR.DDL_DICT_TRACKING);END;/2.5 查询分析结果启动LogMiner后通过查询V$LOGMNR_CONTENTS视图获取解析后的日志内容-- 查看所有解析结果SELECTscn,timestamp,username,seg_owner,table_name,operation,sql_redo,sql_undoFROMv$logmnr_contentsORDERBYscn;-- 查看特定用户的操作SELECTscn,timestamp,operation,sql_redo,sql_undoFROMv$logmnr_contentsWHEREseg_ownerUSER1ORDERBYscn;-- 查看特定表的DML操作SELECTscn,timestamp,operation,sql_redo,sql_undoFROMv$logmnr_contentsWHEREseg_ownerUSER1ANDtable_nameEMPANDoperationIN(INSERT,UPDATE,DELETE)ORDERBYscn;-- 查看特定时间段的操作SELECTscn,timestamp,username,sql_redoFROMv$logmnr_contentsWHEREtimestampBETWEENTO_DATE(2026-08-01 10:00:00,YYYY-MM-DD HH24:MI:SS)ANDTO_DATE(2026-08-01 12:00:00,YYYY-MM-DD HH24:MI:SS);-- 查看DDL操作SELECTscn,timestamp,username,operation,sql_redoFROMv$logmnr_contentsWHEREoperationLIKEDDL%;-- 查看已提交的事务仅显示已提交的变更SELECTscn,timestamp,operation,sql_redo,sql_undoFROMv$logmnr_contentsWHEREcommit_scnISNOTNULL;2.6 结束LogMiner会话分析完成后关闭LogMiner以释放资源EXECDBMS_LOGMNR.END_LOGMNR;2.7 完整案例演示以下是一个完整的LogMiner使用案例步骤1创建测试表并执行操作-- 创建测试表CREATETABLEtest_logminer(id NUMBER,name VARCHAR2(50),create_timeDATE);-- 插入数据INSERTINTOtest_logminerVALUES(1,Tom,SYSDATE);INSERTINTOtest_logminerVALUES(2,Mary,SYSDATE);INSERTINTOtest_logminerVALUES(3,Mike,SYSDATE);COMMIT;-- 更新数据UPDATEtest_logminerSETnameTom_UpdatedWHEREid1;COMMIT;-- 删除数据DELETEFROMtest_logminerWHEREid3;COMMIT;-- 执行DDLALTERTABLEtest_logminerADD(statusVARCHAR2(10));步骤2强制日志切换-- 确保日志已写入归档ALTERSYSTEM SWITCH LOGFILE;ALTERSYSTEM SWITCH LOGFILE;步骤3启动LogMiner分析-- 添加当前在线日志或归档日志-- 1生成添加归档日志的执行脚本根据实际时间范围修改SELECTDISTINCTEXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME||name||, OPTIONSDBMS_LOGMNR.ADDFILE);ASadd_log_sqlFROMGV$ARCHIVED_LOGWHEREcompletion_timeBETWEENTO_DATE(2026-08-06 15:26:00,YYYY-MM-DD HH24:MI:SS)ANDTO_DATE(2026-08-06 15:29:00,YYYY-MM-DD HH24:MI:SS)ANDdest_id1ORDERBY1;-- 2在 SQL*Plus 中执行上述生成的脚本批量添加日志-- (如果是第一个文件请将 OPTIONS 改为 DBMS_LOGMNR.NEW)EXECDBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME/oradata/orcl/fast_recovery_area/ORCL/archivelog/2026_08_06/o1_mf_1_8_o78fyb2g_.arc,OPTIONSDBMS_LOGMNR.new);EXECDBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME/oradata/orcl/fast_recovery_area/ORCL/archivelog/2026_08_06/o1_mf_1_9_o78fybk4_.arc,OPTIONSDBMS_LOGMNR.ADDFILE);-- 启动LogMiner使用在线数据字典BEGINDBMS_LOGMNR.START_LOGMNR(OPTIONSDBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);END;/步骤4查询分析结果-- 查看测试表的所有变更SELECTscn,TO_CHAR(timestamp,YYYY-MM-DD HH24:MI:SS)ASop_time,operation,sql_redo,sql_undoFROMv$logmnr_contentsWHEREseg_ownerUSERANDtable_nameTEST_LOGMINERORDERBYscn;输出示例SCN OP_TIME OPERATION ---------- ------------------- -------------------------------- SQL_REDO -------------------------------------------------------------------------------- SQL_UNDO -------------------------------------------------------------------------------- 1155998 2026-08-06 15:57:39 DDL CREATE TABLE test_logminer ( id NUMBER, name VARCHAR2(50), create_time DATE ); 1156010 2026-08-06 15:57:44 INSERT insert into USER1.TEST_LOGMINER(COL 1,COL 2,COL 3) values (HEXTORAW(c 102),HEXTORAW(546f6d),HEXTORAW(787e0806103a2d)); delete from USER1.TEST_LOGMINER where COL 1 HEXTORAW(c102) and COL 2 HEXTORAW(546f6d) and COL 3 HEXTORAW(787e0806103a2d) and ROWID AAAV XvAAFAAAACnAAA; ......步骤5结束会话EXECDBMS_LOGMNR.END_LOGMNR;2.8 典型应用场景场景一误操作数据恢复当用户误删除或误更新数据时可通过LogMiner找到对应的SQL_UNDO语句执行回退-- 找到误操作对应的UNDO SQLSELECTsql_undo,timestamp,usernameFROMv$logmnr_contentsWHEREseg_ownerSCOTTANDtable_nameEMPANDoperationDELETEANDtimestampSYSDATE-1/24;-- 最近1小时场景二数据库变更审计追踪特定用户或特定时间段的所有数据变更SELECTusername,operation,table_name,TO_CHAR(timestamp,YYYY-MM-DD HH24:MI:SS)ASop_time,sql_redoFROMv$logmnr_contentsWHEREusernameAPP_USERANDtimestampBETWEENTO_DATE(2025-08-01 00:00:00,YYYY-MM-DD HH24:MI:SS)ANDTO_DATE(2025-08-01 23:59:59,YYYY-MM-DD HH24:MI:SS)ORDERBYtimestamp;场景三分析数据增长模式通过分析INSERT操作了解数据增长趋势SELECTTO_CHAR(timestamp,YYYY-MM-DD)ASday,COUNT(*)ASinsert_count,seg_owner,table_nameFROMv$logmnr_contentsWHEREoperationINSERTANDtimestampSYSDATE-30GROUPBYTO_CHAR(timestamp,YYYY-MM-DD),seg_owner,table_nameORDERBYdayDESC;