OraCommit | Practical Oracle Tips, Fixes & Updates: June 2025

How to Generate FRD Trace in Oracle E-Business Suite (EBS) 12.2.x

When troubleshooting forms-related issues in Oracle E-Business Suite (EBS) R12.2.x, capturing an FRD (Forms Runtime Diagnostics) trace can be invaluable. This post walks you through the step-by-step process to enable, capture, and clean up an FRD trace.

✅ Step A: Update ICX:Forms Launcher Profile

Navigate to: EBS Home > System Administrator > Profile > System
In the "Find System Profile Values" window:
- Set Level to User
- Enter the target username (who will reproduce the issue)
- In the Profile field, enter: ICX: Forms Launcher, then click Find

Update the profile value to:

http://hostname:8000/forms/frmservlet?record=collect

Save the changes and log out of EBS.

✅ Step B: Enable Java Console Logging

On the client machine, open Control Panel > Java > Advanced
Enable the options:
- ✔ Show Console
- ✔ Enable Tracing / Logging
Now, log into EBS using the specified user.
Launch a form and reproduce the issue.
In the Java Console, verify that the string record=collect is present. This confirms that tracing is active.

✅ Step C: Retrieve the FRD Trace File

Log in to the EBS application server (as the OS user, typically via SSH or PuTTY).
Run the following commands:

$ echo $FORMS_TRACE_DIR
$ cd $FORMS_TRACE_DIR
$ ls -lrt *collect*

Identify the generated trace file(s) containing collect in the name.

🔄 Step D: Revert Profile Changes

Once the trace is collected:

Return to Profile > System in EBS.
Search for the same ICX: Forms Launcher profile at the user level.
Revert the URL back to the default:
```
http://hostname:8000/forms/frmservlet
```
Save and exit.

💡 Constructing the Correct `frmservlet` URL

Having trouble forming the correct URL? Here are a few tips:

If you're using a Load Balancer, use the same base URL.
Match the protocol (http/https) and port used in your EBS login URL.
Simply replace the end of the URL with /forms/frmservlet

📌 Example:

If your login URL is:

https://oci-host.domain.com:4443/OA_HTML/AppsLocalLogin.jsp

Then your frmservlet URL becomes:

https://oci-host.domain.com:4443/forms/frmservlet

✅ Conclusion

Capturing FRD traces is a straightforward but powerful method for diagnosing runtime issues in Oracle EBS forms. Always remember to revert changes after trace collection to maintain system performance and user experience.

📢 Oracle Linux 10 is Now Generally Available!

Oracle has officially released Oracle Linux 10 for x86_64 (Intel/AMD) and aarch64 (Arm) architectures. This latest major version delivers significant enhancements in performance, security, cloud integration, and support for modern hardware.

🔧 What’s New in Oracle Linux 10

Next-Generation Kernel
Built on Unbreakable Enterprise Kernel (UEK) 8.1, it includes advanced features from the upstream Linux kernel, offering superior stability and performance.
Broad Architecture Support
Native builds for both x86_64 and aarch64 platforms ensure full compatibility and native performance on today’s enterprise-grade servers and edge devices .
Optimized for Modern Workloads
Enhanced virtualization, real-time computing, container orchestration, and networking capabilities—engineered for cloud, on-premises, and hybrid deployments.

🎯 Why Upgrade?

High Performance & Reliability
Includes the latest Linux kernel innovations, optimized for Oracle Database and high-demand environments.
Seamless Enterprise Support
Backed by Oracle's robust support lifecycle, including kernel updates, live patching, and enterprise-grade maintenance.
Security & Compliance
Features kernel live patching, FIPS compliance, and other security enhancements—ideal for critical and regulated workloads .

🛠️ Next Steps

Download Oracle Linux 10 for your platform today.
Review the Oracle Linux 10 documentation to plan upgrades from Oracle Linux 8/9 or other RHEL-based systems.
Oracle Linux 10 sets a new standard for enterprise Linux—designed for the future of cloud, edge, and hybrid computing.

Diagnosing Stuck Threads in oacore on Oracle EBS 12.2

In Oracle EBS 12.2, system hangs, login failures, or forms not loading are often due to stuck threads in WebLogic’s oacore server. This guide outlines a systematic approach to diagnose and resolve such issues.

🔍 Step-by-Step Diagnostic Workflow

1. Review oacore Logs

Navigate to the oacore server logs (e.g., oacore_server?.log, -diagnostic.log, .out) under $EBS_DOMAIN_HOME/servers/oacore_server?/logs and look for stuck-thread warnings.

2. Detect Stuck Threads and Extract ECID

