重庆思庄Oracle、Redhat认证学习论坛

标题: logminer分析实际应用 [打印本页]

作者: 刘泽宇 时间: 2022-1-9 18:17
标题: logminer分析实际应用
1.找到要进行分析的归档日志文件
select to_char(first_time,'yyyy-mm-dd hh24:mi:ss') as TIME,blocks,NAME from v$archived_log where first_time between  to_date('2022-01-05 04','yyyy-mm-dd hh24') and to_date('2022-01-05 05','yyyy-mm-dd hh24') and dest_id=1 order by 1 desc;

2.使用logminer需要运行如下两个文件:
@?/rdbms/admin/dbmslm.sql
@?/rdbms/admin/dbmslmd.sql

3.始使用LogMiner进行日志分析
execute dbms_logmnr.add_logfile('/u01/app/oracle/archivelog/ARC0000412838_0714684421.0001',dbms_logmnr.new);

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

4.对v$logmnr_contents内容进行查找分析
查看这个归档日志时段内进行的操作
select operation,count(*) from v$logmnr_contents group by operation order by 2;

OPERATION    COUNT(*)
-------------------------------- ----------
SELECT_FOR_UPDATE    2
UPDATE 215
DELETE 275
COMMIT 446
START 446
INSERT 920

6 rows selected.

查看什么表进行insert操作数量最多
select table_name,count(*) from v$logmnr_contents where operation='INSERT' group by table_name order by 2;

TABLE_NAME    COUNT(*)
-------------------------------- ----------
YUJIAOJIN          10
YUJIAOJIN2022          15
SHOUJU_WJW_LOG    30
SHOUJU_WJW    30
ZHANGDAN2022    60
FEIYONG2022    63
AUD$    65
JYJG_PT_ZY          132
PIAOJUMINGXI2022 140
WJW_LOG                   336

21 rows selected.

5.分析完成后记得要结束日志挖掘：
begin
  sys.dbms_logmnr.end_logmnr;
end;

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)