1、譽天教育官網：oracle 日志分析工具 LogMiner 使用1.設置日期格式alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile; select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual ;2.添加補充日志Redo log files are generally used for instance recovery and media recovery. The data needed for such opera

2、tions is automatically recorded in the redo log files. However, a redo-based application may require that additional columns be logged in the redo log files. The process of logging these additional columns is called supplemental logging.By default, Oracle Database does not provide any supplemental l

3、ogging, which means that by default LogMiner is not usable. Therefore, you must enable at least minimal supplemental logging before generating log files which will be analyzed by LogMiner.如果數據庫需要使用 logminer，就應該添加,只有添加這個日志之后的才能捕獲 DML了mink，Oracle 在文檔Doc ID: Note:291574.1 中對這個問題進行了詳細說明，如果希望 LOGMNR 可以得到

4、，應該設置 SUPPLEMENTAL LOG DATA PRIMARYKEY 和 UNIQUE INDEX，這樣 Oracle 才能確保 LOGMNR 可以獲取 SQL 語句：SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UIFROM V$DATABASE;SUP SUP- - NO NOSQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUEINDEX) COLUMNS;數據庫已更改。SQL> SELECT SUPPLEMEN

5、TAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UIFROM V$DATABASE;SUP SUP- - YES YES譽天IT 認證站：譽天教育官網：3.開啟歸檔 ,如果沒有歸檔，只能看 online log 的信息檢查是否歸檔SQL> archive log list ; Database log mode Automatic archival Archive destinationOldest online log sequence Current log sequence開啟歸檔模式No Archive Mode DisabledUSE_DB_REC

6、OVERY_FILE_DEST 46shutdown immediate startup mountalter database archivelog;alter database open;在次檢查SQL> archive log list ; Database log mode Automatic archival Archive destinationOldest online log sequence Next log sequence to archiveCurrent log sequenceArchive Mode EnabledUSE_DB_RECOVERY_FILE_D

7、EST 4664.配置 LogMiner 工具要安裝 LogMiner 工具，必須首先要運行下面這樣兩個必須均以 SYS 用分析日志文件。第，這兩個戶運行。其中第一個用來創建 DBMS_LOGMNR 包，該二個用來創建 DBMS_LOGMNR_D 包，該desc DBMS_LOGMNR desc DBMS_LOGMNR_D創建數據字典文件。注意： 沒有就需要跑下面的創建包！$ORACLE_HOME/rdbms/admin/dbmslmd.sql$ORACLE_HOME/rdbms/admin/dbmslm.sql譽天IT 認證站：譽天教育官網：$ORACLE_HOME/rdbms/admin/

8、dbmslm.sq $ORACLE_HOME/rdbms/admin/dbmslmd.sql程序包已創建。5.使用 LogMiner 工具5.1、設置參數 UTL_FILE_DIR數據字典文件是一個文本文件，使用包 DBMS_LOGMNR_D 來創建。如果我們要分析的數據庫中的表有變化，影響到庫的數據字典也發生變化，這時就需要重新創建該字典文件。另 外一種情況是在分析另外一個數據庫文件的重作日志時，也必須要重新生成一遍被分析數據庫的數據字典文件。在 ORACLE8I 的時候，首先在 init.ora 初始化參數文件中，指定數據字典文件的位置，也就是添加一個參數 UTL_FILE_DIR，該參數

9、值為服務器中放置數據字典文件的目錄。如：UTL_FILE_DIR = (/tmp)ORACLE9I 后，推薦使用 SPFILE 啟動，可以動態調整參數；SQL> show parameter spfile;NAMETYPEVALUE- - -+DATA/orcl/spfileorcl.ora- - - - - - - - - - - - - - - -spfilestringSQL> alter system set utl_file_dir='/tmp' scope=spfile;System altered系統已更改。重啟生效Shutdown immediate

10、 ; StartupSQL> show parameter utl_file_dir;NAME- - - -utl_file_dirTYPE- - - - - - - -VALUE- - - - - -string/tmp譽天IT 認證站：譽天教育官網：SQL>5.2 創建數據字典文件SQL> begindbms_logmnr_d.build('testdict.data','/tmp'); end;/PL/SQL procedure successfully completed.host ls -lh /tmp5.3 創建要分析的日志文件列表