Search for lines like BEA-000337 indicating a thread has been busy for hundreds of seconds. Extract the ECID-Context, which contains a unique identifier for tracing the issue.

3. Use ECID to Query Active Sessions

Trim the ECID and query Oracle’s AWR or ASH views:

SELECT *
FROM gv$active_session_history
WHERE ecid LIKE '%<trimmed-ECID>%';

This identifies the user session and activity causing the problem.

4. Retrieve SQL Text and Bind Variables

Once you have the SQL_ID, run a query to pull the statement and bind values tied to the stuck session:

SELECT b.name, b.value_string, sq.sql_text, ...
FROM gv$sql_bind_capture b
JOIN gv$sql sq ON ...
WHERE sq.sql_id = '<SQL_ID>';

This helps pinpoint slow or errant SQL

✅ Recommended Actions

Investigate the identified SQL in dev/test environments to optimize or refactor.
Adjust WebLogic thresholds, such as StuckThreadMaxTime, to reduce excessive thread hang behavior.
Open an SR with Oracle, including ECID thread dumps and session details, to assist with deeper root cause analysis.

Recover Accidentally Deleted APEX Components Using Flashback Export

Deleting a page or shared component in Oracle APEX by mistake can be frustrating—but recovery is possible if you act quickly.

Why Flashback?

Instead of relying on full database recovery (which is time-consuming and resource-intensive), APEX offers a Flashback Export option that utilizes Oracle’s Flashback Query technology via the Undo tablespace.
How It Works:
- Flashback Export is available for applications, pages, and components.
- To recover a deleted page, create a dummy page with the same number, then export it using the Flashback Time setting.
- Import the exported file—APEX will prompt to overwrite the dummy with the restored original.
For Shared Components:
If shared components were also deleted, export the entire application using the Flashback option and import it:
- With the same App ID (overwrites the original app).
- Or with a new App ID (lets you manually copy components from the flashback version).
Best Practice:
Schedule regular exports and store them in source control (e.g., Git), ensuring long-term recovery options—even for changes lost days or weeks ago.

Reference:

https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/5921/index-en.html

Configuring Oracle Enterprise Manager for Active Directory Authentication

This post outlines the steps to integrate Oracle Enterprise Manager (OEM) with Microsoft Active Directory (AD) for centralized user authentication. By enabling LDAP-based authentication, administrators can manage user access through AD without creating separate users in OEM.

Key Highlights:

OEM and AD Integration: Leverages Oracle WebLogic Server’s security providers to authenticate users against Active Directory.
Configuration Steps:
- Update the WebLogic security realm to add a new LDAP authenticator for AD.
- Specify connection details such as host, port, and base DN.
- Set control flag and user/group attribute mappings.
Testing: Validate the integration by logging in to OEM using AD credentials.
Fallback Access: Keep the default weblogic or local OEM user credentials active for administrative access in case of integration issues.

Reference:

https://blogs.oracle.com/ateam/post/configure-oracle-enterprise-manager-for-active-directory-authentication

Understanding Hot Patching and Downtime Mode in Oracle E-Business Suite R12.2

Introduction

In Oracle E-Business Suite (EBS) R12.2 environments, the ability to apply patches quickly during critical situations is essential. While earlier versions such as 11i and 12.1.3 supported “hot patching,” the introduction of the dual file system architecture in R12.2 imposes significant limitations.

Hot patching in R12.2 should not be considered unless explicitly permitted in the patch README or Oracle Support documentation.

Challenges and Risks of Hot Patching in R12.2

Applying patches directly to the run file system (hot patching) in R12.2 carries a number of risks:

Potential Runtime Failures: Hot patching may result in transaction failures due to invalid database objects or disrupted PL/SQL package states.
Code and Data Inconsistencies: Temporary inconsistencies between application code and database objects may occur, along with mismatches between patched components and cached data in the application server memory.
Execution Disruptions: Long-held locks on code or data may lead to execution failures and hinder business operations.
Resource Availability Delays: Patches that include downloadable resources (e.g., Forms client JAR files) may require WebLogic Managed Server restarts before becoming active.

Can a Hot Patch Be Aborted?

No. Unlike standard patching, hot patches cannot be aborted using adop phase=abort. If a hot patch fails, recovery options are extremely limited. The only viable remediation is to restore both the EBS database and the middle tiers from a full backup, making the risk of hot patching significantly higher.

A Safer Alternative: Downtime Mode Patching

Given the risks associated with hot patching, Oracle recommends using downtime mode patching during emergencies as a safer alternative. This method involves applying patches directly to the run file system and edition in a controlled downtime window.

Key Guidelines for Downtime Mode Patching:

