Oracle 10g LogMiner 實戰全解析：從原理到誤刪資料找回完整演示

LogMiner 是什麼？為什麼 DBA 必須精通它？

Oracle LogMiner（日誌挖掘器）是分析 Redo Log（重做日誌）與 Archived Log（歸檔日誌）的核心工具。它能將二進制日誌還原為可讀的 SQL 語句，是 DBA 處理以下場景的「時光機」：

• 事務審計：追蹤特定用戶/時間段的所有 DML/DDL 操作
• 誤操作溯源：查明誰在何時執行了 DELETE/TRUNCATE/DROP
• 資料找回：當 Flashback 無法使用（超過 Undo 保留、表空間離線等）時，透過歸檔日誌產生 SQL_UNDO 反向語句恢復資料

核心套件與關鍵視圖速查表

權限授予：最小權限原則

-- 僅授予必要權限給分析用戶（如 SCOTT）
GRANT EXECUTE ON DBMS_LOGMNR TO scott;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO scott;
GRANT SELECT ON V_$LOGMNR_LOGS TO scott;
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO scott;
GRANT SELECT ON V_$DATABASE TO scoot;  -- 查詢 DBID/RESETLOGS

完整實戰演示：建表 → 插入 → 刪除 → 挖掘還原

步驟 1：準備測試資料並產生歸檔日誌

CONN / AS SYSDBA
ALTER SYSTEM ARCHIVE LOG CURRENT;  -- 切換日誌，確保有完整歸檔

CREATE TABLE mytesttab AS SELECT * FROM scott.dept;  -- 4 筆測試資料
INSERT INTO mytesttab VALUES (80, 'IT', 'WA');
INSERT INTO mytesttab VALUES (90, 'MGR', 'YR');
COMMIT;

DELETE FROM mytesttab;  -- 誤刪全部 6 筆
COMMIT;

ALTER SYSTEM ARCHIVE LOG CURRENT;  -- 再次切換，封存刪除操作

步驟 2：定位目標歸檔日誌檔案

-- 查詢近期歸檔日誌路徑
SELECT name FROM v$archived_log 
WHERE first_time > SYSDATE - 1/24  -- 最近 1 小時
ORDER BY first_time;

-- 假設目標檔案為：
-- D:/oracle/Flash_recovery_area/MYOracle/ARCHIVELOG/2007_08_11/O1_MF_1_4_3CVHLKJM_.ARC

步驟 3：啟動 LogMiner 分析會話

-- 1. 新增日誌檔案到分析清單
EXEC DBMS_LOGMNR.ADD_LOGFILE(
    LOGFILENAME => 'D:/oracle/Flash_recovery_area/MYOracle/ARCHIVELOG/2007_08_11/O1_MF_1_4_3CVHLKJM_.ARC',
    OPTIONS => DBMS_LOGMNR.NEW
);

-- 2. 啟動分析，使用線上資料字典（無需預建字典檔）
EXEC DBMS_LOGMNR.START_LOGMNR(
    OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
);

步驟 4：查詢挖掘結果 V$LOGMNR_CONTENTS

4.1 查看操作類型與時間線

SELECT scn, timestamp, seg_owner, operation
FROM v$logmnr_contents
WHERE seg_name = 'MYTESTTAB';

輸出示例：

4.2 關鍵欄位：SQL_REDO（正向 SQL）與 SQL_UNDO（反向 SQL）

-- 正向重做語句（可用於重演操作）
SELECT sql_redo FROM v$logmnr_contents WHERE seg_name = 'MYTESTTAB';

-- 反向撤銷語句（黃金資料：直接拿來執行即可恢復資料！）
SELECT sql_undo FROM v$logmnr_contents WHERE seg_name = 'MYTESTTAB';

SQL_UNDO 輸出範例（可直接執行恢復）：

INSERT INTO "SYS"."MYTESTTAB"("DEPTNO","DNAME","LOC") VALUES ('10','ACCOUNTING','NEW YORK');
INSERT INTO "SYS"."MYTESTTAB"("DEPTNO","DNAME","LOC") VALUES ('20','RESEARCH','DALLAS');
...
DELETE FROM "SYS"."MYTESTTAB" WHERE "DEPTNO"='80' AND "DNAME"='IT' AND "LOC"='WA' AND ROWID=...;

步驟 5：結束分析會話釋放記憶體

EXEC DBMS_LOGMNR.END_LOGMNR;

進階技巧與避坑指南

1. 多日誌跨時段分析

-- 連續添加多個歸檔日誌
EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '...arc1', OPTIONS => DBMS_LOGMNR.NEW);
EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '...arc2', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '...arc3', OPTIONS => DBMS_LOGMNR.ADDFILE);

2. 依時間/SCN 範圍過濾，大幅提升效能

EXEC DBMS_LOGMNR.START_LOGMNR(
    OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG 
             + DBMS_LOGMNR.START_SCN(123456) 
             + DBMS_LOGMNR.END_SCN(123999)
    -- 或使用時間
    -- START_TIME => TO_DATE('2024-01-15 10:00','YYYY-MM-DD HH24:MI')
    -- END_TIME   => TO_DATE('2024-01-15 11:00','YYYY-MM-DD HH24:MI')
);

3. 只挖掘特定表/用戶/操作類型

-- 僅看 SCOTT 用戶的 EMP 表刪除操作
SELECT sql_undo 
FROM v$logmnr_contents 
WHERE seg_owner = 'SCOTT' 
  AND seg_name = 'EMP' 
  AND operation = 'DELETE';

4. 字典選擇策略

實戰案例：誤刪表資料 30 分鐘內極速恢復

1. 確認資料庫處於歸檔模式（ARCHIVELOG）
2. 定位誤操作時間點前後的歸檔日誌
3. 啟動 LogMiner 指定 START_TIME/END_TIME 縮小範圍
4. 查詢 SQL_UNDO 產生恢復腳本
5. 在測試環境驗證腳本正確性
6. 生產環境執行恢復，提交事務

總結

LogMiner 是 Oracle DBA 工具箱中的「核武器級」工具。掌握 ADD_LOGFILE → START_LOGMNR → 查詢 V$LOGMNR_CONTENTS → END_LOGMNR 核心流程，配合 SQL_UNDO 反向語句，能在 Flashback 失效時成為最後防線。建議每位 DBA 定期演練「誤刪資料 → LogMiner 恢復」全流程，將 RTO（恢復時間目標）壓縮至分鐘級。