Finding alert.log file in 11g

  Finding alert.log file in 11g [ID 438148.1]


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.The 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:

/diag/// 

For example,

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

/diag/rdbms/ora11g/ora11g/

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

Subdirectory Name Contents
alert The XML-formatted alert log
trace Background and server process trace files and SQL trace files and text alert.log file
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_BASE/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

/diag////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

You can also use the ADR Client Interface called 'adrci' to view the alert.log

% adrci
ADRCI> show alert
       show alert -tail 50

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