合 Oracle的告警日志之v$diag_alert_ext视图

最近由于自己写的一个job老是报错，找不出来原因，数据库linux的terminal由于安全原因不让连接，因此告警日志就没有办法阅读，没有办法就想想其它的办法吧，比如采用外部表的形式来阅读告警日志就是一个不错的办法。

告警日志的重要性就不多说了。。。。

1. 实验环境

本次所有的实验环境是Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production,10g的话应该很多是类似的，就不去研究那个了。。。。。

C:\Users\Administrator>sqlplus lhr/lhr@orclasm

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 7月 17 14:34:47 2014

Copyright (c) 1982, 2010, Oracle. All rights reserved.

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL>

1. ADR目录

Automatic Diagnostic Repository (ADR)

一个存放数据库诊断日志、跟踪文件的目录，称作ADR base，对应初始化参数DIAGNOSTIC_DEST，如果设置了ORACLE_BASE环境变量，DIAGNOSTIC_DEST等于 ORACLE_BASE，如果没有设置ORACLE_BASE，则等与ORACLE_HOME/log。**SQL> show parameter DIAGNOSTICNAME TYPE VALUE------------------------------------ ----------- ------------------------------**diagnostic_dest string /oracle/oracle

关于ADR这里不多说了，网上一百度一大堆。。。。。。。

1. 告警文件的路径

首先，告警日志文件有2种类型，一个是纯文本格式的，另外一种是xml文件格式的，不管哪个版本都可以用这个参数得到纯文本格式告警日志的路径：

SQL> show parameter background_dump_dest

NAME TYPE VALUE

background_dump_dest string /u01/app/oracle/diag/rdbms/orc

lasm/orclasm/trace

SQL>

文本格式的日志还可以通过这个视图来查询：

select value from v$diag_info where name='Diag Trace';

还有xml格式的告警日志文件在：

SQL> select value from v$diag_info where name='Diag Alert';

VALUE

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

/u01/app/oracle/diag/rdbms/orclasm/orclasm/alert

SQL>

/u01/app/oracle/diag/rdbms/orclasm/orclasm/alert/log.xml

1. 告警日志的内容

