合 Oracle的告警日志之v$diag_alert_ext视图
Tags： Oracle告警日志v$diag_alert_ext
最近由于自己写的一个job老是报错，找不出来原因，数据库linux的terminal由于安全原因不让连接，因此告警日志就没有办法阅读，没有办法就想想其它的办法吧，比如采用外部表的形式来阅读告警日志就是一个不错的办法。
告警日志的重要性就不多说了。。。。
实验环境
本次所有的实验环境是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>
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这里不多说了，网上一百度一大堆。。。。。。。
告警文件的路径
首先，告警日志文件有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
告警日志的内容
- 消息和错误的类型(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)
使用外部表查看oracle报警日志
关于外部表的使用网上一搜又是一大堆，这里不列举起语法了，直接到使用层次吧。。。。。
先来个最简单的使用方法
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-%');
以上代码细心的网友可能会发现一个缺点，我不能查看历史某一时间段内的告警日志，或者说查看历史某一时间段内的告警日志很困难。。。。别急，，，，哥还有办法的。。。。。以下给出另一段代码，这段代码可以把历史告警日志做了格式化处理，采用了分区表的形式，我不运行了，直接贴代码了：
再来个稍微复杂点的
------创建表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;
