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

Oracle LogMiner 完整指南：從原理到實戰的日誌分析技術

編輯：Oracle 資料庫基礎

一、核心原理：LogMiner 如何解讀 Redo Log

Oracle 資料庫的所有變更都記錄在 Redo Log 中，但原始日誌格式晦澀難懂。LogMiner 是 Oracle 官方提供的唯一分析工具，類似於 tkprof 之於 Trace 文件，它能將二進制日誌重構為可讀的 SQL 語句。透過日誌分析，我們可以達成四大目標：追蹤邏輯變更、偵測並修正誤操作、事後審計、以及變更影響分析。

日誌記錄的關鍵資訊

Redo Log 包含：資料庫變更歷史、操作類型（INSERT/UPDATE/DELETE/DDL）、對應的 SCN 號、執行操作的用戶資訊。LogMiner 會重構等價的 SQL_REDO 和 SQL_UNDO 語句（存放於 V$LOGMNR_CONTENTS 視圖）。注意：重構的是「等價語句」而非原始 SQL。 例如原始執行 DELETE FROM a WHERE c1 != 'cyx'，LogMiner 可能重構為多條單行 DELETE 語句，因為資料庫記錄的是元操作（Row-level operations），同一條 SQL 在不同環境下影響的行數不同，記錄原始 SQL 無實際意義。

資料字典的角色

Redo Log 中記錄的是內部物件編號（Object ID、Column ID），而非名稱。為讓重構 SQL 可讀，LogMiner 需透過 DBMS_LOGMNR_D.BUILD() 提取資料字典，將編號映射為物件名稱。LogMiner 本身可不使用字典，但輸出將顯示 UNKNOWN.OBJ# 6197 和 COL 1 等內部代碼。

二、核心套件與視圖

DBMS_LOGMNR_D：僅含 BUILD() 程序，用於提取字典。
DBMS_LOGMNR：三大核心程序
- ADD_LOGFILE：新增/移除分析日誌檔
- START_LOGMNR：啟動分析，定義時間/SCN 窗口、指定字典檔
- END_LOGMNR：終止會話、釋放記憶體
關鍵視圖：
- V$LOGMNR_DICTIONARY：可用字典資訊
- V$LOGMNR_PARAMETERS：當前參數設定
- V$LOGMNR_LOGS：當前分析的日誌清單
- V$LOGMNR_CONTENTS：分析結果核心視圖

三、Oracle 9i LogMiner 增強特性

支援更多資料/儲存類型：鏈接/遷移列、Cluster 表、Direct Path Insert、DDL 操作（SQL_REDO 可見原始 DDL，CREATE USER 密碼加密顯示）。啟用 TX_AUDITING=TRUE 可記錄資料庫帳號。
字典提取選項擴充：除外部檔案，可直接提取至 Redo Log 流中，提供操作時刻的字典快照，支援離線分析。
交易層級分組：COMMITTED_DATA_ONLY 選項可按 SCN 順序返回已提交交易。
Schema 變更追蹤：DDL_DICT_TRACKING 選項自動比對初始日誌流與當前字典，即使表已修改或刪除，仍能返回正確 DDL。
更豐富的列資訊：UPDATE 操作不僅記錄被更新列，還捕捉前影資訊。
基於數值查詢：可直接查詢涉及特定數值變更的語句（如薪資從 1000 改為 2000），而非僅能篩選所有 UPDATE。

四、Oracle 8i/9i 完整分析流程

前置條件

實例需啟動（至少 MOUNT 狀態）。設定 UTL_FILE_DIR 參數並確保 Oracle 對目錄有讀寫權限，用於存放字典檔。

步驟 1：設定 UTL_FILE_DIR

SQL> SHOW PARAMETER utl_file_dir
NAME         TYPE   VALUE
utl_file_dir string /data6/cyx/logmnr

目錄路徑必須精確匹配，尾部斜線差異會導致錯誤。

步驟 2：產生字典檔

EXEC DBMS_LOGMNR_D.BUILD(
  DICTIONARY_FILENAME => 'dic.ora',
  DICTIONARY_LOCATION => '/data6/cyx/logmnr'
);

若未設定 UTL_FILE_DIR 會報 ORA-01308。Windows 8.1.7 版本可能遇到 VARRAY 限制錯誤，需修改 $ORACLE_HOME/rdbms/admin/dbmslmd.sql 將 VARRAY(513) 改為 VARRAY(700) 並重新編譯套件。

