logminer分析异机db的归档日志(外部文件)

 

1.需要分析日志的db上创建字典文件

declare
begin
dbms_logmnr_d.build('yao7.ora','/home/oracle', options => dbms_logmnr_d.store_in_flat_file);
end;

 

2.将文件拷贝到logminer分析的机器

scp yao7.ora oracle@192.168.1.1:/tmp/

 

3.在logminer的机器上添加分析日志

declare
begin
dbms_logmnr.add_logfile(logfilename=>'/tmp/redo03.log',options=>DBMS_LOGMNR.NEW);
end;

 

4.分析

declare
begin
dbms_logmnr.start_logmnr(dictfilename=>'/tmp/yao7.ora',Options=>0);
end;

 

5.查找ddl和dml

select sql_redo from v$logmnr_contents where lower(sql_redo) like '%tb_test%';

 

6.结束分析

 

declare
begin
dbms_logmnr.end_logmnr;
end;