Introduction: –Steps to enable trace | As we know that tracing the Concurrent program is very mandatory option to Debug any issue while running the concurrent program long. Here we will discuss about how to enable trace for concurrent programs in oracle apps r12 and generate tkprof file to diagnose the ongoing issue.
What does the Purpose enable Trace for concurrent programs?
Tracing the Concurrent Programs is the best technique to Debug the issues in Concurrent Programs or its dependent objects. We are facing slowness issue on daily basis, To debug possible error, it will help.
Steps to enable trace for concurrent program.
Follow the below steps to enable the trace file for concurrent request program to generate tkprof file from trace file.
Login in EBS and do below steps to enable trace in oracle apps r12: –
To generate trace file without any issue, make sure following parameters are set as below.
SQL> alter system set MAX_DUMP_FILE_SIZE=UNLIMITED;
SQL> alter system set TIMED_STATISTICS=TRUE;
SQL> alter system set STATISTICS_LEVEL=ALL;
1. Go to Concurrent Program Definition
Application Developer –> Concurrent –> Program –> check Enable Trace
Query your concurrent program and check the Enable Trace check box at the bottom of the screen.
2. Set the Profile Concurrent: Allow Debugging to YES.
3. Navigate to the Responsibility from where you are running the program.
4. Before Submitting the Concurrent Program in Submit Request screen there will be an option like Debug Options just click on that button.
5. In the Debug Options select the SQL Trace and enable Trace with binds.
6. Submit the Concurrent Program.
7.Trace file name will always be post fixed with oracle_process_id.
To find out the oracle_process_id use the below SQL
=> Using oracle process id, we can identify the actual trace file generate tkprof file. So that we can resolve the issue.
SQL>Select oracle_process_id from fnd_concurrent_requests where request_id= &Request_id;
8.Trace file path can be derived using the below SQL
SQL>Select * from v$parameter where name=’user_dump_dest’;
9. Get the trace file to your local machine.
Go to under user_dump_dest and collect the reported trace file.
10. Open the Command Prompt and run the below command to generate tkprof.
$TKPROF < Trace File_Name.trc> <Output_File_Name.tkprf> SORT=fchela
11. After that new output file will be generated with the name given in the above command. Analyse the Output file to know the answers for your problem.
Use below Sql queries to find the log files by Request id
SQL>select req.oracle_process_id trace_name, req.request_id, req.requested_by,usr.user_name, prog.user_concurrent_program_name, req.request_date, req.phase_code, req.status_code, req.logfile_name , req.outfile_name, dest.value as user_dump_dest from apps. fnd_concurrent_requests req, gv$parameter dest, apps.fnd_concurrent_programs_vl prog, apps.fnd_user usr where req. concurrent_program_id = prog.concurrent_program_id and req.requested_by = usr.user_id –and request_id like ‘9876567’ and dest.name= ‘user_dump_dest’ and request_id like & Request_id;
Sql query find the log files by username
SQL>select req.oracle_process_id tracename, req.request_id, req.requested_by,usr.user_name, prog.user_concurrent_program_name, req.request_date, req.phase_code, req.status_code, req.logfile_name , req.outfile_name, dest.value as user_dump_dest from apps. fnd_concurrent_requests req, gv$parameter dest, apps.fnd_concurrent_programs_vl prog, apps.fnd_user usr where req.concurrent_program_id = prog.concurrent_program_id and req.requested_by = usr.user_id –and request_id like ‘6013239’ and dest.name= ‘user_dump_dest’ and usr.user_name like ‘squamer%G%’ order by request_date desc;
SQL query to find process id.
SQL>Select oracle_process_id from fnd_concurrent_requests where request_id = &Request_id;
SQL>select * from apps. fnd_concurrent_requests req,gv$parameter dest , apps.fnd_concurrent_programs_vl prog where req. concurrent_program_id = prog.concurrent_program_id and request_id like ‘9876567%’ and dest.name= ‘user_dump_dest’;
Hope this will help to enable trace for your running concurrent request. You may also check another post to Find sid from concurrent request in EBS
If you want to another reference to enable trace in concurrent programs, you may check at ‘Enable sql trace’.
You may check latest post:-