Oracle 備份恢復系列 (6)：LogMiner 日誌分析器實戰指南

LogMiner 是 Oracle 8i+ 內建的線上/離線重做日誌分析工具，能將二進位 Redo/Archive Log 解析為可讀的 SQL 陳述式 (DML/DDL)、回滾 SQL、SCN、時間戳、操作用戶等，廣泛用於審計、誤操作定點復原、容量規劃、邏輯損壞時間定位。

核心用途

1. 離線追蹤資料庫變更，零影響線上效能
2. 產生特定變更的 UNDO SQL，避免完整 PITR
3. 分析資料增長模式，輔助擴容規劃
4. 精準定位邏輯損壞發生的 SCN/時間，輔助基於時間/SCN 的恢復
5. 事務級精細邏輯恢復
6. 事後審計：DML/DDL/DCL 執行時間、用戶、物件

版本支援與限制

• 8i：僅分析 DML；9i+：支援 DDL
• 不支援：IOT、LONG、LOB、集合類型、MTS 環境
• 字典檔必須來自源資料庫，分析端與源端字符集、平台必須相同

安裝 (需 SYS 身分)

@$ORACLE_HOME/rdbms/admin/dbmslm.sql   -- 建立 DBMS_LOGMNR 套件
@$ORACLE_HOME/rdbms/admin/dbmslmd.sql  -- 建立 DBMS_LOGMNR_D 套件 (字典匯出)

關鍵物件與字典來源三選一

相關動態視圖

• V$LOGMNR_CONTENTS：分析結果主表 (OPERATION, SQL_REDO, SQL_UNDO, SEG_NAME, TIMESTAMP, SCN...)
• V$LOGMNR_LOGS：已加入分析的日誌清單 (LOW_TIME, HIGH_TIME, LOW_SCN, NEXT_SCN)
• V$LOGMNR_DICTIONARY、V$LOGMNR_PARAMETERS

標準分析流程

1. 設定 UTL_FILE_DIR (靜態參數需重啟)：ALTER SYSTEM SET UTL_FILE_DIR='*' SCOPE=SPFILE; → 重啟
2. 產生字典檔 (Source DB)：

   EXEC DBMS_LOGMNR_D.BUILD(
     DICTIONARY_FILENAME => 'logminer_dict.dat',
     DICTIONARY_LOCATION => '/u01/arch',
     OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE
   );
   

3. 建立日誌清單 (Mining DB，MOUNT/NOMOUNT)：

   EXEC DBMS_LOGMNR.ADD_LOGFILE(
     OPTIONS => DBMS_LOGMNR.NEW,
     LOGFILENAME => '/u01/arch/arc_1_123.arc'
   );
   -- 後續加入更多日誌
   EXEC DBMS_LOGMNR.ADD_LOGFILE(OPTIONS => DBMS_LOGMNR.ADDFILE, LOGFILENAME => '...');
   

4. 啟動分析 (指定時間或 SCN 範圍)：

   EXEC DBMS_LOGMNR.START_LOGMNR(
     DICTFILENAME => '/u01/arch/logminer_dict.dat',
     STARTTIME => TO_DATE('2024-01-01 10:00:00','YYYY-MM-DD HH24:MI:SS'),
     ENDTIME   => TO_DATE('2024-01-01 12:00:00','YYYY-MM-DD HH24:MI:SS')
   );
   

5. 查詢結果：

   -- DML 操作
   SELECT OPERATION, SQL_REDO, SQL_UNDO
   FROM V$LOGMNR_CONTENTS
   WHERE SEG_NAME = 'TARGET_TABLE';
   
   -- DDL 操作 (9i+)
   SELECT TIMESTAMP, SQL_REDO
   FROM V$LOGMNR_CONTENTS
   WHERE UPPER(SQL_REDO) LIKE '%TRUNCATE%';
   

6. 結束分析：EXEC DBMS_LOGMNR.END_LOGMNR;

實務建議

• 生產庫勿直接啟用 LogMiner，建議將 Archive Log 複製至分析伺服器離線分析。
• 字典檔隨應用結構變更 (DDL) 同步重建，否則解析會出現 Object ID 無法轉換。
• 大量日誌分析耗時較久，可先查 V$LOGMNR_LOGS 確認 SCN/時間範圍再縮小 START_LOGMNR 範圍。
• 結合 FLASHBACK_TRANSACTION_QUERY (10g+) 可更直觀地獲取事務級 UNDO SQL。