利用外部表实现SQL查询Oracle告警日志Alert.log

本文根据刘相兵大师的blog的脚本，并做了一些该进：

http://www.oracledatabase12g.com/archives/%E5%88%A9%E7%94%A8%E5%A4%96%E9%83%A8%E8%A1%A8%E5%AE%9E%E7%8E%B0sql%E6%9F%A5%E8%AF%A2oracle%E5%91%8A%E8%AD%A6%E6%97%A5%E5%BF%97alert-log.html

有同学问是否可以用SQL语句直接查询告警日志的内容，即创建一张包含Alert.log内容的表或视图

[sql]  view plain  copy

 

1. --drop table alert_log_view;  
2. --drop directory bdump;  
3.   
4. declare  
5.   path_bdump varchar2(4000);  
6.   name_alert varchar2(4000);  
7.   ins_name   varchar2(200);  
8. begin  
9.   
10.   select value  
11.     into path_bdump  
12.     from sys.v_$parameter  
13.    where name = 'background_dump_dest';  
14.   select 'alert_' || value || '.log'  
15.     into name_alert  
16.     from sys.v_$parameter  
17.    where name = 'instance_name';  
18.   
19.   select value  
20.     into ins_name  
21.     from sys.v_$parameter  
22.    where name = 'instance_number';  
23.   
24.   if ins_name = '0' then  
25.     ins_name := '';  
26.   end if;  
27.   
28.   execute immediate 'create or replace directory bdump'||ins_name||' as ''' || path_bdump || '''';  
29.   execute immediate 'create table ALERT_LOG_VIEW' || ins_name ||  
30.                     '  (MSG_line varchar2(4000)   ) ' ||  
31.                     ' organization external ' || ' (type oracle_loader ' ||  
32.                     ' default directory bdump' || ins_name ||  
33.                     ' access parameters ( ' ||  
34.                     ' records delimited by newline ' || ' nobadfile ' ||  
35.                     ' nologfile ' || ' nodiscardfile ' || ' skip 0 ' ||  
36.                     ' READSIZE 10485760 ' || ' FIELDS LDRTRIM ' ||  
37.                     ' REJECT ROWS WITH ALL NULL FIELDS ' ||  
38.                     ' (MSG_LINE (1:1000) CHAR(1000)) ' || ' ) ' ||  
39.                     ' location (''' || name_alert || ''') )' ||  
40.                     ' reject limit unlimited ' ||  
41.                     ' noparallel nomonitoring ';  
42.   
43. end;  
44. /  

执行以上PL/SQL代码，会创建名为bdump$SID的目录 和ALERT_LOG_VIEW$SID的外部表(如RAC中的1号实例PROD1，则为ALERT_LOG_VIEW1,单实例single instance则为 ALERT_LOG_VIEW)， 需要时直接查询ALERT_LOG_VIEW即可，譬如要从告警信息中找出最近三天ORA-错误的记录:

[sql]  view plain  copy

 

1. col lineno noprint  
2. col ora_error noprint  
3. col msg_line format a132  
4. set pages 0 lines 300 trimspool on trim on  
5. alter session set nls_date_language = 'american';  
6. alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';  
7. alter session set sql_trace=false;  
8. break on thedate  
9. prompt  
10. prompt ERROR IN ALERT LOG FILE - LAST 3 DAYS  
11. prompt =====================================  
12. select "LINENO", "THEDATE", "ORA_ERROR", "MSG_LINE"  
13.   from (select *  
14.           from (select lineno,  
15.                        msg_line,  
16.                        thedate,  
17.                        max(case  
18.                              when (ora_error like 'ORA-%' or  
19.                                   ora_error like 'PLS-%' or  
20.                                   ora_error like 'TNS-%'  
21.                                   )   then  
22.                               rtrim(substr(ora_error, 1, instr(ora_error, ' ') - 1),  
23.                                     ':')  
24.                              else  
25.                               null  
26.                            end) over(partition by thedate) ora_error  
27.                   from (select lineno,  
28.                                msg_line,  
29.                                max(thedate) over(order by lineno) thedate,  
30.                                lead(msg_line) over(order by lineno) ora_error  
31.                           from (select rownum lineno,  
32.                                        substr(msg_line, 1, 132) msg_line,  
33.                                        case  
34.                                          when msg_line like  
35.                                               '___ ___ __ __:__:__ ____' then  
36.                                           to_date(msg_line,  
37.                                                   'Dy Mon DD hh24:mi:ss yyyy')  
38.                                          else  
39.                                           null  
40.                                        end thedate  
41.                                   from ALERT_LOG_VIEW))))  
42.  where ora_error is not null  
43.    and thedate >= (trunc(sysdate) - 3)  
44.  order by thedate  
45. /  

实例输出：

[sql]  view plain  copy

 

1. 10/11/2011 03:15:49 Thu Nov 10 03:15:49 2011  
2.                     Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_5547.trc  (incident=11105):  
3.                     ORA-00700: soft internal error, arguments: [kgerev1], [600], [600], [700], [], [], [], [], [], [], [], []  
4.                     Incident details in: /s01/orabase/diag/rdbms/vprod/VPROD1/incident/incdir_11105/VPROD1_ora_5547_i11105.trc  
5.                     Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_5547.trc  (incident=11106):  
6.                     ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []  
7.                     Incident details in: /s01/orabase/diag/rdbms/vprod/VPROD1/incident/incdir_11106/VPROD1_ora_5547_i11106.trc  
8. 10/11/2011 03:15:52 Thu Nov 10 03:15:52 2011  
9.                     Dumping diagnostic data in directory=[cdmp_20111110031552], requested by (instance=1, osid=5547), summary=[incident=11105].  

 

有些情况下，我们的alert_log 是按天来开始备份的，这样我们可以通过下面的脚本，制定某个报警日志文件，来查询这个报警文件备份中的错误信息：

[sql]  view plain  copy

 

1. --drop table alert_log_view;  
2. --drop directory bdump;  
3.   
4. declare  
5.   path_bdump varchar2(4000);  
6.   name_alert varchar2(4000);  
7.   ins_name   varchar2(200);  
8. begin  
9.   
10.   select value  
11.     into path_bdump  
12.     from sys.v_$parameter  
13.    where name = 'background_dump_dest';  
14.   select 'alert_' || value || '.log'  
15.     into name_alert  
16.     from sys.v_$parameter  
17.    where name = 'instance_name';  
18.   
19.   select value  
20.     into ins_name  
21.     from sys.v_$parameter  
22.    where name = 'instance_number';  
23.   
24.   if ins_name = '0' then  
25.     ins_name := '';  
26.   end if;  
27.   
28.   execute immediate 'create or replace directory bdump'||ins_name||' as ''' || path_bdump || '''';  
29.   execute immediate 'drop table alert_log_view'||ins_name||' purge';  
30.   execute immediate 'create table ALERT_LOG_VIEW' || ins_name ||  
31.                     '  (MSG_line varchar2(4000)   ) ' ||  
32.                     ' organization external ' || ' (type oracle_loader ' ||  
33.                     ' default directory bdump' || ins_name ||  
34.                     ' access parameters ( ' ||  
35.                     ' records delimited by newline ' || ' nobadfile ' ||  
36.                     ' nologfile ' || ' nodiscardfile ' || ' skip 0 ' ||  
37.                     ' READSIZE 10485760 ' || ' FIELDS LDRTRIM ' ||  
38.                     ' REJECT ROWS WITH ALL NULL FIELDS ' ||  
39.                     ' (MSG_LINE (1:1000) CHAR(1000)) ' || ' ) ' ||  
40.                     ' location (''&alert_log'') )' ||  
41.                     ' reject limit unlimited ' ||  
42.                     ' noparallel nomonitoring ';  
43.   
44. end;  
45. / 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25462274/viewspace-2124967/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25462274/viewspace-2124967/