How to identify trace file in USER_DUMP_DIRECTORY?

ORA-600 or ORA-7445 kind of errors are always have lot of things hidden which DBA needs to determine by taking session trace of various database components. There are various way of taking these dumps like enabling session trace, using alter session command, end to end tracing, or by using oradebug utility.

Once we give command to generate trace oracle saves the trace in file in directory configured in USER_DUMP_DEST parameter. Once the tracing ends, we need to find the file according to particular naming convention. Basically, file name have instance name and OS PID through which trace file is generated. The following script returns the path to the trace file that the current session writes.
It returns the path whether or not tracing is enabled.


SQL> select u_dump.value || '/' || instance.value || '_ora_' || v$process.spid
|| nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File"
from V$PARAMETER u_dump
cross join V$PARAMETER instance
cross join V$PROCESS
join V$SESSION on v$process.addr = V$SESSION.paddr
where u_dump.name = 'user_dump_dest'
and instance.name = 'instance_name'
and V$SESSION.audsid=sys_context('userenv','sessionid');

Trace File
----------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/admin/ORCL/udump/ORCL_ora_2631.trc
/u01/app/oracle/admin/ORCL/udump/ORCL_ora_2646.trc
/u01/app/oracle/admin/ORCL/udump/ORCL_ora_2650.trc
/u01/app/oracle/admin/ORCL/udump/ORCL_ora_2682.trc
/u01/app/oracle/admin/ORCL/udump/ORCL_ora_2691.trc
/u01/app/oracle/admin/ORCL/udump/ORCL_ora_2656.trc
/u01/app/oracle/admin/ORCL/udump/ORCL_ora_2640.trc
/u01/app/oracle/admin/ORCL/udump/ORCL_ora_2636.trc
/u01/app/oracle/admin/ORCL/udump/ORCL_ora_2706.trc
/u01/app/oracle/admin/ORCL/udump/ORCL_ora_10767.trc
/u01/app/oracle/admin/ORCL/udump/ORCL_ora_2822.trc
/u01/app/oracle/admin/ORCL/udump/ORCL_ora_10769.trc
/u01/app/oracle/admin/ORCL/udump/ORCL_ora_10771.trc
/u01/app/oracle/admin/ORCL/udump/ORCL_ora_16143.trc
/u01/app/oracle/admin/ORCL/udump/ORCL_ora_10773.trc
/u01/app/oracle/admin/ORCL/udump/ORCL_ora_3547.trc

16 rows selected.

These files can be further processed by TRCSESS, TKPROF tools to get reports in expected format. Because actual trace file have huge dump of data, which can not be understood without having the expertize of examining the trace.

Comments:

Thanks for the above query - in 11g - select tracefile from v$session join v$process on (addr=paddr) and sys_context('userenv','sessionid')=audsid / Regards, Js

Posted by Jagjeet Singh on September 13, 2009 at 04:35 PM IST #

WOW!! Thanks for your invaluable info that you added to this blog. Regards, Shailesh

Posted by shailesh.mishra on September 14, 2009 at 04:21 PM IST #

select u_dump.value || '/' || instance.value || '_ora_' || v$process.spid || nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File" from V$PARAMETER u_dump cross join V$PARAMETER instance cross join V$PROCESS join V$SESSION on v$process.addr = V$SESSION.paddr where u_dump.name = 'user_dump_dest' and instance.name = 'instance_name' and V$SESSION.audsid=sys_context('userenv','sessionid');

Posted by j.nagaraju on September 15, 2009 at 09:21 AM IST #

Thanks Nagaraju.............

Posted by shailesh.mishra on September 15, 2009 at 09:48 AM IST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

What I learned about Oracle

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today