一、导出归档
1,导出数据字典信息到归档
SQL> execute dbms_logmnr_d.build(options=>dbms_logmnr_d.store_in_redo_logs);
PL/SQL procedure successfully completed.
SQL> select name from v$archived_log where dictionary_begin='YES';
NAME
+FRADG/ORASPPDB/ARCHIVELOG/2022_05_19/thread_2_seq_203385.1292.1105128913 (例子)
SQL> select name from v$archived_log where dictionary_end='YES';
NAME
+FRADG/ORASPPDB/ARCHIVELOG/2022_05_19/thread_2_seq_203386.6684.1105128913 (例子)
2,导出问题时间段的归档
Col name format a65
set linesize 2000 long 2000 pagesize 2000
Select thread#,name,sequence#, first_change#,FIRST_TIME,COMPLETION_TIME
FROM v$archived_log where DEST_ID=1
order by 5;
最后需要保留三样东西:
1.dictionary_begin='YES'的归档
2.dictionary_end='YES'的归档
3.问题时段的归档
二、把上面归档传到其他数据库,并开始挖掘
SQL>execute dbms_logmnr.add_logfile(logfilename=>'日志',options=>dbms_logmnr.new); //第一个要加载的日志文件,上述查询的name列
SQL>execute dbms_logmnr.add_logfile(logfilename=>'继续添加日志',options=>dbms_logmnr.addfile); //可以反复添加补充多个日志文件
3)执行启动logmnr 分析
execute dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_redo_logs);
4)select * from v$logmnr_contents where ....... ;(查挖出的内容)
5)execute dbms_logmnr.end_logmnr;