您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle LogMiner 日誌挖掘工具完整指南：字典建立、日誌添加、條件過濾、SQL_REDO/UNDO 提取與結果持久化

編輯：Oracle 資料庫基礎

LogMiner 核心價值：將二進制 Redo/Archive Log 還原為可讀 SQL

適用場景：**事後審計、誤操作溯源、資料找回 (Flashback 不可用時)、合規報表、異構同步驗證**。

四階段標準流程

建立資料字典：讓 LogMiner 解析 Object ID → 物件名、欄位名、型別
添加日誌檔案：指定待分析的 Redo/Archive Log 列表
啟動分析會話：設定字典來源、時間/SCN 範圍、過濾選項
查詢 V$LOGMNR_CONTENTS：提取 SQL_REDO (正向) / SQL_UNDO (反向)

階段一：建立資料字典 (三種來源任選一)

字典來源	建立方式	適用場景	優/缺點
線上目錄 `DICT_FROM_ONLINE_CATALOG`	無需預建，啟動時指定	分析當前資料庫近期日誌，結構未變	✅ 零準備、即時 ❌ 無法分析結構已變更的歷史日誌
Redo Log 內嵌字典 `DICT_FROM_REDO_LOGS`	1. `DBMS_LOGMNR_D.BUILD(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS)` 2. 切換日誌 `ALTER SYSTEM ARCHIVE LOG CURRENT`	需分析過去結構不同的日誌、離線分析	✅ 自包含、可攜帶 ❌ 需預先規劃、佔用 Redo 空間
扁平檔字典 `DICT_FROM_FLAT_FILE`	1. `UTL_FILE_DIR` 設定目錄 2. `DBMS_LOGMNR_D.BUILD('dict.ora','/path')`	跨庫離線分析、無目標資料庫存取權	✅ 完全獨立 ❌ 維護成本高、易過期

扁平檔字典建立完整步驟 (10g/11g 常用)

-- 1. 設定 UTL_FILE_DIR (需重啟實例)
-- init.ora / spfile:
UTL_FILE_DIR = '/u01/app/oracle/admin/ORCL/logmnr_dict'

-- 2. 重啟資料庫使參數生效
SHUTDOWN IMMEDIATE;
STARTUP;

-- 3. 建立字典檔 (需 SYSDBA)
CONN / AS SYSDBA
EXEC DBMS_LOGMNR_D.BUILD(
    DICTIONARY_FILENAME => 'dict_202401.ora',
    DICTIONARY_LOCATION => '/u01/app/oracle/admin/ORCL/logmnr_dict',
    OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE
);
-- 產生 dict_202401.ora 檔案，攜帶此檔即可離線分析

階段二：添加日誌檔案到分析清單

-- 1. 定位目標歸檔日誌
SELECT NAME, FIRST_TIME, NEXT_TIME, SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME BETWEEN TO_DATE('2024-01-15 08:00','YYYY-MM-DD HH24:MI')
                     AND TO_DATE('2024-01-15 12:00','YYYY-MM-DD HH24:MI')
ORDER BY SEQUENCE#;

-- 2. 添加第一個日誌 (NEW 選項建立新清單)
EXEC DBMS_LOGMNR.ADD_LOGFILE(
    LOGFILENAME => '/u01/app/oracle/fra/ORCL/archivelog/2024_01_15/o1_mf_1_123_abc.arc',
    OPTIONS => DBMS_LOGMNR.NEW
);

-- 3. 追加後續日誌 (ADDFILE 選項)
EXEC DBMS_LOGMNR.ADD_LOGFILE(
    LOGFILENAME => '/u01/app/oracle/fra/ORCL/archivelog/2024_01_15/o1_mf_1_124_def.arc',
    OPTIONS => DBMS_LOGMNR.ADDFILE
);
-- 重複 ADDFILE 添加所有目標日誌

階段三：啟動分析會話 (START_LOGMNR 選項組合)

-- 完整選項示範
EXEC DBMS_LOGMNR.START_LOGMNR(
    -- 字典來源 (三選一)
    OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
             -- + DBMS_LOGMNR.DICT_FROM_REDO_LOGS
             -- + DBMS_LOGMNR.DICT_FROM_FLAT_FILE, DICT_FILENAME=>'/path/dict.ora'
    
    -- 時間範圍過濾 (強烈建議，大幅減少分析量)
    , START_TIME => TO_DATE('2024-01-15 08:00:00','YYYY-MM-DD HH24:MI:SS')
    , END_TIME   => TO_DATE('2024-01-15 12:00:00','YYYY-MM-DD HH24:MI:SS')
    
    -- 或 SCN 範圍
    -- , START_SCN => 1234567890
    -- , END_SCN   => 1234567999
    
    -- 其他常用選項
    , DBMS_LOGMNR.CONTINUOUS_MINE      -- 自動挖掘新產生日誌 (串流模式)
    , DBMS_LOGMNR.NO_SQL_DELIMITER     -- SQL_REDO/UNDO 不加分號 (便於程式解析)
    , DBMS_LOGMNR.PRINT_PRETTY_SQL     -- 格式化 SQL (含縮排、換行)
    , DBMS_LOGMNR.COMMITTED_DATA_ONLY  -- 僅顯示已提交事務 (過濾回滾)
);

