Database Reference
In-Depth Information
Finding Trace Files
Trace files are produced by database engine server processes running on the database server. This means they're
written to a disk accessible from the database server. In version 10.2, depending on the type of the process producing
the trace files, they're written in two distinct directories:
•
Dedicated server processes create trace files in the directory configured through the
user_dump_dest
initialization parameter.
•
Background processes create trace files in the directory configured through the
background_dump_dest
initialization parameter.
The process type is available in the
type
column of the
v$session
view. Note that, strangely enough, not all
background processes are listed in the
v$bgprocess
view.
As of version 11.1, with the introduction of the Automatic Diagnostic Repository, the
user_dump_dest
and
background_dump_dest
initialization parameters are deprecated in favor of the
diagnostic_dest
initialization
parameter. Because the new initialization parameter sets the base directory only, you can use the
v$diag_info
view to get the exact location of the trace files. The following queries show the difference between the value of the
initialization parameter and the location of the trace files:
SQL> SELECT value FROM v$parameter WHERE name = 'diagnostic_dest';
VALUE
---------------
/u00/app/oracle
SQL> SELECT value FROM v$diag_info WHERE name = 'Diag Trace';
VALUE
--------------------------------------------------
/u00/app/oracle/diag/rdbms/dbm11203/DBM11203/trace
Be aware that in a 12.1 multitenant environment, the
user_dump_dest
,
background_dump_dest
and
diagnostic_dest
initialization parameters can't be set at the PDB level.
The name of the trace file itself used to be version and platform dependent. In recent versions, however, it has the
following structure:
{instance name}_{process name}_{process id}.trc
Here's a breakdown:
instance name
: This is the value of the
instance_name
initialization parameter. Notice that
especially in a Real Application Clusters environment, this is different from the
db_name
initialization parameter. It's available in the
instance_name
column of the
gv$instance
view.
process name
: This is the lowercase value of the name of the process that's producing
the trace file. For dedicated server processes, the name
ora
is used. For shared server
processes, it's found in the
name
column of either the
v$dispatcher
or
v$shared_server
view. For parallel slave processes, it's found in the
server_name
column of the
v$px_
process
view. For most other background processes, it's found in the
name
column of the
v$bgprocess
view.
process id
: This is the process identifier (thread identifier in Windows) at the operating
system level. Its value is found in the
spid
column of the
v$process
view.
