alert.log 告警日志视图进阶

从11.2.0.1开始，可以通过视图看告警日志内容（老版本的怎么办？看文末）
原理是通过外部表来关联到具体的告警日志文件（你猜是关联xml格式还是.log文件？）

当然有可能出现的问题就是引起高cpu或看不到最新的告警日志
高CPU是因为日志文件太大了 ，解决方法见 [DOC ID 2056666.1]
看不到最新内容可能是文件被损坏了，见 [DOC ID 2262659.1]

从11g开始，oracle提供了 x$dbgalertext 视图，关联xml格式的告警信息

set lin 200 pages 1000
col message_text for a80
col riqi for a22

select to_char(originating_timestamp,'yyyy-mm-dd hh24:mi:ss')riqi,message_text
from x$dbgalertext
where originating_timestamp > sysdate - 7 and
  (message_text = 'ORA-00600'
OR message_text LIKE '%fatal%'
OR message_text LIKE '%error%'
OR message_text LIKE '%ORA-%'
OR message_text LIKE '%terminating the instance%'
);

message_text这么多like 是推荐的，看着MESSAGE_TYPE或 message_leve 很香，但不是你认为的香。

还是老实的like吧，不信的话

col message_text for a80
select message_level,count(0) from x$dbgalertext group by message_level;

col COMPONENT_ID for a20
select COMPONENT_ID,count(0) from x$dbgalertext group by COMPONENT_ID;

col riqi for a12
select to_char(originating_timestamp,'yyyy-mm-dd') riqi,count(0) from x$dbgalertext group by to_char(originating_timestamp,'yyyy-mm-dd') order by 1;

看看效果就知道了

从oracle 12.2开始可以把视图替换为 v$diag_alert_ext 

set lin 200 pages 1000
col message_text for a80
col riqi for a22
select to_char(originating_timestamp,'yyyy-mm-dd hh24:mi:ss')riqi,message_text
from v$diag_alert_ext
where originating_timestamp > sysdate - 7 and
  (
message_text = 'ORA-00600'
OR message_text LIKE '%fatal%'
OR message_text LIKE '%error%'
OR message_text LIKE '%ORA-%'
OR message_text LIKE '%terminating the instance%'
);


或者看最近一小时的(如果嫌慢的话)

col msg for a120
with oneday as (select /*+ materialize */ * from
 v$diag_alert_ext where ORIGINATING_TIMESTAMP>systimestamp-1)
select to_char(ORIGINATING_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')||' ' || message_text msg from oneday
where ORIGINATING_TIMESTAMP>systimestamp-5*(1/24/60) and -- 最近5分钟
message_text like '%ORA-%' and
message_text not like '%result of ORA-609%' and
message_text not like '%result of ORA-28%' and
message_text not like '%(ORA-3136)%' and
message_text not like '%ORA-01013:%';


col message_text for a80
select message_level,count(0) from v$diag_alert_ext group by message_level;

col COMPONENT_ID for a20
select COMPONENT_ID,count(0) from v$diag_alert_ext group by COMPONENT_ID;

col riqi for a12
select to_char(originating_timestamp,'yyyy-mm-dd') riqi,count(0) from v$diag_alert_ext group by to_char(originating_timestamp,'yyyy-mm-dd') order by 1;

效果如下：


11g以前的呢？

create directory BDUMP as '/u01/app/oracle/admin/orcl/bdump';

create table
   alert_log ( msg varchar2(200) )
organization external (
   type oracle_loader
   default directory BDUMP
   access parameters (
      records delimited by newline
   )
   location('alert_test.log')
)
reject limit 1000;

select msg from alert_log where msg like 'ORA-%';

凑合用吧。
但是普通用户无法访问X$DBGALERTEXT, 需要定制一下
create or replace view SYS.MGMT_ALERT_LOG as select * from X$DBGALERTEXT;
grant select on MGMT_ALERT_LOG to DB_MONITOR;