Oracle 11g : Alert Log file

Beginning with Release 11g of Oracle Database, the alert log is written as both an XML-formatted file and as a text file, as in earlier releases. Both these log files are stored inside the ADR home. The ADR root directory is known as ADR BASE. Automatic Diagnostic Repository (ADR) is a directory structure that is stored outside of the database. This parameter is set by DIAGNOSTIC_DEST initialization parameter.

If this parameter is omitted or left null, the database sets DIAGNOSTIC_DEST upon startup as follows:

If environment variable ORACLE_BASE is set, DIAGNOSTIC_DEST is set
to the directory designated by ORACLE_BASE.

If environment variable ORACLE_BASE is not set, DIAGNOSTIC_DEST is
set to ORACLE_HOME/log.

for e.g
SQL> show parameter diagno

NAME TYPE VALUE
-------------------- ------- -----------------------------------------
diagnostic_dest string /u01/oracle/product/ora11g/log

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

ADR_BASE/diag/product_type/product_id/instance_id

For example,
for a database with a SID and database name both equal to ora11g, the ADR home would be in the following location:

ADR_base/diag/rdbms/ora11g/ora11g/

Within the ADR home directory are subdirectories where the database
instance stores diagnostic data.

alert ( The XML-formatted alert log )

trace ( Background and server process trace files and SQL trace files and text alert.logfiles )

cdump ( Core files )

XML formatted alert.log
-------------------------------
The alert log is named log.xml and is stored in the alert subdirectory of ADR home.
To get the log.xml path
ADR_HOME/diag/product_type/product_id/instance_id/alert

from sqlplus
SQL> select value from v$diag_info where name ='Diag Alert';
ADRCI utility to view a text version of the alert log (with XML tags stripped)

Text formatted alert.log
--------------------------------

The alert.log is named alertSID.log and is stored in the trace subdirectory of ADR home.

To view the text only alert.log file
ADR_HOME/diag/product_type/product_id/instance_id/trace

from sqlplus
SQL> select value from v$diag_info where name ='Diag Trace';
or
SQL>show parameter background_dump_dest

Open file alert_SID.log with a text editor

The alert log of a database is a chronological log of messages and errors, including the following:

All internal errors (ORA-600), block corruption errors (ORA-1578), and
deadlock errors (ORA-60) that occur

Administrative operations, such as the SQL statements CREATE ALTER DROP DATABASE TABLESPACE and Oracle Enterprise Manager or SQL*Plus statements STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER

Several messages and errors relating to the functions of shared server and dispatcher processes

Errors during the automatic refresh of a materialized view