How to Determine the Name of the Trace File to be Generated

In many cases we need to find out the name of the latest trace file generated in the USER_DUMP_DEST directory. What we usually do is, that we physically go to the USER_DUMP_DEST location with the operating system browser and sort all the files by date and look for latest files. We can remove this hassle easily if we know what would be the trace file name in advance. Let's have a look ...

Demo : 1 
C:\>sqlplus sys/xxxx@noida as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 18 17:44:49 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database backup controlfile to trace;
Database altered.

The above Command will generate the trace file inside USER_DUMP_DEST. Let's check the location of USER_DUMP_DEST. If we are using Sql*plus then issue,

SQL> show parameter user_dump_dest
NAME                         TYPE           VALUE
--------------                --------     -----------------------------------------------------
user_dump_dest        string       d:\oracle\diag\rdbms\noida\noida\trace

Here the latest files are for latest trace . Sometimes, we may not get the right trace file .Now it would be quite easy task if we knew the name of the trace file to be generated by ALTER DATABASE command. In advance we can get the trace file name as 

SQL> SELECT s.sid, s.serial#, pa.value || '\' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
          '_ora_' || p.spid || '.trc'  AS trace_file        FROM   v$session s,  v$process p, v$parameter pa
   WHERE  pa.name = 'user_dump_dest'     AND    s.paddr = p.addr
   AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
   SID            SERIAL#                     TRACE_FILE
---------        ----------           -----------------------------------------------------------------------
 110             312                  d:\oracle\diag\rdbms\noida\noida\trace\noida_ora_3552.trc
  
the trace file to be generated now will be named as noida_ora_3552.trc . So now issuing, "alter database backup controlfile to trace" will generate the file named d:\oracle\diag\rdbms\noida\noida\trace\noida_ora_3552.trc

Demo : 2 
This method is much simple and easy to identify the trace file. Let's have a look on another demo .

C:\>sqlplus sys/xxxx@noida as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 18 17:49:49 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show   parameter    user_dump_dest
NAME                             TYPE                    VALUE
---------------------           --------       ----------------------------------------
user_dump_dest          string           d:\oracle\diag\rdbms\noida\noida\trace

SQL> alter session set tracefile_identifier='mytracefile' ;
Session altered.
SQL>  alter database backup controlfile to trace; 
Session altered.


Now, go to the user_dump_dest location and find the trace file having name "mytracefile" . In mycase the name is   "noida_ora_3552_mytracefile.trc"

The difference between the two demo is that first demo is on system level so it will give all the trace file generated by different session whereas in second case , it will show  the trace file for particular session only . The another difference between is that in first demo we have to fire the command and then check the tracefile but in second demo we have to set the trace file name so that we can easily identify the correct trace file .

Enjoy      :-)