Oracle 发送QQ邮件监控定时任务

本文链接https://blog.csdn.net/qq_36237672/article/details/86607865

Oracle 中要对job的日志监控 /对定时任务监控

 Oracle数据库开发中免不了要做一些定时任务来同步数据/监控数据。

【建议使用工具：Oracle SQL developer】

【你需要有数据库的dba权限。能够解锁表的查询权限，否则可能你无法实现这个监控过程。如果你符合这个条件>>继续吧！】

【一、原理】

          通过查询job日志  来实现对job日志进行监控。因为job可能会跑死，他不会提示你job没跑。所以只能对job的状态进行检查，查看job的状态STATUS是否为FAILED（FAILED：job跑死了，SUCCEEDE：成功了）。

          查询错误的job,获得报错结果>>发送邮件。

【二、我们需要写的模块:只需要写两个存储过程】

  1.获取需要监控的job日志 存储过程（一般取STATUS就可以搞定，需要进一步信息可取ADDITIONAL_INFO字段内容）

  2.发送邮件 存储过程

【获取job日志信息语句】

--这两个表都可以查询，第二个表可以查询到详细的报错信息。看你需要
select * from DBA_SCHEDULER_JOB_LOG;

select * from dba_scheduler_job_run_details;

【查询指定用户下的指定job日志】

SELECT * 
FROM dba_scheduler_job_run_details
where OWNER='用户名'
AND JOB_NAME='job名';

--ADDITIONAL_INFO 这个字段是报错信息，job正常则为null ，执行报错会有报错日志。可取这个信息来发---详细的邮件报告。
--STATUS 这个字段是执行的状态 SUCCEEDED:成功   FAILED:失败  （大写）

 

【存储过程中 编译报错处理：错误(19,12): PL/SQL: ORA-00942: table or view does not exist】
 

--先使用普通用户登录数据库然后执行下面语句，输入密码。不知道密码？开头呢就已经说了，不知道是无法编译通过的。

SQL> conn sys as sysdba

grant select on dba_scheduler_job_run_details to 用户名;

--提示 grant succeeded 授权成功，存储过程就可以编译通过了。
--执行此句，发现报错。建议手敲代码重新执行。一般手敲的没什么问题，别忘了结束符 ;

【三、我们先创建程序包】

create or replace PACKAGE MONITOR_JOB Authid Current_User AS 
    PROCEDURE JOB_ERROR_MESSAGE;--监控job
    ---发送邮件（我直接使用QQ了，你也可以使用自己公司邮件）
    PROCEDURE sendQQ_mail(p_recipient VARCHAR2,p_subject VARCHAR2,p_message VARCHAR2);
END MONITOR_JOB;

【四、创建主体】（这里只监控了一个job，方便理解）

邮件发送参考别人写的，简单好用，直接拿来用了，后面有参考地址。网上有很多，有些功能写得很全面的。

以下内容如不懂SMTP没关系，后面会有说明。

create or replace PACKAGE BODY MONITOR_JOB 
AS

  PROCEDURE JOB_ERROR_MESSAGE AS
  JOB_SQL VARCHAR2(2000);
  JOB_STATUS VARCHAR2(25);
  ERROR_MESS VARCHAR2(2000);
  BEGIN
    ---监控job
    --获取job状态（我的job每天凌晨跑一次，这个存储过程早上6点跑。）
    --为什么要max? 因为防止job当天没有执行/还在执行，那么job没有日志，查询为空。会报错
    SELECT max(t.STATUS) into JOB_STATUS 
    FROM DBA_SCHEDULER_JOB_LOG t 
    where t.OWNER='用户名' AND t.JOB_NAME='job名' AND t.LOG_DATE>TRUNC(SYSDATE);
    
    if JOB_STATUS='FAILED'--如果失败发送邮件,还可以判断空。pu'huo
    then
       
      SELECT ADDITIONAL_INFO into ERROR_MESS ---获取报错信息
      FROM dba_scheduler_job_run_details t 
      where t.OWNER='用户名' AND t.JOB_NAME='job名' AND t.LOG_DATE>TRUNC(SYSDATE);

      sendQQ_mail('qq邮箱','邮件标题',ERROR_MESS);---调用邮件发送存过
    end if;

END JOB_ERROR_MESSAGE;

PROCEDURE sendQQ_mail(
       p_recipient VARCHAR2, -- 邮件接收人
       p_subject   VARCHAR2, -- 邮件标题
       p_message   VARCHAR2  -- 邮件正文
 )
 IS
      v_mailhost  VARCHAR2(30) := '14.18.245.164'; --SMTP服务器地址(域名无效，必须IP地址)
      v_user      VARCHAR2(30) := 'XXXXXX@qq.com'; --登录SMTP服务器的用户名
      v_pass      VARCHAR2(20) := 'SMTP授权码';      --登录SMTP服务器的密码
      v_sender    VARCHAR2(50) := 'XXXXXX@qq.com'; -发送者邮箱
      
      v_conn  UTL_SMTP.connection ;
      v_msg varchar2(4000);
 BEGIN
      v_conn := UTL_SMTP.open_connection(v_mailhost, 25);
      UTL_SMTP.ehlo(v_conn, v_mailhost); 

      UTL_SMTP.command(v_conn, 'AUTH LOGIN' );
      UTL_SMTP.command(v_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_user))));
      UTL_SMTP.command(v_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_pass))));

      UTL_SMTP.mail(v_conn, v_sender);
      UTL_SMTP.rcpt(v_conn, p_recipient);

      v_msg := 'Date:' || TO_CHAR(SYSDATE, 'dd mon yy hh24:mi:ss' )
          || UTL_TCP.CRLF || 'From: ' || '<' || v_sender || '>'
          || UTL_TCP.CRLF || 'To: ' || '<' || p_recipient || '>'
          || UTL_TCP.CRLF || 'Subject: ' || p_subject
          || UTL_TCP.CRLF || UTL_TCP.CRLF
          || p_message;
          
      UTL_SMTP.open_data(v_conn);
      UTL_SMTP.write_raw_data(v_conn, UTL_RAW.cast_to_raw(v_msg));
      UTL_SMTP.close_data(v_conn);
      UTL_SMTP.quit(v_conn);
 EXCEPTION
      WHEN OTHERS THEN
          DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
          DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);
 END sendQQ_mail;

END MONITOR_JOB;

【注意：这里很重要，上面看不懂的看这里】

STMP服务器地址：百度>>常用邮箱SMTP服务器地址 有很多
这里我只贴QQ的：

QQ邮箱（mail.qq.com）
POP3服务器地址：pop.qq.com（端口：110）
SMTP服务器地址：smtp.qq.com（端口：25）

当然了，SMTP服务器地址直接填  smtp.qq.com 是无效滴。下面就是度娘得强大之处了

【百度：smtp.qq.com IP地址】或者 https://site.ip138.com/smtp.qq.com/  获取IP地址
 下图贴了smtp.qq.com 地址。

IP很多看你心情选。想要哪个要哪个。

---------------------------------------------------------------------------------------------------------------

SMTP服务器地址我们已经搞定了，接下来是登录SMTP服务器的用户名和密码。
SMTP服务器用户名就是QQ号。密码不是QQ密码!   而是邮件的SMTP授权码。获取SMTP授权码看以下步骤：

登录QQ邮箱>>设置>>账户>>开启

点击开启，会有提示让你发短信，然后给你授权码。我这里已经开启了，就不演示了。

接下来就是调用 ：先测试能否发送QQ邮件。然后再试试发送错误告警。

 

存储过程发送邮件参考该博主：https://blog.csdn.net/u010759175/article/details/54897378