The purpose of this post is to bring together methods to active and retrieve trace when executing forms, reports, executables, and others involved in Oracle Applications v. 11.x. There are several notes on this subject; however they are not quite complete or clear for end users.
SCOPE & APPLICATION:Trace files are often needed for support purpose. Customers don't always know how to active trace and retrieve trace file.
CONTENTS:
1.
TRACING DATABASE INSTANCE
2.
TRACING A SESSION
3.
TRACING FROM A FORM
4.
TRACING A FORM
5.
TRACING A REPORT
6.
TRACING AX
7. TRACING OTHER CONCURRENT PROGRAMS
8. TRACING A PROCESS
9. RETRIEVING TRACE FILE
10.
FORMATING TRACE FILE
11.
TRACE FILE FOR IMPRONTANT EVENTS
12.
TUSC OUTPUT OF A PID
13.
TRACING JSP PAGE OR SSWA APPLICATIONS
1. TRACING DATABASE
INSTANCE
Tracing the entire database is not
the best way to analyze issue in Oracle Applications but it could be the only
means in particular cases.
Principle is to:
·
Edit
init<SID>.ora file
·
Add
'SQL_TRACE=TRUE' or 'EVENT=...' parameters
·
Eventually
verify presence of 'TIMED_STATISTICS=TRUE' parameter to track and analyze performance issues
·
Stop
concurrent managers and shutdown database
·
Restart
database and concurrent managers
2. TRACING A
SESSION
You could need to trace a particular
session for example if issue occurs for only one user or if you can't shutdown
database.
Principle is to:
- Retrieve number from a current
session of Oracle Applications, the easiest way is to :
- connect to sqlplus (system account)
- run this script to get session and
serial numbers :
select p.pid, p.spid,
s.sid, s.serial#, s.username
from v$process p ,
v$session s
where p.addr = s.paddr
and p.pid = &your_pid;
=> enter PID found before it will give you needed session and serial
numbers
- Now run this command to active trace
for your session :
Enable
Trace:
Prompt Trace level 1 Trace with no binds
and no waits
Prompt Trace level 4 Trace with binds and
no waits
Prompt Trace level 8 Trace with waits and
no binds
Prompt Trace level 12 Trace with waits and
binds
Accept sid prompt 'Enter Sid:'
Accept serial prompt 'Enter Serial#:'
Accept trace_level prompt 'Enter
tracle_level:'
Exec
dbms_system.set_bool_param_in_session(&sid,&serial,'timed_statistics',true);
--optional
Exec dbms_system.set_int_param_in_session(&sid,&serial,'max_dump_file_size',214783647);
-- optional
Execute
dbms_system.set_sql_trace_in_session(&sid,&serial,true);
Execute
dbms_system.set_ev(&sid,&serial,10046,&trace_level,'');
Disable
Trace:
Accept sid prompt 'Enter Sid:'
Accept serial prompt 'Enter Serial#:'
Execute
dbms_system.set_sql_trace_in_session(&sid,&serial,false);
3. TRACING FROM A
FORM
You can activate/deactivate database
trace from any screen in Oracle Applications via menu Help => Tools =>
Trace.
4. TRACING A FORM
You can also want to trace the
screen itself to collect information on triggers or built-ins which are
executed, or capture messages and unhandled exceptions...
Principle is to:
- Edit html file which launch Oracle
Applications
- Complete <PARAM
name="serverArgs" value="......."> with two values:
o
record=collect
and log=<your file name>
o
Example:
<PARAMname="serverArgs"
value="module=/disk5/ApplFRV1103/fnd/11.0.28/forms/F/FNDSCSGN
userid=APPLSYSPUB/PUB@FRV1103
fndnam=APPS record=collect log=/tmp/debug.out">
A debug utility exists for some AR
screens:
- Transactions (ARXTWMAI)
- Receipts (ARXRWMAI)
- Collections (ARXCWMAI)
Principle is to add parameter
AR_DEBUG_FLAG via Option Examine
5. TRACING A REPORT
Beginning with release 11.x it
becomes easy to activate database trace for a single report. You just need to
check trace option box in concurrent program definition (under System
Administrator responsibility).
If trace option is not available or
you report is customized you can insert an 'alter session set sql_trace=TRUE'
in it to activate database trace.
Principle is to:
- Make a copy of your report
- Generate a .rex file from .rdf (by
command r25convm or r25conv32,
- See respectively Note:1070541.6 and
Note:1020489.102)
- Edit the .rex file and insert the
line immediately after ('FND SRWINIT'); :
- srw.do_sql('alter session set
sql_trace=TRUE');
- Insert another line immediately
after ('FND SRWEXIT'); to stop tracing :
- srw.do_sql('alter session set
sql_trace=FALSE');
- Regenerate the .rdf file
6. TRACING AX
For example it is interesting to
trace Posting Manager when issue occurs.
Principle is to set profile options:
AX: Debug Mode to Yes
AX: Debug Level from 1 to 100
This will submit program 'AX Tracer'
which generates trace file under directory 'utl_file_dir' defined in init<SID>.ora
file (APPLTMP variable must be set also).
File name will be
'ax_<process_id>_<session_id>.trc'
7. TRACING OTHER CONCURRENT PROGRAMS
As for report you can check trace
option box for any concurrent program. There exist also profile options to activate
trace when particular concurrent programs are running. For instance you can set
these profile options below at user level:
INV: Debug Trace
MRP: Trace Mode
OE: Debug Trace
8. TRACING A PROCESS
It is sometimes interesting to retrieve
running SQL statement when a program hangs and process is pending.
Principle is to:
- Retrieve process id
- Then execute oradebug command:
ORADEBUG is an undocumented debugging
utility supplied with Oracle
Available as a standalone utility on Unix
(oradbx), as a standalone utility on VMS (orambx), within Server Manager
(svrmgr) and within SQL*Plus (Oracle 8.1.5 and above)
To use ORADEBUG within SQL*Plus login using
SQLPLUS /NOLOG
SQL> CONNECT
SYS/password AS SYSDBA
To distinguish them from other SQL*Plus
commands, all ORADEBUG commands are prefixed with "ORADEBUG" For
example to list the available options use: ORADEBUG
HELP
SQL> ORADEBUG HELP
HELP [command] Describe one or all commands
SETMYPID Debug current process
SETOSPID <ospid> Set OS pid of process to
debug
SETORAPID <orapid> ['force'] Set Oracle pid of process to debug
DUMP <dump_name> <lvl>
[addr] Invoke named dump
DUMPSGA [bytes] Dump fixed SGA
DUMPLIST Print a list
of available dumps
EVENT <text> Set trace event in process
SESSION_EVENT <text> Set trace event in session
DUMPVAR <p|s|uga> <name>
[level] Print/dump a fixed PGA/SGA/UGA
variable
SETVAR <p|s|uga> <name>
<value> Modify a fixed PGA/SGA/UGA
variable
PEEK <addr> <len> [level] Print/Dump memory
POKE <addr> <len>
<value> Modify memory
WAKEUP <orapid> Wake up Oracle process
SUSPEND Suspend
execution
RESUME Resume execution
FLUSH Flush
pending writes to trace file
CLOSE_TRACE Close trace file
TRACEFILE_NAME Get name of trace
file
LKDEBUG Invoke global
enqueue service debugger
NSDBX Invoke CGS
name-service debugger
-G <Inst-List | def | all> Parallel oradebug command prefix
-R <Inst-List | def | all> Parallel oradebug prefix (return output
SETINST <instance# .. | all> Set instance list in double quotes
SGATOFILE <SGA dump dir> Dump SGA to file; dirname in double
quotes
DMPCOWSGA <SGA dump dir> Dump & map SGA
as COW; dirname in double quotes
MAPCOWSGA <SGA dump dir> Map SGA as COW; dirname in double
quotes
HANGANALYZE [level] Analyze system hang
FFBEGIN Flash Freeze the Instance
FFDEREGISTER FF deregister
instance from cluster
FFTERMINST Call exit and
terminate instance
FFRESUMEINST Resume the flash frozen instance
FFSTATUS Flash freeze
status of instance
SKDSTTPCS <ifname> <ofname> Helps translate PCs to names
WATCH <address> <len>
<self|exist|all|target> Watch a
region of memory
DELETE <local|global|target> watchpoint
<id> Delete a watchpoint
SHOW <local|global|target>
watchpoints Show watchpoints
CORE Dump core
without crashing process
IPC Dump ipc
information
UNLIMIT Unlimit the
size of the trace file
PROCSTAT Dump process
statistics
CALL <func> [arg1] ... [argn] Invoke function with arguments
Some more examples:
Related to EBS OLTP Scripts
==================================================
oradebug setospid
&1
oradebug unlimit
oradebug dump errorstack
10 -- errorstack with level 10
=================================================
oradebug setmypid
oradebug unlimit
oradebug dump
hanganalyze 3 -- Hanganalyze with level 3
=================================================
oradebug setmypid
oradebug unlimit
oradebug dump
library_cache 10 -- Lib cache dump with level 10
=================================================
oradebug setmypid
oradebug unlimit
oradebug dump systemstate
1 -- system state dump with level 1
=================================================
oradebug setmypid
oradebug unlimit
oradebug dump
systemstate 10 -- system statedump with level 10
=================================================
9. RETRIEVING TRACE FILE
Directory where trace files reside
is defined by variable USER_DUMP_DEST, if it is not set, execute sql command
below from sqlplus (apps account):
select value from v$parameter
where name='user_dump_dest';
Now find PID (Oracle Process Id) and
determine corresponding SPID (Session Process Id).
select p.pid, p.spid
from v$process p , v$session s
where p.addr = s.paddr and p.pid = &your_pid;
=> enter PID found before it will
give you SPID
- Now you can retreive precisely
ora_<SPID>.trc file.
10. FORMATING TRACE
FILE
Use tkprof utility to format the raw
trace in more readable file.
For example run this command from
unix :
tkprof <raw trace file .trc> <any output name> explain=apps/<apps password>
11. TRACE FILE FOR
IMPRONTANT EVENTS
Hanganalyze: - alter session set
max_dump_file_size=unlimited tracefile_identifier=hanganalyze events 'immediate
trace name hanganalyze level 3'
Libcachedump: - alter session set max_dump_file_size=unlimited
tracefile_identifier=libcache events 'immediate trace name library_cache level
10'
Systemstatedump: - alter session set
max_dump_file_size=unlimited tracefile_identifier=systemstate events 'immediate
trace name systemstate level 1'
12. TUSC OUTPUT OF
A PID
pid=$1
/opt/tusc/bin/tusc -faepo /tmp/tusc_$pid.out -p $pid
13. TRACING JSP
PAGE OR SSWA APPLICATIONS
This can be done by setting a User Event Trace using “FND_INIT_SQL” profile:
This will create a smaller trace file as compared to setting event trace in the init.ora file. In addition, it is tracing in both core apps and Self-Service Web Applications as long as the user has a login session.
Please note that 'FND_INIT_SQL' profile option is only available in
Release 11i not in Release 11 or lower.
1. Log onto core Applications 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".
5. Save changes and exit this form.
6. Switch responsibility to System Administrator.
7. Navigate: Profile > System.
8. On the "Find System Profile Values" form, make sure the checkboxes for "User" and "Profiles with no Values" are checked.
- Beside the "User" checkbox use the LOV to select the user who's activity you need to trace.
- In the "Profile" field, type: %Initialization% Then click the "Find" button.
9. In the "System Profile Values" form, enter the following in the field for the profile "Initialization SQL Statement - Custom" for the specific User:
begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTER SESSION SET
EVENTS='||''''||'10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'||'''');
end;
Alternatively, use the following SQL in order to specify a file name:
begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG', 'ALTER SESSION SET EVENTS=''10046 TRACE NAME
CONTEXT FOREVER, LEVEL 12'' tracefile_identifier=''MY_TRACE'''); end;
10. Save changes and exit the form.
11. Log onto applications as the user for whom you turned on tracing, and promptly recreate the problem, then log off.
12. As the database Unix user (oracle), go into svrmgrl and type the command "show parameter udump". Make a note of the user_dump_dest directory location and get the trace file from there.
14. Make sure to clear the "Initialization SQL Statement - Custom" profile option for the user who's activity you traced.
