Oracle 11g: find ADR root directory

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.

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

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