logminer操作步驟
前提：
1 數據可以歸檔模式也可以非歸檔模式。
2 需要調整supplemental_log_data_min才能進行日志挖掘。
sys@oratest1(test-for-lihb)> select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
sys@oratest1(test-for-lihb)> alter database add supplemental log data;
Database altered.
sys@oratest1(test-for-lihb)> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
3 日志挖掘是基于session的，如果session退出，需要重新挖掘。
步驟：
1 查看當前在線日志文件使用情況：
sys@oratest1(test-for-lihb)> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ -------------------
1 1 565 52428800 5122 NO CURRENT 8490175 2017-11-13 10:37:37 2.8147E+14
2 1 563 52428800 5122 NO INACTIVE 8481985 2017-11-13 07:00:208489600 2017-11-13 10:21:05
3 1 564 52428800 5122 NO INACTIVE 8489600 2017-11-13 10:21:058490175 2017-11-13 10:37:37
2 創建路徑
sys@oratest1(test-for-lihb)> CREATE DIRECTORY utlfile AS '/home/oracle/logmnr';
Directory created.
3 修改參數
sys@oratest1(test-for-lihb)> alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile;
System altered.
4 建/home/oracle/logmnr目錄，重啟數據庫使utl_file_dir參數生效
[oracle@test-for-lihb ~]$ mkdir -p /home/oracle/logmnr
sys@oratest1(test-for-lihb)> shutdown immediate
sys@oratest1(test-for-lihb)> startup
5 創建數據字典文件
sys@oratest1(test-for-lihb)> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/home/oracle/logmnr');
PL/SQL procedure successfully completed.
6 查看日志文件物理位置（也可以查看歸檔文件）
sys@oratest1(test-for-lihb)> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------------------------------ ------------------------------------------------------------ ---
1 ONLINE /opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_1_dhl9h7tz_.log NO
1 ONLINE /opt/app/oracle/fast_recovery_area/ORATEST1/onlinelog/o1_mf_1_dhl9h8bn_.log YES
2 ONLINE /opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_2_dhl9h8mc_.log NO
2 ONLINE /opt/app/oracle/fast_recovery_area/ORATEST1/onlinelog/o1_mf_2_dhl9h91y_.log YES
3 ONLINE /opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_3_dhl9h98x_.log NO
3 ONLINE /opt/app/oracle/fast_recovery_area/ORATEST1/onlinelog/o1_mf_3_dhl9h9y1_.log YES
6 rows selected.
7 模擬scott用戶插入數據
scott@oratest1(70)> truncate table T;
Table truncated.
scott@oratest1(70)> insert into t values (10086);
1 row created.
scott@oratest1(70)> insert into t values (10087);
1 row created.
scott@oratest1(70)> insert into t values (10088);
1 row created.
scott@oratest1(70)> insert into t values (10089);
1 row created.
scott@oratest1(70)> commit;
Commit complete.
8 加入日志文件（一個日志組如果有多個成員，只需要加入一個即可，因為同一日志組所有成員內容是相同的。）
第一個日志文件參數是dbms_logmnr.NEW
BEGIN
dbms_logmnr.add_logfile(logfilename=>'/opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_3_dhl9h98x_.log',options=>dbms_logmnr.NEW);
END;
/
后續的日志文件參數是dbms_logmnr.ADDFILE
BEGIN
dbms_logmnr.add_logfile(logfilename=>'/opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_1_dhl9h7tz_.log',options=>dbms_logmnr.ADDFILE);
dbms_logmnr.add_logfile(logfilename=>'/opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_2_dhl9h8mc_.log',options=>dbms_logmnr.ADDFILE);
END;
/
9 開始日志挖掘
無限制挖掘：
EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dictionary.ora');
特定時間段挖掘（未測試）：
EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dictionary.ora',StartTime =>to_date('2013-6-8 00:00:00','YYYY-MM-DD HH24:MI:SS')EndTime =>to_date(''2013-6-8 23:59:59','YYYY-MM-DD HH24:MI:SS '));
10 查看挖掘結果
sys@oratest1(test-for-lihb)> select sql_redo from v$logmnr_contents where username='SCOTT';
。。。
insert into "SCOTT"."T"("ID") values ('10086');
insert into "SCOTT"."T"("ID") values ('10087');
insert into "SCOTT"."T"("ID") values ('10088');
insert into "SCOTT"."T"("ID") values ('10089');
。。。。
11 關閉日志挖掘。
EXECUTE DBMS_LOGMNR.END_LOGMNR;
