Tracing Techniques within the Oracle Applications 11i/R12

A trace file is a log of SQL run in a particular session or sesesions  focused on selects, inserts, updates, and deletes.  A trace file can be used in many circumstances including reviewing performance,  finding tables and views referenced, or finding the root of an error. 

How does one enable trace in the Oracle Application screens / forms?

One can enable trace through the forms by using the Help menu, choosing the daignostics menu,trace and then selecting the appropriate trace for your needs .
Most commonly if debugging an error, you should at least provide trace with binds When debugging a performance issue, you may consider using trace with binds and waits.

For example, the following is the navigation to enable trace in a form:

Goto the Oracle Applications -->Login -->Open the form where the error occurs but do not yet cause the error.
Enable SQL*Trace by choosing Help > Diagnostics > Trace > Trace with binds

A message appears indicating that the trace will be recorded
Note the file name and location of the file
Now reproduce the error.
Once the error occurs, disable trace as soon as possible.

Disable SQL*Trace by choosing Help > Diagnostics > Trace > Trace off
The same file name and location will be noted in case you need it again.
Retrieve the trace file.

How does one enable trace for a concurrent program?

A simple way to enable trace in a concurrent program is to review the concurrent program definition and select trace enabled. This will enable trace without binds for all users that run the program. 

For example, the following steps could be used. 
Goto Sysadmin > Concurrent > Program > Define 
Query the concurrent program 
Check the trace box to enable trace 

How does one find a trace file for a concurrent program?

In 11G:

Set Serveroutput ON

DB_Version VARCHAR2(2);
Trace_location VARCHAR2(240);
SELECT SUBSTR(vi.version, 1, INSTR(vi.version, '.')-1) INTO DB_Version FROM v$instance vi;

IF TO_NUMBER(DB_Version) >= 11 THEN
/* Following Line included as Execute Immediate as V$DIAG_INFO does not exist in DB Versions prior to 11g */
SELECT value INTO Trace_location FROM v$parameter WHERE name = 'user_dump_dest';
dbms_output.put_line('Trace File should be located in the directory :- '|| trace_location);

 Before 11G:


accept request prompt 'Please enter the concurrent request id for the appropriate concurrent program:' 

column traceid format a8 
column tracename format a80 
column user_concurrent_program_name format a40 
column execname format a15 
column enable_trace format a12 
set lines 80 
set pages 22 
set head off 

SELECT 'Request id: '||request_id , 
'Trace id: '||oracle_Process_id, 
'Trace Flag: '||req.enable_trace, 
'Trace Name: 
'Prog. Name: '||prog.user_concurrent_program_name, 
'File Name: '||execname.execution_file_name|| execname.subroutine_name , 
'Status : '||decode(phase_code,'R','Running') 
'SID Serial: '||ses.sid||','|| ses.serial#, 
'Module : '||ses.module 
from fnd_concurrent_requests req, v$session ses, v$process proc, 
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog, 
fnd_executables execname 
where req.request_id = &request 
and req.oracle_process_id=proc.spid(+) 
and proc.addr = ses.paddr(+) 
and req.concurrent_program_id = prog.concurrent_program_id 
and req.program_application_id = prog.application_id 
and prog.application_id = execname.application_id 
and prog.executable_id=execname.executable_id;

Checking Snapshot info

Excute this command as an apps user

Find Blocking Session details

How to find Oracle Database Blocking Session Details
In first step, find SID from v$session.

SQL> select process,sid, blocking_session from v$session where blocking_session 
 is not null;
 ———— ———- —————-
 1234 365 366
 1234 366 365

In second step find the serial number for the Blocking Session to kill using SID
 SQL> select SERIAL# from v$session where SID=365;

In third step, kill the blocking session using SID and serial number

SQL> alter system kill session ’365,130′;
 System altered.

REP-3000 Oracle Toolkit Error

