alert.log 告警日志视图进阶-凯发app官方网站

从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 '�tal%'
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 '�tal%'
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;

阅读(556) | 评论(0) | 转发(0) |

上一篇：总是提示temp不足与rman有关

下一篇：更新达梦license

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们