Enable Trace In Oracle Apps

Tracing Self Service Web Applications For A User

You enable trace for all actions that a user takes by setting a profile option for the user. This will function when the user logs into the Oracle Web Applications or forms. This method uses an Event Trace.

Set up Event Trace

A. Ensure permissions are set for the FND_INIT_SQL profile option

1.    Log onto the Applications Forms with the Application Developer Responsibility

2.    Navigate to the Profile menu

3.    Query up the profile name "FND_INIT_SQL"

4.    In the bottom block of the form, make sure that ALL checkboxes are checked typically, you will have to enable the checkboxes under "User Access" to make it "Visible" and "Updatable".

B. Enable Trace for Specific User

1.    Log onto System Administrator Responsibility

2.    Navigate: Profile > System

3.    Search for the profile option Initialization SQL Statement - Custom at     USER level.
Make sure you enter the Username for the user you want to turn on trace for.

4.    Enter the following sql statement into the User Level profile option.
This must be one complete line of text. Ensure you change USERID to the actual username of the user.

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

Ensure that the user has logged out and has no session open.

SELECT user_name, count(*) How_many_sessions
FROM icx_sessions icx, fnd_user u
WHERE icx.user_id = u.user_id
AND last_connect > sysdate - (4/24)
AND disabled_flag != 'Y'
AND PSEUDO_FLAG = 'N'
GROUP BY user_name
ORDER BY 2 desc

C. Reproduce the Problem

1.    Log onto the Application as the User that has trace set on.

2.    Reproduce the problem.

IMPORTANT  : if you save and blank the profile immediate before and after the test you have only the trace in this window time
D. Turn OFF Tracing

1.    Log back onto System Administrator

2.    Query the profile option Initialization SQL Statement - Custom at USER level.

3.    Set the profile option back to blank

E. Find Raw Trace File

1.    Obtain the Trace File Location

2.    Go to that directory on the Database server

3.    Search for the file with the current date/time that has the value you entered as USERID in the profile option string in the filename

To locate the Trace File Location run the following SQL in SQL*Plus:

select name, value from v$parameter where name like 'user_dump_dest';

Name and path of trace file:

SELECT s.sid,
       s.serial#,
       pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||    
       '_ora_' || p.spid || '.trc' AS trace_file
FROM   v$session s,
       v$process p,
       v$parameter pa
WHERE  pa.name = 'user_dump_dest'
AND    s.paddr = p.addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');




F. TKPROF Raw Trace File
A trace file can be reviewed using TKPROF.  TKPROF reformats the raw data so that it is easier to review. The TKPROF commands are normally run at the operating system command prompt. This will be signified with a $ as this is a common prompt in UNIX.

1.    Retrieve the trace file.

2.    Issue a command like the following to create a TKPROF version of the trace file. The explain option will look to see how each query is handled by the database in terms of what tables and indexes are referenced.

$tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no

Where:

raw_trace_file.trc: Name of trace file
output_file:        tkprof out file
explain:            This option provides the explain plan for the sql                         statements
sort:               This provides the sort criteria in which all sql 
                    statements will be sorted. This will bring the bad sql at 
                    the top of the outputfile.
sys=no:             Disables sql statements issued by user SYS

A handy technique when trouble shooting performance issues is to use TKPROF to look at the longest running queries. Since trace files related to performance can be huge, one might spend hours looking through the results to find the queries causing the issue. If you sort the file by the longest running queries first, it makes it much easier to investigate. The following example sorts by longest running queries first with the sort options selected as "sort='(prsela,exeela,fchela)'" and limits the results to the "Top 10" long running queries with the "print=10" command:

$ tkprof <filename.trc> <output_filename> sys=no explain=apps/<password>
sort='(prsela,exeela,fchela)' print=10

Before running tkprof, remember to have the DBA check the version of tkprof that is being used by your environment with the command "which tkprof". If the tkprof version does not match the database version, you must change your environment to use the database server version of tkprof. Using the wrong version can produce wildly skewed and incorrect data in the output. Before uploading the tkprof to Support, open the file and check that the database version in the tkprof header is the same as that of your server. If it says 8.0.6.3.0 and you are on 9.2.0.4.0, it is not going to give accurate information.

Debug Log Information For Self Service Web Applications

To get debug for OAF , you need to do the following A or B:

A. Set the following profile options for login at User Level:

1.    Set the Profile Option FND: Debug Log Enabled to Yes

2.    Set the Profile Option FND: Debug Log Level at user level to STATEMENT (the most detailed level) and FND: Message Level Threshold to low level [If FND: Message Level Threshold has value High , only high severity messages will be inserted in log table]

3.    Set the Profil Option FND: Debug Log Filename

o    Leave blank if you want log messages to go to the database.

o    If you want middle tier log messages to go to a file directly, then set the profile at user level to something like "/tmp/ASN_Debug.txt".

4.    Set profile FND: Debug Log Module at user level to a value of "%" or "asn.%".

o    It specifies the modules you want to log the message for. Multiple filters can also be specified, for example "fnd.%, jtf.%, asn.%". If you want to log all modules enter "%". 

o    Note: Logging at STATEMENT level will have a bad impact on performance. The Page will show a warning "Low-level logging is currently enabled. Your application will not perform as well while Low_logging is on." If % is specified in FND: Debug Log Module, then logging will perform less optimally than if specific modules like asn.% are specified.

5.    Run the flows that you want to generate a log for

B. Viewing Log Messages

If you specified a filename in the profile FND: Debug Log Filename (e.g. "/tmp/ASN_Debug.txt"), you can get the log message for the middle tier log messages from the appropriate middle tier.

If you left the profile FND: Debug Log Filename as blank, you can get the log message as follows:

1.    At User Level set the profile FND: Diagnostics to Yes for a user which has System Administrator Responsibility.

2.    Using the System Administrator Responsibility click on the Diagnostics Link and choose the 'Show Log' option.

3.    Enter the appropriate date range in Post Before and Post After. In module enter % or asn.% depending upon the issue that you are investigating. Select Any for level. You can also do an advanced search whee you have the ability to view log messages for a particular user.

4.    Click on download all to export the Log.

In additional to viewing all logged messages using the show log option, you can also view the middle tier log messages on individual pages. To do this you need to click on the Diagnostics link for any user that has the profile FND: Diagnostics set to yes. Select 'Show Log on Screen', select 'Statement; and click Go.
The log is archived in FND_LOG_MESSAGES table. You can run a concurrent program called Purge Debug Log (FNDLGPRG) to purge the table.

Tracing a Concurrent Program

To trace a concurrent program you need to:

1.    Check the enable trace checkbox on the Concurrent Program definition in System Administrator

2.    Set the profile option Concurrent: Allow Debugging to Yes in System Administrator

3.    Click Debug Options (B) when running the concurrent program

4.    Select the SQL trace checkbox

5.    Run the Concurrent Program