11、Oracle 的重作日志分為兩種，（online）和離線（offline）歸檔日志文件，我這里主要分析歸檔日志，日志原理一樣。-（online）SQL> select GROUP# ,SEQUENCE# ,STATUSfrom v$log;GROUP#SEQUENCE# STATUS- - - -1- - -4 INACTIVE25 INACTIVESQL> select member from v$logfile ;MEMBER- - - - - - - - - - - - - - - - - - - -+DATA/orcl/onlinelog/group_2.262.84232

12、5115+FRA/orcl/onlinelog/group 2.258.842325123+DATA/orcl/onlinelog/group_1.261.842325105+FRA/orcl/onlinelog/group_1.257.842325111譽天IT 認證站：+DATA/orcl/onlinelog/group_3.263.842325127+FRA/orcl/onlinelog/group_3.259.84232513336 CURRENT譽天教育官網：現在做任何操作都是的事物日志原來的值到第二個日志組的日志文件比如：SQL> select ename ,sal from

13、 scott.emp ;ENAME- - SMITH ALLEN WARDJONESSAL-800160012502975125028502450MARTIN BLAKECLARKSQL> update scott.emp set sal=0;14 rows updated.SQL> commit;Commit complete.該事物的日志寫入了第三日志組A.創建列表SQL> execdbms_logmnr.add_logfile('+DATA/orcl/onlinelog/group_3.263.842325127',dbms_logmnr.);PL/SQ

14、L procedure successfully completed. B.添加另外的日志文件到列表SQL>execute dbms_logmnr.add_logfile(options=>dbms_logmnr,logfilename=>'+DATA/orcl/onlinelog/group_1.261.808534813');SQL> execute dbms_logmnr.add_logfile(options=>dbms_logmnr.a,logfilename=>'+DATA/orcl/onlinelog/group_2.2

15、62.808534823');譽天IT 認證站：ddfile.addfilenew譽天教育官網：/#說明：dbms_logmnr.new -用于建一個日志分析表dbms_logmnr.addfile -用于加，入用于分析的日志文件dbms_logmnr.removefile -用于移出，用于分析的日志文件刪除execute dbms_logmnr.add_logfile(options =>dbms_logmnr.removefile,logfilename=>'+DATA/orcl/onlinelog/group_2.262.808534823');查看日

16、志文件列表:select db_name, thread_sqn,filename from v$logmnr_logsSQL> /DB_NAME- - - - ORCLTHREAD_SQN FILENAME- - - - - - - - - - - -6 +DATA/orcl/onlinelog/group_3.263.842325127-離線（offline）歸檔日志文件 歸檔日志BEGINdbms_logmnr.add_logfile('+fra/orcl/archivelog/2014_03_16/ thread_1_seq_6.260.842331263', D

17、BMS_LOGMNR.new );END;5.4 啟動 LogMiner 進行分析5.4.1條件SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/tmp/testdict.data') ;譽天IT 認證站：譽天教育官網：PL/SQL procedure successfully completed.5.4.2 限制條件BEGINdbms_logmnr.start_logmnr( dictfilename => '/tmp/testdict.data',StartTime => to_da

18、te('2011-02-18 16:40:26','YYYY-MM-DD HH24:MI:SS'), EndTime => to_date('2011-02-18 16:44:41','YYYY-MM-DD HH24:MI:SS '); END;/5.5 觀察分析結果（v$logmnr_contents）到現在為止，我們已經分析得到了重作日志文件中的內容。動態性能視圖包含 LogMiner 分析得到的所有的信息。SELECT sql_redo FROM v$logmnr_contents;SELECT sql_redo FR

19、OM v$logmnr_contents where seg_name='EMP' SELECT sql_undo FROM v$logmnr_contents where seg_name='EMP'SELECT sql_redo FROM v$logmnr_contents where username='scott'and seg_name='scott.t1' and upper(operation)='delete'SELECT sql_redo FROM v$logmnr_contents where

20、seg_name='t1' andupper(operation)='delete'SELECT sql_redo FROM v$logmnr_contents where username='SYS' andtable_name='T1'6.關閉 LogMiner可以把 v$logmnr_contents 視圖的內容創建一個的數據庫表將非常有幫助sql> create table logmnr_contents as select * from v$logmnr_contents;當完成了重做日志的檢查，運行 dbms_l

21、ogmnr 中的 end_logmnr execute dbms_logmnr.end_logmnr();譽天IT 認證站：譽天教育官網：建議做一次轉存(練習)準備工作：開始補全日志SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UIFROM V$DATABASE;SUP SUP- - NO NOSQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUEINDEX) COLUMNS;數據庫已更改。SQL> SELECT SUPPLE

