My Blog

Accessing the Alert Log from SQL

by Nigel Fowler on 1st June 2016 3 comments

Ever wanted a programmatic way to access the alert log? I know I have! For quite a few reasons – firstly, I will confess that sometimes, you run into a machine where the alert log is just … well … (cough) … a little bit hard to find. Secondly, if you’re not on your favourite operating system, just viewing a large file can be a challenge. Thirdly, and most importantly, it can be a challenge to sort through a large number of messages, and search for the “interesting” messages, around the time you are interested in.

Solution: use SQL!! Here’s an example of the x$ table (x$dbgalertext) that can help:

col RECORD_ID format 9999999 head ID
col TSTMP format a20 heading Date
col MESSAGE_TEXT format a120 heading AlertLogEntry
select *
from (
select record_id, to_char(originating_timestamp,’DD.MM.YYYY HH24:MI:SS’) tstmp, message_text
from x$dbgalertext
order by RECORD_ID desc )
where rownum <= 10 order by RECORD_ID asc;

 

Of course, now that you can use the SQL interface to access the log, you can use any combination of filters and sorting to display exactly what you want! By the way, if you don’t like the x$ table, you can use v$diag_alert_ext. Here’s my attempt showing all the “ORA-nnnn” errors in the last 1.5 days:

 

set lines 160 pages 50 col inst_id format 99999999 head “Instance” col NFTS format A25 head “Date/Time” col message_text format A92 wrap select inst_id, TO_CHAR(originating_timestamp,’MM/DD/YYYY HH24:MI:SS’) as NFTS , message_text from v$diag_alert_ext where originating_timestamp > (sysdate – 1.5)
and message_text like ‘%ORA-%’
order by inst_id, originating_timestamp;

As you can see, this is a lot more sophisticated than manually trawling through a log file!

 

A bonus question: how can you do this across RAC nodes? Because it would be handy to simultaneously display and correlate message from multiple nodes. Sadly, there is no gv$diag_alert_ext. However, we can use the beautiful and powerful “TABLE(gv$(cursor” trick to conjure up one out of thin air:

set lines 160 pages 50
col originating_timestamp format A35 head ‘Orig Timesheet’
select inst_id, originating_timestamp, message_text
from TABLE(gv$(cursor(
select inst_id, originating_timestamp, message_text
from v$diag_alert_ext
where originating_timestamp > (sysdate – 1.5)
and message_text like ‘%ORA-%’
)))
order by inst_id, originating_timestamp;

 

Totally awesome dude! Now we can get the exact alert log messages we want, from all nodes, correlate by time or text!!! Enjoy.

 

A big thanks to Darren Bock for pointing me in the right direction here.

 

Kind regards,
Nigel

Nigel FowlerAccessing the Alert Log from SQL

3 comments

Join the conversation
  • Lukasz Feldman - 2nd June 2016 reply

    Nice one! 🙂

  • Anisur rehman - 2nd June 2016 reply

    Appreciate for the valuable information.

  • John Boyle - 9th June 2016 reply

    What about ADRCI ?

Join the conversation