oracle logminer日志挖掘

说明

oracle数据库需要在oracle开归档得情况下进行，必须开归档。

实操

• 找到需要恢复的日志位置

select name,thread#,sequence#,completion_time from v$archived_log where deleted='NO' order by completion_time desc;
--按照时间查询
select name, thread#, sequence#, completion_time
  from v$archived_log
 where deleted = 'NO'
   and completion_time >
       to_date('2018-7-30 07:00:00', 'YYYY-MM-DD hh24:mi:ss')
   and completion_time <
       to_date('2018-7-30 09:00:00', 'YYYY-MM-DD hh24:mi:ss')
 order by completion_time desc;

• 如果缺少日志，需要恢复一下日志

run{ 
ALLOCATE CHANNEL d1 DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/u01/app/oracle/hp/HP-Catalyst-RMAN-Plugin/bin/libisvsupport_rman.so ENV=(CONFIG_FILE=/u01/app/oracle/hp/HP-Catalyst-RMAN-Plugin/config/plugin.conf)';
set archivelog destination to '/u01/app/oracle/zxb2';
restore archivelog from logseq 4202 until logseq 4222 thread 1;
release channel d1;
}

• 执行需要挖掘的日志（注意：第一个用dbms_logmnr.new，后面需要dbms_logmnr.addfile）（在注意：都要在同一窗口下执行）

exec dbms_logmnr.add_logfile('/u01/app/oracle/zxb/1_4212_973457531.dbf',dbms_logmnr.new);
exec dbms_logmnr.add_logfile('/u01/app/oracle/zxb/1_4213_973457531.dbf',dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile('/u01/app/oracle/zxb/1_4214_973457531.dbf',dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile('/u01/app/oracle/zxb/2_4578_973457531.dbf',dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile('/u01/app/oracle/zxb/2_4579_973457531.dbf',dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile('/u01/app/oracle/zxb/2_4580_973457531.dbf',dbms_logmnr.addfile);

• 执行logminer开始脚本（在注意：都要在同一窗口下执行）

exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

• 创建一个临时表

create table fmssa.zxb3 as select * from v$logmnr_contents;

• 关闭窗口或者直接退出command窗口

exec dbms_logmnr.end_logmnr;