22、MENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UIFROM V$DATABASE;SUP SUP- - YES YES開啟歸檔模式Archive log list 查看 如果shutdown immediate startup mountalter database archivelog;alter database open;開啟歸檔創建挖掘的數據字典目錄SQL> alter system set utl_file_dir='/tmp' scope=spfile;譽天IT 認證站：譽天教育官網：System altered系統已更改

23、。重啟生效Shutdown immediate ; StartupSQL> show parameter utl_file_dir;NAME- - - - - - - - - -utl_file_dir SQL>TYPE- - - - - - - -VALUE-string/tmp創建數據字典文件SQL> begindbms_logmnr_d.build('testdict.data','/tmp'); end;/PL/SQL procedure successfully completed.host ls -lh /tmp1 scott 登陸

24、 修改表的SQL> conn scott/oracle Connected.SQL> update scott.emp set sal=0;14 rows updated.SQL> commit;Commit complete.2 查出當前的日志組SQL> set linesize 100 SQL> lselect GROUP#,THREAD#,SEQUENCE#,STATUS ,FIRST_CHANGE#,FIRST_TIMEfrom v$log譽天IT 認證站：譽天教育官網：SQL> /GROUP#THREAD#SEQUENCE# STATUSFIRST

25、_CHANGE# FIRST_TIME-1-17 INACTIVE1045167 2013-02-28 01:33:353當前日志組是 216 INACTIVE1021200 2013-02-28 01:13:09在查 2 號日志組的日志文件名SQL>select MEMBERfrom v$logfile where group#=2;MEMBER+DATA/orcl/onlinelog/group_2.262.808534823+FRA/orcl/onlinelog/group_2.258.808534829添加日志exec dbms_logmnr.add_logfile('+

26、DATA/orcl/onlinelog/group_2.262.808534823',dbms_logmnr.ne w);PL/SQL procedure successfully completed. 你可以檢查你對上面日志文件進行挖掘select db_name, thread_sqn,filename from v$logmnr_logs;DB_NAME THREAD_SQN-FILENAME-ORCL8+DATA/orcl/onlinelog/group_2.262.808534823開啟挖掘SQL> exec dbms_logmnr.(dictfilename=>

27、'/tmp/testdict.data');PL/SQL procedure successfully completed.查看結果SQL> SELECT OPERATION, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTSWHERE SEG_OWNER = 'SCOTT' AND SEG_NAME = 'EMP' ANDUSERNAME = 'SCOTT'譽天IT 認證站：譽天教育官網：OPERATIONSQL_REDOSQL_UNDOUPDATEupdate "SCOTT&q

28、uot;."EMP" set "SAL" = '0' where "EMPNO" = '7369' and "SAL" = '800' and ROWID = 'AAAR3xAAEAAAACXAAA'update "SCOTT"."EMP" set "SAL" ='800' where "EMPNO"= '7369' and "SA

29、L"= '0' and ROWID = 'AAAR3xAAEAAAACXAAA'UPDATEOPERATIONSQL_REDOSQL_UNDOupdate "SCOTT"."EMP" set "SAL" '1600' and ROWID = 'AAAR3xAAEAAAACXAAB'='0' where "EMPNO" ='7499' and "SAL" =update "SCOTT

