Oracle logminer

归档日志量统计

SELECT TRUNC(FIRST_TIME) "TIME",
SUM(BLOCK_SIZE * BLOCKS) / 1024 / 1024 / 1024 "SIZE(GB)"
FROM V$ARCHIVED_LOG
GROUP BY TRUNC(FIRST_TIME) order by TRUNC(FIRST_TIME);

日志生成量最大的对象 (按小时统计)

select * from (
SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI') snap_time,
dhsso.object_name,
SUM(db_block_changes_delta)
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhsso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhsso.obj#
AND dhss.dataobj# = dhsso.dataobj#
AND begin_interval_time> sysdate - 120/1440
GROUP BY to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),
dhsso.object_name
order by 3 desc)
where rownum<=5;

使用logminer分析归档日志

1.使用脚本创建相关的包

@$ORACLE_HOME/rdbms/admin/dbmslm.sql

@$ORACLE_HOME/rdbms/admin/dbmslmd.sql;

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

exec sys.dbms_logmnr.add_logfile(logfilename => '/archivelog/node/1_771348_78836667000.arc',options => dbms_logmnr.new);

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

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

4.查询分析出来的归档日志内容:

select seg_owner,count(*) from v$logmnr_contents group by seg_owner;

select count(1),substr(sql_redo,1,50) from v$logmnr_contents group by substr(sql_redo,1,50) order by count(1) desc ;