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

Oracle 外部表實戰：讀取 Alert Log 進行自動化監控

編輯：Oracle 資料庫基礎

外部表核心概念

定義儲存在資料字典，資料儲存在 OS 檔案
唯讀：不支援 DML (INSERT/UPDATE/DELETE)，不支援索引
用途：直接用 SQL 查詢 OS 檔案、載入資料到資料庫表
存取驅動程式：ORACLE_LOADER (文字檔)、ORACLE_DATAPUMP (二進位轉儲)

建立外部表讀取 Alert Log 完整步驟

1. 準備目錄與權限


-- OS 層建立目錄並確保 Oracle 進程可讀
mkdir -p /u01/app/oracle/admin/orcl/adump
chown oracle:oinstall /u01/app/oracle/admin/orcl/adump

-- 資料庫建立 Directory 物件
CREATE DIRECTORY alert_log_dir AS '/u01/app/oracle/admin/orcl/adump';
GRANT READ ON DIRECTORY alert_log_dir TO monitor_user;

2. 建立外部表對應 alert_.log


CREATE TABLE alert_log_ext (
  log_date   VARCHAR2(100),
  log_text   VARCHAR2(4000)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY alert_log_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    NOBADFILE
    NOLOGFILE
    NODISCARDFILE
    FIELDS TERMINATED BY '¥'  -- 使用不會出現的分隔符，整行作為單一欄位
    MISSING FIELD VALUES ARE NULL
    (
      log_date CHAR(100),
      log_text CHAR(4000)
    )
  )
  LOCATION ('alert_orcl.log')
)
REJECT LIMIT UNLIMITED;

3. 查詢驗證


SELECT * FROM alert_log_ext WHERE ROWNUM <= 10;

-- 實用查詢：找出錯誤訊息
SELECT log_date, log_text 
FROM alert_log_ext 
WHERE log_text LIKE '%ORA-%' 
   OR log_text LIKE '%ERROR%' 
   OR log_text LIKE '%FAILED%'
ORDER BY log_date DESC;

進階：結構化解析 Alert Log

Alert Log 格式雜亂，建議建立 View 進行正規化：


CREATE OR REPLACE VIEW v_alert_log_parsed AS
SELECT 
  TO_TIMESTAMP(
    REGEXP_SUBSTR(log_text, '^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}.\d{3}'), 
    'YYYY-MM-DD"T"HH24:MI:SS.FF3'
  ) AS log_timestamp,
  REGEXP_SUBSTR(log_text, 'ORA-\d+') AS ora_error,
  REGEXP_REPLACE(log_text, '^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}.\d{3}\s*', '') AS message
FROM alert_log_ext
WHERE log_text IS NOT NULL;

自動化監控應用

1. 定期檢查新錯誤 (搭配 DBMS_SCHEDULER)


BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'CHECK_ALERT_LOG_ERRORS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[
      DECLARE
        v_count NUMBER;
      BEGIN
        SELECT COUNT(*) INTO v_count
        FROM v_alert_log_parsed
        WHERE log_timestamp > SYSTIMESTAMP - INTERVAL '15' MINUTE
          AND ora_error IS NOT NULL;
        IF v_count > 0 THEN
          -- 發送郵件/簡訊/寫入監控表
          NULL; -- 實作通知邏輯
        END IF;
      END;
    ]',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=15',
    enabled         => TRUE
  );
END;
/

2. 歷史趨勢分析


SELECT TRUNC(log_timestamp, 'HH') AS hour, 
       COUNT(*) AS error_count
FROM v_alert_log_parsed
WHERE ora_error IS NOT NULL
  AND log_timestamp > SYSTIMESTAMP - INTERVAL '7' DAY
GROUP BY TRUNC(log_timestamp, 'HH')
ORDER BY hour;

注意事項與限制

外部表不支援並行查詢 (12c+ 部分支援)
大型 Alert Log 查詢較慢，建議定期載入到一般表 (CTAS) 再建索引分析
多實例環境每個實例需個別建立 Directory 與外部表
Windows 路徑格式：CREATE DIRECTORY ... AS 'D:\app\oracle\diag\rdbms\orcl\orcl\trace'
ADR (Automatic Diagnostic Repository) 下的 XML 格式 Alert Log 需用 ORACLE_DATAPUMP 或 XDB 解析

替代方案比較

方法	優點	缺點
外部表	SQL 介面、無需複製檔案、即時查詢	大檔案慢、不支援索引、格式解析需自行處理
ADRCI 命令列工具	原生支援 XML 格式、可篩選匯出	需 OS 存取、難整合排程
Enterprise Manager / Cloud Control	圖形化、內建告警、歷史趨勢	需授權、架構較重
Shell/Python 腳本 + 郵件	輕量、彈性高	維護成本、非資料庫原生