LogMiner 数据库日志挖掘

01.查看归档路径

select distinct name,first_time from v$archived_log where name is not null order by first_time;

02.建立日志分析列表

begin

 --sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/archlog/xlstest1_arc_1_10506_1034075045.log',options=>dbms_logmnr.new);

 --sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/archlog/xlstest1_arc_1_10507_1034075045.log',options=>dbms_logmnr.addfile);

 --sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/archlog/xlstest1_arc_1_10508_1034075045.log',options=>dbms_logmnr.addfile);

 --sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/archlog/xlstest1_arc_1_10509_1034075045.log',options=>dbms_logmnr.addfile);

 sys.dbms_logmnr.add_logfile(logfilename=>'+FRA/xlsdb/archivelog/2021_09_29/thread_2_seq_14704.1259.1084555009',options=>dbms_logmnr.new);

 sys.dbms_logmnr.add_logfile(logfilename=>'+FRA/xlsdb/archivelog/2021_09_29/thread_2_seq_14705.1293.1084557151',options=>dbms_logmnr.addfile);

 sys.dbms_logmnr.add_logfile(logfilename=>'+FRA/xlsdb/archivelog/2021_09_29/thread_2_seq_14706.729.1084559669',options=>dbms_logmnr.addfile);

end;

03.启动分析

begin

  sys.dbms_logmnr.start_logmnr(Options =>dbms_logmnr.dict_from_online_catalog);

end;

04.查看日志分析结果

select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents;

--根据表名及操作查询出语句及回滚SQL

select timestamp,sql_redo,sql_undo from v$logmnr_contents where table_name = 'T_USR_DEVICE' and operation = 'DELETE'

05.关闭分析

begin

 sys.dbms_logmnr.end_logmnr;

end;