轉載-找出Oracle alert檔案中的ORA錯誤

找出Oracle alert檔案中的ORA錯誤

位於$ORACLE_BASE/[SID]/bdump/的alert_[SID].log檔案雖然不影響Oracle的正常執行，但是對於Oracle DBA 來說是一個定位問題的重要線索。今天周大師提出一個需求，通常來說alert檔案比較大，裡面記錄了很多有用的資訊，但是通常來說比較注重看ORA-XXXX的錯誤資訊，如果使用Linux的grep（grep ORA < alert_[sid].log）命令雖然可以輕易地找出所有ORA-XXXX的錯誤，缺丟失了“時間”這個重要的資訊。於是乎，我用Perl寫了個小程式，用於同時獲得這兩個重要的資訊。使用Perl進行編寫的原因是：

1。Linux環境下基本上都有Perl的執行環境。

2。Perl處理文字實在太爽了。

首先，看看alert檔案內容的大概樣子：

 ...  
 ....  
 Fri Aug 28 22:47:27 2009  
 Errors in file /u01/app/oracle/admin/ora10g/udump/ora10g_ora_17805.trc:  
 ORA-00313: open failed for members of log group 1 of thread 1  
 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ora10g/redo01.log'  
 ORA-27037: unable to obtain file status  
 Linux-x86_64 Error: 2: No such file or directory  
 Additional information: 3  
 Fri Aug 28 22:47:28 2009  
 Errors in file /u01/app/oracle/admin/ora10g/udump/ora10g_ora_17805.trc:  
 ORA-00313: open failed for members of log group 2 of thread 1  
 ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ora10g/redo02.log'  
 ORA-27037: unable to obtain file status  
 Linux-x86_64 Error: 2: No such file or directory  
 Additional information: 3  
 Fri Aug 28 22:47:29 2009  
 Errors in file /u01/app/oracle/admin/ora10g/udump/ora10g_ora_17805.trc:  
 ORA-00313: open failed for members of log group 3 of thread 1  
 ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ora10g/redo03.log'  
 ORA-27037: unable to obtain file status  
 Linux-x86_64 Error: 2: No such file or directory  
 Additional information: 3  
 ....  
 ...

你看到時間和錯誤資訊了嗎 :) 。以下是我寫的perl小程式：

#! /usr/bin/perl -w  
 # alert_ora_finder.pl  
 use strict;  
   
 my ($argv) = @ARGV;  
 if ( $argv && ! -e $argv )  
 {  
     print '  
  Welcome To Use  alert_ora_finder  (Oracle Alert File ORA Error Finder)  
                                                    Powered By killkill  
  $ cat alert_[sid].log | alert_ora_finder.pl  
  $ tail -f alert_[sid].log | alert_ora_finder.pl  
  $ alert_ora_finder.pl alert_[sid].log  
   
 ';  
     exit;  
 }  
 my $cap_regex = '^(\w+ \w+ \d{2} \d{2}:\d{2}:\d{2} \d{4})|(ORA-\d+:.+)$';  
 my $tmp_date = "";  
 my $line_counter = 0;  
 while ( <> )  
 {  
     $line_counter++;  
     if( m/$cap_regex/oi )  
     {  
         if ($1)  
         {  
             $tmp_date = $1;  
             next;  
         }  
         print "$line_counter | $tmp_date | $2 \n" if ($2);  
     }  
 }

輸入如下：

[root@oracle01 perl]# ./alert_ora_finder.pl /u01/app/oracle/admin/ora10g/bdump/alert_ora10g.log   
 230 | Fri Aug 28 22:47:27 2009 | ORA-00313: open failed for members of log group 1 of thread 1   
 231 | Fri Aug 28 22:47:27 2009 | ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ora10g/redo01.log'   
 232 | Fri Aug 28 22:47:27 2009 | ORA-27037: unable to obtain file status   
 237 | Fri Aug 28 22:47:28 2009 | ORA-00313: open failed for members of log group 2 of thread 1   
 238 | Fri Aug 28 22:47:28 2009 | ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ora10g/redo02.log'   
 239 | Fri Aug 28 22:47:28 2009 | ORA-27037: unable to obtain file status   
 244 | Fri Aug 28 22:47:29 2009 | ORA-00313: open failed for members of log group 3 of thread 1   
 245 | Fri Aug 28 22:47:29 2009 | ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ora10g/redo03.log'   
 246 | Fri Aug 28 22:47:29 2009 | ORA-27037: unable to obtain file status   
 1411 | Wed Sep 23 23:18:07 2009 | ORA-1653: unable to extend table GAME.MANY_OBJECTS by 128 in                 tablespace GAME    
 1413 | Wed Sep 23 23:18:07 2009 | ORA-1653: unable to extend table GAME.MANY_OBJECTS by 128 in                 tablespace GAME    
 1415 | Wed Sep 23 23:18:07 2009 | ORA-1653: unable to extend table GAME.MANY_OBJECTS by 128 in                 tablespace GAME    
 1469 | Wed Sep 23 23:18:07 2009 | ORA-1652: unable to extend temp segment by 128 in tablespace                 GAME  

其中，最左邊的是輸入端的行號，第二列是時間，第三列就是ORA-XXXX的錯誤資訊了。