Working with Trace file for a concurrent program

-----------------------------------------------------------------
Enable trace file for a concurrent program 
-----------------------------------------------------------------


Log on to oracle application
      1.    Go to System Administrator -> Concurrent -> Program -> Define.
      2.    Query the concurrent program for which you want to enable the trace.
      3.    Enable the check box “Enable Trace” and save it.
      4.    Now from the appropriate responsibility, run the concurrent program.
      5.    A SQL Trace will be Saved in the ../udump directory of the database server







-----------------------------------------------------------------
How to extract the trace file of a concurrent program?
-----------------------------------------------------------------


Following steps need to be performed:


1. Setup the profile value at user level with the following value


Profile name: "Initialization SQL Statement - Custom"
User: abc
Profile value:
BEGIN FND_CTL.FND_SESS_CTL('','','TRUE','TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER = abc MAX_DUMP_FILE_SIZE = 5000000 EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'||'''');END;


2. Now on ward all the trace will be tracked in the .trc file. Find out where the trace file resides in the DB.


Go to Help -> Diagnosics -> Trace. enable trace. After doing so, a pop up will show the server path where the trace will get create..


usually .trc file will get created in DB server. The file path may be say "/u01/app/oracle/admin/RAC11D/udump/RAC11D_ora_17181_abc.trc". the path will be stored along with the trc file name. usually the value in the profile value TRACEFILE_IDENTIFIER=AKONDURU is used in the trace file naming


3. Now we have enabled trace for your user login. So what ever you do. say run a concurrent program. how the concurrent program is executed? the sql queries executed. how the control is going... every thing is tracked in the trace file.




4. Log in to the DB server. extract the file and convert to readable format.


Go to the following path in the DB server -> '/u01/app/oracle/admin/RAC11D/udump/'. Identify the file name which is prompted while enabling the trace (look point 2).


the .trc file is not readable. You have to convert it (tkprof) to make it readable. use the following command to do so.




tkprof (input).trc output.txt EXPLAIN=()


where input.trc is the trace file and output.txt is in readable format


5. After you get the trace file. make sure to remove the profile value that you have set for the profile : "Initialization SQL Statement - Custom".. else the system will go on creating a huge trace file.


--------------------------------------------------------------------------
Query to locate the trace file, it takes as input the ' Concurrent Request id'
--------------------------------------------------------------------------


SELECT
'Request id: '||request_id , 
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'), 
'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module,
TO_CHAR(req.actual_start_date,'DD-MON-YYYY HH24:MI:SS') Request_start_date ,

  TO_CHAR(req.actual_completion_date,'DD-MON-YYYY HH24:MI:SS') Request_completion_date ,

  req.logfile_name ,

  req.outfile_name ,

  req.argument_text parameters_passed

from
fnd_concurrent_requests req, 
v$session ses,
v$process proc, 
v$parameter dest, 
v$parameter dbnm,
fnd_concurrent_programs_vl prog, 
fnd_executables execname where req.request_id = &request 
and req.oracle_process_id=proc.spid(+) 
and proc.addr = ses.paddr(+) 
and dest.name='user_dump_dest' 
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id 
and req.program_application_id = prog.application_id 
and prog.application_id = execname.application_id 
and prog.executable_id=execname.executable_id;


The output would be 'directory'/'filename.trc'
eg: 'directory'/ora_3465_act.trc.


In the above directory, do 'ls -l' for '*3465*trc' and any latest file that you see with 3465 as a part of the file name would be the trace file.