根据 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/tianlesoftware/archive/2010/08/27/5842754.aspx

二. 存储过程

/* 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/tianlesoftware/archive/2009/10/22/4715218.aspx

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');

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

Blog： http://blog.youkuaiyun.com/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)

DBA 超级群：63306533(满); DBA4 群： 83829929 DBA5群： 142216823

DBA6 群：158654907 聊天 群：40132017 聊天2群：69087192

--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请