Oracle 日志挖掘(LogMiner)使用详解

logminer依赖于2个包：dbms_logmnr和dbms_logmnr_d，oracle 11g默认已安装

logminer 基本使用步骤

<1>. specify a logminer dictionary.   指定logminer字典

<2>. specify a list of redo log files for analysis.   指定需要挖掘的redo或者archivelog日志文件

<3>. start logminer.    开始日志挖掘

<4>. request the redo data of interest.    查询v$logmnr_contents获取挖掘的结果

<5>. end the logminer session.    结束日志挖掘

日志挖掘使用示例：

1.开启补全日志：

sql> alter database add supplemental log data;

2.找出需要挖掘的归档日志文件路径

sql> select name from v$archived_logwhere first_time between to_date('2018-07-06 08:20:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018-07-06 09:40:00','yyyy-mm-dd hh24:mi:ss') order by 1;
name
--------------------------------------------------------------------------------
/u01/app/archive/1_342433_904747849.dbf
/u01/app/archive/1_342434_904747849.dbf
/u01/app/archive/1_342435_904747849.dbf

3.指定logminer字典 (三选一)

oracle 11g 指定logminer字典有三种方法

1.using the online catalog   使用在线目录

2.extracting a logminer dictionary to the redo log files   抽取字典到redo日志文件中

3.extracting the logminer dictionary to a flat file   抽取字典到平面文件中（需要设置utl_file_dir参数，重启数据库，不推荐）

execute dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
or
execute dbms_logmnr_d.build( options=> dbms_logmnr_d.store_in_redo_logs);
or
execute dbms_logmnr_d.build('dictionary.ora', '/oracle/database/', dbms_logmnr_d.store_in_flat_file);

4.logminer中添加归档日志文件

execute dbms_logmnr.add_logfile( logfilename => '/u01/app/archive/1_342433_904747849.dbf', options => dbms_logmnr.new);

继续添加日志：

execute dbms_logmnr.add_logfile( logfilename => '/u01/app/archive/1_342434_904747849.dbf', options => dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile( logfilename => '/u01/app/archive/1_342435_904747849.dbf', options => dbms_logmnr.addfile);

查看添加的日志列表：

select filename from v$logmnr_logs;

5.开始日志挖掘 (三选一，与步骤3对应)

execute dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
or
execute dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_redo_logs );
or
execute dbms_logmnr.start_logmnr(dictfilename =>'/oracle/database/dictionary.ora');

6.获取挖掘结果

select username as usr, (xidusn || '.' || xidslt || '.' || xidsqn) as xid, 
sql_redo, sql_undo from v$logmnr_contents where username in ('hr', 'oe');

把挖取结果保存到指定表格中： create table logtab as select * from v$logmnr_contents;

7.结束日志挖掘

execute dbms_logmnr.end_logmnr;

总结

以上所述是www.887551.com给大家介绍的oracle 日志挖掘(logminer)使用详解，希望对大家有所帮助