How to generate a trace file for a PL/SQL based concurrent program?

Oracle Apps Interview QuestionsCategory: AOL-SysAdminHow to generate a trace file for a PL/SQL based concurrent program?
Questions Master asked 4 years ago
1 Answers
Shailender Thallam Staff answered 4 years ago

Step1: Enable Trace at the Report Definition.
Go to System Administrator -> Concurrent Programs -> Define , Query the report and check the ‘Enable trace‘ check box

 Navigate to:
  Profile->system
  Query on the Profile Option: “Concurrent: Allow Debugging”

  This should be set to ‘yes’ at ‘Site’ level.

  If it isn’t set, then set it, then logout and bounce the APPS services.

  The ‘Debug Options’ button on the concurrent program will now be enabled. )

Step2: Run the report for Step 1. above

Step3: Get the request id from below query. i.e. Request id = 11111111

SELECT fcr.request_id “Request ID”
  –, fcr.oracle_process_id “Trace ID”
  ,
  p1.value
  ||’/’
  ||lower(p2.value)
  ||’_ora_’
  ||fcr.oracle_process_id
  ||’.trc’ “Trace File” ,
  TO_CHAR(fcr.actual_completion_date, ‘dd-mon-yyyy hh24:mi:ss’) “Completed” ,
  fcp.user_concurrent_program_name “Program” ,
  fe.execution_file_name
  || fe.subroutine_name “Program File” ,
  DECODE(fcr.phase_code,’R’,’Running’)
  ||’-‘
  ||DECODE(fcr.status_code,’R’,’Normal’) “Status” ,
  fcr.enable_trace “Trace Flag”
FROM fnd_concurrent_requests fcr ,
  v$parameter p1 ,
  v$parameter p2 ,
  fnd_concurrent_programs_vl fcp ,
  fnd_executables fe
WHERE p1.name                  =’user_dump_dest’
AND p2.name                    =’db_name’
AND fcr.concurrent_program_id  = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcp.application_id         = fe.application_id
AND fcp.executable_id          =fe.executable_id
AND ((fcr.request_id           =
  &request_id
OR fcr.actual_completion_date > TRUNC(sysdate)))
ORDER BY DECODE(fcr.request_id,
  &request_id, 1, 2),
  fcr.actual_completion_date DESC;

–you will be prompted to enter the request_id;

Step4: trace file directory using an sql query
SELECT value FROM v$parameter WHERE name = ‘user_dump_dest’;

Step5: Go to directory in Step 4. above.

Step6: Find out the trace file using GREP command
eg:- grep ‘11111111’ *.trc

Step7: Run TKPROF to get a structured information from trace file.
Syntax of the command:
$ tkprof <RAW TRACE> <output> explain=apps_uname/apps_pwd sys=no  sort=prsela,exeela,fchela