1. Trace a concurrent program with database .trc file
1) As System Administrator, go to Concurrent-> programs -> Define, and query program PROGRAM_NAME ie ARRGTA, or RAXTRX
2) Enable (or make sure) the check box called 'Enable Trace' and save
3) Before starting trace, make sure the following DB parameters are set:
MAX_DUMP_FILE_SIZE = UNLIMITED
TIMED_STATISTICS = TRUE
STATISTICS_LEVEL = ALL
If they are not set as above, run the following as SYS:
SQL> alter system set MAX_DUMP_FILE_SIZE=UNLIMITED;
SQL> alter system set TIMED_STATISTICS=TRUE;
SQL> alter system set STATISTICS_LEVEL=ALL;
4) Ask the user to rerun the report reproducing the issue
5) After the process finishes, run tkprof for the trace file with sort=exeela,prsela,fchela and
explain=<apps owner>/<apps password>. Example:
tkprof <sourcefile> <outputfile> sys=no explain=apps/<pw> sort=exeela,prsela,fchela
Please make sure the process already finished. If the process has not finished the rows column in tkprof will be all zeros and useless. Be sure to use the TKPROF under the RDBMS Oracle Home /bin directory. The TKPROF under the Applications Oracle Home will not yield accurate results.
2. Oracle Support steps to trace just one session (when trace can not be turned on on a program) using SQL code.
1) Make sure trace is NOT enabled for any of the concurrent programs we will be running:
System Administrator > Concurrent > Programs > Define
Query the program ARRGTA
Make sure trace is DISABLED (It is important trace is disabled for the program so it does not interfere with the rest of the steps)
2) Create the following Procedure as database user APPS:
CREATE OR REPLACE PROCEDURE cp_level12_trace_util AS
req_id number:=fnd_global.conc_request_id;
prog_id number:=fnd_global.conc_program_id;
appl_id number:=fnd_global.RESP_APPL_ID;
prog_name varchar2(128);
trc_label varchar2(128);
BEGIN
if req_id > 0 then
select max(concurrent_program_name) into prog_name
from fnd_concurrent_programs
where concurrent_program_id=prog_id
and application_id=appl_id;
trc_label:='CP_'||prog_name||'_REQ_'||req_id;
FND_CTL.FND_SESS_CTL(''
,''
,''
,'TRUE'
,''
,'ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED');
FND_CTL.FND_SESS_CTL(''
,''
,''
,'TRUE'
,''
,'ALTER SESSION SET TIMED_STATISTICS=TRUE');
FND_CTL.FND_SESS_CTL(''
,''
,''
,'TRUE'
,''
,'ALTER SESSION SET STATISTICS_LEVEL=ALL');
FND_CTL.FND_SESS_CTL(''
, ''
, ''
, 'TRUE'
, ''
, 'ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''
|| trc_label
|| ''''
|| ' EVENTS ='
|| ''''
|| ' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '
|| '''');
end if;
END;
/
3) Set the following profile option at user level, only for the user who reproduces the issue:
Profile option: Initialization SQL Statement - Custom
Value: begin cp_level12_trace_util; end;
NOTES: Be careful, as sometimes it may block user from login, and also see FND_INIT_SQL may cause error.
4) Run the concurrent request by the user, and upload all trace files, which have the request_id in their name (It will have something like CP_PROGNAME_REQ_999999, where PROGNAME is the short name of the program, and 999999 is the request id). If Trace files are too big, do not upload them for now
5) Run tkprof for the above trace files, and upload the output:
tkprof <trace file> <output file> sort=exeela,prsela,fchela explain=apps/<password>
Make sure the process already finished (if it hangs, please leave it running for a few hours before canceling). If the process has not finished the rows column in tkprof will be all zeros.
6) Upload also the log files for the concurrent requests
7) Do not forget to set the profile option “Initialization SQL Statement – Custom” back to BLANK afterwards.
3. Trace EBS Forms session
1) Change Profile option Utilities:Diagnostics to "Yes" at user level
2) Help --> Diagnostics --> Trace --> to choose a tracing level. Then, a popup box will tell where the trace file .trc is saved.
4. Find which node an EBS R12 session is connecting to
1) Change Profile option FND: Diagnostics to "Yes" at user level
2) Help -> About Oracle Applications (below section will tell which form node you have connected):
----------------------------------------
Forms Server
----------------------------------------
Oracle Forms Version : 10.1.2.3.0
Application Object Library : 12.0.0
Machine : node_name.domain.COM
NOTES:
- Turn "FND: Diagnostics" profile to Yes at user level will also add "About this Page" to EBS Homepage's left bottom after login and add "Diagnostics" on the right top of OA Framework pages. Clean the cache if they do not show up immediately after re-login.
- This profile option is different from turning "Utilities:Diagnostics" to Yes, which enables tracing on sub-menus of Help on GUI forms.
- I found that even "FND: Diagnostics" profile is set to No, "About this Page" may still shows up. That could be controlled by other things, such as other profile options for Personalization:
Personalize Self-Service Defn = YES
FND: Personalization Region Link Enabled = YES
Disable Self-Service Personal = NO (Only at Site)
