Oracle11g: find trace files localisation

To display the localisation of all trace files under Oracle11g, use the following command under sqlplus

SQL> select * from v$diag_info;

The result will be something like this:

INST_ID NAME                      VALUE
---------- ------------------------- --------------------------------------------------
1 Diag Enabled              TRUE
1 ADR Base                  /database/DB/sgbd/DB/bdump
1 ADR Home                  /database/DB/sgbd/DB/bdump/diag/rdbms/db/
DB
1 Diag Trace                /database/DB/sgbd/DB/bdump/diag/rdbms/db/
DB/trace
1 Diag Alert                /database/DB/sgbd/DB/bdump/diag/rdbms/db/
DB/alert
1 Diag Incident             /database/DB/sgbd/DB/bdump/diag/rdbms/db/
DB/incident
1 Diag Cdump                /database/DB/sgbd/DB/cdump
1 Health Monitor            /database/DB/sgbd/DB/bdump/diag/rdbms/db/
DB/hm

1 Default Trace File        /database/DB/sgbd/DB/bdump/diag/rdbms/db/
DB/trace/DB_ora_7534.trc
1 Active Problem Count      0
1 Active Incident Count     0

How to define trace location for an Oracle instance

The root directory of the ADR referential is defined with the value of the DIAGNOSTIC_DEST initialization parameter.

If the parameter DIAGNOSTIC_DEST is not defined :
If the DIAGNOSTIC_DEST initialization parameter is not set and if the ORACLE_BASE is defined then the DIAGNOSTIC_DEST takes the value of the ORACLE_BASE variable. In this way, trace files are located under the directory :

$ORACLE_BASE/diag/rdbms/<database_id>/<instance_id>/trace

If ORACLE_BASE is not defined and ORACLE_HOME is defined then the DIAGNOSTIC_DEST takes the value of ORACLE_HOME/log. In this way trace files are located under :

$ORACLE_HOME/log/diag/rdbms/<database_id>/<instance_id>/trace

If DIAGNOSTIC_DEST is defined :
Trace files are located using the DIAGNOSTIC_DEST database parameter.
Oracle use this parameter in order to define the full directory path of all trace files like this :

DIAGNOSTIC_DEST/diag/<instance_type>/<database_id>/<instance_id>/

For example, if the instance name is ORCL and the value of the DIAGNOSTIC_DEST parameter is /opt/oracle then the path will be /opt/oracle/diag/rdbms/orcl/ORCL.
The trace directory will be /opt/oracle/diag/rdbms/orcl/ORCL/trace.

Alert log location
Alert log in text format is defined under the trace directory of the ADR referential :

DIAGNOSTIC_DEST/diag/<instance_type>/<database_id>/<instance_id>/trace

Alert log in xml format is defined under the alert directory of the ADR referential :

DIAGNOSTIC_DEST/diag/<instance_type>/<database_id>/<instance_id>/alert

The alert log file is commonly named log.xml.