Oracle 11g - Accessing Alert log details in the database

The difficulty with most monitoring scripts is that they look for errors in the alert log. And you can’t easily work out (programmatically) when the problem occurred. This is improved slightly with Oracle 11g in that instead of using the text based alert log, you have the XML alert log. But this still requires decoding as it were.

 

I worked at one company that grep’d the ORA- lines from the textual alert log and then emailed these to the support team if they didn’t match those previously recorded. It did this every 15 minutes. But you still needed to review the alert log to find out when the errors occurred.

 

This solution to the problem involves accessing the X$DBGALERTEXT view which contains all of the information you need, right inside the database. It can be a little slow with a where clause. In this example I’m retrieving any errors that occurred within a day and 5 minutes (don’t ask).

 

sqlplus -s / as sysdba <<EOF

set lines 132

set pages 1000

col message_Text for a80

 

select to_char(ORIGINATING_TIMESTAMP, 'dd-mon-yy hh24:mi:SS'), message_text

from X$DBGALERTEXT

where message_text like '%ORA-%'

and ORIGINATING_TIMESTAMP > (sysdate-1)-(5/1440)

/

 

quit

EOF

 

Happyjohn.