LogMiner日志挖掘

一个典型的LogMiner的操作包含如下步骤： 1、进行初始化设置：开启附加日志，设置LogMiner的表空间，设置UTL_FILE_DIR静态参数的值 2、提取一个字典：将字典文件直接使用Online Catalog（看一），提取为Flat File（看二）或Redo日志（看三） 3、指定需要分析的Redo日志文件：利用DBMS_LOGMNR.ADD_LOGFILE来添加日志； 4、开始LogMiner：执行DBMS_LOGMNR.START_LOGMNR来启动LogMiner； 5、查询V$LOGMNR_CONTENTS视图； 6、结束LogMiner：通过执行EXECUTE DBMS_LOGMNR.END_LOGMNR来结束分析。 默认情况下，Oracle 10g以上已经安装了LogMiner工具。若是没有安装，则可以运行下面两个脚本： $ORACLE_HOME/rdbms/admin/dbmslm.sql $ORACLE_HOME/rdbms/admin/dbmslmd.sql 一、没开归档的情况下，直接使用online catalog LogMiner 1.开启补充日志 SQL> alter database add supplemental log data;    2.查看日志组 SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/xdb/redo01.log /u01/app/oracle/oradata/xdb/redo02.log /u01/app/oracle/oradata/xdb/redo03.log 3.LogMiner日志组，注意第一个要写new，后面的写addfile SQL> execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/xdb/redo01.log',dbms_logmnr.new); PL/SQL procedure successfully completed. SQL> execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/xdb/redo02.log',dbms_logmnr.addfile); PL/SQL procedure successfully completed. SQL> execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/xdb/redo03.log',dbms_logmnr.addfile); PL/SQL procedure successfully completed. 开始挖掘，使用online_catalog SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only); PL/SQL procedure successfully completed. When you specify the COMMITTED_DATA_ONLY option, LogMiner groups together all DML operations that belong to the same transaction. Transactions are returned in the order in which they were committed. 4.验证如下 SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name='T1'; SQL_REDO -------------------------------------------------------------------------------- SQL_UNDO -------------------------------------------------------------------------------- ALTER TABLE "T1"."T1" RENAME TO "BIN$s9/jzkW9CjvgUzwFqMAF1Q==$0" ; drop table t1 AS "BIN$s9/jzkW9CjvgUzwFqMAF1Q==$0" ; select sql_redo,sql_undo from v$logmnr_contents where table_name='T1' and OPERATION='DROP'; 二、开启归档的情况下，提取字典为Flat File 1.开启补全日志 SQL> alter database add supplemental log data; 2.找出需要挖掘的归档日志文件路径 SQL>select name from v$ARCHIVED_LOG WHERE FIRST_TIME BETWEEN TO_DATE('2020-11-12 14:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2020-11-12 14:40:00','yyyy-mm-dd hh24:mi:ss'); /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_84_1056076841.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_85_1056076841.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_86_1056076841.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_87_1056076841.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_88_1056076841.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_89_1056076841.dbf 3.将字典文件提取为一个Flat File（平面文件） SQL> alter system set utl_file_dir='/home/oracle' scope=spfile; SQL> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora','/home/oracle',DBMS_LOGMNR_D.STORE_IN_FLAT_FILE); PL/SQL procedure successfully completed. 4.添加归档日志 SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_84_1056076841.dbf',DBMS_LOGMNR.NEW); SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_85_1056076841.dbf',DBMS_LOGMNR.ADDFILE); PL/SQL procedure successfully completed. SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_86_1056076841.dbf',DBMS_LOGMNR.ADDFILE); PL/SQL procedure successfully completed. SQL>  EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_88_1056076841.dbf',DBMS_LOGMNR.ADDFILE); PL/SQL procedure successfully completed. SQL>  EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_88_1056076841.dbf',DBMS_LOGMNR.ADDFILE); BEGIN DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_88_1056076841.dbf',DBMS_LOGMNR.ADDFILE); END; * ERROR at line 1: ORA-01289: cannot add duplicate logfile /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_88_1056076841.dbf ORA-06512: at "SYS.DBMS_LOGMNR", line 68 ORA-06512: at line 1 SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_87_1056076841.dbf',DBMS_LOGMNR.ADDFILE); PL/SQL procedure successfully completed. SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_89_1056076841.dbf',DBMS_LOGMNR.ADDFILE); 5.查看添加的日志列表 SQL> SELECT FILENAME FROM V$LOGMNR_LOGS; FILENAME -------------------------------------------------------------------------------- /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_84_1056076841.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_85_1056076841.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_86_1056076841.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_87_1056076841.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_88_1056076841.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_89_1056076841.dbf 6.开始挖掘 SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>'/home/oracle/dictionary.ora'); PL/SQL procedure successfully completed. 7.查看挖掘并保存所有结果到指定表中 SQL>SELECT (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,    USERNAME, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE USERNAME != 'SYS'    AND SEG_OWNER IS NULL OR SEG_OWNER NOT IN ('SYS', 'SYSTEM'); SQL> create table logminer_tab as select * from v$logmnr_contents; 8.结束LogMiner SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR; 三、抽取字典到redo日志文件中 1.开启补全日志 SQL> alter database add supplemental log data; 2.找出需要挖掘的归档日志文件路径 SQL>select name from v$ARCHIVED_LOG WHERE FIRST_TIME BETWEEN TO_DATE('2020-11-12 14:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2020-11-12 14:40:00','yyyy-mm-dd hh24:mi:ss'); /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_84_1056076841.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_85_1056076841.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_86_1056076841.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_87_1056076841.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_88_1056076841.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_89_1056076841.dbf 3.抽取字典到redo中 SQL>EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); PL/SQL procedure successfully completed. 4.LogMiner添加归档日志文件 SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_85_1056076841.dbf',DBMS_LOGMNR.ADDFILE); PL/SQL procedure successfully completed. SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_86_1056076841.dbf',DBMS_LOGMNR.ADDFILE); PL/SQL procedure successfully completed. 5.查看添加的日志列表 SQL> select filename from v$logmnr_logs; 6.开始挖掘 SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); PL/SQL procedure successfully completed. 7.获取挖掘结果 SELECT (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,    USERNAME, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE USERNAME != 'SYS'    AND SEG_OWNER IS NULL OR SEG_OWNER NOT IN ('SYS', 'SYSTEM'); SQL> create table logminer_tab as select * from v$logmnr_contents; 8.结束LogMiner SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;