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

Oracle 歸檔日誌暴增根因分析與 LogMiner 實戰排查指南

編輯：Oracle 教學

Oracle 歸檔日誌暴增根因分析與 LogMiner 實戰排查指南

歸檔日誌 (Archived Redo Log) 空間暴增通常意味著大量 DML 操作 (INSERT/UPDATE/DELETE) 產生海量 Redo。本文提供從現象確認、時間分佈分析、LogMiner 深度採礦到 TOAD 視覺化輔助的完整排查鏈路，助你精準定位異常 SQL 與業務模組。

一、現象確認：每日歸檔產生量趨勢

SELECT TRUNC(FIRST_TIME) "DAY",
       SUM(BLOCK_SIZE * BLOCKS) / 1024 / 1024 / 1024 "SIZE_GB"
FROM V$ARCHIVED_LOG
GROUP BY TRUNC(FIRST_TIME)
ORDER BY 1;

識別異常峰值日期/小時，鎖定問題時間窗口。
配合 V$LOG_HISTORY 確認切換頻率 (建議 15-30 分鐘/次)。

二、核心工具：LogMiner (SQL 採礦紅寶石)

LogMiner 可離線解析歸檔/線上 Redo Log，還原 DML/DDL 原始 SQL，是事後審計神器。

1. 環境準備 (僅需首次執行)

EXEC SYS.DBMS_LOGMNR.ADD_LOGFILE(
  LOGFILENAME => '/archivelog/node/1_771348_78836667000.arc',
  OPTIONS     => DBMS_LOGMNR.NEW);  -- NEW 表示新建分析會話

-- 追加更多檔案 (同一會話)
EXEC SYS.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/archivelog/node/1_773333_78836667000.arc');
EXEC SYS.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/archivelog/node1/1_773334_78836667000.arc');

3. 啟動採礦：使用線上數據字典 (最便捷)

-- 4.1 統計修改量最大的 Schema (鎖定應用模組)
SELECT SEG_OWNER, COUNT(*) CNT
FROM V$LOGMNR_CONTENTS
GROUP BY SEG_OWNER
ORDER BY CNT DESC;

-- 4.2 統計高頻 SQL 類型 (前 30 字元分組)
SELECT COUNT(*), SUBSTR(SQL_REDO, 1, 30) SQL_HEAD
FROM V$LOGMNR_CONTENTS
GROUP BY SUBSTR(SQL_REDO, 1, 30)
ORDER BY COUNT(*) DESC;

-- 4.3 針對特定表/時間/操作類型深入
SELECT OPERATION, TABLE_NAME, SQL_REDO, TIMESTAMP, USERNAME
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER = 'SCOTT' AND TABLE_NAME = 'EMP'
  AND OPERATION IN ('INSERT','UPDATE','DELETE')
  AND TIMESTAMP BETWEEN SYSDATE-1 AND SYSDATE;

5. 結束採礦釋放資源

EXEC SYS.DBMS_LOGMNR.END_LOGMNR;

三、輔助工具：TOAD Log Miner 視覺化介面

路徑：Database → Log Miner。
拖入目標歸檔檔案 → Start Mining。
提供類 Excel 表格檢視：SCN、Time、User、Table、Operation、SQL Redo、Undo SQL，支援篩選、排序、匯出 CSV。
適合快速瀏覽、非 PL/SQL 熟手、跨團隊溝通呈現。

四、常見暴增模式與對策

模式特徵	典型 SQL_REDO	可能原因	優化方向
大量單列 UPDATE	UPDATE T SET COL = :B1 WHERE PK = :B2	批次處理未提交、無索引全表掃描、觸發器級聯	批次提交、加索引、改 MERGE/批次 UPDATE、停用非必要觸發器
頻繁 INSERT/DELETE 同表	INSERT INTO T ... / DELETE FROM T WHERE ...	臨時表模擬佇列、快取刷新邏輯	改用 Global Temporary Table (ON COMMIT PRESERVE/DELETE ROWS)、Advanced Queuing
大量 LOB 寫入	UPDATE T SET CLOB_COL = ...	文件/日誌存資料庫、未啟用 SecureFiles/壓縮	啟用 SecureFiles DECOMPRESS/ENCRYPT、外部表/檔案系統存儲
全表刪除後重載	DELETE FROM T; INSERT INTO T SELECT ...	ETL 全量覆蓋	改 TRUNCATE + INSERT /+ APPEND /、Partition Exchange Load