Oracle作业监控重写求助：无需自定义配置表实现精准监控

我之前参与过Oracle作业监控系统的重写项目，刚好碰到过和你一样的痛点——依赖自定义配置表不仅容易遗漏作业，还得手动维护阈值，简直是运维噩梦。下面分享几个我实际用过的思路和代码片段，应该能帮到你：

一、核心思路：用Oracle自带元数据替代自定义配置表 #

我们不需要为每个作业手动配置阈值，完全可以通过Oracle的系统视图提取历史运行数据、解析调度规则，自动计算出合理的预期执行时长和预期执行次数。

1. DBMS Jobs的基准值计算 #

DBMS Jobs的调度信息存在DBA_JOBS，历史运行记录可以查DBA_JOBS_HISTORY（注意默认可能没开启，需要确保JOB_QUEUE_PROCESSES参数大于0，且LOG_HISTORY设置了足够的保留天数）。

• 预期执行次数：解析DBA_JOBS.INTERVAL字段的调度周期，比如'SYSDATE + 1'就是每天1次，'SYSDATE + 1/24'就是每小时1次。可以通过字符串匹配或者转换为时间间隔来计算指定时间窗口内的预期次数。
• 预期执行时长：取最近10次成功运行的时长，用「均值+2倍标准差」作为阈值（过滤单次异常的超长运行），代码示例：

-- 计算DBMS Jobs的历史运行时长基准（最近10次成功）
SELECT job,
       owner,
       what AS job_name,
       ROUND(AVG(actual_secs)) AS avg_duration_sec,
       ROUND(AVG(actual_secs) + 2*STDDEV(actual_secs)) AS max_allowed_sec
FROM (
    SELECT j.job,
           j.owner,
           j.what,
           -- 计算单次运行时长（秒）
           (CAST(r.run_date AS DATE) + r.run_duration - CAST(r.run_date AS DATE)) * 86400 AS actual_secs,
           ROW_NUMBER() OVER (PARTITION BY j.job ORDER BY r.run_date DESC) AS rn
    FROM dba_jobs j
    LEFT JOIN dba_jobs_history r ON j.job = r.job
    WHERE r.status = 'SUCCEEDED'
)
WHERE rn <= 10
GROUP BY job, owner, what;

如果新作业没有历史记录，可以先设置一个临时默认阈值（比如30分钟），等收集到5次以上运行数据后再自动更新。

2. Scheduler Jobs的基准值计算 #

Scheduler Jobs的系统视图更完善，DBA_SCHEDULER_JOBS存调度规则，DBA_SCHEDULER_JOB_RUN_DETAILS有完整的运行日志。

• 预期执行次数：用Oracle自带的DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING函数解析REPEAT_INTERVAL，精准计算指定时间窗口内的预期运行次数，示例代码：

DECLARE
    l_start TIMESTAMP := SYSTIMESTAMP - INTERVAL '24' HOUR; -- 过去24小时
    l_end TIMESTAMP := SYSTIMESTAMP;
    l_next_run TIMESTAMP;
    l_expected_runs NUMBER := 0;
BEGIN
    l_next_run := l_start;
    -- 循环计算每个预期运行时间
    LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
            calendar_string => 'FREQ=HOURLY;INTERVAL=2', -- 替换为目标作业的REPEAT_INTERVAL
            start_date => l_start,
            return_date_after => l_next_run,
            next_run_date => l_next_run
        );
        EXIT WHEN l_next_run > l_end;
        l_expected_runs := l_expected_runs + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('过去24小时预期运行次数：' || l_expected_runs);
END;
/

• 预期执行时长：和DBMS Jobs逻辑类似，取最近10次成功运行的统计值作为阈值：

-- 计算Scheduler Jobs的历史运行时长基准（最近10次成功）
SELECT job_name,
       owner,
       ROUND(AVG(run_duration_sec)) AS avg_duration_sec,
       ROUND(AVG(run_duration_sec) + 2*STDDEV(run_duration_sec)) AS max_allowed_sec
