Oracle LogMiner 實戰教學：從歸檔日誌還原誤操作時間點完整指南

什麼是 LogMiner？

LogMiner 是 Oracle 內建的日誌分析工具，可讀取線上/歸檔 Redo Log，重建 DML/DDL 操作的 SQL 陳述式，協助 DBA 定位誤操作時間點、進行時間點恢復 (PITR) 或審計追蹤。

適用版本與限制

不支援物件：Index-Organized Table (IOT)、LONG、LOB、Collection 型別、MTS (Shared Server) 環境。

操作環境準備

1. 設定 UTL_FILE_DIR (9i 以前) 或建立 DIRECTORY 物件 (9i+) 指定字典檔與日誌檔存放路徑。

   -- 9i+ 建議使用 DIRECTORY
   CREATE DIRECTORY logminer_dir AS '/u01/arch';
   GRANT READ, WRITE ON DIRECTORY logminer_dir TO dba_user;

2. 資料庫需啟用歸檔模式：ARCHIVELOG 模式並已產生歸檔日誌。

標準操作流程 (PL/SQL API)

步驟 1：建立 LogMiner 資料字典檔

BEGIN
  DBMS_LOGMNR_D.BUILD(
    dictionary_filename => 'logminer_dict.dat',
    dictionary_location => 'logminer_dir'  -- DIRECTORY 物件名
  );
END;
/
-- 產生的字典檔包含物件名稱、欄位定義，供後續解析 Redo 記錄使用

步驟 2：建立日誌分析清單並加入日誌檔

-- 首次加入使用 NEW 選項
BEGIN
  DBMS_LOGMNR.ADD_LOGFILE(
    options     => DBMS_LOGMNR.NEW,
    logfilename => 'logminer_dir/arc_20240115.arc'
  );
END;
/

-- 後續加入使用 ADDFILE
BEGIN
  DBMS_LOGMNR.ADD_LOGFILE(
    options     => DBMS_LOGMNR.ADDFILE,
    logfilename => 'logminer_dir/arc_20240116.arc'
  );
END;
/

-- 移除檔案使用 REMOVEFILE
BEGIN
  DBMS_LOGMNR.ADD_LOGFILE(
    options     => DBMS_LOGMNR.REMOVEFILE,
    logfilename => 'logminer_dir/arc_20240115.arc'
  );
END;
/

步驟 3：啟動 LogMiner 分析

BEGIN
  DBMS_LOGMNR.START_LOGMNR(
    dictfilename => 'logminer_dir/logminer_dict.dat',
    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')
    -- 也可使用 startscn/endscn 指定 SCN 範圍
  );
END;
/

步驟 4：查詢分析結果 (V$LOGMNR_CONTENTS)

查詢 DML 操作

SELECT operation, sql_redo, sql_undo, timestamp, scn, username, table_name
FROM v$logmnr_contents
WHERE table_name = 'EMPLOYEES'
  AND operation IN ('INSERT','UPDATE','DELETE')
ORDER BY timestamp;

關鍵欄位說明：

• OPERATION：INSERT/UPDATE/DELETE/DDL
• SQL_REDO：正向重做 SQL（可直接執行復原）
• SQL_UNDO：反向回滾 SQL（可撤銷操作）
• TIMESTAMP、SCN：操作時間與系統變更號
• USERNAME：執行使用者

查詢 DDL 操作 (9i+)

SELECT timestamp, sql_redo, username
FROM v$logmnr_contents
WHERE operation = 'DDL'
  AND UPPER(sql_redo) LIKE '%TRUNCATE%'
ORDER BY timestamp;

步驟 5：結束 LogMiner

BEGIN
  DBMS_LOGMNR.END_LOGMNR;
END;
/

相關字典視圖速查

實戰案例：找出誤刪資料的時間點

1. 確知大約發生日期 (如 1 月 15 日)。
2. 加入該日期前後的歸檔日誌。
3. 啟動 LogMiner 並查詢：WHERE operation='DELETE' AND table_name='ORDERS'
4. 從 SQL_UNDO 取得 INSERT 陳述式，直接執行即可恢復資料。
5. 記錄 TIMESTAMP/SCN 作為 PITR 恢復依據。

進階技巧

• 連續挖掘：對於大量日誌，分批加入 ADDFILE、查詢、REMOVEFILE 避免 PGA 爆量。
• 只挖掘特定 Schema：DBMS_LOGMNR.START_LOGMNR(..., options => DBMS_LOGMNR.SKIP_CORRUPTION + DBMS_LOGMNR.NO_SQL_DELIMITER) 配合 WHERE owner='SCOTT' 過濾。
• 整合 Enterprise Manager：OEM 提供圖形化 LogMiner 介面，支援視覺化時間軸篩選。

結語

LogMiner 是 DBA 從 Redo Log 還原真相的關鍵工具。熟練掌握字典建立、日誌加入、時間/SCN 範圍過濾、SQL_REDO/UNDO 應用，可將誤操作損失降至最低。