oracle logminer日志挖掘

1.查看当前归档日志的sequence

SYS@PROD5 >archive log list 
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36
Next log sequence to archive   38
Current log sequence	       38

2.创建测试表,插入数据

create table zhangyun.t5(id number,name varchar2(2000));
insert into zhangyun.t5 values(1,'zhangyun');
insert into zhangyun.t5 values(2,'zhangsan');
commit;

3.切日志

SYS@PROD5 >alter system switch logfile;

System altered.

4.找到对应的日志

SYS@PROD5 >alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.
SYS@PROD5 >col name for a50
SYS@PROD5 >select recid, name, first_time from v$archived_log;
     RECID NAME 					      FIRST_TIME
---------- -------------------------------------------------- -------------------
	33 /u01/app/oracle/fast_recovery_area/PROD5H1/archive 2022-10-16 15:59:56
	   log/2022_10_16/o1_mf_1_38_knqgprtt_.arc

查看某一时刻之后的归档日志

select name,first_time,next_time from v$archived_log where first_time>=to_date('2007-01-19 08:00:00','yyyy-mm-dd hh24:mi:ss');

5.调用dbms_logmnr.add_logfile包

关于dbms_logmnr.add_logfile包的Options选项

NEW :隐式调用该过程以结束当前 LogMiner 会话，然后创建一个新会话。新会话将从您指定的重做日志文件开始，启动要分析的重做日志文件的新列表。

ADDFILE:将指定的重做日志文件添加到要分析的重做日志文件列表中。任何添加重复文件的尝试都会引发异常 。如果未指定选项标志，则这是默认设置。

下面把我们要分析的归档日志加进去（这里我演示出new和addfile2种加法，实际上本次实验我只用了add）

exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PROD5H1/archivelog/2022_10_16/o1_mf_1_39_knqhjygx_.arc',options=> dbms_logmnr.new);
exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PROD5H1/archivelog/2022_10_16/o1_mf_1_40_knqhjygx_.arc',options=> dbms_logmnr.addfile);

SYS@PROD5 >exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PROD5H1/archivelog/2022_10_16/o1_mf_1_38_knqgprtt_.arc',options=> dbms_logmnr.new);

PL/SQL procedure successfully completed.

可以查看现在添加了哪些归档日志

SYS@PROD5 >select filename from v$logmnr_logs;

FILENAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/PROD5H1/archivelog/2022_10_16/o1_mf_1_38_knqgprtt_.arc

6.调用dbms_logmnr.start_logmnr包

关于dbms_logmnr.start_logmnr包的Options选项，本次使用以下2个

COMMITTED_DATA_ONLY：只有提交了的数据才会在Logminer中显示
DICT_FROM_ONLINE_CATALOG：使用数据库的数据字典文件而不使用Logminer指定的数据字典文件

指定多个选项，要在它们之间使用加号 （+）
这次只找提交的数据

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

7.查询V$LOGMNR_CONTENTS 解析结果

SYS@PROD5 >select scn,username,substr(sql_redo,1,60) sub_sql_redo from v$logmnr_contents where seg_owner='ZHANGYUN' and lower(sql_redo) like '%insert%';

       SCN USERNAME
---------- ------------------------------
SUB_SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   2635927 UNKNOWN
insert into "ZHANGYUN"."T5"("ID","NAME") values ('1','zhangy

   2635928 UNKNOWN
insert into "ZHANGYUN"."T5"("ID","NAME") values ('2','zhangs

发现有地方不对，username是unknown怀疑是因为sys用户导致再实验一次

ZHANGYUN@PROD5 >insert into zhangyun.t5 values(5,'zhangli');

1 row created.

ZHANGYUN@PROD5 >commit;

Commit complete.

ZHANGYUN@PROD5 >archive log list
ORA-01031: insufficient privileges
ZHANGYUN@PROD5 >conn / as sysdba   
Connected.
SYS@PROD5 >archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     37
Next log sequence to archive   39
Current log sequence	       39
SYS@PROD5 >alter system switch logfile;

System altered.

SYS@PROD5 >exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PROD5H1/archivelog/2022_10_16/o1_mf_1_39_knqhjygx_.arc',options=> dbms_logmnr.new);

PL/SQL procedure successfully completed.

SYS@PROD5 >exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

SYS@PROD5 >select scn,username,substr(sql_redo,1,60) sub_sql_redo from v$logmnr_contents where seg_owner='ZHANGYUN' and lower(sql_redo) like '%insert%';

       SCN USERNAME
---------- ------------------------------
SUB_SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   2638100 ZHANGYUN
insert into "ZHANGYUN"."T5"("ID","NAME") values ('5','zhangl

可以看到这次成功显示出了执行用户和语句