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;