• 消息和错误的类型(Types of messages and errors)
• ORA-600内部错误(ORA-600 internal errors that need immediate support from Oracle's customer support )'
• ORA-1578块损坏错误(ORA-1578 block corruption errors that require recovery)
• ORA-12012(作业队列错误(ORA-12012 job queue errors)
• 实例启动关闭，恢复等信息(STARTUP & SHUTDOWN, and RECOVER statement execution messages)
• 特定的DDL命令(Certain CREATE, ALTER, & DROP statements )
• 影响表空间，数据文件及回滚段的命令(Statements that effect TABLESPACES, DATAFILES, and ROLLBACK SEGMENTS )
• 可持续的命令被挂起(When a resumable statement is suspended )
• LGWR不能写入到日志文件(When log writer (LGWR) cannot write to a member of a group )
• 归档进程启动信息(When new Archiver Process (ARCn) is started )
• 调度进程的相关信息(Dispatcher information)
• 动态参数的修改信息(The occurrence of someone changing a dynamic parameter)

1. 使用外部表查看oracle报警日志

关于外部表的使用网上一搜又是一大堆，这里不列举起语法了，直接到使用层次吧。。。。。

1. 先来个最简单的使用方法

SQL> drop directory DIR_ALERT;

目录已删除。

SQL> create directory DIR_ALERT as '/u01/app/oracle/diag/rdbms/orclasm/orclasm/trace';

目录已创建。

SQL>

SQL>

SQL> drop table alert_log;

表已删除。

SQL> create table alert_log(

2 　　text varchar2(500)

3 　　)organization external

4 　　(type oracle_loader

5 　　default directory DIR_ALERT

6 　　access parameters

7 　　(records delimited by newline

8 　　)location('alert_orclasm.log')

9 　　) reject limit unlimited;

表已创建。

SQL>

查看ora错误：

select * from alert_log where text like 'ORA-%';

-------查看最新的10条告警日志记录

select * from (

select rownum rn,a.text from alert_log a) b where b.rn>=(select count(1)-10 from alert_log a);

-------查看最新的10条ora告警日志记录

SELECT *

FROM (SELECT rownum rn,

a.text

FROM alert_log a

WHERE a.text LIKE 'ORA-%') b

WHERE b.rn >=

(SELECT COUNT(1) - 10 FROM alert_log a WHERE a.text LIKE 'ORA-%');

以上代码细心的网友可能会发现一个缺点，我不能查看历史某一时间段内的告警日志，或者说查看历史某一时间段内的告警日志很困难。。。。别急，，，，哥还有办法的。。。。。以下给出另一段代码，这段代码可以把历史告警日志做了格式化处理，采用了分区表的形式，我不运行了，直接贴代码了：

1. 再来个稍微复杂点的

------创建表xb_alert_log_lhr用于存放告警日志的历史信息

-- drop table xb_alert_log_lhr;

​ create table xb_alert_log_lhr (

​ id number primary key,

​ alert_date date,

​ alert_text varchar2(500)

​ ) nologging

​ partition by range(alert_date)

interval(numtoyminterval(1,'month'))

(partition P201406 VALUES LESS THAN(TO_DATE('201407','YYYYMM')));

​

​ create sequence s_xb_alert_log_lhr ;

​ create index alert_log_idx on xb_alert_log_lhr(alert_date) local nologging ; --为表alert_log创建索引

​ column db new_value _DB noprint;

​ column bdump new_value _bdump noprint;

​ select instance_name db from v$instance; --获得实例名以及告警日志路径

​ select value bdump from v$parameter

​ where name ='background_dump_dest';

​

​

-- drop directory DIR_ALERT_LHR;

​ create directory DIR_ALERT_LHR as '/u01/app/oracle/diag/rdbms/orclasm/orclasm/trace';

-- drop table xb_alert_log_disk_lhr;

​ create table xb_alert_log_disk_lhr ( text varchar2(500) ) --创建外部表

​ organization external (

​ type oracle_loader

​ default directory DIR_ALERT_LHR

​ access parameters (

​ records delimited by newline nologfile nobadfile

​ )

​ location('alert_orclasm.log')

​ ) reject limit unlimited;

CREATE OR REPLACE PROCEDURE pro_alert_log_lhr AS

isdate NUMBER := 0;

start_updating NUMBER := 0;

v_rows_inserted NUMBER := 0;

v_alert_date DATE;

v_max_date DATE;

v_alert_text xb_alert_log_disk_lhr.text%TYPE;

BEGIN

EXECUTE IMMEDIATE 'alter session set NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';

EXECUTE IMMEDIATE 'alter session set nls_date_language=''american''';

/ find a starting date /

SELECT MAX(v_alert_date) INTO v_max_date FROM xb_alert_log_lhr;

IF (v_max_date IS NULL) THEN

v_max_date := to_date('01-01-1980', 'dd-mm-yyyy');

END IF;

--使用for循环从告警日志过滤信息

FOR cur IN (SELECT *

FROM xb_alert_log_disk_lhr

) LOOP

isdate := 0;

v_alert_text := NULL;

SELECT COUNT(*)

INTO isdate --设定标志位，用于判断该行是否为时间数据

FROM dual

WHERE substr(cur.text, 21) IN

('2009', '2010', '2011', '2012', '2013', '2014', '2015') ---Sat Jun 14 23:22:14 2014

AND length(cur.text) = 24;

IF (isdate = 1) THEN

--将时间数据格式化

SELECT to_date(substr(cur.text, 5), 'Mon dd hh24:mi:ss rrrr')

INTO v_alert_date

FROM dual;

IF (v_alert_date > v_max_date) THEN

--设定标志位用于判断是否需要update

start_updating := 1;

END IF;

ELSE

v_alert_text := cur.text;

END IF;

IF (v_alert_text IS NOT NULL) AND (start_updating = 1) THEN

--start_updating标志位与v_alert_text为真，插入记录

INSERT INTO xb_alert_log_lhr nologging

(id, alert_date, alert_text)

VALUES

(s_xb_alert_log_lhr.nextval, v_alert_date, v_alert_text);

v_rows_inserted := v_rows_inserted + 1;

COMMIT;

END IF;

END LOOP;

sys.dbms_output.put_line('Inserting after date ' ||

to_char(v_max_date, 'YYYY-MM-DD HH24:MI:SS'));

sys.dbms_output.put_line('Rows Inserted: ' || v_rows_inserted);

COMMIT;

END pro_alert_log_lhr;

/

执行存过：

begin

pro_alert_log_lhr;

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。