Oracle LogMiner 實戰手冊：安裝、字典構建、日志添加與 V$LOGMNR_CONTENTS 分析全流程

適用場景

• 誤操作回溯（DELETE 無 WHERE、TRUNCATE 表）。
• 審計追蹤：誰在何時修改了什麼數據。
• 異遷移校驗：對比源端與目標端變更一致性。

前置要求

1. 分析端與被分析端：Oracle 8.0+、同平台、同字符集、同 Block Size。
2. 使用被分析庫生成的字典文件（或聯機字典 10g+）。

步驟 1：安裝 LogMiner 套件

-- 必須以 SYS 執行
@$ORACLE_HOME/rdbms/admin/dbmslm.sql
@$ORACLE_HOME/rdbms/admin/dbmslmd.sql

步驟 2：構建數據字典文件（離線模式）

-- 1. 設置 UTL_FILE_DIR (9i/10g 需重啟，11g+ 可動態)
-- init.ora: UTL_FILE_DIR = '/u01/app/oracle/logmnr_dict'
-- 11g+: ALTER SYSTEM SET UTL_FILE_DIR='/u01/app/oracle/logmnr_dict' SCOPE=SPFILE; -- 仍需重啟

-- 2. 重啟後構建字典
EXEC DBMS_LOGMNR_D.BUILD(
  DICTIONARY_FILENAME => 'logmnr_dict.ora',
  DICTIONARY_LOCATION => '/u01/app/oracle/logmnr_dict'
);

10g+ 推薦聯機字典模式：DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)，無需文件維護，自動追蹤 DDL 變更。

步驟 3：添加待分析日志文件

-- 添加首個日志 (NEW)
EXEC DBMS_LOGMNR.ADD_LOGFILE(
  LOGFILENAME => '/u01/app/oracle/oradata/arch/ARC00123.001',
  OPTIONS => DBMS_LOGMNR.NEW
);

-- 追加更多日志 (ADDFILE)
EXEC DBMS_LOGMNR.ADD_LOGFILE(
  LOGFILENAME => '/u01/app/oracle/oradata/arch/ARC00124.001',
  OPTIONS => DBMS_LOGMNR.ADDFILE
);

-- 移除日志 (REMOVEFILE)
EXEC DBMS_LOGMNR.ADD_LOGFILE(
  LOGFILENAME => '/u01/app/oracle/oradata/arch/ARC00123.001',
  OPTIONS => DBMS_LOGMNR.REMOVEFILE
);

步驟 4：啟動分析會話

-- 使用離線字典
EXEC DBMS_LOGMNR.START_LOGMNR(
  DICTFILENAME => '/u01/app/oracle/logmnr_dict/logmnr_dict.ora',
  STARTTIME => TO_DATE('2024-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),
  ENDTIME   => TO_DATE('2024-01-15 11:00:00', 'YYYY-MM-DD HH24:MI:SS')
  -- 或使用 SCN 範圍: STARTSCN => 12345678, ENDSCN => 12345999
);

步驟 5：查詢分析結果

-- 核心視圖
DESC V$LOGMNR_CONTENTS;

-- 典型查詢：找出 SCOTT 用戶在 EMP 表的 DELETE 操作
SELECT TIMESTAMP, SCN, OPERATION, TABLE_NAME, SQL_REDO, SQL_UNDO
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER = 'SCOTT'
  AND TABLE_NAME = 'EMP'
  AND OPERATION = 'DELETE'
ORDER BY TIMESTAMP;

關鍵欄位：OPERATION(INSERT/UPDATE/DELETE/DDL)、SQL_REDO(重做 SQL)、SQL_UNDO(回滾 SQL)、ROW_ID、USERNAME。

步驟 6：結束會話

EXEC DBMS_LOGMNR.END_LOGMNR;

最佳實踐提示

• 大量日志分析耗費 PGA，建議 ALTER SESSION SET PGA_AGGREGATE_TARGET=500M。
• 聯機字典模式自動處理 DDL，離線字典遇到 DDL 需重建字典。
• 生產庫直接分析可能影響性能，建議將歸檔日志複製到分析服務器離線分析。
• 結合 Flashback Transaction Query (10g+) 可更直觀查看事務級變更。