Reading Alert Log

With Oracle 10g, external table was required to read alert log  but luckily Oracle 11g created a system table that can be used to access alert log from SQL:  X$DBGALERTEXT. Oracle reads the log.xml from alert directory (which contains all the data what alert.log does), parses it and returns the details back as rows.

Beginning with Release 11g, the alert log file is written as XML formatted and as a text file (as in previous releases). The default location of both these files is the new ADR home (Automatic Diagnostic Respository, another dump dest in 11g).

The database, Automatic Storage Management (ASM), Cluster Ready Services (CRS), and other Oracle products or components store all diagnostic data in the ADR.

The ADR is set by using the DIAGNOSTIC_DEST initialization parameter. If this parameter is omitted, then, the default location of ADR is, ‘$ORACLE_HOME/log’

The location of an ADR home is given by the following path, which starts at the ADR base directory:

$ORACLE_BASE/diag/product_type/product_id/insance_id

v$diag_info can be queried to check ADR locations, including ADR BASE and ADR home.

Within the ADR home directory are subdirectories:

alert       – XML formatted alertlog
trace      – Files and text alert.log file
cdump – core files

message_text can have multiple lines of ORA- errors and related tracing file information, but for monitoring only need first error code.

SELECT        record_id, substr(message_text, instr(message_text,'ORA-'),
              instr(message_text,chr(10),1,2)-instr(message_text,'ORA-') ) message_text
FROM          <strong>X$DBGALERTEXT</strong>
WHERE         originating_timestamp > sysdate-1
              AND message_text like '%ORA-%'
ORDER BY  record_id desc

Query to get errors for the past day:

</span>
<pre>SELECT  distinct    originating_timestamp,   message_text
FROM    X$DBGALERTEXT
WHERE   originating_timestamp > sysdate-1
AND     (  message_text = 'ORA-00600'  OR
           message_text like '%Fatal%'
        )

Query to get errors for the last 10 minutes:

</span>
<pre>SELECT         rownum       “line”,    message_text “error”
FROM           X$DBGALERTEXT
WHERE          originating_timestamp > (sysdate – 10/1440)  AND
               message_text like ‘%ORA-%’
ORDER BY       originating_timestamp;

Links:

Oracle 11g Reading Alert Log Via SQL

Note: 1072547.1 – How To Edit, Read, and Query the Alert.Log

Oracle 11g Automatic Diagnostic Repository