怎么打印oracle错误日志,ORACLE监控系统错误日志过程

--创建临时表用来存放系统错误信息

create table superflow(cust_id number(10),cust_name varchar2(100),d varchar(50),error_info varchar2(500),client_ip varchar2(50),protocol varchar2(100),t number(20),sj date) tablespace db_data;

--drop table superflow

--创建数据写入路径

create directory db_dir as '/home/oracle';

--把目录授权给用户

grant read,write on directory db_dir to db;

监控系统错误日志

过程完成任务有

把抽取出来错误日志插入到superflow

错误日志会写入操作系统/home/oracle/sql.txt文件下面

create or replace procedure pro_superflow as

pro_name  VARCHAR2(50);

err_info  VARCHAR2(1000);

v_Sql     VARCHAR2(1000);

sj        VARCHAR2(20);

JL        NUMBER(10);

--cursor record_t_sms_customer_errlog is select c.cust_id "客户ID",cu.cust_name "客户名称",c.d "状态",c.error_info "错误信息",c.client_ip "客户IP",c.protocol "协议",c.t "记录数" from (select CUST_ID,case ACTION when '1' then '登入' else '下行' end d,ERROR_INFO,CLIENT_IP,PROTOCOL,COUNT(ERRLOG_ID) t from t_sms_customer_errlog where create_time > sysdate - interval '1' hour and err_code=8 group by cust_id, action, ERROR_INFO,client_ip, protocol) c,t_sms_customer cu where c.cust_id=cu.cust_id and t>5;

cursor record_t_sms_customer_errlog is select c.cust_id "客户ID",cu.cust_name "客户名称",c.d "状态",c.error_info "错误信息",c.client_ip "客户IP",c.protocol "协议",c.t "记录数" from (select CUST_ID,case ACTION when '1' then '登入' else '下行' end d,ERROR_INFO,CLIENT_IP,PROTOCOL,COUNT(ERRLOG_ID) t from t_sms_customer_errlog where err_code=8  group by cust_id, action, ERROR_INFO,client_ip, protocol) c,t_sms_customer cu where c.cust_id=cu.cust_id;

--all_record t_sms_customer_errlog%rowtype;

type table_type is record( cust_id_1 number(10),cust_name_1 varchar2(100),d varchar2(20),error_info_1 varchar2(1000),client_ip_1 varchar2(50),protocol_1 varchar2(1000),t_1 number(10));

all_record table_type;

vsfile  UTL_FILE.file_type;

begin

SELECT COUNT(1)INTO JL FROM superflow;

IF JL >10000 THEN

delete FROM SUPERFLOW where sj < sysdate - interval '7' day;

END IF;

vsfile :=

UTL_FILE.fopen ('DB_DIR',  ---&gt使用fopen打开文件,定义了文件路径,文件名,读写方式以及每一行字符的最大长度,缺省为1024

'sql.txt',

'W');

for all_record in record_t_sms_customer_errlog

loop

insert into superflow values(all_record.客户id,all_record.客户名称,all_record.状态,all_record.错误信息,all_record.客户ip,all_record.协议,all_record.记录数,sysdate);

commit;

UTL_FILE.put_line (vsfile, all_record.客户id);

UTL_FILE.put_line (vsfile, all_record.客户名称);

UTL_FILE.put_line (vsfile, all_record.状态);

UTL_FILE.put_line (vsfile, all_record.错误信息);

UTL_FILE.put_line (vsfile, all_record.客户ip);

--UTL_FILE.put_line (vsfile, all_record.协议)

UTL_FILE.put_line (vsfile, all_record.记录数);

UTL_FILE.put_line (vsfile, sysdate);

-- 把输入内容写入文件

end loop;

UTL_FILE.fflush (vsfile);

UTL_FILE.fclose (vsfile);

exception

when others then

--sg_log_err('manage_partition',sqlerrm);

pro_name :='manage_partition';

err_info :=sqlerrm;

select sysdate into sj from dual;

v_Sql := 'insert into err_log values('||'''PRO_SUPERFLOW'''||','''||err_info||''','''||sj||''')';

execute immediate v_Sql;

commit;

dbms_output.put_line(sqlcode||sqlerrm);

end pro_superflow;

把错误日志以邮件形式发出去

echo '监控日志'|mail -s '系统日志' 25235255@163.com

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29065182/viewspace-1681138/,如需转载,请注明出处,否则将追究法律责任。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值