本文根据刘相兵大师的blog的脚本,并做了一些该进:
有同学问是否可以用SQL语句直接查询告警日志的内容,即创建一张包含Alert.log内容的表或视图
- --drop table alert_log_view;
- --drop directory bdump;
- declare
- path_bdump varchar2(4000);
- name_alert varchar2(4000);
- ins_name varchar2(200);
- begin
- select value
- into path_bdump
- from sys.v_$parameter
- where name = 'background_dump_dest';
- select 'alert_' || value || '.log'
- into name_alert
- from sys.v_$parameter
- where name = 'instance_name';
- select value
- into ins_name
- from sys.v_$parameter
- where name = 'instance_number';
- if ins_name = '0' then
- ins_name := '';
- end if;
- execute immediate 'create or replace directory bdump'||ins_name||' as ''' || path_bdump || '''';
- execute immediate 'create table ALERT_LOG_VIEW' || ins_name ||
- ' (MSG_line varchar2(4000) ) ' ||
- ' organization external ' || ' (type oracle_loader ' ||
- ' default directory bdump' || ins_name ||
- ' access parameters ( ' ||
- ' records delimited by newline ' || ' nobadfile ' ||
- ' nologfile ' || ' nodiscardfile ' || ' skip 0 ' ||
- ' READSIZE 10485760 ' || ' FIELDS LDRTRIM ' ||
- ' REJECT ROWS WITH ALL NULL FIELDS ' ||
- ' (MSG_LINE (1:1000) CHAR(1000)) ' || ' ) ' ||
- ' location (''' || name_alert || ''') )' ||
- ' reject limit unlimited ' ||
- ' noparallel nomonitoring ';
- end;
- /
执行以上PL/SQL代码,会创建名为bdump$SID的目录 和ALERT_LOG_VIEW$SID的外部表(如RAC中的1号实例PROD1,则为ALERT_LOG_VIEW1,单实例single instance则为 ALERT_LOG_VIEW), 需要时直接查询ALERT_LOG_VIEW即可,譬如要从告警信息中找出最近三天ORA-错误的记录:
- col lineno noprint
- col ora_error noprint
- col msg_line format a132
- set pages 0 lines 300 trimspool on trim on
- alter session set nls_date_language = 'american';
- alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
- alter session set sql_trace=false;
- break on thedate
- prompt
- prompt ERROR IN ALERT LOG FILE - LAST 3 DAYS
- prompt =====================================
- select "LINENO", "THEDATE", "ORA_ERROR", "MSG_LINE"
- from (select *
- from (select lineno,
- msg_line,
- thedate,
- max(case
- when (ora_error like 'ORA-%' or
- ora_error like 'PLS-%' or
- ora_error like 'TNS-%'
- ) then
- rtrim(substr(ora_error, 1, instr(ora_error, ' ') - 1),
- ':')
- else
- null
- end) over(partition by thedate) ora_error
- from (select lineno,
- msg_line,
- max(thedate) over(order by lineno) thedate,
- lead(msg_line) over(order by lineno) ora_error
- from (select rownum lineno,
- substr(msg_line, 1, 132) msg_line,
- case
- when msg_line like
- '___ ___ __ __:__:__ ____' then
- to_date(msg_line,
- 'Dy Mon DD hh24:mi:ss yyyy')
- else
- null
- end thedate
- from ALERT_LOG_VIEW))))
- where ora_error is not null
- and thedate >= (trunc(sysdate) - 3)
- order by thedate
- /
实例输出:
- 10/11/2011 03:15:49 Thu Nov 10 03:15:49 2011
- Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_5547.trc (incident=11105):
- ORA-00700: soft internal error, arguments: [kgerev1], [600], [600], [700], [], [], [], [], [], [], [], []
- Incident details in: /s01/orabase/diag/rdbms/vprod/VPROD1/incident/incdir_11105/VPROD1_ora_5547_i11105.trc
- Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_5547.trc (incident=11106):
- ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []
- Incident details in: /s01/orabase/diag/rdbms/vprod/VPROD1/incident/incdir_11106/VPROD1_ora_5547_i11106.trc
- 10/11/2011 03:15:52 Thu Nov 10 03:15:52 2011
- Dumping diagnostic data in directory=[cdmp_20111110031552], requested by (instance=1, osid=5547), summary=[incident=11105].
有些情况下,我们的alert_log 是按天来开始备份的,这样我们可以通过下面的脚本,制定某个报警日志文件,来查询这个报警文件备份中的错误信息:
- --drop table alert_log_view;
- --drop directory bdump;
- declare
- path_bdump varchar2(4000);
- name_alert varchar2(4000);
- ins_name varchar2(200);
- begin
- select value
- into path_bdump
- from sys.v_$parameter
- where name = 'background_dump_dest';
- select 'alert_' || value || '.log'
- into name_alert
- from sys.v_$parameter
- where name = 'instance_name';
- select value
- into ins_name
- from sys.v_$parameter
- where name = 'instance_number';
- if ins_name = '0' then
- ins_name := '';
- end if;
- execute immediate 'create or replace directory bdump'||ins_name||' as ''' || path_bdump || '''';
- execute immediate 'drop table alert_log_view'||ins_name||' purge';
- execute immediate 'create table ALERT_LOG_VIEW' || ins_name ||
- ' (MSG_line varchar2(4000) ) ' ||
- ' organization external ' || ' (type oracle_loader ' ||
- ' default directory bdump' || ins_name ||
- ' access parameters ( ' ||
- ' records delimited by newline ' || ' nobadfile ' ||
- ' nologfile ' || ' nodiscardfile ' || ' skip 0 ' ||
- ' READSIZE 10485760 ' || ' FIELDS LDRTRIM ' ||
- ' REJECT ROWS WITH ALL NULL FIELDS ' ||
- ' (MSG_LINE (1:1000) CHAR(1000)) ' || ' ) ' ||
- ' location (''&alert_log'') )' ||
- ' reject limit unlimited ' ||
- ' noparallel nomonitoring ';
- end;
- /
转:http://blog.csdn.net/kiwi_kid/article/details/25413355