REP-3000: Internal error starting Oracle Toolkit
1.  Ensure the correct DISPLAY environment variable is set on the E-Business server(s)
In the main environment file, as pointed to by $APPLFENV, verify / set the DISPLAY environment variable to an active and available X display server.
DISPLAY=<hostname>:<display_number>.0 ; export DISPLAY
i.e. ; export DISPLAY
Note 2: Some notes, such as 200474.1, advocates placing the DISPLAY environment variable in startup scripts:,, Caution should be observed; these scripts override the DISPLAY value in the main environment file and may add a level of confusion if different values are present on each server or script.
On Autoconfig ready instances, make any environment variable changes via the Autoconfig Context Editor or Oracle Applications Manager (OAM) and re-run Autoconfig on all nodes in order to ensure that all changes are properly synchronized across all tiers and to prevent the lost of the current environment changes next time Autoconfig runs.
2. Invoke or re-invoke the xhost command as root.
On the server where the X display server resides, as root, execute xhost +<hostname1>+<hostname2>… Where <hostname> are the E-Business server names requiring X display server access, such as the Concurrent Processing Server, Oracle Reports Server, etc.
Typically, this action needs to be done any time the host with the X display server is bounced.
3. As the “root” user, bounce (restart) the X display server and/or any required window manager (i.e. mwm, olwm, fvwm, twm, etc.) associated with the X display server or window system.
Make sure to perform the xhost + command again.
Note 3: Please reference the X display server’s guide or contact the vendor for assistance on stopping or starting the X sever or window manager in used.
4. Validate that the Concurrent Processing Server sees the correct DISPLAY value.
Run the Sysadmin report “Prints environment variable values” with the “DISPLAY” parameter and check that the correct DISPLAY value is present. If this value is incorrect, then the concurrent processing server is not aware of the proper DISPLAY setting. In short, this environment variable needs to be defined before starting the concurrent processing server.
The Sysadmin report “Generate concurrent processing environment information” also lists all environment variables visible to the concurrent processing server.
5. Verify that the DISPLAY variable is usable by running a concurrent report in Postscript format.
Run the Sysadmin report “CP Postscript Report Regression Test” with the parameter BASIC. If the reports completes successfully, the concurrent processing server is aware of the DISPLAY value and the current variable value is usable.
Note 4: If Pasta is being utilized and the IX_RENDERING variable is set without a corresponding displayfontpath entry, a REP-3000 can occur–see Note 361639.1 “Cannot Open Display Font File: … REP-3000: Internal Error Starting Oracle Toolkit”
6. If bitmap (Postscript, PDF, etc) reports fail from within Oracle Applications, does a report in Postscript format run successfully from the OS command line?
a) Login as the applmgr on the host where the concurrent processing server resides and source the main environment file.
b) Set the DISPLAY environment variable, if not already set by the main environment file.
DISPLAY=<hostname>:0.0 ; export DISPLAY
c) Run the “Active Users” report in Postscript format with the ar60runb executable and the following options. Provide the apps password an populate the parameters with the full path of $FND_TOP and $APPLTMP.
— 12.0 Example — userid=apps/apps report=/<$FND_TOP>/reports/US/FNDSCURS.rdf batch=yes destype=file mode=bitmap desname=/<$APPLTMP>/ desformat=/<10.1.2.$ORACLE_HOME>/reports/printers/psl132.prt errfile=/<$APPLTMP>/ActUsr.log
Note: The full path to is contained within the $APPLORB environment variable
— 11.5 Example —
ar60runb userid=apps/apps report=/<$FND_TOP>/reports/US/FNDSCURS.rdf batch=yes destype=file mode=bitmap desname=/<$APPLTMP>/ desformat=/<8.0.6.$ORACLE_HOME>/reports60/admin/printer/psl132.prt errfile=/<$APPLTMP>/ActUsr.log
7. If bitmap reports still fail with a REP-3000 from Oracle Applications or from the OS command line, the most likely cause of the error is that the X display server and/or window manager is not accessible, usable, or started.
a) Review the following documents for further insight and additional recommended checks:
Note 200474.1 “Comprehensive REP-3000 Troubleshooting and Overview Guide”, particularly the startup script examples at section “VI-6. GENERAL ISSUES:”
Note 207532.1 “Troubleshooting Tips for REP-3000 Error when Running PDF/POSTSCRIPT/HTML Reports”
Note 153960.1 “FAQ: X Server testing and troubleshooting”
Note 181244.1 “Configuring VNC Or XVFB As The X Server For Applications 11i(“Configuring an X Display Server for Applications on Unix Platforms”)”
b) Update any on-going Oracle service request with the results of the previous steps.
c) Use the command xdpyinfo -display <hostname>:<display_number>.0 to inspect and verify the display settings. Please consult the vendor’s documentation for detailed instructions on modifying any settings.
d) Contact the vendor of the third party X display server or VNC product for assistance on configuring their product for use.

Oracle E-Business Suite 12.2 Architecture

There are lots of updates coming in this release, but from a technology stack perspective, EBS 12.2 will be notable for two things:
  1. Replacing Oracle Containers for Java (OC4J) 10g with WebLogic Server 11g
  2. Online Patching support via 11gR2 Edition-Based Redefinition
Oracle E-Business Suite 12.2 Technology Highlights:
1.  Latest middleware: Fusion Middleware 11g.
2.  WebLogic Server to replace Fusion Middleware 10g Oracle Containers for Java (OC4J)
Latest database:  Oracle 11gR2 11.2 Database
  •  Online patching via Edition-Based Redefinition
  • Improved performance via database result caching.
  • Functionality uses native 11gR2
  • features that allow online application
  • upgrade with uninterrupted
  • availability of the application
  • • Patches can be applied while the
  • production system is fully operational
  • • Only a short downtime is required to
  • complete patching process
  • The amount of downtime required by
  • Any patch will be more predictable
Oracle E-Business Suite 12.2 Technology Highlights 
Enhanced user interface:
  • Home page improvements
  • New slide-out navigator
  • Personalization improvements
  • AutoVue print services for attachments
Performance enhancements:
  • Workflow RAC Affinity
  • Result set caching for profiles