logminer日誌挖掘操作步驟

alter system set utl_file_dir = '/backup' scope = spfile;
重啟資料庫才有效

實驗資料
SQL> create table x2 (a number);

Table created
SQL> insert into x2 values (1);

1 row created.

SQL> insert into x2 values (2);

1 row created.

SQL> update x2 set a=3 where a=2;

1 row updated..

SQL> update x2 set a=3 where a=1;    

1 row updated.

SQL> commit;

Commit complete.
SQL> alter system switch logfile;

System altered.
首次新增要分析的日誌
execute dbms_logmnr.add_logfile (logfilename=>'/home/orcl/app/fast_recovery_area/ORCL/archivelog/2015_06_03/o1_mf_1_3_bpxgpcwn_.arc',options=>dbms_logmnr.new);
分析歸檔可以在v$archived_log檢視中找到歸檔名
分析重做日誌可以在v$logfile檢視中找日誌名（只能分析未歸檔重做日誌，不然會系統重複錯誤）
再次新增
execute dbms_logmnr.add_logfile (logfilename=>'/home/orcl/app/fast_recovery_area/ORCL/archivelog/2015_06_03/o1_mf_1_2_bpxgpcwn_.arc',options=>dbms_logmnr.addfile）;
如果要挖掘的歸檔日誌檔案很多可以使用下面語句解放勞動力
select 'exec dbms_logmnr.add_logfile(LogFileName=>'''||name||''',Options=>dbms_logmnr.addfile);' from v$archived_log;
刪除挖掘日誌
execute dbms_logmnr.add_logfile (logfilename=>'/home/orcl/app/fast_recovery_area/ORCL/archivelog/2015_06_03/o1_mf_1_2_bpxgpcwn_.arc',options=>dbms_logmnr.removefile);
執行儲存過程dbms_logmnr_d.build執行儲存過程dbms_logmnr_d.build建立挖掘日誌檔案的物理檔案路徑
execute dbms_logmnr_d.build('estlogminer.ora','/backup');
啟動logminer
execute dbms_logmnr.start_logmnr(dictfilename=>'/backup/testlogminer.ora',options=>dbms_logmnr.ddl_dict_tracking);指明瞭字典物理檔案'/backup/testlogminer.ora
按SCN來過濾
execute dbms_logmnr.start_logmnr(dictfilename=>'/backup/testlogminer.ora',startscn=>50,endscn=>100);
按時間過濾
execute dbms_logmnr.start_logmnr(dictfilename=>'/backup/testlogminer.ora',starttime=>to_date('03-Jun-2015 14:20:20' ,'DD-MON-YY HH:MI:SS'),starttime=>to_date('03-Jun-2015 17:20:20' ,'DD-MON-YY HH:MI:SS'));
開啟logminer之後
select filename from v$logmnr_logs;可以查詢logminer正在分析哪些日誌
可以在v$logmnr_contents中線上分析挖掘日誌;
結束logminer
 SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;