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.