oracle alert下的文件,转载-找出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的错误信息了。