Wednesday, 11 July 2012

Tracing/Debugging Oracle Applications Concurrent Programmes - 11i/R12

Diagnosing/Tracing Oracle Applications Concurrent Programmes 11i/R12
  1. Simple tracing :- Creates a trace file without bind or wait (tracing without using event 10046 at level 12)

    1. Query the Concurrent programmes definition
    2. Check the Enable Trace check box

    3. Run the concurrent program using relevant responsibility
    4. Note down the concurrent request number after submitting
    5. Go to the oracle udump directory and find the trace file. Trace file names normally contain the os process id.

  2. Extended Tracing:- Creates a trace file with bind and wait (tracing with binds and waits using event 10046 at level 12)
  • Set Profile Value at respective level 

  • Go to the SRS screen of the respective responsibility to submit the request you want to trace
  • Select the name of the request 
  • Click on the debug options button (If you don’t set the profile option the debug option won’t be enabled)
  • Clicking debug options take you to create debug rule page   

  • Select the SQL Trace check box and select SQL Trace with Binds and waits
  • Click the OK button

  •  Click the Ok button

  • Submit the Request
  • After submitting the request note down the request id
  • Find out the process id which is actually doing the work
  • Go to the udump directory on the database server and find out the trace file, trace file name contains the process id you have from the above step

    Use the following sql to find out process id
    select request_id,oracle_process_id from fnd_concurrent_requests WHERE request_id = :request_id ;

    Go the udump location and search for trace file which contains this process id, the above query gave me my process id 16301.
    So here is my trace file name test_ora_16301.trc

    Use tkprof or trcanlzr to format the trace file.
    tkprof does not report on bind variables but you can see bind variable info in the trace file

Note: On R12 make sure you have applied Patch:8743459(MOS:1273539.1) . Trace file can be found in the user_dump_dust location with file name  <dbname>_ora_<process_id>_<USER_NAME>_CR<reqno>.trm