How To Find The Alert.log File (11g and Later)

How To Find The Alert.log File (11g and Later)

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:

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

Name        Contents

alert ----------- The XML-formatted alert log

trace------------ Background and server process trace files and SQL trace files and text alert.logfile

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 alert_SID.log and is stored in the trace subdirectory of ADR home.

To view the text only alert.log file

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

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

$ adrci
ADRCI> show alert
ADRCI> show alert -tail 50

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

Oracle Database uses the alert log to keep a record of these events as an alternative to displaying the information on an operator's console. (Many systems also display this information on the console.) If an administrative operation is successful, a message is written in the alert log as "completed" along with a time stamp.

Reference metalink Doc ID 438148.1