階段四：查詢挖掘結果 V$LOGMNR_CONTENTS

核心欄位速查

欄位	含義	用途
`SCN`	系統變更號	精確定位時間點、排序
`TIMESTAMP`	操作時間	人類可讀時間線
`SEG_OWNER`	段擁有者 (Schema)	過濾特定 Schema
`SEG_NAME`	段名 (表/索引名)	過濾特定表
`OPERATION`	操作類型	INSERT/UPDATE/DELETE/DDL/START/COMMIT/ROLLBACK
`SQL_REDO`	正向重做 SQL	重演操作、異構同步
`SQL_UNDO`	反向撤銷 SQL	資料恢復核心、回滾誤操作
`USERNAME`	執行用戶	審計特定用戶
`SESSION_INFO`	Session 識別資訊	關聯應用會話
`ROW_ID`	列 Rowid	精確定位單列
`XID`	事務 ID	事務層面分析

實戰查詢範例

-- 1. 查看特定表的所有操作時間線
SELECT SCN, TIMESTAMP, SEG_OWNER, OPERATION, SQL_REDO
FROM V$LOGMNR_CONTENTS
WHERE SEG_NAME = 'EMP'
ORDER BY SCN;

-- 2. 提取 SCOTT 用戶對 EMP 表的誤刪除 SQL_UNDO (恢復腳本)
SELECT SQL_UNDO
FROM V$LOGMNR_CONTENTS
WHERE USERNAME = 'SCOTT'
  AND SEG_NAME = 'EMP'
  AND OPERATION = 'DELETE'
ORDER BY SCN;

-- 3. 查看特定事務的完整操作
SELECT SCN, OPERATION, SQL_REDO, SQL_UNDO
FROM V$LOGMNR_CONTENTS
WHERE XID = (SELECT XID FROM V$LOGMNR_CONTENTS WHERE SQL_REDO LIKE 'DELETE FROM SCOTT.EMP%' AND ROWNUM=1)
ORDER BY SCN;

-- 4. DDL 操作審計
SELECT SCN, TIMESTAMP, USERNAME, SQL_REDO
FROM V$LOGMNR_CONTENTS
WHERE OPERATION = 'DDL'
ORDER BY SCN;

結果持久化：避免會話結束資料消失

關鍵限制：V$LOGMNR_CONTENTS 資料存在於 **PGA 記憶體** 中，**隨著 END_LOGMNR 或 Session 結束而消失**，其他 Session 不可見。

持久化策略

-- 方法 1：建立實體表保存 (最常用)
CREATE TABLE logmnr_archive_20240115 AS
SELECT SCN, TIMESTAMP, SEG_OWNER, SEG_NAME, OPERATION, 
       SQL_REDO, SQL_UNDO, USERNAME, XID, ROW_ID
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER IN ('SCOTT','HR','APP_USER');

-- 方法 2：匯出到檔案 (Data Pump / Spool)
SPOOL logmnr_export_20240115.lst
SELECT SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE SEG_NAME = 'EMP' AND OPERATION = 'DELETE';
SPOOL OFF

-- 方法 3：12c+ 可用 DBMS_LOGMNR 套件直接寫入外部表 (進階)

常見錯誤與排查

錯誤	原因	解決
ORA-01306: must call START_LOGMNR first	未啟動分析會話直接查 V$LOGMNR_CONTENTS	先執行 `DBMS_LOGMNR.START_LOGMNR`
ORA-01280: cannot open logfile	路徑錯誤、權限不足、檔案不存在/已刪除	驗證路徑、確認檔案在 FRA/備份位置、OS 權限
ORA-01304: invalid logfile	日誌檔案損壞、非 Oracle Redo Log、版本不符	用 `DBMS_LOGMNR.ADD_LOGFILE` 驗證單檔、檢查 V$LOGMNR_LOGS
SQL_REDO 顯示內部欄位名 (SYS_NC00001$)	字典版本與日誌不匹配 (結構變更後未重建字典)	重建字典：`DBMS_LOGMNR_D.BUILD` 或用 `DICT_FROM_REDO_LOGS`
分析極慢 / PGA 爆滿	日誌範圍過大、無時間/SCN 過濾	必加 START_TIME/END_TIME 或 START_SCN/END_SCN

總結

LogMiner 是 Oracle 核心診斷工具，**標準四步驟（字典→日誌→啟動→查詢）+ 必加時間範圍過濾 + 結果持久化** 為核心最佳實踐。**SQL_UNDO 是資料恢復的黃金鑰匙**，配合 COMMITTED_DATA_ONLY 過濾回滾噪音。12c+ 引入 CONTINUOUS_MINE 串流模式，支援近即時異構同步。掌握字典三種來源選擇策略，能應對從線上審計到離線跨庫分析的全場景需求。