日誌挖掘 log miner

一、log miner的作用：

資料庫恢復中有時會需要對Redo log進行分析， 要會使用log miner，以便確定要恢復的時間點或SCN

二、有兩種日誌挖掘方法 針對DML和DDL，整理如下 ：

1.對語句DML進行日誌挖掘：

1）新增database補充日誌
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

//注意：透過PL/SQL包的DML的日誌挖掘，這步要先執行，在此之後的DML操作才能從日誌裡挖到。在OEM中也是要求先做這一步，不同的是之前的DML操作是可以挖到的。

2）確定要分析的日誌範圍，新增日誌，分析
SQL>execute dbms_logmnr.add_logfile(logfilename=>’日誌’,options=>dbms_logmnr.new); //第一個要載入的日誌檔案

SQL>execute dbms_logmnr.add_logfile(logfilename=>’補充日誌’,options=>dbms_logmnr.addfile); //可以反覆新增補充多個日誌檔案

3）執行logmnr 分析
SQL>execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

4）查詢分析結果，可以設定時間格式，也可以在顯示方式裡再確定格式.

SQL>select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name=’表名';

5）關閉日誌分析
SQL>execute dbms_logmnr.end_logmnr;

例
sys:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

scott:
SQL> create table a (id int);
SQL> insert into a values(1);
SQL> update a set id=5;
SQL> commit;
SQL> delete a;
SQL> commit;
Commit complete.

sys:

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
———- ———- ———- ———- ———- ——– —————- ————- ———–
1 1 26 52428800 2 NO CURRENT 2257870 2014-6-05 19
2 1 25 52428800 2 YES INACTIVE 2257866 2014-6-05 19
3 1 23 52428800 2 YES INACTIVE 2257862 2014-6-05 19
4 1 24 52428800 2 YES INACTIVE 2257864 2014-6-05 19

//上面a 表的DML操作都寫進了current組裡，記住sequence#是26號。然後手工切換當前日誌進archive裡。

SQL> alter system switch logfile;

SQL> /

SQL> /

SQL> /

SQL> select name from v$archived_log;

/u01/disk1/timran/arch_1_789252862_21.log
/u01/disk1/timran/arch_1_789252862_22.log
/u01/disk1/timran/arch_1_789252862_23.log
/u01/disk1/timran/arch_1_789252862_24.log
/u01/disk1/timran/arch_1_789252862_25.log
/u01/disk1/timran/arch_1_789252862_26.log
/u01/disk1/timran/arch_1_789252862_27.log
/u01/disk1/timran/arch_1_789252862_28.log
/u01/disk1/timran/arch_1_789252862_29.log

62 rows selected

//切換後，sequence#26的日誌的應該對應的是/u01/disk1/timran/arch_1_789252862_26.log

SQL> execute dbms_logmnr.add_logfile(logfilename=>’/u01/disk1/timran/arch_1_789252862_26.log’,options=>dbms_logmnr.new);

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

SQL>select scn,to_char(timestamp,’yyyy-mm-dd hh24:mi:ss’) timestamp,sql_redo from v$logmnr_contents where seg_name=’A';

SCN TIMESTAMP SQL_REDO
———- ——————- ——————————————————————————–
2258232 2014-06-05 19:43:16 drop table a purge;
2258334 2014-06-05 19:44:20 create table a (id int);
2258341 2014-06-05 19:44:28 insert into “SCOTT”.”A”(“ID”) values (‘1′);
2258349 2014-06-05 19:44:46 update “SCOTT”.”A” set “ID” = ‘5’ where “ID” = ‘1’ and ROWID = ‘AAANBAAAEAAAAGEA
2258353 2014-06-05 19:44:52 delete from “SCOTT”.”A” where “ID” = ‘5’ and ROWID = ‘AAANBAAAEAAAAGEAAA';

2.對DDL的操作的log miner：

1）如果是第一次做，先要建好logmnr目錄，

設定logmnr 引數，存放資料字典檔案dict.ora
$ mkdir /home/oracle/logmnr

SQL> alter system set utl_file_dir=’/home/oracle/logmnr’ scope=spfile;

2）建立資料字典檔案dict.ora
SQL> execute dbms_logmnr_d.build(‘dict.ora’,’/home/oracle/logmnr’,dbms_logmnr_d.store_in_flat_file);

3）新增日誌分析
SQL> execute dbms_logmnr.add_logfile(logfilename=>’日誌檔案’,options=>dbms_logmnr.new);
SQL> execute dbms_logmnr.add_logfile(logfilename=>’追加日誌’,options=>dbms_logmnr.addfile);

4）執行分析
SQL> execute dbms_logmnr.start_logmnr(dictfilename=>’/home/oracle/logmnr/dict.ora’,options=>dbms_logmnr.ddl_dict_tracking);

5）檢視分析結果
SQL> select username,scn,to_char(timestamp,’yyyy-mm-dd hh24:mi:ss’),sql_redo from v$logmnr_contents WHERE USERNAME =’SCOTT’ and lower(sql_redo) like ‘%table%';

6）關閉日誌分析
SQL> execute dbms_logmnr.end_logmnr;