日志挖掘 log miner

 log miner的作用：

Oracle LogMiner 是一个实际非常有用的分析工具

使用该工具可以轻松获得Oracle在线/归档日志文件中的具体内容，特别是该工具可以分析出所有对于数据库操作的DML和DDL语句。该工具特别适用于调试、审计或者回退某个特定的事务。

有两种日志挖掘方法针对DML和DDL

整理如下 ：

 对语句DML进行日志挖掘：

1）添加database补充日志

SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA 

//注意：通过PL/SQL包的DML的日志挖掘，这步要先执行，在此之后的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;

例

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

session2 scott:
create table a (id int);
insert into a values(1);
update a set id=5;
commit;
delete a;
 //delete这条没有commit，我们要证明uncommit的DML操作也写入了日志。

这个session2留着，换session1进入sys

session1 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 2012-7-23 9
         2          1         25   52428800          2 YES      INACTIVE               2257866 2012-7-23 9
         3         1         23   52428800          2YES      INACTIVE               2257862 2012-7-23 9
         4          1         24   52428800          2 YES      INACTIVE               2257864 2012-7-23 9

//上面a 表的DML操作都写进了current组里，记住sequence#是26号。然后手工切换当前日志到archive里。

SQL> alter system switch logfile; 
SQL> / 
SQL> /
 
SQL> select name from v$archived_log;
 
/u01/disk1/prod/arch_1_789252862_21.log
/u01/disk1/prod/arch_1_789252862_22.log
/u01/disk1/prod/arch_1_789252862_23.log
/u01/disk1/prod/arch_1_789252862_24.log
/u01/disk1/prod/arch_1_789252862_25.log
/u01/disk1/prod/arch_1_789252862_26.log
/u01/disk1/prod/arch_1_789252862_27.log
/u01/disk1/prod/arch_1_789252862_28.log
/u01/disk1/prod/arch_1_789252862_29.log
......
62 rows selected

//切换后，sequence#26的日志的应该对应的是/u01/disk1/prod/arch_1_789252862_26.log

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/disk1/prod/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 2012-07-23 09:43:16 drop table a purge;
   2258334 2012-07-23 09:44:20 create table a (id int);
   2258341 2012-07-23 09:44:28 insert into "SCOTT"."A"("ID") values ('1');
   2258349 2012-07-23 09:44:46 update "SCOTT"."A" set "ID" = '5' where "ID" = '1' and ROWID = 'AAANBAAAEAAAAGEA
   2258353 2012-07-23 09:44:52 delete from "SCOTT"."A" where "ID" = '5' and ROWID = 'AAANBAAAEAAAAGEAAA';

 对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;