Enable Tracing in EBS: "Initialization SQL Statement – Custom"

A user event trace is very handy for tracing sql operations to debug various issues. The benefit of a user event trace is that it is linked to a specific user so that only code run by this user is traced. This makes diagnosis easier when compared to similar tracing methods at the database level where all user calls are traced.

Step 1
Login to Oracle Applications and select the System Administrator responsibility.
Choose Profile - System


Step 2
In the find profile field select the user which you wish to trace. In the profile field enter 'Initialization SQL Statement - Custom"
Select find


Step 3
In the find profile results form copy and paste the following into the 'user' field. Do not update the site level field.

BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'USERID' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;

Note: copy and paste the above as one line. Failure to paste the values properly or pasting the values with incorrect syntax will result in this user not being able to login.


Step 4 (Optional)
Save the profile option change. Stop and start the java virtual machine for the change to take effect
11i: use adapcctl.sh
r12: use adoacorectl.sh


Step 5
Login to the application and reproduce the issue. Then quickly log off. Try and avoid any un-necessary keystrokes as this simply makes the log files larger and the issue hard to pinpoint in the logs.


Step 6
login to unix/windows as the oracle user. Navigate to the user dump destination which us usually $ORACLE_HOME/admin/<context>/udump
You will see a series of trace files generated during the time of your issue reproduction. Tkprof all of the generated files and upload both the raw and tkprof files to your service request.

You can also check the user dump destination via the following:
sql> show parameter user_dump_dest;



Notes:
When using this profile, DO NOT turn trace on the menu, as doing so would actually turnoff Event 10046. Turning Event 10046 to any level, turns trace automatically.

Event 10046 level 1 is regular trace, level 4 is tracing with bind variables, level 8 is with database waits and level 12 with both,bind variables and database waits.

alter system set max_dump_file_size=UNLIMITED scope=both;