Extracting “ORA“ messages from alert log in oracle 11g

Sometimes it is very difficult for us to examine the alert log file in lieu for finding error messages starting with “ORA-“.Obviously there is a long text in the alert log and we need to search the error one by one. What if we can have a file created from alert log containing only the error message as per timestamp.

Well below post is the answer of the above question!

In oracle 11g we have X$DBGALERTEXT view through which we can query the alert log contents as per our requirement.

SQL>spool on

SQL>spool c:\alert_error.txt

SQL> select ORIGINATING_TIMESTAMP, MESSAGE_TEXT from X$DBGALERTEXT where MESSAGE_TEXT like '%ORA-%';

ORIGINATING_TIMESTAMP                                                          

---------------------------------------------------------------------------    

MESSAGE_TEXT                                                                   

--------------------------------------------------------------------------------

07-MAY-11 12.09.22.609 PM +05:30                                               

ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...                      

                                                                               

07-MAY-11 12.09.55.937 PM +05:30                                               

Errors in file d:\oracle\diag\rdbms\testdata\testdata\trace\testdata_ora_3268.tr

c:                                                                             

ORA-00313: open failed for members of log group 1 of thread 1                  

ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TESTDATA\REDO01.LOG'      

ORA-27041: unable to open file                                                 

ORIGINATING_TIMESTAMP                                                           

---------------------------------------------------------------------------    

MESSAGE_TEXT                                                                   

--------------------------------------------------------------------------------

OSD-04002: unable to open file                                                 

O/S-Error: (OS 2) The system cannot find the file specified.                   

                                                                               

07-MAY-11 12.10.00.000 PM +05:30                                               

Errors in file d:\oracle\diag\rdbms\testdata\testdata\trace\testdata_ora_3268.tr

c:                                                                              

ORA-00313: open failed for members of log group 2 of thread 1                  

ORA-00312: online log 2 thread 1: 'D:\ORACLE\ORADATA\TESTDATA\REDO02.LOG'      

ORA-27041: unable to open file                                                 

ORIGINATING_TIMESTAMP                                                          

---------------------------------------------------------------------------    

MESSAGE_TEXT                                                                    

--------------------------------------------------------------------------------

OSD-04002: unable to open file                                                 

O/S-Error: (OS 2) The system cannot find the file specified.                   

                                                                               

07-MAY-11 12.10.01.984 PM +05:30                                               

Errors in file d:\oracle\diag\rdbms\testdata\testdata\trace\testdata_ora_3268.tr

c:                                                                             

ORA-00313: open failed for members of log group 3 of thread 1                  

ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\TESTDATA\REDO03.LOG'       

ORA-27041: unable to open file                                                 

ORIGINATING_TIMESTAMP                                                          

---------------------------------------------------------------------------    

MESSAGE_TEXT                                                                   

--------------------------------------------------------------------------------

OSD-04002: unable to open file                                                 

O/S-Error: (OS 2) The system cannot find the file specified.                   

                                                                               

16-MAY-11 11.34.29.927 AM +05:30                                               

ORA-1119 signalled during: create undo tablespace un datafile ' D:\ORACLE\ORADAT

A\TESTDATA\un01.dbf' size 4m...                                                

                                                                               

16-MAY-11 11.36.18.193 AM +05:30                                               

ORA-1089 : opidrv aborting process J000 ospid (2460_384)                       

ORIGINATING_TIMESTAMP                                                          

---------------------------------------------------------------------------    

MESSAGE_TEXT                                                                   

--------------------------------------------------------------------------------

                                                                                

16-MAY-11 11.45.09.177 AM +05:30                                               

ORA-1089 : opidrv aborting process J000 ospid (2460_2500)                      

                                                                                

16-MAY-11 03.14.36.771 PM +05:30                                               

WARNING: inbound connection timed out (ORA-3136)                               

                                                                                

8 rows selected.

SQL> spool off

The file alert.txt will contains only the error message information that you really looking for.

EnjoyJ