無需啟用審計即可事後分析：誰在什麼時間執行了什麼DML/DDL、數據如何變更、事務提交/回滾細節。支援Online Redo Log與Archived Redo Log。
-- 1. 設置UTL_FILE目錄權限（需重啟實例生效）
-- init.ora / spfile
utl_file_dir = '/u01/app/oracle/logminer'
-- 或 12c+ 使用目錄對象
CREATE DIRECTORY logminer_dir AS '/u01/app/oracle/logminer';
GRANT READ, WRITE ON DIRECTORY logminer_dir TO SYSTEM;
-- 2. 構建數據字典文件（離線模式，推薦）
-- 需以SYSDBA執行
EXEC DBMS_LOGMNR_D.BUILD(
dictionary_filename => 'dict_orcl.ora',
dictionary_location => '/u01/app/oracle/logminer'
);
-- 生成 dict_orcl.ora，可重複使用，但不可跨庫共享
-- 查看可用日志
SELECT * FROM v$logfile;
SELECT * FROM v$archived_log WHERE first_time > SYSDATE-1;
-- 添加日志（同一會話內操作）
EXEC DBMS_LOGMNR.ADD_LOGFILE(
logfilename => '/u01/app/oracle/oradata/ORCL/redo01.log',
options => DBMS_LOGMNR.NEW -- 首個文件用NEW建立列表
);
EXEC DBMS_LOGMNR.ADD_LOGFILE(
logfilename => '/u01/app/oracle/oradata/ORCL/redo02.log',
options => DBMS_LOGMNR.ADDFILE
);
-- 移除文件
EXEC DBMS_LOGMNR.ADD_LOGFILE(
logfilename => '/u01/app/oracle/oradata/ORCL/redo03.log',
options => DBMS_LOGMNR.REMOVEFILE
);
-- 查看已添加列表
SELECT * FROM v$logmnr_logs;
-- 設置會話時間格式（必須與查詢格式一致）
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
-- 查詢日志SCN/時間範圍
SELECT low_time, high_time, low_scn, next_scn FROM v$logmnr_logs;
-- 啟動（四選一：SCN範圍 / 時間範圍 / 字典文件 / 在線字典）
EXEC DBMS_LOGMNR.START_LOGMNR(
dictfilename => '/u01/app/oracle/logminer/dict_orcl.ora',
startscn => 12345678,
endscn => 12345999
-- 或 starttime => TO_DATE('2024-01-15 10:00:00','YYYY-MM-DD HH24:MI:SS'),
-- endtime => TO_DATE('2024-01-15 12:00:00','YYYY-MM-DD HH24:MI:SS'),
-- options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG -- 使用在線字典（無需構建文件）
);
-- 標準查詢：SCN、時間、會話、回滾/重做SQL
SELECT scn,
timestamp,
session#,
username,
sql_redo,
sql_undo,
operation,
seg_owner,
seg_name
FROM v$logmnr_contents
WHERE seg_owner IN ('SCOTT','HR','APP_USER') -- 過濾目標Schema
AND operation IN ('INSERT','UPDATE','DELETE','DDL')
ORDER BY scn, timestamp;
-- 統計高頻操作用戶
SELECT username, operation, COUNT(*)
FROM v$logmnr_contents
GROUP BY username, operation
ORDER BY COUNT(*) DESC;
EXEC DBMS_LOGMNR.END_LOGMNR;
-- 會話結束自動清理v$logmnr_logs/v$logmnr_contents
| 現象 | 原因與對策 |
|---|---|
| ORA-01310: dictionary mismatch | 字典文件與日志來源庫版本/Schema不匹配 → 重新BUILD字典 |
| 查詢v$logmnr_logs無數據 | 必須在同一會話內ADD_LOGFILE並查詢，新會話列表為空 |
| 中文亂碼 | 確保NLS_LANG、數據庫字符集、會話NLS_DATE_FORMAT一致 |
| 性能極慢 | 日志量過大 → 縮小SCN/時間範圍、過濾SEG_OWNER、使用在線字典避免文件I/O |
最佳實踐：
• 關鍵業務庫常態化開啟SUPPLEMENTAL LOGGING（ALTER DATABASE ADD SUPPLEMENTAL LOG DATA），確保LogMiner能完整還原UPDATE前值。
• 結合DBMS_LOGMNR.CONTINUOUS_MINE實時挖掘在線日志，構建低延遲數據同步/審計平台。
• 12c+支援DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS)直接從Redo日志提取字典，無需預構建文件。
• 生產環境嚴禁長時間運行LogMiner會話佔用共享池，用完即END_LOGMNR。
