oracle database -----日志挖掘

DML日志挖掘：

1、添加database补充日志
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 
            
2、确定要分析的日志范围，添加日志，分析
SQL>execute dbms_logmnr.add_logfile(logfilename=>'/redo1/redo05_1.log',options=>dbms_logmnr.new);

SQL>execute dbms_logmnr.add_logfile(logfilename=>'/archlog/arch_1.arc',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;

----------------------------------------------------------------------------------------------------------------------------
DDL日志挖掘：

添加database补充日志
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 

1、新建目录
$ mkdir /home/oracle/logmnr
SQL> alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile; 

SQL> execute dbms_logmnr_d.build('dict01.ora','/home/oracle/logmnr',dbms_logmnr_d.store_in_flat_file);


2、添加日志分析

SQL> execute dbms_logmnr.add_logfile(logfilename=>'归档文件',options=>dbms_logmnr.new); 

SQL> execute dbms_logmnr.add_logfile(logfilename=>'归档文件',options=>dbms_logmnr.addfile);      

3、执行分析

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dict01.ora',options=>dbms_logmnr.ddl_dict_tracking);
    
4、查看分析结果                                                      
SQL>select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='表名';
                                                              
5、关闭日志分析
SQL> execute dbms_logmnr.end_logmnr;