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:
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;