Jul 22, 2021
Requested Trace Files- Files
The trace files you typically expect to be generated as the result of enabling trace via DBMS_MONITOR or using the extended trace facility via the 10046 event might be as follows:
SQL> alter session set events ‘10046 trace name context forever, level 12’; Session altered.
These trace files contain diagnostic and performance-related information. They provide invaluable insights into the inner workings of your database application. You will see these trace files more often than any other kind of trace file in a normally operating database.
File Locations
Whether you use DBMS_MONITOR or the extended trace facility, Oracle will start generating a trace file on the database server derived from the setting of the DIAGNOSTIC_DEST initialization parameter. The structure of the directory specified by DIAGNOSTIC_DEST is as follows:
/diag/rdbms//
This location is known as the Automatic Diagnostic Repository (ADR) Home. For example, if the database name is CDB and the instance name is CDB, the ADR home directory would be /diag/rdbms/cdb/CDB. You can view the directory settings with this query:
SQL> select name, value from v$diag_info;
AME VALUE
Diag Enabled TRUE
ADR Base /opt/oracle
ADR Home /opt/oracle/diag/rdbms/cdb/CDB
Diag Trace /opt/oracle/diag/rdbms/cdb/CDB/trace
Diag Alert /opt/oracle/diag/rdbms/cdb/CDB/alert
Diag Incident /opt/oracle/diag/rdbms/cdb/CDB/incident
Diag Cdump /opt/oracle/diag/rdbms/cdb/CDB/cdump
Health Monitor /opt/oracle/diag/rdbms/cdb/CDB/hm
Default Trace File /opt/oracle/diag/rdbms/cdb/CDB/trace/CDB_ora_15884.trc …
The most important rows are
•\ Diag Trace: This is where the text-based alert log goes along with the trace file.
•\ Diag Alert: This is where the XML-based alert log file is written.
As a DBA, you’ll spend time working with files in the Diag Trace directory. These files are used so often (debugging and maintenance) that DBAs will often create aliases that will take them to that directory directly and tail the alert log. For example:
$ alias cda=’cd /opt/oracle/diag/rdbms/cdb/CDB/trace’
$ alias ta=’tail -f /opt/oracle/diag/rdbms/cdb/CDB/trace/alert_CDB.log’
The aliases create shortcuts for working with these critical files.
Naming Convention
The trace file naming convention changes from time to time in Oracle, but if you have an example trace file name from your system, it is easy to see the template in use. For example, on my various UNIX/Linux servers, a trace file name has this general format:
ora.trc
The trace file name can be broken down as follows:
•\ The first part of the file name is the ORACLE_SID.
•\ The next bit of the file name is just ora.
•\ The number in the trace file name is the process ID of your dedicated server, available to you from the V$PROCESS view.
A query, then, to generate your trace file name could be
SQL> column trace new_val TRACE format a100
SQL> select i.value || ‘/’ || d.instance_name || ‘ora‘ || a.spid || ‘.trc’ trace
from v$process a, v$session b, v$diag_info i , v$instance d where a.addr = b.paddr
and b.audsid = userenv(‘sessionid’)
and i.name=’Diag Trace’;
TRACE
/opt/oracle/diag/rdbms/cdb/CDB/trace/CDB_ora_16908.trc
And this just shows that if the file exists, you’ll be able to access it via that name (assuming you have the permissions to read the trace directory). The following example generates a trace file, showing how the file is created once the trace is enabled:
SQL> !ls &TRACE
ls: cannot access /opt/oracle/diag/rdbms/cdb/CDB/trace/CDB_ora_16908.trc:
No such file or directory
SQL> exec dbms_monitor.session_trace_enable PL/SQL procedure successfully completed.
SQL> !ls &TRACE
/opt/oracle/diag/rdbms/cdb/CDB/trace/CDB_ora_16908.trc
As you can see, before we enabled tracing in that session, no file existed; as soon as tracing is enabled, however, we are able to see it.
And if you’re using Windows, it should be obvious that you’d replace the / with .
More Details