Use apply_mode=downtime: Execute the ADOP patch application with apply_mode=downtime to ensure changes are made in a stable, non-live environment.
Ensure No Active Patch Cycles: Confirm that no other ADOP patching sessions are running to prevent conflicts.
Register Custom Code Directories: Any directories containing custom code on the application tier must be registered with a custom synchronization driver to support proper file system synchronization during the adop sync phase.

Important Warning

Most Oracle-delivered patches for EBS R12.2 are not tested for downtime or hot patch modes. Therefore, these methods should only be used in emergency situations and not adopted as part of regular maintenance procedures.

Conclusion

Hot patching in Oracle EBS R12.2 should be avoided unless explicitly advised by Oracle. When emergencies require immediate patching, downtime mode patching with proper precautions offers a more stable and supportable alternative. Always ensure full backups are in place before applying any non-standard patching strategies.

How to View and Monitor Database Jobs in Oracle

Oracle provides powerful job scheduling capabilities through the DBMS_SCHEDULER package. This guide outlines how to list existing jobs, check their execution status, and retrieve job actions.

1. List All Scheduled Database Jobs

Use the following query to retrieve details of all scheduled jobs in the database, including their repeat interval, status, and next scheduled run:

SELECT OWNER,
       JOB_NAME,
       REPEAT_INTERVAL,
       ENABLED,
       STATE,
       LAST_START_DATE,
       LAST_RUN_DURATION,
       NEXT_RUN_DATE
FROM DBA_SCHEDULER_JOBS;

2. Check the Status of a Specific Job

To view the execution status of a specific job for a given date, execute the below query. Replace DD-MON-YY with the desired date and &JOB_NAME with the job name.

SELECT LOG_ID,
       JOB_NAME,
       STATUS,
       LOG_DATE
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE TRUNC(LOG_DATE) = 'DD-MON-YY'
  AND JOB_NAME = '&JOB_NAME';

3. View the Job Action of a Specific Job

To determine the actual action or procedure executed by a job, use the following SQL query:

SELECT JOB_ACTION
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'JOB_NAME';

Replace 'JOB_NAME' with the actual name of your scheduled job.

Conclusion:

These queries offer a straightforward way to manage and monitor scheduled database jobs. Regularly reviewing job statuses and actions helps ensure that background operations are running as expected and supports proactive troubleshooting.

How to Use SQL Tuning Advisor for a Specific SQL Statement in Oracle

The SQL Tuning Advisor helps identify and resolve performance issues with SQL queries. Below are the step-by-step instructions to tune a SQL statement using its SQL_ID.

Step 1: Create the Tuning Task

You can create a tuning task either using AWR snapshot information or directly for a running session.

a) Using AWR Snapshots:

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 11716,
                          end_snap    => 11717,
                          sql_id      => 'f4am5zd2tc2ys',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 6000,
                          task_name   => 'dyfp8d71pjym8_tuning_task',
                          description => 'Tuning task for statement f4am5zd2tc2ys in AWR.');
  DBMS_OUTPUT.put_line('Task ID: ' || l_sql_tune_task_id);
END;
/

b) For a Currently Running Session:

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => 'f4am5zd2tc2ys',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 6000,
                          task_name   => 'f4am5zd2tc2ys_tuning_task',
                          description => 'Tuning task for statement f4am5zd2tc2ys');
  DBMS_OUTPUT.put_line('Task ID: ' || l_sql_tune_task_id);
END;
/

Step 2: Execute the Tuning Task

Check the task status and execute it:

SELECT task_name, status
FROM dba_advisor_log
WHERE task_name LIKE 'f4am5zd2tc2ys_tuning_task%';

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'f4am5zd2tc2ys_tuning_task');

Step 3: Generate the Tuning Report

To review the tuning recommendations, run:

SET lines 200 pages 1000
SET LONG 999999999
SET longchunksize 200

SELECT DBMS_SQLTUNE.report_tuning_task('f4am5zd2tc2ys_tuning_task')
FROM dual;

Step 4: (Optional) Drop the Tuning Task

After reviewing the report, you may remove the tuning task to clean up:

EXEC DBMS_SQLTUNE.drop_tuning_task(task_name => 'f4am5zd2tc2ys_tuning_task');

Conclusion:

By following these steps, you can effectively leverage the SQL Tuning Advisor to analyze and optimize query performance using SQL_ID. Always review the advisor's suggestions carefully before applying them in production.

Accelerating Issue Resolution in Oracle E-Business Suite with Built-in Diagnostics

Effective troubleshooting of critical issues in Oracle E-Business Suite (EBS) is essential for maintaining business continuity. Leveraging diagnostic tools provided by Oracle Support can significantly reduce downtime and expedite resolution. We recently encountered a situation that underscores the importance of using these tools to address system-wide issues swiftly and efficiently.