FROM (
    SELECT job_name,
           owner,
           -- 计算单次运行时长（秒）
           (CAST(end_date AS DATE) - CAST(start_date AS DATE)) * 86400 AS run_duration_sec,
           ROW_NUMBER() OVER (PARTITION BY job_name, owner ORDER BY end_date DESC) AS rn
    FROM dba_scheduler_job_run_details
    WHERE status = 'SUCCEEDED'
)
WHERE rn <= 10
GROUP BY job_name, owner;

二、Nagios集成优化：统一检查逻辑 #

不用分开调用两个函数，建议写一个PL/SQL包统一处理DBMS Jobs和Scheduler Jobs的检查，返回标准化的Nagios状态码（0=OK，1=WARNING，2=CRITICAL）和描述信息，然后Nagios通过sqlplus或者Oracle插件调用这个包。

简化的PL/SQL包示例：

CREATE OR REPLACE PACKAGE job_monitor AS
    -- 定义检查结果类型
    TYPE check_result IS RECORD (
        job_type VARCHAR2(20), -- DBMS_JOB/SCHEDULER_JOB
        job_owner VARCHAR2(30),
        job_name VARCHAR2(100),
        check_item VARCHAR2(50), -- 检查项：执行时长/执行次数/运行状态
        status NUMBER, -- Nagios状态码
        message VARCHAR2(200) -- 描述信息
    );
    TYPE check_result_table IS TABLE OF check_result;
    
    -- 统一检查所有作业，参数为时间窗口（小时）
    FUNCTION check_all_jobs(p_time_window NUMBER DEFAULT 24) RETURN check_result_table;
END job_monitor;
/

CREATE OR REPLACE PACKAGE BODY job_monitor AS
    FUNCTION check_all_jobs(p_time_window NUMBER DEFAULT 24) RETURN check_result_table IS
        l_results check_result_table := check_result_table();
        -- 在这里实现具体的检查逻辑：
        -- 1. 查询DBMS Jobs的运行状态、实际执行次数/时长，和基准值对比
        -- 2. 查询Scheduler Jobs的运行状态、实际执行次数/时长，和基准值对比
        -- 3. 将检查结果存入l_results
    BEGIN
        -- 示例：添加一条测试结果
        l_results.EXTEND;
        l_results(l_results.COUNT) := (
            'SCHEDULER_JOB',
            'SYS',
            'BACKUP_JOB',
            '执行时长',
            0,
            '运行时长120秒，低于阈值300秒'
        );
        RETURN l_results;
    END check_all_jobs;
END job_monitor;

然后Nagios的命令配置可以这样写：

define command {
    command_name    check_oracle_jobs
    command_line    $USER1$/check_oracle_plus -H $HOSTADDRESS$ -u $ARG1$ -p $ARG2$ -s "SET SERVEROUTPUT ON; DECLARE l_res job_monitor.check_result_table; BEGIN l_res := job_monitor.check_all_jobs(24); FOR i IN 1..l_res.COUNT LOOP DBMS_OUTPUT.PUT_LINE(l_res(i).status || ' ' || l_res(i).job_type || ' ' || l_res(i).job_owner || '.' || l_res(i).job_name || ': ' || l_res(i).message); END LOOP; END;"
}

三、额外注意事项 #

• 权限配置：确保Nagios使用的数据库用户有SELECT权限访问DBA_JOBS、DBA_JOBS_HISTORY、DBA_SCHEDULER_JOBS、DBA_SCHEDULER_JOB_RUN_DETAILS这些系统视图。
• 新作业处理：对于没有历史运行记录的新作业，可以标记为「待观察」，等收集到至少5次成功运行数据后再自动计算基准值。
• 动态更新：可以每天凌晨跑个定时任务，更新所有作业的基准值，避免因为作业运行模式变化导致阈值过时。

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