oracle 11g 日志挖掘測試

需要開啟補充日志

alter database add supplemental log data;
SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI
FROM V$DATABASE;

在用戶下執行一些刪除，插入等操作

SQL> create table zxy( a int);Table created.
SQL> insert into zxy values (2);1 row created.SQL> insert into zxy values (3);1 row created.
SQL>  delete from zxy where a=2;1 row deleted.SQL> commit;Commit complete.

手動切換下日志

SQL> alter system switch logfile;System altered.找到該段時間的歸檔文件
-rw-r----- 1 oracle oinstall   3823104 Jun  7 16:07 1_430_1119886393.dbf

日志挖掘

1. 增加需要分析的歸檔日志，可以一直增加：
exec sys.dbms_logmnr.add_logfile(LogFileName=>'/u01/app/oracle/fast_recovery_area/DBOGG/archivelog/1_430_1119886393.dbf',options => dbms_logmnr.new);增加多個日志
exec sys.dbms_logmnr.add_logfile(LogFileName=>'/u01/app/oracle/fast_recovery_area/DBOGG/archivelog/1_429_1119886393.dbf',options => dbms_logmnr.addfile);2. 查看是否增加成功
SQL> select filename from v$logmnr_logs;FILENAME
--------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/DBOGG/archivelog/1_430_1119886393.dbf3. 開始日志挖掘：
exec sys.dbms_logmnr.start_logmnr( options => sys.dbms_logmnr.dict_from_online_catalog);4. 把挖掘到的內容，按需求過濾，存到一張臨時表：
create table tmp_logmnr as select * from v$logmnr_contents where seg_name='ZXY';5. 關閉日志挖掘：
exec sys.dbms_logmnr.end_logmnr;6. 查看挖掘到的信息：
select TIMESTAMP,OPERATION,USERNAME,SQL_REDO,MACHINE_NAME from tmp_logmnr where TABLE_NAME='ZXY';
TIMESTAMP		OPERATION			 USERNAME			SQL_REDO		       MACHINE_NAME
----------------------- -------------------------------- ------------------------------ ------------------------------ --------------------
07-JUN-2024 16:04:25	DDL				 TEST				create table zxy( a int);      zhang3
07-JUN-2024 16:05:14	INSERT				 TEST				insert into "TEST"."ZXY"("A")  zhang3values ('2');07-JUN-2024 16:05:24	INSERT				 TEST				insert into "TEST"."ZXY"("A")  zhang3values ('3');07-JUN-2024 16:06:29	DELETE				 TEST				delete from "TEST"."ZXY" where zhang3"A" = '2' and ROWID = 'AAAV8HAAHAAAFVbAAA';select * from tmp_logmnr where;查詢是否存在LOGMNR使用的session
SELECT COUNT(*) from v$LOGMNR_SESSION;