Alert log file in 11g

From 11g, the alert log is written as both an XML-formatted file and as a text file also.

Both these log files are stored inside the ADR home.The ADR root directory is known as ADR BASE.The Automatic Diagnostic Repository (ADR) is a directory structure that is stored outside of the database.

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

XML formatted alert.log
-------------------------

The alert log is named log.xml and is stored in the alert sub-directory 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

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



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 the 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