Oracle跨Schema数据迁移函数异常处理及日志记录故障排查

问题背景 #

该函数用于跨DB_LINK迁移数据，需求是异常发生时仅记录错误日志至ERR_LOG表，函数继续执行后续步骤，但当前存在两个核心问题：

1. 遇异常直接停止执行并回滚，无法继续后续迁移步骤
2. 部分场景下错误日志未被持久化记录

核心问题点分析 #

1. 全局异常处理阻断执行流程

   • 主EXCEPTION块中，WHEN OTHERS分支执行ROLLBACK TO SAVEPOINT INITI后直接RETURN 2，导致函数终止，后续步骤（如TUSR表插入）完全无法执行
   • DUP_VAL_ON_INDEX分支插入日志后无任何后续逻辑，函数会直接结束，不会继续执行下一个插入步骤

2. 日志未持久化

   • DUP_VAL_ON_INDEX分支插入日志后未执行COMMIT，若后续触发其他异常导致回滚，这部分日志会被一并回滚
   • 主事务回滚会连带回滚日志插入操作，无法保证日志的持久化

3. 未处理结构差异类异常

   • 源端与目标端Schema结构差异（如列类型不匹配、列缺失）会触发OTHERS异常，但当前日志仅记录"Error"，未保留具体错误信息，不利于排查
   • 批量插入无局部异常处理，单条数据异常会导致整批插入失败，无法跳过错误数据继续处理

4. 保存点设计不合理

   • 全局仅设置一个初始保存点，回滚会清空所有已执行的迁移操作，不符合"继续执行"的需求

修复后的函数代码 #

CREATE OR REPLACE FUNCTION FUN_INST
    RETURN NUMBER
IS
    V_STEP_NUM   NUMBER;
    vid          VARCHAR2 (32);
    vcrtUER      VARCHAR2 (32);
    
    -- 定义自治事务用于日志记录，确保日志不受主事务回滚影响
    PROCEDURE LOG_ERROR(p_step_num NUMBER, p_note VARCHAR2) IS
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        INSERT INTO ERR_LOG (ERROR_MESSAGE,
                             PROGRAM_UNIT,
                             STEP_NUM,
                             INCIDENCE_DATE,
                             NOTE)
             VALUES (SQLERRM,  -- 记录具体错误信息
                     'FUN_INST',
                     p_step_num,
                     SYSDATE,
                     p_note);
        COMMIT; -- 自治事务独立提交，确保日志持久化
    EXCEPTION
        WHEN OTHERS THEN
            -- 日志记录本身异常的保底处理
            COMMIT;
    END LOG_ERROR;

BEGIN
    V_STEP_NUM := 1;
    -- 为TRACES表插入单独添加局部异常处理
    BEGIN
        INSERT INTO TRACES (ID, crtUER, crtDAT, upUSR, upDAT)
            SELECT ID, crtUER, crtDAT, upUSR, upDAT FROM TRACES@dB_LINK1 
            RETURNING ID, CRTUER INTO VID, VCRTUER;
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            LOG_ERROR(V_STEP_NUM, 'UNIQUE_CONSTRAINT_VIOLATED');
        WHEN OTHERS THEN
            LOG_ERROR(V_STEP_NUM, 'TRACES_TABLE_MIGRATION_FAILED');
    END;

    V_STEP_NUM := 2;
    -- 为TUSR表插入单独添加局部异常处理
    BEGIN
        INSERT INTO Tusr (ID, UERname, crtDAT, usrfname, usrlname, lastSeen, lastAction)
        SELECT ID, UERname, crtDAT, usrfname, usrlname, NULL, NULL
          FROM Tusr@db_link1;
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            LOG_ERROR(V_STEP_NUM, 'UNIQUE_CONSTRAINT_VIOLATED');
        WHEN OTHERS THEN
            LOG_ERROR(V_STEP_NUM, 'TUSR_TABLE_MIGRATION_FAILED');
    END;

    -- 所有步骤执行完成后提交主事务
    COMMIT;
    RETURN 1;

EXCEPTION
    -- 全局兜底异常处理，确保函数不会意外终止
    WHEN OTHERS THEN
        LOG_ERROR(V_STEP_NUM, 'GLOBAL_UNEXPECTED_ERROR');
        RETURN 2;
END FUN_INST;

关键修复说明 #

1. 引入自治事务日志存储过程

   • 使用PRAGMA AUTONOMOUS_TRANSACTION定义独立事务，日志插入操作不受主事务回滚影响，确保异常日志100%持久化
   • 日志中记录SQLERRM获取具体错误信息，便于定位问题

2. 局部异常处理隔离步骤

   • 每个表的插入操作包裹独立的BEGIN-EXCEPTION块，某一步骤异常时仅记录日志，不会阻断后续步骤执行
   • 针对不同异常场景（唯一键冲突、其他异常）分别记录备注信息，提升排查效率

3. 移除全局回滚逻辑

   • 删除原有的ROLLBACK TO SAVEPOINT INITI，避免回滚已成功执行的迁移步骤
   • 主事务仅在所有步骤完成后统一提交，保证数据一致性

4. 优化执行流程

   • 去掉全局初始保存点，改为步骤级异常隔离，符合"继续执行"的核心需求
   • 全局EXCEPTION块仅做兜底处理，确保函数无论遇到何种异常都能返回明确状态码

内容的提问来源于stack exchange，提问作者sam