Oracle数据挖掘

一、导出归档

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;