根据 dba_errors 制定 数据库报警邮件

一.  dba_errors 表

 

官网对这个表的说明如下：

 

ALL_ERRORS：describes the current errors on the stored objects accessible to the current user.

DBA_ERRORS：describes the current errors on all stored objects in the database.

 

USER_ERRORS： describes the current errors on the stored objects owned by the current user. This view does not display the OWNER column.

 

示例：

SQL> select owner,name,type,text,attribute from dba_errors;

 

OWNER      NAME                      TYPE         TEXT                                               ATTRIBUTE

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

SYS        PROC_ALARM_KC_SCB         PROCEDURE    PL/SQL: ORA-00942: table or view does not exist    ERROR

SYS        PROC_ALARM_KC_SCB         PROCEDURE    PL/SQL: SQL Statement ignored                      ERROR

SYS        PROC_ALARM_KC_SCB         PROCEDURE    PLS-00201: identifier 'JOBLOG.WRITELOG' must be de ERROR

SYS        PROC_ALARM_KC_SCB         PROCEDURE    PL/SQL: Statement ignored                          ERROR

SYS        PROC_ALARM_KC_SCB         PROCEDURE    PLS-00201: identifier 'RUNLOG.ERRORLOG' must be de ERROR

SYS        PROC_ALARM_KC_SCB         PROCEDURE    PL/SQL: Statement ignored                          ERROR

 

       从这个表里面，我们可以看到数据库当前存在的错误，这些错误信息包含对象名称，类型，所有者，错误类型和错误原因。

       如果该表里有这些错误，就可以调用Oracle 的发送邮件的存储过程，将这些信息发送到相关邮箱或者移动的139邮箱，该邮箱可以直接将邮件发送到手机。 这样就可以起到一个及时通知的作用。

 

发送邮件的存储过程参考Blog：

      

Oracle 发送邮件 存储过程

http://blog.youkuaiyun.com/xujinyang/article/details/6830560

 

二. 存储过程

 

/* Formatted on 2011/5/19 20:48:30 (QP5 v5.163.1008.3004) */

CREATE OR REPLACE PROCEDURE getsenterrlog

AS

   email_title      VARCHAR2 (100);                                 --email 标题

   email_content    VARCHAR2 (1000);                                --email 内容

   email_server     VARCHAR2 (20);                                -- email 服务器

   email_sender     VARCHAR2 (20);                                --email 发送邮箱

   email_user       VARCHAR2 (20);                                  --email 用户

   email_pwd        VARCHAR2 (20);                                   --emai 密码

   email_receiver1   VARCHAR2 (100);                               --email  接收人

   email_receiver2   VARCHAR2 (100);                               --email  接收人

   email_receiver3   VARCHAR2 (100);                               --email  接收人

   CURSOR c1

   IS

      SELECT * FROM dba_errors;

BEGIN

   /**

   过程内容： 查询dba_errors 表，如果有错误，就发送到139邮箱

   作者： Dave

   时间：2011-5-19

   */

   email_server := '192.168.1.100';

   email_sender := 'dvd.dba@gmail.com';

   email_user := 'tianlesoftware';

   email_pwd := 'pwd';

   email_receiver1 :=

      '13888888888@139.com;dvd.dba@gmail.com';

   email_receiver2 :=

      '13888888888@139.com;dvd.dba@gmail.com';

   email_receiver3 :=

      '13888888888@139.com;dvd.dba@gmail.com';

 

   FOR x IN c1

   LOOP

      IF x.owner = 'SYS'

      THEN

         email_title := x.owner|| ',s '|| x.TYPE|| ': '|| x.name|| ' report ' || x.attribute || '!';

         email_content := x.text;

         --  DBMS_OUTPUT.put_line (email_title);

         --DBMS_OUTPUT.put_line (email_content);

         procsendemail (email_content,email_title, email_sender,email_receiver1, email_server,25,1,email_user,email_pwd, '', 'bit 7');

      ELSIF x.owner = 'SYSTEM'

      THEN

         email_title := x.owner|| ',s '|| x.TYPE|| ': '|| x.name|| ' report ' || x.attribute || '!';

         email_content := x.text;

         --  DBMS_OUTPUT.put_line (email_title);

         --DBMS_OUTPUT.put_line (email_content);

              --调用发送邮件过程

         procsendemail (email_content,email_title, email_sender,email_receiver2, email_server,25,1,email_user,email_pwd, '', 'bit 7');

      ELSE

         email_title := x.owner|| ',s '|| x.TYPE|| ': '|| x.name|| ' report ' || x.attribute || '!';

         email_content := x.text;

         --  DBMS_OUTPUT.put_line (email_title);

         --DBMS_OUTPUT.put_line (email_content);

         procsendemail (email_content,email_title, email_sender,email_receiver3, email_server,25,1,email_user,email_pwd, '', 'bit 7');

      END IF;

   END LOOP;

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

      RETURN;

   WHEN OTHERS

   THEN

      RETURN;

END;

 

       不同用户的错误，发送到相关的责任人，当然DBA 肯定都要收到。

 

三. 使用Scheduler Job 部署

 

Oracle 10g Scheduler 特性

http://blog.youkuaiyun.com/xujinyang/article/details/6830222

 

3.1 创建Job：

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'Jobgetsenterrlog',

job_type => 'STORED_PROCEDURE',

job_action => 'GETSENTERRLOG',  --调用的过程名称

start_date => sysdate,

repeat_interval => 'FREQ=MINUTELY;INTERVAL=1'); -- 每个一分钟执行一次

END;

/

 

       关于这些参数的说明，参考上面的链接。

 

注意：

       JOB 虽然成功创建了，但却并未执行.因为ENABLED 参数当不显式指定时，该参数的默认值为false。

 

3.2 启用Job

exec dbms_scheduler.enable('Jobgetsenterrlog');

 

3.3 停止Job

exec dbms_scheduler.disable(' Jobgetsenterrlog');

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