Oracle 19c触发器不支持调度作业，如何实现调度作业自动备份？

Oracle 19c中常规的AFTER CREATE OR ALTER DDL触发器无法捕获调度作业（DBMS_SCHEDULER作业）的创建或修改操作，因为这类作业本质是通过PL/SQL包过程维护的，不属于DDL触发器的监控范围。以下是几种可行的实现方案：

方案1：使用Scheduler事件触发器（推荐，实时性高） #

Scheduler组件本身支持事件驱动触发器，可直接捕获作业创建、修改的系统事件，触发备份逻辑。

步骤示例： #

1. 创建备份作业信息的存储过程（假设已有基础备份表JOB_BACKUP_LOG）：

CREATE OR REPLACE PROCEDURE BACKUP_SCHED_JOB(p_job_name IN VARCHAR2, p_event_type IN VARCHAR2) IS
    v_job_def CLOB;
    v_oper_user VARCHAR2(100);
    v_oper_time TIMESTAMP;
BEGIN
    v_oper_user := SYS_CONTEXT('USERENV', 'SESSION_USER');
    v_oper_time := SYSTIMESTAMP;
    -- 导出作业定义
    DBMS_SCHEDULER.EXPORT_JOB(
        job_name => p_job_name,
        job_definition => v_job_def
    );
    -- 插入备份日志
    INSERT INTO JOB_BACKUP_LOG(
        JOB_NAME, OPER_USER, OPER_TIME, OPER_TYPE, JOB_DEFINITION
    ) VALUES(
        p_job_name, v_oper_user, v_oper_time, 
        CASE p_event_type WHEN 'ORA_SCHEDULER.JOB_CREATED' THEN 'CREATE' ELSE 'ALTER' END,
        v_job_def
    );
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

2. 创建Scheduler事件触发器：

BEGIN
    DBMS_SCHEDULER.CREATE_TRIGGER(
        trigger_name => 'TRG_SCHED_JOB_CHANGE',
        event_condition => 'EVENT_NAME IN (''ORA_SCHEDULER.JOB_CREATED'', ''ORA_SCHEDULER.JOB_UPDATED'')',
        action => 'BEGIN BACKUP_SCHED_JOB(:job_name, :event_name); END;',
        enabled => TRUE,
        comments => '捕获调度作业创建/修改事件并触发备份'
    );
END;
/

该触发器会在作业被创建或修改时自动调用备份过程，实时性强。

方案2：定时检查Scheduler视图（适合非实时场景） #

通过定时任务定期对比DBA_SCHEDULER_JOBS视图的变更，捕获新增或修改的作业并备份。

步骤示例： #

1. 创建备份状态记录表（记录上次检查的作业状态）：

CREATE TABLE JOB_LAST_CHECK(
    JOB_NAME VARCHAR2(128) PRIMARY KEY,
    LAST_UPDATED TIMESTAMP,
    VERSION NUMBER
);

2. 创建定时检查并备份的存储过程：

CREATE OR REPLACE PROCEDURE CHECK_AND_BACKUP_JOBS IS
    CURSOR c_changed_jobs IS
        SELECT j.JOB_NAME, j.LAST_UPDATED, j.VERSION
        FROM DBA_SCHEDULER_JOBS j
        LEFT JOIN JOB_LAST_CHECK l ON j.JOB_NAME = l.JOB_NAME
        WHERE l.JOB_NAME IS NULL -- 新增作业
           OR j.LAST_UPDATED > l.LAST_UPDATED -- 修改过的作业
           OR j.VERSION != l.VERSION; -- 版本变更
    v_job_def CLOB;
