Oracle dbms_job故障时自动发送邮件通知的配置咨询

首先得说明：DBMS_JOB 是Oracle较早期的作业调度组件，本身没有原生的故障通知能力，所以我们有两种可行方案——迁移到更强大的DBMS_SCHEDULER并利用其原生通知机制（更推荐，功能更完善），或者在作业的存储过程中手动添加异常捕获逻辑主动发邮件。下面分别详细拆解：

方案一：迁移到DBMS_SCHEDULER（推荐方案） #

1. 补全邮件服务器属性配置 #

你已经创建了邮件凭证，现在需要补全SMTP服务器的剩余关键配置（Gmail的SMTP要求TLS加密）：

BEGIN
  DBMS_SCHEDULER.set_scheduler_attribute ('email_server', 'smtp.gmail.com:587');
  -- 启用TLS加密（Gmail SMTP强制要求）
  DBMS_SCHEDULER.set_scheduler_attribute ('email_server_encryption', 'TLS');
  -- 设置默认发件人（可选，也可以在通知规则里单独指定）
  DBMS_SCHEDULER.set_scheduler_attribute ('email_sender', 'test@gmail.com');
END;
/

2. 创建作业+绑定失败通知规则 #

先创建替代原DBMS_JOB的SCHEDULER作业，再为它绑定失败事件的邮件通知：

BEGIN
  -- 创建主作业（对应你原来的DBMS_JOB）
  DBMS_SCHEDULER.create_job(
    job_name        => 'TEST_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'test_job_procedure',
    start_date      => SYSDATE,
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=5', -- 对应你原来的SYSDATE + 1/24/12（每5分钟执行一次）
    enabled         => TRUE,
    comments        => '测试作业，执行失败时自动发送告警邮件'
  );

  -- 创建专门的邮件通知作业
  DBMS_SCHEDULER.create_job(
    job_name        => 'TEST_JOB_FAILURE_ALERT',
    job_type        => 'SEND_EMAIL',
    job_action      => 'recipient=>''your-alert-email@xxx.com'', subject=>''紧急：作业TEST_JOB执行失败'', message=>''作业TEST_JOB在''||SYSTIMESTAMP||''执行失败，请立即排查！''',
    enabled         => TRUE,
    comments        => 作业失败触发的告警邮件任务'
  );

  -- 将通知作业绑定到主作业的失败事件
  DBMS_SCHEDULER.add_event_queue_subscriber(
    subscriber_name => 'TEST_JOB_FAILURE_ALERT',
    event_queue     => 'SYS.SCHEDULER$_EVENT_QUEUE',
    condition       => 'tab.user_job_name = ''TEST_JOB'' AND tab.event_type = ''JOB_FAILED'''
  );
END;
/

3. 权限检查 #

确保当前操作的用户拥有必要权限：

GRANT EXECUTE ON DBMS_SCHEDULER TO your_user;
GRANT MANAGE SCHEDULER TO your_user;

方案二：保留DBMS_JOB，手动添加异常捕获 #

如果不想更换调度组件，可以修改你的作业存储过程，加入异常捕获逻辑，出错时主动发邮件：

1. 先创建通用的发邮件存储过程 #

CREATE OR REPLACE PROCEDURE send_alert_email(p_subject VARCHAR2, p_message VARCHAR2) AS
BEGIN
  UTL_MAIL.send(
    sender     => 'test@gmail.com',
    recipients => 'your-alert-email@xxx.com',
    subject    => p_subject,
    message    => p_message,
    smtp_host  => 'smtp.gmail.com',
    smtp_port  => 587,
    username   => 'test@gmail.com',
    password   => 'test'
  );
END;
/

注意：使用UTL_MAIL需要先做初始化配置：

ALTER SYSTEM SET utl_mail_smtp_server = 'smtp.gmail.com:587' SCOPE=BOTH;
GRANT EXECUTE ON UTL_MAIL TO your_user;

2. 修改原作业存储过程，加入异常处理 #

CREATE OR REPLACE PROCEDURE test_job_procedure AS
BEGIN
  -- 这里放你原来的作业业务逻辑代码
  -- ... 你的业务处理代码 ...
EXCEPTION
  WHEN OTHERS THEN
    -- 捕获所有异常，发送告警邮件
    send_alert_email(
      p_subject => 'DBMS_JOB作业执行失败告警',
      p_message => '作业执行失败时间：' || SYSTIMESTAMP || CHR(10) || '错误详情：' || SQLERRM
    );
    -- 可选：重新抛出异常，让DBMS_JOB标记作业为失败状态
    RAISE;
END;
/

3. 重新提交DBMS_JOB #

DECLARE
  jobno NUMBER;
BEGIN
  DBMS_JOB.submit(
    jobno,
    'test_job_procedure;',
    SYSDATE,
    'SYSDATE + 1/24/12' -- 保持原有的执行频率
  );
  COMMIT;
END;
/

关键注意事项 #

• 针对Gmail的SMTP服务：
  • 如果账号启用了两步验证，需要使用应用专用密码代替普通密码
  • 如果未启用两步验证，需要开启账号的「低权限应用访问」（部分地区可能已默认关闭）
  • 确保Oracle服务器所在的网络可以访问smtp.gmail.com的587端口
• 若使用其他邮件服务商，只需对应调整SMTP地址、端口和加密配置即可

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