Problem: How to Enable trace for a specific transaction in Sieble
Solution:
Method1: through form
-----------------------
Enable SQL*Trace by choosing Help > Diagnostics > Trace > Trace with binds
Disable SQL*Trace by choosing Help > Diagnostics > Trace > Trace off
Method2: through concurrent program
-----------------------
Please enable trace on the concurrent program using the following steps:
Goto Sysadmin > Concurrent > Program > Define
Query the concurrent program
Check the trace box to enable trace
Method3: through bde script for concurrent programs with binds
-----------------------
*** This method will enable trace with binds for all new sessions and connections
Download and review the script, bde_system_event_10046.sql, from Note.179848.1.
Run the script and press enter when prompted
Return to the application
Execute the concurrent program
Monitor the program until it begins to run
Return to SQL*Plus and press enter to cancel tracing new programs
Method4: Level 12 Event Trace
-----------------------
INSTRUCTIONS FOR FND SESSION SQL
a. Please deliver a complete trace file created in the following manner:
First enable trace for a specific user:
- Bring up the Core application - System Administrator responsibility
- Move to Profile/System
- Check off the USER box - and enter your username that you are using when
getting the error
- Search on the following profile option - 'Initialization SQL Statement -
Custom'
Please set this at the user level with the following string:
BEGIN FND_CTL.FND_SESS_CTL(,, , 'TRUE',,'ALTER SESSION
SET TRACEFILE_IDENTIFIER='||'||'TAR' ||'||' EVENTS
='||'||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||');
END;
(Cut and paste the above sql statement against the initialization sql -
Custom Profile at the User Level )
This must be one complete line of text - so may be best to copy the string
into notepad prior to putting it into the profile.
b. Once the profile is set, please change responsibilities and directly go
and reproduce the problem.
To locate the trace file, use the following sql in sqlplus:
select name, value from v$parameter where name like 'user_dump_dest';
- The value is the location of the trace file on the database server
- Please move to this directory and search for the file having TAR in the
filename
- This is the trace file created - please tkprof and upload BOTH the raw
and tkprof trace file to metalink.
c. Ensure that the profile option is unset before exiting the Application.
This is a very important step. If this is not unset, unnecessary trace
files will be generated.
Method5: finding concurrent program trace files:
-----------------------
column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off
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
where "&request" is the concurrent request id for the inventory
transaction
worker. also get the outputs of the following:
Method6: Creating a TKPROF file
-----------------------
A trace file is a raw set of data produced by the Oracle Database.
TKPROF reformats the raw data so that it is easier to review.
1. Retrieve the trace file.
2. Issue a command like the following to create a TKPROF version of the
trace file:
tkprof
sys=no
explain=apps/
Method7: Creating a TraceAnalyzer File
-----------------------
Note#224270.1: Trace Analyzer
A trace file is a raw set of data produced by the Oracle Database.
TraceAnalyzer reformats the raw data so that it is easier to review.
It is a more powerful tool than TKPROF.
It translates things like bind variables and make them easy to find.
1. Download TraceAnalyzer from Note.224270.1
2. Install trace analyzer in SQL: @TRCACREA.sql;
3. Retrieve the trace file.
4. In SQL*Plus, issue a command like the following to create a TraceAnalyzer
version of the trace file:
@TRCANLZR.sql UDUMP vis015_ora_22854.trc