步驟 3：新增分析日誌檔

EXEC DBMS_LOGMNR.ADD_LOGFILE(
  LOGFILENAME => '/data6/cyx/rac1arch/arch_1_197.arc',
  OPTIONS => DBMS_LOGMNR.NEW  -- 建立新清單
);
EXEC DBMS_LOGMNR.ADD_LOGFILE(
  LOGFILENAME => '/data6/cyx/rac1arch/arch_2_86.arc',
  OPTIONS => DBMS_LOGMNR.ADDFILE  -- 追加至清單
);

OPTIONS 可為 NEW、ADDFILE、REMOVEFILE。

步驟 4：啟動 LogMiner 分析

-- 使用字典檔
EXEC DBMS_LOGMNR.START_LOGMNR(
  DICTFILENAME => '/data6/cyx/logmnr/dic.ora'
);

-- 不使用字典檔
EXEC DBMS_LOGMNR.START_LOGMNR();

-- 指定時間窗口（需注意 NLS_DATE_FORMAT）
EXEC DBMS_LOGMNR.START_LOGMNR(
  DICTFILENAME => '/data6/cyx/logmnr/dic.ora',
  STARTTIME => TO_DATE('2003-09-21 09:39:00','YYYY-MM-DD HH24:MI:SS'),
  ENDTIME   => TO_DATE('2003-09-21 09:45:00','YYYY-MM-DD HH24:MI:SS')
);

亦可使用 STARTSCN/ENDSCN 定義 SCN 範圍。啟動後可查詢 SELECT DISTINCT TIMESTAMP FROM V$LOGMNR_CONTENTS 確認有效日誌時間範圍。

步驟 5：查詢分析結果

核心視圖為 V$LOGMNR_CONTENTS。RAC 環境下 V$LOGMNR_LOGS 會顯示不同 THREAD_ID。常見查詢場景：

追蹤 DDL（僅 9i 支援）：SELECT TIMESTAMP, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE UPPER(SQL_REDO) LIKE '%CREATE%'
偵測誤/惡意操作：利用 SESSION_INFO 欄位（含登入用戶、OS 用戶、機器名、終端、程式名）。結合登入觸發器記錄 Client IP：
```
CREATE OR REPLACE TRIGGER ON_LOGON_TRIGGER
AFTER LOGON ON DATABASE
BEGIN
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('USERENV','IP_ADDRESS'));
END;
```
則可透過 SESSION_INFO 中的 CLIENT_INFO 定位具體 IP。

步驟 6：結束分析

EXEC DBMS_LOGMNR.END_LOGMNR();

或直接退出 SQL*Plus 自動終止。

五、跨版本/平台分析 Oracle 8 日誌

LogMiner 可分析舊版日誌，步驟：將 Oracle 8i 的 dbmslmd.sql 複製至 Oracle 8 主機執行建立套件，再用 DBMS_LOGMNR_D.BUILD 產生字典檔，將歸檔日誌與字典檔複製至 Oracle 8i/9i 主機，從「新增日誌檔」步驟開始分析。

限制條件：

字典檔必須與日誌檔來自同一資料庫，且字符集一致。
硬體平台必須一致（作業系統版本可不同）。平台不一致會報 ORA-01284/ORA-27048 錯誤。

六、V$LOGMNR_CONTENTS 進階查詢技巧

1. 追蹤結構變更（DDL）

SELECT TIMESTAMP, SQL_REDO 
FROM V$LOGMNR_CONTENTS 
WHERE UPPER(SQL_REDO) LIKE '%CREATE%';

2. 定位惡意操作實戰

某員工透過程式修改收費類型為免費。查詢更新 HMLX 表的操作：

SELECT SESSION_INFO, SQL_REDO
FROM V$LOGMNR_CONTENTS
WHERE UPPER(OPERATION) = 'UPDATE'
  AND UPPER(SQL_REDO) LIKE '%HMLX%';

輸出範例：

SESSION_INFO: login_username=C CLIENT_INFO=10.16.98.26 OS_USERNAME=sz-xjs-chengyx MACHINE_NAME=GDTEL\SZ-XJS-CHENGYX
SQL_REDO: UPDATE "C"."HMLX" SET "NAME" = 'free' WHERE "NAME" = 'ncn.cn' AND ROWID = 'AAABhTAAFAAABRaAAE'

結合登入觸發器記錄的 IP，即可精準定位操作者。