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 go to physically to the USER_DUMP_DEST 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.

For example issuing,
D:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 10 21:58:15 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database backup controlfile to trace;

Database altered.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
will generated a trace file inside USER_DUMP_DEST. Let's see the location of USER_DUMP_DEST.

If you use Sql*plus then issue,
SQL> show parameter user_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string E:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\UDUMP

Querying from v$parameter,
SQL> select value from v$parameter where name='user_dump_dest';

VALUE
-------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP

On windows changes to directory E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP and issue,
E:\oracle\product\10.2.0\admin\orcl\udump>dir /OD

The latest files are for latest trace.

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 by,

SQL> set linesize 130
SQL> COL trace_file FOR A60
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
---------- ---------- ------------------------------------------------------------
       146         11 E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_4968.trc

So the trace file to be generated now will be named as orcl_ora_4968.trc

So now issuing, "alter database backup controlfile to trace" will generate the file named E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_4968.trc.

On windows you can issue like this,
SQL> alter database backup controlfile to trace;

Database altered.

SQL> host notepad E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_4968.trc
Related Documents
http://arjudba.blogspot.com/2009/04/how-to-tracediagnosis-oracle-data-pump.html