绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
通过log Miner抓起用户的DML操作
2020-08-31 11:34:41

当前的归档日志目前只有12:00开始的日志,我们取12:20 开始的日志(此时切换比较频繁)

SQL>select name,thread#,archived,status from v$archived_log where COMPLETION_TIME between to_date('20200422 1220','yyyymmdd hh24mi') and sysdate and name not like 'hr%'
SQL> /

NAME THREAD# ARC S
-------------------------------------------------------------------------------- ---------- --- -
+FLASH/hcs/archivelog/2020_04_22/thread_2_seq_97143.1680.1038399651 2 YES A
+FLASH/hcs/archivelog/2020_04_22/thread_2_seq_97144.1826.1038399741 2 YES A
+FLASH/hcs/archivelog/2020_04_22/thread_1_seq_95716.729.1038399743 1 YES A
+FLASH/hcs/archivelog/2020_04_22/thread_2_seq_97145.5621.1038399837 2 YES A


对前两个日志作日志挖掘。

SQL> EXECUTE DBMS_LOGMNR.ADD_logfile(LOGFILENAME=>'+FLASH/hrbqjxcs/archivelog/2020_04_22/thread_2_seq_97143.1680.1038399651',OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.


SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+FLASH/hrbqjxcs/archivelog/2020_04_22/thread_2_seq_97144.1826.1038399741',OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.


SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.


对用户的DML语句做统计。

col operation for a10;
col sql_redo for a50;
col machine_name for a15;
col seg_name for a10;
col seg_owner for a15;
select operation,substr(sql_redo,1,50) "sql_redo",machine_name,seg_name,seg_owner
FROM V$LOGMNR_CONTENTS where username='XC' AND operation in ('INSERT','UPDATE','DELETE');

SQL> select username,operation,count(*) FROM V$LOGMNR_CONTENTS where operation in ('INSERT','UPDATE','DELETE') group by username,operation order by count(*);

USERNAME OPERATION COUNT(*)
------------------------------ ---------- ----------
LW INSERT 1
UNKNOWN DELETE 1
LW UPDATE 1
SYS DELETE 3
PJCZJK UPDATE 6
PJCZJK INSERT 9
SYS INSERT 10
HCQS UPDATE 11
SYS UPDATE 33
TOAD INSERT 52
D18 UPDATE 53
LJXC UPDATE 63
UNKNOWN INSERT 67
D18 INSERT 280
ZTC DELETE 545
ZTC INSERT 1077
ZTC UPDATE 1241
UNKNOWN UPDATE 3069
HCQS DELETE 6633
HCQS INSERT 6710
XC DELETE 7321
XC INSERT 22298
XC UPDATE 102136

23 rows selecte
目前看XC用户的操作最多,进一步查询XC用户的DML操作 见文件“xc用户redo语句以及用户连接信息”。



col operation for a10;
col sql_redo for a50;
col machine_name for a15;
col seg_name for a10;
col seg_owner for a15;
select sql_redo,machine_name,seg_name
FROM V$LOGMNR_CONTENTS where username='XC' AND operation in ('INSERT','UPDATE','DELETE');  

这里可以进一步通过用户过滤DML操作,DBA完全可以根据V$LOGMNR_CONTENTS中的字段从更多的维度去分析,这里就不一一演示了。

分享好友

分享这个小栈给你的朋友们,一起进步吧。

Oracle运维新鲜事-技术与管理各占半边天
创建时间:2020-08-04 11:34:57
本技术栈旨在分享技术心得,运维趣事,故障处理经验,调优案例,故障处理涉及集群,DG,OGG,大家生产中遇到的问题基本都会囊括了,我会发布生产库遇到的故障,希望在交流中互助互益,共同提高,也希望大家讨论,如果您有生产中遇到的集群问题,也可以在这里提出来,一起讨论,现实中也帮助不少同学解决了生产库的故障。
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

栈主、嘉宾

查看更多
  • Abraham林老师
    栈主
  • 小雨滴
    嘉宾
  • hawkliu
    嘉宾
  • u_97a59a25246404
    嘉宾

小栈成员

查看更多
  • 栈栈
  • dapan
  • 小菜鸟___
  • hwayw
戳我,来吐槽~