BEGIN
    FOR rec IN c_changed_jobs LOOP
        DBMS_SCHEDULER.EXPORT_JOB(rec.JOB_NAME, v_job_def);
        -- 合并更新备份表
        MERGE INTO JOB_BACKUP_LOG b
        USING (
            SELECT rec.JOB_NAME,
                   SYS_CONTEXT('USERENV', 'SESSION_USER') AS OPER_USER,
                   SYSTIMESTAMP AS OPER_TIME,
                   CASE WHEN l.JOB_NAME IS NULL THEN 'CREATE' ELSE 'ALTER' END AS OPER_TYPE,
                   v_job_def AS JOB_DEFINITION
            FROM DUAL
            LEFT JOIN JOB_LAST_CHECK l ON rec.JOB_NAME = l.JOB_NAME
        ) src
        ON (b.JOB_NAME = src.JOB_NAME AND b.OPER_TYPE = src.OPER_TYPE)
        WHEN MATCHED THEN
            UPDATE SET b.JOB_DEFINITION = src.JOB_DEFINITION, b.OPER_TIME = src.OPER_TIME
        WHEN NOT MATCHED THEN
            INSERT (JOB_NAME, OPER_USER, OPER_TIME, OPER_TYPE, JOB_DEFINITION)
            VALUES (src.JOB_NAME, src.OPER_USER, src.OPER_TIME, src.OPER_TYPE, src.JOB_DEFINITION);
        -- 更新检查状态表
        MERGE INTO JOB_LAST_CHECK l
        USING (SELECT rec.JOB_NAME, rec.LAST_UPDATED, rec.VERSION FROM DUAL) src
        ON (l.JOB_NAME = src.JOB_NAME)
        WHEN MATCHED THEN
            UPDATE SET l.LAST_UPDATED = src.LAST_UPDATED, l.VERSION = src.VERSION
        WHEN NOT MATCHED THEN
            INSERT (JOB_NAME, LAST_UPDATED, VERSION)
            VALUES (src.JOB_NAME, src.LAST_UPDATED, src.VERSION);
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

3. 创建定时调度作业：

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'JOB_CHECK_JOB_CHANGES',
        job_type => 'PLSQL_BLOCK',
        job_action => 'BEGIN CHECK_AND_BACKUP_JOBS; END;',
        start_date => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MINUTELY; INTERVAL=10', -- 每10分钟检查一次
        enabled => TRUE,
        comments => '定时检查调度作业变更并备份'
    );
END;
/

方案3：启用审计捕获操作 #

通过Oracle审计功能，捕获对DBMS_SCHEDULER包的调用，从审计日志中提取信息并备份作业。

步骤示例： #

1. 启用DBMS_SCHEDULER包的审计：

AUDIT EXECUTE ON DBMS_SCHEDULER BY ACCESS;

2. 创建解析审计日志并备份的过程：

CREATE OR REPLACE PROCEDURE AUDIT_AND_BACKUP_JOBS IS
    -- 记录已处理审计记录的表
    CURSOR c_audit_records IS
        SELECT USERNAME, TIMESTAMP, SQL_TEXT
        FROM DBA_AUDIT_TRAIL
        WHERE OBJECT_NAME = 'DBMS_SCHEDULER'
          AND ACTION_NAME = 'EXECUTE'
          AND TIMESTAMP > (SELECT NVL(MAX(LAST_PROCESSED), TO_DATE('1900-01-01', 'YYYY-MM-DD')) FROM AUDIT_PROCESSED_LOG)
          AND (SQL_TEXT LIKE '%CREATE_JOB%' OR SQL_TEXT LIKE '%SET_ATTRIBUTE%');
    v_job_name VARCHAR2(128);
BEGIN
    FOR rec IN c_audit_records LOOP
        -- 从SQL语句中提取作业名称（需根据实际调用格式调整正则）
        IF rec.SQL_TEXT LIKE '%CREATE_JOB%' THEN
            v_job_name := REGEXP_SUBSTR(rec.SQL_TEXT, 'JOB_NAME\s*=>\s*''([^'']+)''', 1, 1, 'i', 1);
        ELSIF rec.SQL_TEXT LIKE '%SET_ATTRIBUTE%' THEN
            v_job_name := REGEXP_SUBSTR(rec.SQL_TEXT, 'NAME\s*=>\s*''([^'']+)''', 1, 1, 'i', 1);
        END IF;
        
        IF v_job_name IS NOT NULL THEN
            BACKUP_SCHED_JOB(v_job_name, 'ORA_SCHEDULER.JOB_UPDATED');
        END IF;
        
        -- 标记已处理的审计记录
        INSERT INTO AUDIT_PROCESSED_LOG(USERNAME, TIMESTAMP, SQL_TEXT)
        VALUES(rec.USERNAME, rec.TIMESTAMP, rec.SQL_TEXT);
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

注意：审计日志的解析逻辑需根据实际调用SQL格式调整，长期开启审计需定期清理日志以避免存储占用过高。

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