根据 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;

OWNERNAMETYPETEXTATTRIBUTE

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

SYSPROC_ALARM_KC_SCBPROCEDUREPL/SQL: ORA-00942: table or view does not existERROR

SYSPROC_ALARM_KC_SCBPROCEDUREPL/SQL: SQL Statement ignoredERROR

SYSPROC_ALARM_KC_SCBPROCEDUREPLS-00201: identifier 'JOBLOG.WRITELOG' must be de ERROR

SYSPROC_ALARM_KC_SCBPROCEDUREPL/SQL: Statement ignoredERROR

SYSPROC_ALARM_KC_SCBPROCEDUREPLS-00201: identifier 'RUNLOG.ERRORLOG' must be de ERROR

SYSPROC_ALARM_KC_SCBPROCEDUREPL/SQL: Statement ignoredERROR

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

如果该表里有这些错误,就可以调用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) */

CREATEORREPLACEPROCEDUREgetsenterrlog

AS

email_titleVARCHAR2(100);--email标题

email_contentVARCHAR2(1000);--email内容

email_serverVARCHAR2(20);-- email服务器

email_senderVARCHAR2(20);--email发送邮箱

email_userVARCHAR2(20);--email用户

email_pwdVARCHAR2(20);--emai密码

email_receiver1VARCHAR2(100);--email接收人

email_receiver2VARCHAR2(100);--email接收人

email_receiver3VARCHAR2(100);--email接收人

CURSORc1

IS

SELECT*FROMdba_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';

FORxINc1

LOOP

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

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

ENDIF;

ENDLOOP;

EXCEPTION

WHENNO_DATA_FOUND

THEN

RETURN;

WHENOTHERS

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



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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值