Oracle 自治事務實戰：錯誤日誌持久化的最佳模式與陷阱規避

核心痛點：主事務回滾導致日誌丟失

標準事務遵循 ACID，ROLLBACK 會撤銷該事務內所有 DML，包括寫入錯誤日誌表的 INSERT。若在異常處理塊中記錄日誌再拋出異常，日誌將隨主事務回滾而消失，導致故障無跡可查。

自治事務機制原理

• 獨立事務上下文：PRAGMA AUTONOMOUS_TRANSACTION 標記的 PL/SQL 塊/過程/函數/觸發器，在啟動時掛起主事務，開啟獨立事務分支
• 獨立提交/回滾：自治事務內的 COMMIT/ROLLBACK 僅影響自身，不影響主事務
• 可見性規則：自治事務 不可見 主事務未提交的變更 (類併發會話)；主事務 可見 已提交的自治事務結果
• 觸發器特例：行級觸發器中若無自治事務標記，禁止 COMMIT；加標記後，僅觸發器邏輯提交，主事務仍可回滾

標準錯誤日誌表設計

CREATE TABLE error_log (
  log_id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- 12c+ Identity
  log_timestamp   TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
  log_user        VARCHAR2(128) DEFAULT SYS_CONTEXT('USERENV','SESSION_USER'),
  module_name     VARCHAR2(100),
  error_code      NUMBER,
  error_msg       VARCHAR2(4000),
  sql_text        CLOB,
  bind_values     CLOB,
  call_stack      CLOB,
  session_id      NUMBER DEFAULT SYS_CONTEXT('USERENV','SID'),
  client_info     VARCHAR2(64)
);

-- 11g 兼容寫法
CREATE SEQUENCE error_log_seq;
CREATE OR REPLACE TRIGGER error_log_bir
BEFORE INSERT ON error_log FOR EACH ROW
WHEN (NEW.log_id IS NULL)
BEGIN SELECT error_log_seq.NEXTVAL INTO :NEW.log_id FROM DUAL; END;

自治事務日誌記錄過程 (最佳實踐模板)

CREATE OR REPLACE PROCEDURE log_error(
  p_module      IN VARCHAR2,
  p_error_code  IN NUMBER,
  p_error_msg   IN VARCHAR2,
  p_sql_text    IN CLOB DEFAULT NULL,
  p_binds       IN CLOB DEFAULT NULL,
  p_stack       IN VARCHAR2 DEFAULT NULL
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_call_stack VARCHAR2(32767);
BEGIN
  IF p_stack IS NULL THEN
    l_call_stack := DBMS_UTILITY.FORMAT_ERROR_STACK || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
  ELSE
    l_call_stack := p_stack;
  END IF;

  INSERT INTO error_log (module_name, error_code, error_msg, sql_text, bind_values, call_stack)
  VALUES (p_module, p_error_code, p_error_msg, p_sql_text, p_binds, l_call_stack);

  COMMIT;  -- 關鍵：自治事務必須顯式提交
EXCEPTION
  WHEN OTHERS THEN
    -- 日誌記錄本身失敗不應影響主事務，僅寫入 Alert Log
    DBMS_SYSTEM.KSDWRT(DBMS_SYSTEM.ALERT_FILE, 'ERROR LOG FAILED: '||SQLERRM);
    ROLLBACK; -- 回滾自治事務
END log_error;
/
GRANT EXECUTE ON log_error TO PUBLIC;  -- 或特定角色

業務過程調用示例

CREATE OR REPLACE PROCEDURE process_order(p_order_id NUMBER) IS
  l_sql_text CLOB := 'UPDATE orders SET status=''PROCESSING'' WHERE order_id = :1';
  l_binds    CLOB := 'ORDER_ID='||p_order_id;
BEGIN
  UPDATE orders SET status='PROCESSING' WHERE order_id = p_order_id;
  -- 業務邏輯...
  IF some_failure_condition THEN
    log_error('PROCESS_ORDER', -20001, 'Inventory allocation failed', l_sql_text, l_binds);
    RAISE_APPLICATION_ERROR(-20001, 'Order processing failed, logged');
  END IF;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    log_error('PROCESS_ORDER', SQLCODE, SQLERRM, l_sql_text, l_binds);
    RAISE;  -- 重新拋出，主事務回滾，但日誌已持久化
END process_order;
/

驗證測試腳本

-- 1. 構造測試表
CREATE TABLE test_txn (id NUMBER PRIMARY KEY, val VARCHAR2(100));

-- 2. 執行帶自治事務日誌的失敗事務
BEGIN
  INSERT INTO test_txn VALUES (1, 'before error');
  log_error('TEST_TXN', -20999, 'Simulated failure', 'INSERT INTO test_txn...', 'ID=1');
  RAISE_APPLICATION_ERROR(-20999, 'Force rollback');
EXCEPTION WHEN OTHERS THEN NULL; END;
/

-- 3. 驗證：主事務回滾，test_txn 無數據，error_log 有記錄
SELECT 'test_txn' tbl, COUNT(*) cnt FROM test_txn
UNION ALL SELECT 'error_log', COUNT(*) FROM error_log;

常見陷阱與規避

進階擴展：異步化與雲原生適配

1. DBMS_AQ 異步隊列：主事務入隊列，獨立消費者進程批量寫入日誌表，解耦延遲
2. Application Continuity (12c+)：可重放事務，配合自治事務日誌實現故障自愈
3. OpenTelemetry 集成：日誌表增加 trace_id/span_id 欄位，打通分布式追蹤體系
4. JSON 結構化日誌：bind_values JSON、call_stack JSON，支援 ELK/ClickHouse 直接入庫分析