Issue Encountered

Last week, we faced a widespread issue within Oracle E-Business Suite R12.2 where users were unable to view the output of concurrent programs. When selecting the "View Output" button, the system consistently displayed a blank page instead of the expected report output. The issue affected all users across the environment. Despite reviewing the available logs in detail, no actionable information could be gathered, prompting us to raise a Service Request (SR) with Oracle Support.

Diagnosis and Resolution via Oracle SR

During the diagnostic process, we discovered a built-in tool within EBS R12.2 named "FS Diagnostic Test Program." This utility proved invaluable in identifying the root cause of the issue.

The FS Diagnostic Test Program is designed to simulate file transfer operations typically handled by concurrent processing components such as FNDFS and FNDWRR.exe. It includes logic to generate diagnostic logs, facilitating easier debugging of issues related to output and log file delivery.

The program can be executed in various modes, controlled by the Mode parameter:

FULL – Executes all available tests (default)
FILE – Tests file copy operations
REQLOG – Verifies access to concurrent request log files
REQOUT – Verifies access to concurrent request output files
MGR – Tests viewing concurrent manager log files
RFILE – Tests remote file copying

We ran the diagnostic test using the appropriate modes and uploaded the results to the open Oracle SR. Based on the diagnostic output, Oracle Support was able to quickly analyze the issue and provide an effective solution.

Key Takeaway

This experience reinforced the value of leveraging built-in diagnostics to accelerate support resolution. Oracle Support can offer faster, more targeted assistance when provided with relevant diagnostic outputs upfront. We highly recommend incorporating tools like the FS Diagnostic Test Program into your standard troubleshooting toolkit when working with Oracle E-Business Suite R12.2.

OraCommit | Practical Oracle Tips, Fixes & Updates

How to Generate FRD Trace in Oracle E-Business Suite (EBS) 12.2.x

✅ Step A: Update ICX:Forms Launcher Profile

✅ Step B: Enable Java Console Logging

✅ Step C: Retrieve the FRD Trace File

🔄 Step D: Revert Profile Changes

💡 Constructing the Correct `frmservlet` URL

✅ Conclusion

📢 Oracle Linux 10 is Now Generally Available!

🔧 What’s New in Oracle Linux 10

🎯 Why Upgrade?

🛠️ Next Steps

Diagnosing Stuck Threads in oacore on Oracle EBS 12.2

🔍 Step-by-Step Diagnostic Workflow

✅ Recommended Actions

Recover Accidentally Deleted APEX Components Using Flashback Export

Configuring Oracle Enterprise Manager for Active Directory Authentication

Key Highlights:

Understanding Hot Patching and Downtime Mode in Oracle E-Business Suite R12.2

Introduction

Challenges and Risks of Hot Patching in R12.2

Can a Hot Patch Be Aborted?

A Safer Alternative: Downtime Mode Patching

Important Warning

How to View and Monitor Database Jobs in Oracle

1. List All Scheduled Database Jobs

2. Check the Status of a Specific Job

3. View the Job Action of a Specific Job

How to Use SQL Tuning Advisor for a Specific SQL Statement in Oracle

Step 1: Create the Tuning Task

Step 2: Execute the Tuning Task

Step 3: Generate the Tuning Report

Step 4: (Optional) Drop the Tuning Task

Accelerating Issue Resolution in Oracle E-Business Suite with Built-in Diagnostics

Issue Encountered

Diagnosis and Resolution via Oracle SR

Key Takeaway

Labels

Blog Archive

✅ Step A: Update ICX:Forms Launcher Profile

✅ Step B: Enable Java Console Logging

✅ Step C: Retrieve the FRD Trace File

🔄 Step D: Revert Profile Changes

💡 Constructing the Correct frmservlet URL

✅ Conclusion

🔧 What’s New in Oracle Linux 10

🎯 Why Upgrade?

🛠️ Next Steps

🔍 Step-by-Step Diagnostic Workflow

✅ Recommended Actions

Key Highlights:

Introduction

Challenges and Risks of Hot Patching in R12.2

Can a Hot Patch Be Aborted?

A Safer Alternative: Downtime Mode Patching

Important Warning

1. List All Scheduled Database Jobs

2. Check the Status of a Specific Job

3. View the Job Action of a Specific Job

Step 1: Create the Tuning Task

Step 2: Execute the Tuning Task

Step 3: Generate the Tuning Report

Step 4: (Optional) Drop the Tuning Task

Issue Encountered

Diagnosis and Resolution via Oracle SR

Key Takeaway

💡 Constructing the Correct `frmservlet` URL