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 2011Demo : 1
C:\>sqlplus sys/xxxx@noida as sysdba
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
'_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 2011Copyright (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 :-)
