Thursday, October 29, 2009

How to Enable the Concurrent Program Trace

How to find the Trace file generated for a concurrent program? (Doc ID 967966.1)
Enabling Concurrent Program Traces (Doc ID 123222.1)

Login as Sysadmin -> System Administrator -> Concurrent : Program -> Define
Press F11 -> Enter the COncurrent Program Name (Eg. Active Users) -> Press Ctrl + F11
Enable the check box “Enable Trace” above “Copy To” button.
Click on Save.
Close the window.
Select Requests -> Run
Submit the concurrent request.

After completion or during execution of the request, you should see a trace file generated on the database server under udump directory.

SQL to identify the trace file

select oracle_process_id from fnd_concurrent_requests where request_id=[request_id];

or

select value from v$parameter where name = 'user_dump_dest';

select value from v$diag_info where name = 'Diag Trace'


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',
--'Prog. Name: '||prog.user_concurrent_program_name,
'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
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;


DB Node
cd $ORACLE_HOME/admin/[SID]_[hostname]/udump
ls -latr *[oracle_process_id]*.*

P.S: Dont forget to disable the trace

Query to find the child requests from parent requests

SELECT req.* FROM (SELECT con.user_concurrent_program_name ,req.request_id ,parent_request_id ,req.phase_code ,req.status_code FROM apps.fnd_concurrent_requests req ,apps.fnd_concurrent_programs_vl con WHERE 1 = 1 AND con.concurrent_program_id = req.concurrent_program_id ) req_set, (SELECT con.user_concurrent_program_name ,req.request_id ,req.phase_code ,req.request_date ,req.requested_start_date ,req.actual_completion_date ,req.status_code, parent_request_id FROM apps.fnd_concurrent_requests req ,apps.fnd_concurrent_programs_vl con WHERE 1 = 1 AND con.concurrent_program_id = req.concurrent_program_id ) req WHERE req_set.parent_request_id = 5337352 -- Request id of the submitted request set. AND req.parent_request_id = req_set.request_id order by requested_start_date desc;

No comments:

user level export and import

expdp parfile=PLCT170.par oracle@uslp123sd7dfcvxsza > more PLCT050.par userid= "/ as sysdba" dumpfile=T050.dmp logfile=expdpT0...