Find the trace file on the server

Oracle writes the trace on the database server, in the ADR (Automatic Diagnostic Repository) trace folder, with a name such as ORCL1_ora_4242.trc: the instance SID, ora and the operating system process number. Mark the name before turning on tracing, find the path with a query and copy the .trc file.

Before you start

• Access to the operating system of the database server, to copy the file, or someone who can copy it for you.
• Privilege to query the V$DIAG_INFO, V$SESSION and V$PROCESS views.
• Oracle 11g or later, which has the ADR and these diagnostic views.

Mark the name with tracefile_identifier

In the session that will be traced, before turning on tracing, give the file an identifier:

alter session set tracefile_identifier = 'zz_exemplo';

Result: the session's next trace file has the identifier in its name, in the format sid_ora_pid_identifier.trc, for example ORCL1_ora_4242_zz_exemplo.trc.

• The parameter changes only with alter session: it applies to your own session, not to someone else's.
• Each change of the identifier opens a new file, and Oracle marks in both that they belong to the same process, with *** TRACE CONTINUES IN FILE and *** TRACE CONTINUED FROM FILE. Upload the files together (analyze together).
• The file name becomes the report title: use a neutral identifier (what to check before uploading).

In a multitenant database, alter session also works in a session connected to a PDB. The TRACEFILE_IDENTIFIER reference marks the parameter as Modifiable in a PDB: No, but that property says whether the PDB can have its own value, set with alter system (administering PDBs); tracefile_identifier only changes per session. To check, the TRACEID column of V$PROCESS shows the identifier in effect for the session's process, and TRACEFILE shows the file path (find the file of another session).

Find the file of your own session

select value
from v$diag_info
where name = 'Default Trace File';

Result: the full path of the trace for the session in which you ran the query, such as /u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_ora_4242.trc.

Find the file of another session

The trace belongs to the process that serves the session. Join V$SESSION and V$PROCESS on the process address and filter by SID:

select s.sid, s.serial#, p.tracefile
from v$session s
join v$process p on p.addr = s.paddr
where s.sid = 123;

Result: the TRACEFILE column has the file path. In RAC, use the GV$ views and also join on the instance: the file is on that instance's server.

select s.inst_id, s.sid, s.serial#, p.tracefile
from gv$session s
join gv$process p on p.inst_id = s.inst_id and p.addr = s.paddr
where s.inst_id = 1
  and s.sid = 123;

Where the files are

The ADR is in the directory set by the DIAGNOSTIC_DEST parameter, and each instance's trace folder follows the path diag/rdbms/<db_unique_name>/<SID>/trace under it. This query returns the folder of the instance you are in:

select value
from v$diag_info
where name = 'Diag Trace';

In a multitenant database, the traces of sessions in all PDBs are in the instance folder. In RAC, each instance has its own folder, on its own server. The queries on this page are those in the Oracle diagnostics guide.

Copy the file

1. Turn off tracing before copying: while it is on, the file keeps growing.
2. Copy the .trc from the trace folder to your machine:

   scp oracle@db-exemplo:/u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_ora_4242_zz_exemplo.trc .

3. Leave behind the .trm with the same name. It holds metadata about the trace structure, which the ADR uses for searching and navigation, and WaitScope ignores it.
4. If the file is large, compress it before uploading:

   gzip -k ORCL1_ora_4242_zz_exemplo.trc

Result: a .trc, or .trc.gz, ready to upload from the browser or from the Android app.

Common problems

• The file does not exist. Tracing was not turned on, the session has not made any call since it was turned on, or, in RAC, you are looking on the server of another instance.
• The name does not have the identifier. The identifier was set in another session. If it was set after the trace started, the beginning of the operation is in the previous file: upload both together.
• The same process left several files. The application changed tracefile_identifier in the middle of the operation: upload them all together (analyze together).
• The file contains several operations. Tracing stayed on beyond the operation, or the process served other tasks. Generate it again, on only during the operation (turn it on and off at the right time).

Next steps

• Check what the trace contains before uploading.
• Upload from the browser or the Android app.
• If ORA-00060 wrote the file, see upload a deadlock trace.