30、"."EMP" set "SAL" = '1600' where "EMPNO" = '7499' and "SAL"= '0' and ROWID = 'AAAR3xAA EAAAACXAAB'UPDATE譽天IT 認證站：譽天教育官網：update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = &#

31、39;7521' and "SAL" ='1250' and ROWID = 'AAAR3xAAOPERATIONSQL_REDOSQL_UNDOEAAAACXAAC'update "SCOTT"."EMP" set "SAL" = '1250' where "EMPNO" = '7521' and "SAL"= '0' and ROWID = 'AAAR3xAAEAAAACXAA

32、C'UPDATEupdate "SCOTT"."EMP" set "SAL"'2975' and ROWID = 'AAAR3xAA EAAAACXAAD'= '0' where "EMPNO" = '7566' and "SAL" =OPERATIONSQL_REDOSQL_UNDOupdate "SCOTT"."EMP" set "SAL" = '297

33、5' where "EMPNO" = '7566' and "SAL"= '0' and ROWID = 'AAAR3xAA EAAAACXAAD'UPDATE譽天IT 認證站：譽天教育官網：update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7654' and "SAL" = '1250' and

34、 ROWID = 'AAAR3xAAEAAAACXAAE'update "SCOTT"."EMP" set "SAL" = '1250' where "EMPNO" = '7654' and "SAL"= '0' and ROWID = 'AAAR3xAAOPERATIONSQL_REDOSQL_UNDOEAAAACXAAE'UPDATEupdate "SCOTT"."EMP"

35、 set "SAL"'2850' and ROWID = 'AAAR3xAA EAAAACXAAF'= '0' where "EMPNO" = '7698' and "SAL" =update "SCOTT"."EMP" set "SAL" = '2850' where "EMPNO" = '7698' and "SAL"= '0&#

36、39; and ROWID='AAAR3xAAEAAAACXAAF'OPERATIONSQL_REDOSQL_UNDOUPDATE譽天IT 認證站：譽天教育官網：update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7782' and "SAL" = '2450' and ROWID = 'AAAR3xAAEAAAACXAAG'update "SCOTT

37、"."EMP" set "SAL" = '2450' where "EMPNO" = '7782' and "SAL"= '0' and ROWID = 'AAAR3xAA EAAAACXAAG'OPERATIONSQL_REDOSQL_UNDOUPDATEupdate "SCOTT"."EMP" set "SAL"'3000' and ROWID = 'AA

38、AR3xAA EAAAACXAAH'= '0' where "EMPNO" = '7788' and "SAL" =update "SCOTT"."EMP" set "SAL" = '3000' where "EMPNO" = '7788' and "SAL"= '0' and ROWID='AAAR3xAAEAAAACXAAH'UPDATEOPERAT

39、IONSQL_REDOSQL_UNDO譽天IT 認證站：譽天教育官網：update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7839' and "SAL" = '5000' and ROWID = 'AAAR3xAAEAAAACXAAI'update "SCOTT"."EMP" set "SAL" = '5000

40、' where "EMPNO" = '7839' and "SAL"= '0' and ROWID = 'AAAR3xAA EAAAACXAAI'UPDATEupdate "SCOTT"."EMP" set "SAL" '1500' and ROWID = 'AAAR3xAA= '0' where "EMPNO" = '7844' and "SAL&quo

41、t; =OPERATIONSQL_REDOSQL_UNDOEAAAACXAAJ'update "SCOTT"."EMP" set "SAL" = '1500' where "EMPNO" = '7844' and "SAL"= '0' and ROWID = 'AAAR3xAAEAAAACXAAJ'UPDATEupdate "SCOTT"."EMP" set "SAL&quo

42、t;'1100' and ROWID = 'AAAR3xAA EAAAACXAAK'= '0' where "EMPNO" = '7876' and "SAL" =OPERATIONSQL_REDOSQL_UNDO譽天IT 認證站：譽天教育官網：update "SCOTT"."EMP" set "SAL" = '1100' where "EMPNO" = '7876' and "SAL"= '0' and ROWID = 'AAAR3xAA EAAAACXAAK'UPDATEupdate "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = &#