How to Enable Oracle EBS Trace and FND Debug for Concurrent Programs

 

Introduction

When a Concurrent Program in Oracle E-Business Suite (EBS) fails, performs slowly, or produces unexpected results, Oracle Support often requests a database trace file and FND debug logs for analysis.

This guide explains how to enable Oracle Trace (Level 4 or Level 12) and FND Debug logging, reproduce the issue, and collect the required diagnostic files.

---

When Should You Enable Trace and Debug?

Use tracing and debugging when:

• A concurrent request ends in Error.

• Performance is slow.

• Data is not being processed correctly.

• Oracle Support requests diagnostic files.

• You need to identify SQL statements causing issues.

---

Step 1: Enable Database Trace

Navigation

Responsibility: System Administrator

Path: Profile → System

Search for the following profile:

Initialization SQL Statement - Custom

Select the user who will submit the concurrent request.

---

Enable Level 4 Trace (SQL Trace with Binds)

Level 4 captures:

• SQL statements

• Bind variables

Enter the following value:

BEGIN FND_CTL.FND_SESS_CTL ('','','TRUE','TRUE','LOG',
'ALTER SESSION SET max_dump_file_size=unlimited TRACEFILE_IDENTIFIER =''TEST_TRACE''
EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 4''');
END;

Replace:

TEST_TRACE

with a meaningful trace name.

Example:

BEGIN FND_CTL.FND_SESS_CTL ('','','TRUE','TRUE','LOG',
'ALTER SESSION SET max_dump_file_size=unlimited TRACEFILE_IDENTIFIER =''sessiondetails''
EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 4''');
END;

---

Enable Level 12 Trace (Recommended)

Level 12 captures:

• SQL statements

• Bind variables

• Wait events

This is the most commonly requested trace level by Oracle Support.

Example:

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

Click Save.

---

Step 2: Enable FND Debug Logging

Navigation

Responsibility: System Administrator

Path: Profile → System

Search for:

FND: Debug%

Set the following profile values at the USER level.

Profile Option	Value
FND: Debug Log Enabled	Yes
FND: Debug Log Filename	NULL
FND: Debug Log Level	STATEMENT
FND: Debug Log Module	%

Save the changes.

---

Step 3: Reproduce the Issue

Submit the concurrent request and reproduce the problem.

Best Practice

Keep the test case as simple as possible:

• Use minimum data.

• Execute only the required steps.

• Avoid unrelated activities.

This makes trace analysis easier and reduces file size.

---

Step 4: Disable Trace and Debug

After reproducing the issue:

• Remove the value from Initialization SQL Statement - Custom

• Reset FND Debug profiles to their original values

This prevents unnecessary trace generation and system overhead.

---

Step 5: Retrieve FND Debug Log

Use the Concurrent Request ID generated during testing.

Run the following query:

SELECT *
FROM FND_LOG_MESSAGES LOG,
     FND_LOG_TRANSACTION_CONTEXT CON
WHERE CON.TRANSACTION_ID = &REQUEST_ID
AND CON.TRANSACTION_TYPE = 'REQUEST'
AND CON.TRANSACTION_CONTEXT_ID = LOG.TRANSACTION_CONTEXT_ID
ORDER BY LOG.LOG_SEQUENCE;

Export the results to Excel for review.

The output contains detailed application-level debugging information.

---

Step 6: Locate the Database Trace File

Database Versions Prior to 11g

SELECT name, value
FROM v$parameter
WHERE name='user_dump_dest';

---

Database Version 11g and Above

SELECT value
FROM v$diag_info
WHERE name='Diag Trace';

The query returns the trace directory location.

---

Find the Trace File

Navigate to the trace directory and search using the trace identifier.

Example:

ls *lockbox*.trc

Sample Output:

visar07_ora_22989_lockbox.trc
visar07_ora_23234_lockbox.trc

---

Step 7: Generate TKPROF Output

Raw trace files are difficult to read.

Use TKPROF to create a formatted report.

Basic TKPROF

tkprof tracefile.trc output.out explain=apps/apps sys=no

---

TKPROF Sorted by Execution Time

Useful for performance investigations.

tkprof tracefile.trc output.out \
explain=apps/apps \
sys=no \
sort=exeela,prsela,fchela

Example:

tkprof sys_1499133.trc tk1499133.out \
explain=apps/apps \
sys=no \
sort=exeela,prsela,fchela