FRD TRACE in R12 :

Enable FRD Trace for Application User:

login to Oracle ebiz R12 as sysadmin.
Navigate to profile options : Profile à System

Enter profile option : ICX: Forms Launcher
Enter User Name : Manjunatha

Copy the url from site level and append ?record=collect to the url and paste the url at user level

http://waseem.learning.com:8000/forms/frmservlet?record=collect

save it.

Provide the url to the user tell user to login using the url mentioned. Reproduce the issue.
once the Issue is reproduced revert back the changes.

location of trace file  : $FORMS_TRACE_DIR is the place where trace file will be generated.
trace file name will be with collect*
uploade the collect* to oracle.
$FORMS_TRACE_DIR = $INST_TOP/logs/ora/10.1.2/forms

After enabling FRD TRACE : User may get the below error messages after entering login credentials :

Application is not authorized to access the database
Take backup of the “context_file”
search for the parameter s_appserverid_authentication change the value of this parameter from SECURE to OFF as shown below:

change the value of "s_appserverid_authentication" to OFF from SECURE and run autoconfig
e.g
<appserverid_authentication oa_var="s_appserverid_authentication">SECURE</appserverid_authentication>
to
<appserverid_authentication oa_var="s_appserverid_authentication">OFF</appserverid_authentication>

Stop the Application Services.
Run Autoconfig.
Start the Application Services again.
Inform the user to login and test.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
To Enable/Disable Trace :

To start tracing:

exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', TRUE);

/* execute your selects to be traced */

To stop tracing:

exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', FALSE);

To start tracing:

exec sys.dbms_monitor.session_trace_enable(session_id=>18,serial_num=>226, waits=>true, binds=>true);

/* execute your selects to be traced */

To stop tracing:

exec sys.dbms_monitor.session_trace_disable(session_id=>18,serial_num=>226);

Disable Trace :

exec dbms_monitor.session_trace_disable;

begin

dbms_monitor.session_trace_disable(18,226);

end;

/

++++++++++++++++++++++++++++++++++++++++++++++

This post covers overview of How to troubleshoot long running concurrent request in Oracle
Apps 11i/R12

Step 1 : Check Concurrent Request ID of long running concurrent request from front end
Step 2 : Find SID, SERIAL# and SPID by running SQL (given below)
Step 3 : Enable event 10046 trace with level 12 using oradebug ( for 15-20 minute)
Step 4 : Disable trace (once you are happy with trace size)
Step 5 : Convert raw trace to TKPROF using various sort options like fchela, prsela, execpu
Step 6 : Check TKPROF out file to find root cause of slow concurrent request

Step 1 : Check Request ID from Find Concurrent request screen (In my case Request ID is 1145)

Step 2 : Run below command to find SPID, provide concurrent request ID (1145 in my case)
when prompted

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID;

Step 3.1 : Check and confirm SPID on Database Node
$ ps-ef | grep 2987
proddb 2987 1 0 13:30:43 ?
0:03 oracledbr12 (LOCAL=NO)

Step 3.2 : Set OSPID (2987 in my case) for ORADEBUG
SQL> oradebug setospid 2987

Step 3.3 : Enable trace for 10046 event with level 12
SQL> oradebug event 10046 trace name context forever, level 12
LEVEL 12 – Both Binds and Waits
LEVEL 8 – Only WAITS
LEVEL 4 – Only BIND Variables

Step 3.4 : Locate Trace file as
SQL>oradebug tracefile_name
/oracle/apps/proddb/10.2.0/admin/prod_CSDoracle/udump/ prod _ora_2987.trc
Wait for 15-20 minutes

Step 4 : Disable trace
SQL> oradebug event 10046 trace name context off

Step 5: Create tkprof file like
$ tkprof prod _ora_2987.trc prod _ora_2987.txt explain=apps/pwd sort=(exeela,fchela)
sys=no

Step 6 : Check TKPROF file to find root cause of slow concurrent request

++++++++++++++++++++++++++++++++++

MOS ID 296559.1 

++++++++++++++++++++++++++++++++++