1、譽(yù)天教育官網(wǎng)：oracle 日志分析工具 LogMiner 使用1.設(shè)置日期格式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.添加補(bǔ)充日志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.如果數(shù)據(jù)庫(kù)需要使用 logminer，就應(yīng)該添加,只有添加這個(gè)日志之后的才能捕獲 DML了mink，Oracle 在文檔Doc ID: Note:291574.1 中對(duì)這個(gè)問題進(jìn)行了詳細(xì)說明，如果希望 LOGMNR 可以得到

4、，應(yīng)該設(shè)置 SUPPLEMENTAL LOG DATA PRIMARYKEY 和 UNIQUE INDEX，這樣 Oracle 才能確保 LOGMNR 可以獲取 SQL 語(yǔ)句：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;數(shù)據(jù)庫(kù)已更改。SQL> SELECT SUPPLEMEN

5、TAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UIFROM V$DATABASE;SUP SUP- - YES YES譽(yù)天IT 認(rèn)證站：譽(yù)天教育官網(wǎng)：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 工具，必須首先要運(yùn)行下面這樣兩個(gè)必須均以 SYS 用分析日志文件。第，這兩個(gè)戶運(yùn)行。其中第一個(gè)用來創(chuàng)建 DBMS_LOGMNR 包，該二個(gè)用來創(chuàng)建 DBMS_LOGMNR_D 包，該desc DBMS_LOGMNR desc DBMS_LOGMNR_D創(chuàng)建數(shù)據(jù)字典文件。注意： 沒有就需要跑下面的創(chuàng)建包！$ORACLE_HOME/rdbms/admin/dbmslmd.sql$ORACLE_HOME/rdbms/admin/dbmslm.sql譽(yù)天IT 認(rèn)證站：譽(yù)天教育官網(wǎng)：$ORACLE_HOME/rdbms/admin/

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

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

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

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譽(yù)天IT 認(rèn)證站：+DATA/orcl/onlinelog/group_3.263.842325127+FRA/orcl/onlinelog/group_3.259.84232513336 CURRENT譽(yù)天教育官網(wǎng)：現(xiàn)在做任何操作都是的事物日志原來的值到第二個(gè)日志組的日志文件比如：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.創(chuàng)建列表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');譽(yù)天IT 認(rèn)證站：ddfile.addfilenew譽(yù)天教育官網(wǎng)：/#說明：dbms_logmnr.new -用于建一個(gè)日志分析表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 啟動(dòng) LogMiner 進(jìn)行分析5.4.1條件SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/tmp/testdict.data') ;譽(yù)天IT 認(rèn)證站：譽(yù)天教育官網(wǎng)：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 觀察分析結(jié)果（v$logmnr_contents）到現(xiàn)在為止，我們已經(jīng)分析得到了重作日志文件中的內(nèi)容。動(dòng)態(tài)性能視圖包含 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.關(guān)閉 LogMiner可以把 v$logmnr_contents 視圖的內(nèi)容創(chuàng)建一個(gè)的數(shù)據(jù)庫(kù)表將非常有幫助sql> create table logmnr_contents as select * from v$logmnr_contents;當(dāng)完成了重做日志的檢查，運(yùn)行 dbms_l

21、ogmnr 中的 end_logmnr execute dbms_logmnr.end_logmnr();譽(yù)天IT 認(rèn)證站：譽(yù)天教育官網(wǎng)：建議做一次轉(zhuǎn)存(練習(xí))準(zhǔn)備工作：開始補(bǔ)全日志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;數(shù)據(jù)庫(kù)已更改。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;開啟歸檔創(chuàng)建挖掘的數(shù)據(jù)字典目錄SQL> alter system set utl_file_dir='/tmp' scope=spfile;譽(yù)天IT 認(rèn)證站：譽(yù)天教育官網(wǎng)：System altered系統(tǒng)已更改

23、。重啟生效Shutdown immediate ; StartupSQL> show parameter utl_file_dir;NAME- - - - - - - - - -utl_file_dir SQL>TYPE- - - - - - - -VALUE-string/tmp創(chuàng)建數(shù)據(jù)字典文件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 查出當(dāng)前的日志組SQL> set linesize 100 SQL> lselect GROUP#,THREAD#,SEQUENCE#,STATUS ,FIRST_CHANGE#,FIRST_TIMEfrom v$log譽(yù)天IT 認(rèn)證站：譽(yù)天教育官網(wǎng)：SQL> /GROUP#THREAD#SEQUENCE# STATUSFIRST

25、_CHANGE# FIRST_TIME-1-17 INACTIVE1045167 2013-02-28 01:33:353當(dāng)前日志組是 216 INACTIVE1021200 2013-02-28 01:13:09在查 2 號(hào)日志組的日志文件名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. 你可以檢查你對(duì)上面日志文件進(jìn)行挖掘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.查看結(jié)果SQL> SELECT OPERATION, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTSWHERE SEG_OWNER = 'SCOTT' AND SEG_NAME = 'EMP' ANDUSERNAME = 'SCOTT'譽(yù)天IT 認(rèn)證站：譽(yù)天教育官網(wǎng)：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譽(yù)天IT 認(rèn)證站：譽(yù)天教育官網(wǎng)：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譽(yù)天IT 認(rèn)證站：譽(yù)天教育官網(wǎng)：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譽(yù)天IT 認(rèn)證站：譽(yù)天教育官網(wǎng)：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譽(yù)天IT 認(rèn)證站：譽(yù)天教育官網(wǎng)：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譽(yù)天IT 認(rèn)證站：譽(yù)天教育官網(wǎng)：update "SCOTT"."EMP" set "SAL" = '1100' where "EMPNO" = '7876' and "SAL"= '0' and ROWID = 'AAAR3xAA EAAAACXAAK'UPDATEupdate "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = &#