ORACLE归档日志分析工具logminer

logminer该工具可查看归档日志由什么操作生成。

使用方法

1.指定要分析的日志文件

exec sys.dbms_logmnr.add_logfile(logfilename => '/u02/app/oracle/NKYYDB/archivelog/2019_06_20/ o1_mf_1_3569_gjpn2bwt_.arc',options => dbms_logmnr.new);

2.使用本地的在线数据字典分析归档日志

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

3.查询分析出来的归档日志内容，例如统计某个SCHEMA下各个表的修改量

select to_char(TIMESTAMP,'YYYY-MM-DD HH24') TIME_1,
          seg_owner,
          table_name,
          operation,
          count(1)
from v$logmnr_contents
where seg_owner = 'NK_ZFFX'
group by to_char(TIMESTAMP,'YYYY-MM-DD HH24'),seg_owner,table_name,operation
order by seg_owner,table_name,to_char(TIMESTAMP,'YYYY-MM-DD HH24'),operation;

4.增加别的日志文件

exec sys.dbms_logmnr.add_logfile(logfilename=>’/u02/app/oracle/NKYYDB/archivelog/2019_06_20/ o1_mf_1_3570_gjpr6jyc_.arc’);

5.结束分析归档日志

exec sys.dbms_logmnr.end_logmnr;