通过log Miner抓起用户的DML操作

当前的归档日志目前只有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中的字段从更多的维度去分析，这里就不一一演示了。