Thursday, September 3, 2026

patch applied

SELECT bug_number, language, creation_date

FROM apps.ad_bugs

WHERE bug_number IN ('20007138','20518047','19863340','19195514','19907901','19900999')

ORDER BY bug_number, language, creation_date;

Sunday, August 30, 2026

Blogger post: Migrating an Execution Plan Between Oracle Databases Using SQL Tuning Sets and SQL Plan Management

Every DBA eventually meets this scenario: a query runs beautifully in UAT but picks a terrible execution plan in Production. The data is comparable, the code is identical, yet the optimizer disagrees with itself across environments. Rather than hinting the SQL (application change), locking statistics (broad side effects), or gambling on a SQL Profile, the cleanest supported fix is often to transport the known-good plan itself using a SQL Tuning Set (STS) and enforce it with SQL Plan Management (SPM).

This post is a production-hardened, end-to-end runbook. Replace the placeholders <SQL_ID>, <GOOD_PLAN_HASH>, and the schema/paths with your values. Tested approach applies to Oracle 11.2 through 19c and works unchanged in Oracle E-Business Suite environments.

Workflow at a Glance

Capture the optimal plan into a SQL Tuning Set on the source database.
Pack the STS into a staging table.
Export and transfer the staging table with Data Pump.
Import and unpack the STS on the target database.
Load the plan as an accepted SQL Plan Baseline.
Purge the bad cursor from the shared pool.
Verify the baseline is actually being used — the step most runbooks forget.

Prerequisites & Pre-Checks

Privileges: ADMINISTER SQL TUNING SET (or DBA) on both databases; Data Pump export/import privileges.
Working schema: Use a regular administrative schema (shown here as DBADMIN) for the staging table. Oracle explicitly recommends not staging in SYS, and keeping SYSTEM clean is good hygiene.
Baselines must be enabled on target. If this parameter is FALSE, everything below succeeds silently and changes nothing:

SHOW PARAMETER optimizer_use_sql_plan_baselines
-- must be TRUE

Plan reproducibility: the target must have the same indexes, comparable statistics, and a compatible optimizer environment. A baseline is a request, not a command — if the optimizer cannot reproduce the plan (missing index, dropped partition), it silently ignores the baseline. This is why the verification phase at the end is mandatory.

Phase 1 — Source Database: Capture and Export

Step 1: Identify the Good Plan

SELECT sql_id, plan_hash_value, executions,
       ROUND(elapsed_time/DECODE(executions,0,1,executions)/1e6,3) avg_elapsed_sec
FROM   v$sql
WHERE  sql_id = '<SQL_ID>';

Note the PLAN_HASH_VALUE of the efficient plan — you will filter on it at every subsequent step so that only the good plan travels, never the bad one.

Step 2: Create and Populate the SQL Tuning Set

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET (
    sqlset_name => 'MIGRATE_PLAN_STS',
    description => 'Transfer optimal plan for <SQL_ID> to production'
  );
END;
/

DECLARE
  c_cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN c_cur FOR
    SELECT VALUE(p)
    FROM   TABLE(
             DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
               'sql_id = ''<SQL_ID>'' AND plan_hash_value = <GOOD_PLAN_HASH>'
             )
           ) p;

  DBMS_SQLTUNE.LOAD_SQLSET(
    sqlset_name     => 'MIGRATE_PLAN_STS',
    populate_cursor => c_cur
  );
END;
/

Verify the STS contains exactly what you expect — one statement, one plan:

SELECT sql_id, parsing_schema_name, plan_hash_value, elapsed_time, buffer_gets
FROM   TABLE(DBMS_SQLTUNE.SELECT_SQLSET('MIGRATE_PLAN_STS'));

Step 3: Pack the STS into a Staging Table

Pass the schema explicitly to CREATE_STGTAB_SQLSET and keep it consistent with staging_schema_owner in the pack call — a mismatch here is the classic cause of ORA-19381: staging table does not exist.

BEGIN
  DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
    table_name  => 'STS_STAGING_TAB',
    schema_name => 'DBADMIN'
  );
END;
/

BEGIN
  DBMS_SQLTUNE.PACK_STGTAB_SQLSET (
    sqlset_name          => 'MIGRATE_PLAN_STS',
    sqlset_owner         => USER,
    staging_table_name   => 'STS_STAGING_TAB',
    staging_schema_owner => 'DBADMIN'
  );
END;
/

Step 4: Export and Transfer

Do not create or replace DATA_PUMP_DIR — it already exists in every database and repointing the default is a bad habit. Use a purpose-built directory object:

CREATE DIRECTORY STS_MIG_DIR AS '/u01/exports/sts_migration';

-- OS command:
expdp dbadmin DIRECTORY=STS_MIG_DIR DUMPFILE=migrate_plan_sts.dmp \
      LOGFILE=migrate_plan_sts_exp.log TABLES=DBADMIN.STS_STAGING_TAB

scp /u01/exports/sts_migration/migrate_plan_sts.dmp \
    oracle@target_host:/u01/imports/sts_migration/

Phase 2 — Target Database: Import and Enforce

Step 5: Import the Staging Table

CREATE DIRECTORY STS_MIG_DIR AS '/u01/imports/sts_migration';

-- OS command (add REMAP_SCHEMA if the schema differs on target):
impdp dbadmin DIRECTORY=STS_MIG_DIR DUMPFILE=migrate_plan_sts.dmp \
      LOGFILE=migrate_plan_sts_imp.log TABLES=DBADMIN.STS_STAGING_TAB

Step 6: Unpack the SQL Tuning Set

Name the STS explicitly rather than using the '%' wildcard — you want deliberate, auditable actions in production, and replace => TRUE combined with a wildcard can silently overwrite unrelated tuning sets.

BEGIN
  DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
    sqlset_name          => 'MIGRATE_PLAN_STS',
    sqlset_owner         => '%',
    replace              => TRUE,
    staging_table_name   => 'STS_STAGING_TAB',
    staging_schema_owner => 'DBADMIN'
  );
END;
/

Step 7: Load the Plan as an Accepted Baseline

VARIABLE v_plan_cnt NUMBER;

BEGIN
  :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name  => 'MIGRATE_PLAN_STS',
    sqlset_owner => 'DBADMIN',
    basic_filter => 'sql_id = ''<SQL_ID>'' AND plan_hash_value = <GOOD_PLAN_HASH>'
  );
END;
/

PRINT v_plan_cnt
-- MUST be >= 1. Zero means the filter matched nothing
-- (typo in sql_id / plan_hash) — stop and investigate.

Confirm the baseline exists, is enabled, and is accepted:

SELECT sql_handle, plan_name, enabled, accepted, fixed, origin
FROM   dba_sql_plan_baselines
WHERE  created > SYSDATE - 1/24
ORDER  BY created DESC;

Optional — pin the plan: a loaded baseline is ACCEPTED but not FIXED. If you want to prevent future auto-evolved plans from competing with it, fix it:

DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
         sql_handle      => '<SQL_HANDLE>',
         plan_name       => '<PLAN_NAME>',
         attribute_name  => 'fixed',
         attribute_value => 'YES');
END;
/

Step 8: Purge the Bad Cursor from the Shared Pool

An existing cursor is not invalidated by a new baseline, so the bad plan keeps executing until it ages out. Purge it. On RAC, the purge is instance-local — generate and run the command on every instance:

SELECT inst_id,
       'EXEC DBMS_SHARED_POOL.PURGE ('''||address||','||hash_value||''', ''C'');' purge_cmd
FROM   gv$sqlarea
WHERE  sql_id = '<SQL_ID>';

-- Run the generated command connected to each instance listed:
EXEC DBMS_SHARED_POOL.PURGE ('<ADDRESS>,<HASH_VALUE>', 'C');

Step 9: Verify the Baseline Is Actually Used

This is the step that separates a runbook from a hope. Have the application (or a test harness with the same binds) execute the statement, then check:

SELECT sql_id, child_number, plan_hash_value, sql_plan_baseline
FROM   v$sql
WHERE  sql_id = '<SQL_ID>';
-- SQL_PLAN_BASELINE must be non-null and
-- PLAN_HASH_VALUE must equal <GOOD_PLAN_HASH>

-- Full plan detail:
SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle => '<SQL_HANDLE>',
                                       format     => 'BASIC NOTE'));

If SQL_PLAN_BASELINE stays null, the optimizer could not reproduce the plan on the target — go back and compare indexes, statistics, and optimizer parameters between the environments before anything else.

Practical Notes

Shortcut when the good plan already exists in the target's cursor cache (e.g., it ran well last week before a stats change): skip the entire STS transfer and load directly:
```
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE('<SQL_ID>', <GOOD_PLAN_HASH>);
  DBMS_OUTPUT.PUT_LINE('Plans loaded: '||n);
END;
/
```
Single-statement alternative: for a one-off transfer of a single SQL_ID, Oracle's coe_xfr_sql_profile.sql (bundled with SQLT, MOS Doc ID 1955195.1) generates a self-contained script on the source that you simply execute on the target — no Data Pump, no staging table. Use STS/SPM when you want a genuine baseline with evolution history, or when moving multiple statements.
Bind-sensitive SQL: if the statement is bind-aware (check V$SQL.IS_BIND_AWARE), pinning a single plan can penalize other bind value sets. Confirm the good plan is good across representative binds before fixing it.
Cleanup: once verified, drop the staging tables on both sides and, if no longer needed, the STS (DBMS_SQLTUNE.DROP_SQLSET) to keep environments tidy.

Summary

STS + SPM is the supported, code-change-free way to move a proven execution plan between databases: capture with a plan-hash filter, pack, transport, unpack, load as an accepted baseline, purge the stale cursor on every instance, and — always — verify that V$SQL.SQL_PLAN_BASELINE lights up. The transfer mechanics are easy; the discipline is in the pre-checks (baselines enabled, matching objects) and the post-check (plan reproduction). Skip those and you have a baseline in the dictionary and the same bad plan in production.

Wednesday, August 26, 2026

Oracle EBS: How to Find Application Tables with Supplemental Logging Enabled

When working with replication tools (like Fivetran HVR, Oracle GoldenGate, or Qlik Replicate) in an Oracle E-Business Suite (EBS) environment, Change Data Capture (CDC) requires Supplemental Logging to be enabled. This ensures that the Redo Log stream contains the necessary pre- and post-image column values for change tracking.

As DBAs, we often need to audit which EBS application tables are currently configured with supplemental log groups and inspect the exact columns being logged. Here are the SQL queries to identify, detail, and summarize supplemental logging across all EBS schemas.

1. List All EBS Tables with Supplemental Log Groups

This query identifies all EBS application schema tables that have supplemental logging enabled (both system-generated key log groups and user-defined CDC log groups):

SELECT lg.owner,
       lg.table_name,
       lg.log_group_name,
       lg.log_group_type,
       lg.always,
       lg.generated
FROM dba_log_groups lg
WHERE lg.owner IN (
    SELECT oracle_username 
    FROM fnd_oracle_userid
    UNION
    SELECT 'APPS' FROM dual
)
ORDER BY lg.owner, lg.table_name;

2. Detailed Column-Level Supplemental Logging Breakdown

To inspect the exact columns included within each supplemental log group (useful for identifying non-key logged columns added by replication software):

SELECT lg.owner,
       lg.table_name,
       lg.log_group_name,
       lg.log_group_type,
       lc.column_name,
       lc.position,
       lc.logging_property
FROM dba_log_groups lg
JOIN dba_log_group_columns lc
  ON  lg.owner = lc.owner
  AND lg.table_name = lc.table_name
  AND lg.log_group_name = lc.log_group_name
WHERE lg.owner IN (
    SELECT oracle_username 
    FROM fnd_oracle_userid
    UNION
    SELECT 'APPS' FROM dual
)
ORDER BY lg.owner, lg.table_name, lg.log_group_name, lc.position;

3. Aggregated Summary by EBS Schema / Module

To quickly gauge replication footprint across different functional modules (e.g., AP, AR, GL, INV, PO), run this aggregation query:

SELECT lg.owner AS ebs_schema,
       COUNT(DISTINCT lg.table_name) AS table_count,
       COUNT(lg.log_group_name) AS log_group_count
FROM dba_log_groups lg
WHERE lg.owner IN (
    SELECT oracle_username 
    FROM fnd_oracle_userid
    UNION
    SELECT 'APPS' FROM dual
)
GROUP BY lg.owner
ORDER BY table_count DESC;

Quick Reference: Understanding `LOG_GROUP_TYPE` Values

PRIMARY KEY LOGGING: Logs all primary key columns when an update occurs.
UNIQUE KEY LOGGING: Logs unique key columns when an update occurs.
FOREIGN KEY LOGGING: Logs foreign key columns when an update occurs.
USER LOG GROUP: Custom column-list log group explicitly created for replication tools (e.g., HVR or GoldenGate).
ALL COLUMN LOGGING: Logs all table columns (often set during initial load or specific conflict resolution rules).

Tuesday, August 25, 2026

Oracle EBS Concurrent Request Stuck in Pending Standby – Conflict Resolution Manager Troubleshooting

In Oracle E-Business Suite 12.2, a concurrent request may sometimes remain in Pending / Standby status even when the Standard Concurrent Manager has sufficient available processes.

This does not necessarily indicate a problem with the Standard Manager. A request in Pending / Standby is normally being evaluated by the Conflict Resolution Manager (CRM) because the concurrent program has incompatibility or serialization rules associated with it.

This post provides a generic troubleshooting approach for identifying whether the request is waiting normally because of an incompatible program or whether there is an issue with the Conflict Resolution Manager itself.

1. Typical Symptom

A concurrent request may appear as follows:

Request ID : 123456789
Phase      : Pending
Status     : Standby
Manager    : Conflict Resolution Manager

The Request Diagnostics window may display a message similar to:

This request is waiting to be processed by the Conflict Resolution Manager.

This request cannot yet begin execution because other requests may
conflict with it.

The Conflict Resolution Manager will determine when this request
may begin execution.

No action required. This is a normal condition.

The important point is that the request has not yet been released to a normal Concurrent Manager.

2. What Does Pending / Standby Mean?

Internally the concurrent request normally has:

PHASE_CODE  = P
STATUS_CODE = Q

Which represents:

P = Pending
Q = Standby

A request in this state is generally considered a constrained concurrent request.

The processing flow is approximately:

Concurrent Request Submitted
          |
          v
Is Program Constrained?
          |
     +----+----+
     |         |
    No        Yes
     |         |
     v         v
Concurrent   Conflict Resolution
Manager      Manager
              |
              v
       Check Incompatibilities
              |
       +------+------+
       |             |
   Conflict       No Conflict
   Exists
       |             |
       v             v
   Standby       Release Request
                     |
                     v
              Concurrent Manager
                     |
                     v
                  Running

3. Check the Concurrent Request

Always start by checking the database status of the affected request. Replace the example Request ID with the actual Request ID.

set lines 220
set pages 100

column request_date format a20
column requested_start_date format a20
column actual_start_date format a20

SELECT request_id,
       program_application_id,
       concurrent_program_id,
       phase_code,
       status_code,
       hold_flag,
       TO_CHAR(request_date,
               'DD-MON-YYYY HH24:MI:SS') request_date,
       TO_CHAR(requested_start_date,
               'DD-MON-YYYY HH24:MI:SS') requested_start_date,
       TO_CHAR(actual_start_date,
               'DD-MON-YYYY HH24:MI:SS') actual_start_date,
       controlling_manager,
       parent_request_id
FROM   fnd_concurrent_requests
WHERE  request_id = 123456789;

Typical output:

REQUEST_ID   PHASE_CODE   STATUS_CODE
----------   ----------   -----------
123456789    P            Q

This confirms:

Pending / Standby

4. Identify the Concurrent Program

Determine which concurrent program is associated with the request and whether it has special queue or Run Alone characteristics.

set lines 220
set pages 100

column application_short_name format a20
column concurrent_program_name format a35
column user_concurrent_program_name format a60

SELECT fa.application_short_name,
       cp.concurrent_program_name,
       cpt.user_concurrent_program_name,
       cp.enabled_flag,
       cp.run_alone_flag,
       cp.queue_method_code
FROM   fnd_concurrent_programs cp,
       fnd_concurrent_programs_tl cpt,
       fnd_application fa,
       fnd_concurrent_requests r
WHERE  r.request_id = 123456789
AND    cp.application_id = r.program_application_id
AND    cp.concurrent_program_id = r.concurrent_program_id
AND    cpt.application_id = cp.application_id
AND    cpt.concurrent_program_id = cp.concurrent_program_id
AND    cpt.language = USERENV('LANG')
AND    fa.application_id = cp.application_id;

Pay particular attention to:

RUN_ALONE_FLAG
QUEUE_METHOD_CODE

If the program is constrained, the Conflict Resolution Manager must evaluate its incompatibility rules before releasing it.

5. Check Conflict Resolution Manager Status

The next important step is to verify that the Conflict Resolution Manager is actually running.

set lines 200
set pages 100

column concurrent_queue_name format a20
column user_concurrent_queue_name format a40
column target_node format a30

SELECT concurrent_queue_name,
       user_concurrent_queue_name,
       running_processes,
       max_processes,
       target_node,
       control_code,
       enabled_flag
FROM   fnd_concurrent_queues_vl
WHERE  concurrent_queue_name = 'FNDCRM';

A healthy CRM would normally show something similar to:

CONCURRENT_QUEUE_NAME : FNDCRM
RUNNING_PROCESSES     : 1
MAX_PROCESSES         : 1
TARGET_NODE           : APPNODE01

If RUNNING_PROCESSES = 0, the Conflict Resolution Manager itself should be investigated.

6. Check How Many Requests Are in Pending / Standby

Determine whether the problem affects only one request or many concurrent requests.

SELECT COUNT(*) standby_requests
FROM   fnd_concurrent_requests
WHERE  phase_code = 'P'
AND    status_code = 'Q';

If only a small number of requests are in Standby, they may legitimately be waiting for incompatible programs.

If hundreds or thousands of requests are accumulating in Standby, investigate the Conflict Resolution Manager immediately.

7. List All Pending / Standby Requests

set lines 220
set pages 500

column user_name format a20
column user_concurrent_program_name format a60
column request_date format a20
column requested_start_date format a20

SELECT r.request_id,
       u.user_name,
       cp.user_concurrent_program_name,
       TO_CHAR(r.request_date,
               'DD-MON-YYYY HH24:MI:SS') request_date,
       TO_CHAR(r.requested_start_date,
               'DD-MON-YYYY HH24:MI:SS') requested_start_date,
       ROUND((SYSDATE-r.request_date)*24*60,1) pending_minutes
FROM   fnd_concurrent_requests r,
       fnd_concurrent_programs_tl cp,
       fnd_user u
WHERE  r.phase_code = 'P'
AND    r.status_code = 'Q'
AND    cp.concurrent_program_id = r.concurrent_program_id
AND    cp.application_id = r.program_application_id
AND    cp.language = USERENV('LANG')
AND    u.user_id = r.requested_by
ORDER BY r.request_date;

This query is useful for identifying the oldest requests waiting on the Conflict Resolution Manager.

8. Find the Oldest Standby Request

SELECT COUNT(*) standby_count,
       TO_CHAR(MIN(request_date),
               'DD-MON-YYYY HH24:MI:SS') oldest_standby
FROM   fnd_concurrent_requests
WHERE  phase_code = 'P'
AND    status_code = 'Q';

An old Standby request may indicate either:

A legitimate long-running incompatibility
A request waiting for another request set stage
A Run Alone restriction
A stale or abnormal CRM condition
A Conflict Resolution Manager processing problem

9. Check Concurrent Program Incompatibilities

Oracle EBS stores concurrent program incompatibility definitions in FND_CONCURRENT_PROGRAM_SERIAL.

The following query identifies programs configured as incompatible with the program associated with the sample request.

set lines 220
set pages 200

column target_program format a60
column incompatible_program format a60
column incompatibility_type format a10

SELECT cp1.user_concurrent_program_name target_program,
       cp2.user_concurrent_program_name incompatible_program,
       s.incompatibility_type
FROM   fnd_concurrent_program_serial s,
       fnd_concurrent_programs_tl cp1,
       fnd_concurrent_programs_tl cp2
WHERE  cp1.application_id =
       s.to_run_application_id
AND    cp1.concurrent_program_id =
       s.to_run_concurrent_program_id
AND    cp2.application_id =
       s.running_application_id
AND    cp2.concurrent_program_id =
       s.running_concurrent_program_id
AND    cp1.language = USERENV('LANG')
AND    cp2.language = USERENV('LANG')
AND   (
          (s.to_run_application_id,
           s.to_run_concurrent_program_id)
          =
          (SELECT program_application_id,
                  concurrent_program_id
           FROM   fnd_concurrent_requests
           WHERE  request_id = 123456789)

       OR

          (s.running_application_id,
           s.running_concurrent_program_id)
          =
          (SELECT program_application_id,
                  concurrent_program_id
           FROM   fnd_concurrent_requests
           WHERE  request_id = 123456789)
      );

10. Find Currently Running Incompatible Requests

The following SQL attempts to identify currently running requests that have an incompatibility relationship with the program waiting in Standby.

set lines 220
set pages 200

column blocker_program format a60
column actual_start_date format a20
column argument_text format a70

WITH target_req AS
(
    SELECT request_id,
           program_application_id,
           concurrent_program_id
    FROM   fnd_concurrent_requests
    WHERE  request_id = 123456789
),
incompat AS
(
    SELECT s.running_application_id        blocker_app_id,
           s.running_concurrent_program_id blocker_program_id,
           s.incompatibility_type
    FROM   fnd_concurrent_program_serial s,
           target_req t
    WHERE  s.to_run_application_id =
           t.program_application_id
    AND    s.to_run_concurrent_program_id =
           t.concurrent_program_id

    UNION

    SELECT s.to_run_application_id,
           s.to_run_concurrent_program_id,
           s.incompatibility_type
    FROM   fnd_concurrent_program_serial s,
           target_req t
    WHERE  s.running_application_id =
           t.program_application_id
    AND    s.running_concurrent_program_id =
           t.concurrent_program_id
)
SELECT r.request_id blocker_request_id,
       cp.user_concurrent_program_name blocker_program,
       r.phase_code,
       r.status_code,
       TO_CHAR(r.actual_start_date,
               'DD-MON-YYYY HH24:MI:SS') actual_start_date,
       ROUND((SYSDATE-r.actual_start_date)*24*60,2)
            running_minutes,
       i.incompatibility_type,
       r.argument_text
FROM   incompat i,
       fnd_concurrent_requests r,
       fnd_concurrent_programs_tl cp
WHERE  r.program_application_id =
       i.blocker_app_id
AND    r.concurrent_program_id =
       i.blocker_program_id
AND    r.phase_code = 'R'
AND    cp.application_id =
       r.program_application_id
AND    cp.concurrent_program_id =
       r.concurrent_program_id
AND    cp.language = USERENV('LANG')
ORDER BY r.actual_start_date;

If this query returns a running request, the Standby condition may be completely normal.

11. Check Previous Executions of the Same Program

It is useful to determine whether the same concurrent program regularly goes into Standby or whether the behavior is new.

set lines 220
set pages 100

SELECT r.request_id,
       r.phase_code,
       r.status_code,
       TO_CHAR(r.request_date,
               'DD-MON-YYYY HH24:MI:SS') request_date,
       TO_CHAR(r.actual_start_date,
               'DD-MON-YYYY HH24:MI:SS') actual_start_date,
       TO_CHAR(r.actual_completion_date,
               'DD-MON-YYYY HH24:MI:SS') completion_date
FROM   fnd_concurrent_requests r
WHERE  r.program_application_id =
       (
           SELECT program_application_id
           FROM   fnd_concurrent_requests
           WHERE  request_id = 123456789
       )
AND    r.concurrent_program_id =
       (
           SELECT concurrent_program_id
           FROM   fnd_concurrent_requests
           WHERE  request_id = 123456789
       )
ORDER BY r.request_id DESC
FETCH FIRST 50 ROWS ONLY;

This helps answer:

Is only one submission stuck?

OR

Does every execution of this program enter Pending / Standby?

12. Check Whether the Request Is on Hold

SELECT request_id,
       phase_code,
       status_code,
       hold_flag
FROM   fnd_concurrent_requests
WHERE  request_id = 123456789;

Normally:

HOLD_FLAG = N

If the request is explicitly placed on hold, that should be investigated separately from CRM processing.

13. Check Concurrent Manager Capacity

A Pending / Standby request is normally controlled by CRM rather than worker availability. However, manager availability should still be checked after CRM releases the request.

set lines 220
set pages 200

column manager_name format a50
column target_node format a30

SELECT user_concurrent_queue_name manager_name,
       concurrent_queue_name,
       target_node,
       running_processes,
       max_processes,
       control_code,
       enabled_flag
FROM   fnd_concurrent_queues_vl
ORDER BY user_concurrent_queue_name;

For example, a Standard Manager may have:

Standard Manager
Maximum Processes : 40
Running Processes : 40

Even if all workers are busy, that normally results in a request waiting for a manager worker after CRM processing. It does not by itself explain why the request remains under Conflict Resolution Manager control.

14. Check Conflict Resolution Manager at OS Level

Source the Oracle EBS application environment first.

. ./EBSapps.env run

Then check the application processes:

ps -ef | grep FNDLIBR | grep -v grep

Also check Service Manager processes:

ps -ef | grep FNDSM | grep -v grep

15. Locate the CRM Log

Concurrent Manager logs are normally available below:

$APPLCSF/$APPLLOG

For example:

cd $APPLCSF/$APPLLOG

ls -ltr | grep -i FNDCRM

If the exact file name is unknown:

find $APPLCSF/$APPLLOG -type f -mtime -1 -ls

Review the latest CRM log for database errors, manager communication problems, or repeated processing failures.

16. Check ICM and CRM Together

set lines 200
set pages 100

column concurrent_queue_name format a20
column user_concurrent_queue_name format a40
column target_node format a30

SELECT concurrent_queue_name,
       user_concurrent_queue_name,
       running_processes,
       max_processes,
       target_node,
       control_code,
       enabled_flag
FROM   fnd_concurrent_queues_vl
WHERE  concurrent_queue_name IN
       ('FNDICM','FNDCRM');

This quickly confirms whether both the Internal Concurrent Manager and Conflict Resolution Manager are operational.

17. Useful Summary Query

The following query provides a quick summary of all requests currently waiting in Standby.

set lines 220
set pages 500

column program_name format a60
column submitted_by format a20
column request_date format a20

SELECT r.request_id,
       cp.user_concurrent_program_name program_name,
       fu.user_name submitted_by,
       TO_CHAR(r.request_date,
               'DD-MON-YYYY HH24:MI:SS') request_date,
       ROUND((SYSDATE-r.request_date)*24*60,2)
            waiting_minutes,
       r.hold_flag,
       r.parent_request_id
FROM   fnd_concurrent_requests r,
       fnd_concurrent_programs_tl cp,
       fnd_user fu
WHERE  r.phase_code = 'P'
AND    r.status_code = 'Q'
AND    cp.application_id =
       r.program_application_id
AND    cp.concurrent_program_id =
       r.concurrent_program_id
AND    cp.language = USERENV('LANG')
AND    fu.user_id = r.requested_by
ORDER BY r.request_date;

18. Troubleshooting Decision Tree

Concurrent Request
       |
       v
Pending / Standby
       |
       v
Check FNDCRM
       |
       +-------------------------+
       |                         |
FNDCRM Running?              FNDCRM Down?
       |                         |
      Yes                        No
       |                         |
       v                         v
Check Program               Investigate CRM
Incompatibility             / ICM / Node
       |
       v
Running Incompatible Request?
       |
   +---+---+
   |       |
  Yes      No
   |       |
   v       v
Normal    Check:
Wait      - CRM backlog
          - CRM logs
          - Run Alone flag
          - Request Set
          - Parent request
          - Stale Standby requests
          - Manager state

19. Scenario 1 – Running Incompatible Request Found

Example:

Request 123456789
Pending / Standby

Waiting because:

Request 123450001
Program: Payables Purge
Phase  : Running

In this situation the CRM is functioning correctly.

Once the incompatible request finishes, CRM should reevaluate the waiting request and release it.

20. Scenario 2 – No Incompatible Request Found

Suppose:

Request 123456789
Pending / Standby

CRM Running           : YES
Incompatible Requests : NONE
Standby Duration      : Several Hours

This situation requires deeper investigation.

Check:

Conflict Resolution Manager log
ICM log
CRM process health
Concurrent Manager database state
Run Alone configuration
Parent request or request set dependencies
Whether many requests are accumulating in P/Q status

21. Scenario 3 – Many Requests in Pending / Standby

For example:

Standby Requests : 1500
Oldest Request   : Several Hours Old
FNDCRM Processes : 0

This strongly suggests a Conflict Resolution Manager problem rather than an individual concurrent program issue.

The CRM/ICM logs and manager processes should be investigated before taking any corrective action.

22. Scenario 4 – Only a Few Standby Requests

For example:

Standby Requests : 3
FNDCRM Processes : 1

Oracle E-Business Suite 12.2: Concurrent Request Performance Troubleshooting and Essential Diagnostic

Oracle E-Business Suite 12.2: Concurrent Request Performance Troubleshooting and Essential Diagnostic SQL

When an Oracle E-Business Suite concurrent request runs much longer than expected, appears to hang, or suddenly performs worse than earlier runs, the safest approach is to investigate in layers. First confirm the request state, then map it to its database session, inspect the SQL and wait event, check for blocking, and enable tracing only when the read-only evidence is insufficient.

This guide provides a production-oriented workflow for Oracle EBS 12.2 with an Oracle 19c database. It also includes frequently used SQL and UNIX commands for patch, file-version, statistics, profile-option, and executable-level checks.

Production safety: Most queries in this post are read-only. Trace activation, statistics gathering, materialized-view refresh, and relinking change system state or consume resources. Perform those actions only through an approved change and after validating the scope in a lower environment.

1. Investigation Flow for a Slow or Hanging Concurrent Request

Confirm the request phase, status, start time, and database process ID.
Map the operating-system process to the Oracle session.
Identify the current or most recently executed SQL.
Review the session wait event and elapsed wait time.
Check whether another session is blocking the request.
Compare the runtime with previous executions of the same program.
Capture a targeted trace only when required.

2. Check the Current Concurrent Request Status

The following query returns the program name, phase, status, timestamps, and Oracle process ID for one or more request IDs.

SELECT r.request_id,
       cp.user_concurrent_program_name,
       phase.meaning AS request_phase,
       status.meaning AS request_status,
       TO_CHAR(r.request_date, 'DD-MON-YYYY HH24:MI:SS') AS request_date,
       TO_CHAR(r.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') AS actual_start_date,
       TO_CHAR(r.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') AS actual_completion_date,
       r.oracle_process_id
FROM   apps.fnd_concurrent_requests r
JOIN   apps.fnd_concurrent_programs_tl cp
       ON cp.application_id = r.program_application_id
      AND cp.concurrent_program_id = r.concurrent_program_id
      AND cp.language = 'US'
JOIN   apps.fnd_lookups phase
       ON phase.lookup_type = 'CP_PHASE_CODE'
      AND phase.lookup_code = r.phase_code
JOIN   apps.fnd_lookups status
       ON status.lookup_type = 'CP_STATUS_CODE'
      AND status.lookup_code = r.status_code
WHERE  r.request_id IN (&request_id1, &request_id2)
ORDER BY r.request_id;

3. Map the Request to Its Database Session and SQL

On Oracle 19c, use SQL_ID and PREV_SQL_ID instead of joining only through the legacy SQL address. A request may be between SQL calls, so the previous SQL ID is also useful.

SELECT r.request_id,
       s.sid,
       s.serial#,
       p.spid AS os_process_id,
       s.status AS session_status,
       s.module,
       s.action,
       s.sql_id,
       s.prev_sql_id,
       q.sql_text
FROM   apps.fnd_concurrent_requests r
JOIN   v$process p
       ON p.spid = TRIM(r.oracle_process_id)
JOIN   v$session s
       ON s.paddr = p.addr
LEFT JOIN v$sql q
       ON q.sql_id = COALESCE(s.sql_id, s.prev_sql_id)
      AND q.child_number = (
            SELECT MIN(q2.child_number)
            FROM   v$sql q2
            WHERE  q2.sql_id = COALESCE(s.sql_id, s.prev_sql_id)
          )
WHERE  r.request_id IN (&request_id1, &request_id2)
ORDER BY r.request_id;

If the request is no longer running, its database session may already have disconnected. In that case, use AWR/ASH if licensed and retained, or correlate the request timestamps with archived diagnostic data.

4. Review the Current Wait Event

V$SESSION provides the current wait information and is preferred over the older V$SESSION_WAIT view.

SELECT r.request_id,
       s.sid,
       s.serial#,
       s.event,
       s.wait_class,
       s.state,
       s.seconds_in_wait,
       s.blocking_session_status,
       s.blocking_instance,
       s.blocking_session
FROM   apps.fnd_concurrent_requests r
JOIN   v$process p
       ON p.spid = TRIM(r.oracle_process_id)
JOIN   v$session s
       ON s.paddr = p.addr
WHERE  r.request_id IN (&request_id1, &request_id2)
ORDER BY r.request_id;

A wait event is not automatically a problem. Interpret it with the wait class, duration, request behavior, SQL plan, and workload. For example, an idle wait is normally expected, while a sustained concurrency or user-I/O wait may need deeper investigation.

5. Identify Blocking Sessions

This session-based query is more useful than checking a table name alone because it identifies the waiting request and the blocking database session directly.

SELECT r.request_id,
       s.sid AS waiting_sid,
       s.serial# AS waiting_serial,
       s.event,
       s.seconds_in_wait,
       s.blocking_instance,
       s.blocking_session,
       bs.serial# AS blocking_serial,
       bs.username AS blocking_username,
       bs.module AS blocking_module,
       bs.sql_id AS blocking_sql_id
FROM   apps.fnd_concurrent_requests r
JOIN   v$process p
       ON p.spid = TRIM(r.oracle_process_id)
JOIN   v$session s
       ON s.paddr = p.addr
LEFT JOIN gv$session bs
       ON bs.inst_id = s.blocking_instance
      AND bs.sid = s.blocking_session
WHERE  r.request_id = &request_id;

Do not terminate a blocking session solely because it appears in this output. First identify its owner, transaction, business function, and rollback impact.

6. Compare Runtime with Previous Executions

Historical runtime helps determine whether degradation was gradual, intermittent, or sudden.

SELECT cp.user_concurrent_program_name,
       r.request_id,
       r.actual_start_date,
       r.actual_completion_date,
       ROUND((r.actual_completion_date - r.actual_start_date) * 86400) AS runtime_seconds,
       phase.meaning AS request_phase,
       status.meaning AS request_status
FROM   apps.fnd_concurrent_requests r
JOIN   apps.fnd_concurrent_programs_tl cp
       ON cp.application_id = r.program_application_id
      AND cp.concurrent_program_id = r.concurrent_program_id
      AND cp.language = 'US'
JOIN   apps.fnd_lookups phase
       ON phase.lookup_type = 'CP_PHASE_CODE'
      AND phase.lookup_code = r.phase_code
JOIN   apps.fnd_lookups status
       ON status.lookup_type = 'CP_STATUS_CODE'
      AND status.lookup_code = r.status_code
WHERE  cp.user_concurrent_program_name = '&concurrent_program_name'
AND    r.actual_start_date >= SYSDATE - &history_days
ORDER BY r.actual_start_date DESC;

7. Tracing a Concurrent Request

Option A: Enable Trace on the Concurrent Program

Navigate to:

System Administrator → Concurrent → Program → Define

Query the program and select Enable Trace. Submit a controlled test request, collect the trace, and disable the option after testing so later requests are not traced unintentionally.

Find the Trace File on Oracle 19c

USER_DUMP_DEST is obsolete for modern ADR-managed databases. Use the diagnostic destination or query the session trace file directly.

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

SELECT r.request_id,
       s.sid,
       s.serial#,
       p.spid AS os_process_id,
       p.tracefile,
       s.module,
       s.sql_id
FROM   apps.fnd_concurrent_requests r
JOIN   v$process p
       ON p.spid = TRIM(r.oracle_process_id)
JOIN   v$session s
       ON s.paddr = p.addr
WHERE  r.request_id = &request_id;

Option B: Initialization SQL Statement – Custom

For a single user and controlled reproduction, temporarily set the profile option Initialization SQL Statement – Custom at user level:

BEGIN
  EXECUTE IMMEDIATE q'[ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SR_NUMBER']';
  EXECUTE IMMEDIATE q'[ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED]';
  EXECUTE IMMEDIATE q'[ALTER SESSION SET EVENTS '10046 trace name context forever, level 12']';
END;

Run only the affected activity, then restore the profile to its previous value immediately. Level 12 captures SQL waits and bind values and can generate substantial output; protect sensitive trace data accordingly.

Option C: Trace a Reproducible SQL*Plus Test

ALTER SESSION SET statistics_level = ALL;
ALTER SESSION SET tracefile_identifier = 'ORGPERF';
ALTER SESSION SET events '10046 trace name context forever, level 12';

-- Execute only the problematic SQL here.

ALTER SESSION SET events '10046 trace name context off';

Format the Trace with TKPROF

tkprof input_trace.trc output_trace.txt sort=exeela,fchela,prsela sys=no

8. Patch and File-Version Diagnostics

Identify the Patch That Delivered a Specific File Version

This read-only SQL returns the patching history for a particular EBS file. It correlates the file with its recorded version, translation level, patch, driver, patch run, APPL_TOP, and application date. Enter the EBS applications-system name for &SID and supply the filename in uppercase for &file_name_in_caps.

SELECT atp.name AS appl_top_name,
       DECODE(f.app_short_name,
              'DUMMY', NULL,
              'SQLAP', 'AP',
              'SQLGL', 'GL',
              'OFA',   'FA',
              f.app_short_name) AS product,
       DECODE(f.subdir, 'DUMMY', NULL, f.subdir) AS directory_name,
       f.filename,
       fv.version
         || DECODE(fv.translation_level,
                   0, NULL,
                   ':' || TO_CHAR(fv.translation_level)) AS file_version,
       TO_CHAR(pr.end_date, 'DD-MM-YYYY HH24:MI:SS') AS date_applied,
       ap.patch_name AS patch_id,
       ap.applied_patch_id,
       pr.end_date,
       fv.version_segment1,
       fv.version_segment2,
       fv.version_segment3,
       fv.version_segment4,
       fv.version_segment5,
       fv.version_segment6,
       fv.version_segment7,
       fv.version_segment8,
       fv.version_segment9,
       fv.version_segment10,
       fv.translation_level,
       pr.patch_run_id,
       pr.patch_top,
       pr.patch_action_options,
       TO_CHAR(pr.start_date, 'DD-MM-YYYY HH24:MI:SS') AS patch_start_date,
       pr.program_run_id,
       pr.session_id,
       pd.patch_driver_id,
       pd.driver_file_name,
       pd.platform
FROM   ad_appl_tops atp,
       ad_applied_patches ap,
       ad_patch_drivers pd,
       ad_patch_runs pr,
       ad_patch_run_bugs prb,
       ad_file_versions fv,
       ad_patch_run_bug_actions prba,
       ad_files f
WHERE  f.file_id = prba.file_id
AND    prba.executed_flag = 'Y'
AND    prba.patch_run_bug_id = prb.patch_run_bug_id
AND    pr.appl_top_id = atp.appl_top_id
AND    prb.patch_run_id = pr.patch_run_id
AND    pr.patch_driver_id = pd.patch_driver_id
AND    pd.applied_patch_id = ap.applied_patch_id
AND    prba.patch_file_version_id = fv.file_version_id
AND    UPPER(atp.applications_system_name) = UPPER('&SID')
AND    UPPER(f.filename) IN ('&file_name_in_caps')
GROUP BY f.app_short_name,
         f.subdir,
         f.filename,
         atp.name,
         fv.version,
         fv.version_segment1,
         fv.version_segment2,
         fv.version_segment3,
         fv.version_segment4,
         fv.version_segment5,
         fv.version_segment6,
         fv.version_segment7,
         fv.version_segment8,
         fv.version_segment9,
         fv.version_segment10,
         fv.translation_level,
         ap.patch_name,
         pr.end_date,
         ap.applied_patch_id,
         pr.patch_run_id,
         pr.patch_top,
         pr.patch_action_options,
         pr.start_date,
         pr.program_run_id,
         pr.session_id,
         pd.patch_driver_id,
         pd.driver_file_name,
         pd.platform
ORDER BY f.app_short_name,
         f.subdir,
         atp.name,
         fv.version_segment1 DESC,
         fv.version_segment2 DESC,
         fv.version_segment3 DESC,
         fv.version_segment4 DESC,
         fv.version_segment5 DESC,
         fv.version_segment6 DESC,
         fv.version_segment7 DESC,
         fv.version_segment8 DESC,
         fv.version_segment9 DESC,
         fv.version_segment10 DESC,
         fv.translation_level DESC,
         pr.end_date DESC;

Usage notes: Use the base filename only, such as FNDLIBR or AFCPRUN.SQL, and enter it in uppercase. If the same filename exists in multiple products or directories, use the returned product and directory columns to identify the correct record. This query reports the history stored in the AD patch tables; validate the deployed file on the relevant run and patch file systems when investigating an EBS 12.2 discrepancy.

Check Whether an EBS Patch Is Recorded

SELECT bug_number, creation_date
FROM   apps.ad_bugs
WHERE  bug_number = '&bug_number';

SELECT patch_name, applied_patch_id, creation_date
FROM   apps.ad_applied_patches
WHERE  patch_name = '&patch_name';

For EBS 12.2 online patching, also correlate the result with the relevant ADOP session and patch records. A row in one table alone may not describe the complete patching-cycle outcome.

Check the EBS Release

SELECT release_name
FROM   apps.fnd_product_groups;

Check Database Component Versions

SELECT comp_name, version, status
FROM   dba_registry
ORDER BY comp_name;

Inspect Package Header and Selected Source Lines

SELECT owner, name, type, line, text
FROM   all_source
WHERE  owner = UPPER('&owner')
AND    name = UPPER('&package_name')
AND    line <= 10
ORDER BY type, line;

SELECT owner, name, type, line, text
FROM   all_source
WHERE  owner = UPPER('&owner')
AND    name = UPPER('&package_name')
AND    line BETWEEN &line_from AND &line_to
ORDER BY type, line;

9. Index and Statistics Checks

List Index Columns

SELECT ic.index_owner,
       ic.index_name,
       ic.column_position,
       ic.column_name,
       i.status,
       i.last_analyzed
FROM   dba_ind_columns ic
JOIN   dba_indexes i
       ON i.owner = ic.index_owner
      AND i.index_name = ic.index_name
WHERE  ic.table_owner = UPPER('&table_owner')
AND    ic.table_name = UPPER('&table_name')
ORDER BY ic.index_name, ic.column_position;

Check When Table Statistics Were Gathered

SELECT owner, table_name, num_rows, stale_stats, last_analyzed
FROM   dba_tab_statistics
WHERE  owner = UPPER('&table_owner')
AND    table_name = UPPER('&table_name');

Gather EBS Table Statistics

EXEC apps.fnd_stats.gather_table_stats('&schema_name', '&table_name');

This is a state-changing operation. Confirm the correct FND_STATS signature for your EBS release, estimate the impact, and schedule it through change control.

10. Retrieve Profile Option Values

The following query resolves site, application, responsibility, user, server, and organization-level values without assuming that the display name is unique across languages.

SELECT po.profile_option_name,
       pot.user_profile_option_name,
       pov.level_id,
       pov.level_value,
       pov.level_value2,
       pov.profile_option_value
FROM   apps.fnd_profile_options po
JOIN   apps.fnd_profile_options_tl pot
       ON pot.profile_option_name = po.profile_option_name
      AND pot.application_id = po.application_id
      AND pot.language = 'US'
JOIN   apps.fnd_profile_option_values pov
       ON pov.profile_option_id = po.profile_option_id
      AND pov.application_id = po.application_id
WHERE  UPPER(pot.user_profile_option_name) =
       UPPER('&user_profile_option_name')
ORDER BY pov.level_id, pov.level_value;

11. Materialized View Refresh

BEGIN
  DBMS_MVIEW.REFRESH(
    list   => '&schema_name.&materialized_view_name',
    method => '&refresh_method'
  );
END;
/

A refresh can be resource-intensive and may lock or modify the materialized view. Validate the refresh method and run it only in an approved window.

12. Useful UNIX Commands

Find a File Version Embedded in an Executable

strings -a <executable_name> | grep -i '<file_name>' | grep '\$Header'

List All Embedded File Headers

strings -a <executable_name> | grep '\$Header' > executable_versions.txt

Check Soft and Hard Resource Limits

ulimit -aS
ulimit -aH

Compare an ODF Object with the Database

adodfcmp odffile=<file_name> \
  userid=apps \
  mode=views \
  logfile=/tmp/adodfcmp.log \
  touser=apps \
  priv_schema=system \
  changedb=n

Allow the utility to prompt for passwords; do not place database passwords in commands, scripts, screenshots, or shell history.

13. Relinking: Use Only Through an Approved Change

Relinking is not a diagnostic read-only action. Source the correct run-edition environment, stop the affected service or process as required, take backups, review the product-specific procedure, and validate afterward.

adrelink.sh force=y ranlib=y "<product_short_name>"

adrelink.sh force=y ranlib=y "<product_short_name> <executable_name>"

Recommended Evidence to Capture for an RCA

Request ID, program name, parameters, phase, and status.
Expected runtime and actual runtime.
SID, serial number, OS process ID, SQL ID, and execution plan.
Wait event, blocking-session details, and object involved.
CPU, memory, I/O, and load during the incident window.
Recent statistics, patches, configuration changes, and data-volume growth.
Request log/output and any targeted trace or TKPROF report.
Comparison with previous successful executions.

Conclusion

A slow concurrent request should not be diagnosed from a single query or wait event. Build a time-correlated evidence chain from the EBS request, Oracle session, SQL, execution plan, waits, blockers, host utilization, and historical runtime. Start with read-only checks, keep tracing narrowly scoped, and use change control for statistics gathering, refreshes, relinking, or session termination.

Suggested Blogger labels: Oracle EBS 12.2, Apps DBA, Concurrent Manager, Performance Tuning, SQL, Oracle 19c, Troubleshooting

Saturday, August 22, 2026

ADOP Failure Investigation in Oracle EBS 12.2: A Production Troubleshooting Runbook

Online patching failures in Oracle E-Business Suite Release 12.2 are rarely resolved by searching for the word “ERROR” in one log file. An ADOP session coordinates database editions, application-tier file systems, patch workers, WebLogic components and multiple application nodes.

A reliable investigation must answer four questions:

Which ADOP phase failed?
What was the first actionable error?
Is the problem at the database, file-system, patch-worker or node level?
Can the current session be restarted safely, or must it be aborted?

This article presents a structured Apps DBA workflow for investigating ADOP failures without immediately jumping to destructive recovery actions.

1. Understand the ADOP Failure Boundary

An online patching cycle normally contains the following phases:

prepare → apply → finalize → cutover → cleanup

Each phase has a different failure profile.

ADOP phase	Common problem areas
Prepare	Run/patch file-system synchronization, context mismatch, patch edition creation, database connectivity
Apply	Patch driver failure, failed workers, invalid objects, missing files, prerequisite patches
Finalize	Invalid objects, editioned-object validation, compilation or readiness checks
Cutover	Service shutdown/startup, WebLogic failure, database edition switch, insufficient cutover time
Cleanup	Old editions, obsolete objects, database space, blocking sessions
fs_clone	File-system space, permissions, context mismatch, node connectivity, incomplete previous synchronization

Always identify the failing phase before attempting recovery.

2. Confirm the Correct EBS Environment

Before running any diagnostic command, confirm that the environment points to the expected instance and file-system edition.

echo $CONTEXT_FILE
echo $FILE_EDITION
echo $RUN_BASE
echo $PATCH_BASE
echo $APPL_TOP
echo $AD_TOP

Expected considerations:

Normal ADOP phases are initiated from the run file system.
Patch-side investigation may require sourcing the patch environment.
The database SID, context file and application node must belong to the same environment.
In a multi-node environment, verify the environment on every participating node.

A surprising number of troubleshooting mistakes occur because the DBA investigates the wrong edition or sources an environment left over from another instance.

3. Check the Current ADOP Session

Start with the session status:

adop -status

This identifies whether:

An online patching cycle is active
A phase is complete, running or failed
A previous session requires attention
The system is ready for a new patching cycle

Do not start another prepare phase merely because no ADOP process is visible at the operating-system level. The database may still record an active or incomplete patching session.

For a quick configuration and online-patching health check, run:

adop -validate

Validation can expose configuration, edition or synchronization problems that may not be obvious from the failed patch log alone.

4. Scan the Logs Before Opening Individual Files

ADOP creates logs across multiple directories, phases, nodes and workers. Instead of manually searching hundreds of files, begin with adopscanlog.

Scan the latest session:

adopscanlog

Display error-level messages:

adopscanlog loglevel=error

Scan all available sessions:

adopscanlog session_id=0

Depending on the EBS code level, supported arguments and output can vary. Check the local help before building automation around a specific syntax:

adopscanlog -help

The objective is not to collect every warning. It is to locate the first meaningful failure that triggered the later cascade of errors.

For example, messages such as the following may be secondary symptoms:

Worker failed
Phase failed
ADOP exiting with status 1

The real cause may appear earlier:

ORA-01652: unable to extend temp segment
ORA-04021: timeout occurred while waiting to lock object
Permission denied
No space left on device
Patch prerequisite is missing
Invalid username/password

Always investigate upward from the final failure message.

5. Locate the ADOP Log Directory

The non-editioned file system stores ADOP logs beneath:

$NE_BASE/EBSapps/log/adop

List the most recently updated files:

find $NE_BASE/EBSapps/log/adop -type f -exec ls -lt {} \; | head

A typical structure separates logs by:

ADOP session
Timestamp
Phase
Application node
Patch number
Worker

When a multi-node session fails, do not inspect only the primary node. The controlling ADOP log may report that another node failed while the actual Java, WebLogic, file-copy or patch error exists only in the remote-node log.

Useful searches include:

grep -i "error" logfile
grep -i "failed" logfile
grep -i "ORA-" logfile
grep -i "adop exit status" logfile
grep -i "not found" logfile
grep -i "permission denied" logfile

Avoid assuming every line containing “error” is fatal. Some patch logs include expected exceptions or informational error counters. Correlate the message with the phase result and timestamp.

6. Monitor a Running Session with ADOPMON

For long-running phases, use:

adopmon

adopmon provides a continuously refreshed view of important online-patching actions. It is useful during:

Large patch applications
Finalize
Cutover
Multi-node patching
Sessions that appear to be hanging

Treat “no visible progress” carefully. An ADOP operation may be waiting on:

A database lock
A long SQL statement
Object compilation
A remote application node
A WebLogic operation
A patch worker
File-system synchronization

Before stopping any process, confirm whether work is still active at the database and operating-system levels.

7. Investigate Failed Patch Workers

An apply-phase failure frequently originates from one or more AD workers.

Check the worker status using the AD Controller utility:

adctrl

Typical actions include:

Display worker status
Review the failed worker
Restart a worker after correcting the cause
Skip a failed job only when explicitly permitted by the patch documentation or Oracle Support

Review the worker log before restarting it. Common worker-level failures include:

SQL compilation errors
Object locks
Missing grants or synonyms
Tablespace exhaustion
Invalid custom objects
Failed form or report generation
File permission problems
Incorrect product-top configuration

Do not repeatedly restart a worker without resolving the underlying error. Repeated attempts usually add noise while leaving the actual condition unchanged.

8. Query AD_ZD_LOGS for Database-Side Evidence

Online patching also stores diagnostic information in database tables. AD_ZD_LOGS can help when application-tier logs are incomplete or when the error originated inside the database.

Connect using an approved secure method:

sqlplus /nolog

CONNECT apps

Review recent entries:

SET LINESIZE 220
SET PAGESIZE 100
SET LONG 100000
SET LONGCHUNKSIZE 100000

SELECT log_sequence,
       TO_CHAR(log_timestamp, 'YYYY-MM-DD HH24:MI:SS') log_time,
       message_text
FROM   ad_zd_logs
ORDER  BY log_sequence DESC
FETCH FIRST 100 ROWS ONLY;

Search for likely failure messages:

SELECT log_sequence,
       TO_CHAR(log_timestamp, 'YYYY-MM-DD HH24:MI:SS') log_time,
       message_text
FROM   ad_zd_logs
WHERE  UPPER(message_text) LIKE '%ERROR%'
OR     UPPER(message_text) LIKE '%FAILED%'
OR     UPPER(message_text) LIKE '%ORA-%'
ORDER  BY log_sequence DESC;

Capturing the latest sequence before reproducing an issue makes it easier to isolate new messages:

SELECT MAX(log_sequence)
FROM   ad_zd_logs;

After reproducing the failure:

SELECT log_sequence,
       TO_CHAR(log_timestamp, 'YYYY-MM-DD HH24:MI:SS') log_time,
       message_text
FROM   ad_zd_logs
WHERE  log_sequence > :starting_sequence
ORDER  BY log_sequence;

Use a sequence captured from the same environment and incident window. An arbitrary historical sequence value can produce misleading output.

9. Use ADZDSHOWLOG.sql

Oracle supplies a SQL script for displaying online-patching log details:

sqlplus /nolog

CONNECT apps
@$AD_TOP/sql/ADZDSHOWLOG.sql

This is particularly helpful when:

The application-tier message is generic
A database editioning operation failed
The log hierarchy is difficult to navigate
Oracle Support requests database-side online-patching evidence

Run the script from the correct application environment so that $AD_TOP resolves to the intended EBS instance.

10. Generate an Online-Patching Diagnostic Report

The adzdreport.pl utility can collect detailed information about online-patching and editioning status.

Check the local usage first:

$AD_TOP/bin/adzdreport.pl -help

Run the utility according to the syntax supported by your EBS code level.

Avoid putting the APPS password directly in a shared shell script, command history, email or incident ticket. If a utility requires credentials, enter them interactively or use the secure method approved by your organization.

The report is useful for identifying:

Editioning inconsistencies
AD_ZD component problems
Run and patch edition status
Online-patching object issues
Conditions that require deeper Oracle Support analysis

Because the output may contain hostnames, paths and configuration details, review it before sharing outside the DBA or support team.

11. Perform Database Health Checks

An ADOP failure may be a database-capacity or concurrency problem rather than a patching-tool defect.

Check invalid objects

SELECT owner,
       object_type,
       COUNT(*) invalid_count
FROM   dba_objects
WHERE  status = 'INVALID'
GROUP  BY owner, object_type
ORDER  BY owner, object_type;

Do not treat every invalid object as an ADOP failure. Compare the list with the pre-patching baseline and focus on newly invalid objects related to the failing patch.

Check tablespace usage

SELECT tablespace_name,
       ROUND(used_percent, 2) used_percent
FROM   dba_tablespace_usage_metrics
ORDER  BY used_percent DESC;

Also verify:

TEMP space
UNDO space and retention
Archive destination availability
FRA usage, where applicable
Datafile autoextend limits
Operating-system file-system usage

Check blocking sessions

SELECT sid,
       serial#,
       username,
       status,
       event,
       blocking_session,
       seconds_in_wait
FROM   v$session
WHERE  blocking_session IS NOT NULL
ORDER  BY seconds_in_wait DESC;

Do not kill a blocking session solely because it appears in this query. Identify the owner, business operation and transaction impact before taking action.

12. Check File-System and Node Health

On every application node, verify:

df -g

On Linux, use:

df -h

Also verify:

hostname
date
ulimit -a

Check the ownership and permissions of:

$RUN_BASE
$PATCH_BASE
$NE_BASE
$APPL_TOP
$COMMON_TOP
$FMW_HOME
Patch staging directories
Temporary directories

In multi-node systems, confirm:

All required nodes are reachable
Passwordless SSH is working where required
Context files contain the correct node information
Patch files exist in the same $PATCH_TOP location on non-shared file systems
Clocks are synchronized
Shared mounts are available
No node has a full local file system

A primary-node log may only say that a remote operation failed. The actual error can be in the remote node’s log or operating-system event log.

13. Build a Failure Timeline

A disciplined incident timeline is more useful than a large ZIP file containing every log.

Capture:

ADOP session ID
Patch number
Failed phase
Failure timestamp
Application node
Failed worker number
First actionable error
Database alert-log timestamp
Corrective action
Restart timestamp
Final validation result

Correlate events across:

Main ADOP log
Patch driver log
Worker log
AD_ZD_LOGS
Database alert log
Listener log
WebLogic logs
OHS logs
Operating-system logs

Use timestamps to distinguish the root cause from the cleanup messages generated after the failure.

14. Restart or Abort: Make the Correct Decision

Restart the existing session when:

The root cause is understood
The condition is reversible
Disk space, permission, lock or connectivity issues have been corrected
The patch documentation supports restart
The ADOP session remains recoverable

Restart the failed phase using the appropriate ADOP command. ADOP maintains restart information and can resume supported operations after the underlying problem is corrected.

Consider abort only when:

The cycle has not entered cutover
The failure cannot be resolved within the maintenance plan
The patch edition must be abandoned
The decision has been reviewed by the Apps DBA lead and change owner
The recovery sequence is understood

Oracle documents that abort is available before cutover. A normal recovery sequence is:

adop phase=abort
adop phase=cleanup cleanup_mode=full
adop phase=fs_clone

Abort and cleanup may also be combined where appropriate:

adop phase=abort,cleanup cleanup_mode=full

After an abort, full cleanup is required. If patch application was attempted, synchronize the patch file system using fs_clone before beginning another online-patching cycle.

Do not run abort, cleanup or forced fs_clone merely as a generic response to an error. These are recovery operations, not diagnostic commands.

15. Special Caution After Cutover Starts

Once cutover begins, the operational situation changes significantly.

Do not assume that adop phase=abort can roll back a cutover. Oracle documents that abort is available only before cutover is initiated.

If cutover fails:

Preserve all logs.
Determine whether the database edition switch occurred.
Check the status of application services.
Review the cutover logs on every node.
Confirm the active run and patch file systems.
Follow the applicable patch README and Oracle Support guidance.
Avoid manually changing edition metadata or ADOP tables.

A failed cutover requires controlled recovery because application availability and file-system edition state may already have changed.

16. Post-Recovery Validation

After the issue is corrected, do not stop at “ADOP completed successfully.”

Run:

adop -status
adop -validate

Then verify:

Required ADOP phase completed
No failed workers remain
Application services are running
Login page is available
Forms and OAF pages open
Concurrent Managers are operating
Workflow Mailer and other critical services are healthy
No unexpected invalid objects remain
Database and application logs contain no new critical errors
Run and patch file systems are synchronized as required
Patch level matches the intended change

For cutover incidents, also perform a business smoke test with the application team.

17. Production-Safe Command Checklist

echo $CONTEXT_FILE
echo $FILE_EDITION
echo $APPL_TOP
echo $AD_TOP
echo $NE_BASE

adop -status
adop -validate
adopscanlog
adopscanlog loglevel=error
adopmon

df -g
ulimit -a

Database-side tools:

@$AD_TOP/sql/ADZDSHOWLOG.sql

Diagnostic utility:

$AD_TOP/bin/adzdreport.pl -help

Recovery commands—use only after diagnosis and approval:

adop phase=abort
adop phase=cleanup cleanup_mode=full
adop phase=fs_clone

18. Common Troubleshooting Mistakes

Avoid the following practices:

Starting another prepare phase without checking adop -status
Searching only the final ADOP log
Ignoring remote-node logs
Restarting failed workers without correcting the cause
Killing database sessions without identifying the transaction owner
Using skipsyncerror=yes without proving that a subsequent patch will resolve the synchronization failure
Running abort or forced fs_clone as a first response
Updating ADOP or AD_ZD tables manually
Supplying APPS passwords on the command line or saving them in scripts
Deleting patching logs before completing the RCA
Treating every invalid object as patch-related
Declaring success without application-level validation

Conclusion

Effective ADOP troubleshooting is not about knowing one special command. It is about collecting evidence in the correct sequence.

A strong Apps DBA workflow is:

Confirm environment
        ↓
Check ADOP session and phase
        ↓
Scan consolidated logs
        ↓
Locate the first actionable error
        ↓
Correlate worker, database and node evidence
        ↓
Correct the underlying condition
        ↓
Restart when recoverable
        ↓
Abort only through a controlled decision
        ↓
Validate the complete EBS service

Tools such as adop -status, adop -validate, adopscanlog, adopmon, ADZDSHOWLOG.sql, AD_ZD_LOGS and adzdreport.pl become most valuable when used together as part of a repeatable incident runbook.

Thursday, August 20, 2026

How to Find Which Concurrent Program Is Executed by Which Custom Concurrent Manager in Oracle EBS 12.2

In Oracle E-Business Suite 12.2, concurrent programs may be eligible to run under the Standard Manager or one or more custom Concurrent Managers based on specialization rules.

However, eligibility does not necessarily prove which manager actually executed a request. To identify the manager that processed a concurrent request, use the CONTROLLING_MANAGER column in FND_CONCURRENT_REQUESTS.

The following production-safe, read-only SQL queries provide:

Concurrent programs executed by each custom manager
Request-level execution details
Program execution counts
First and last execution times
Average runtime
Manager name and short name

Important distinction

There are two different requirements:

Actual execution history: Which manager actually executed a request?
Manager eligibility: Which managers are allowed to execute a program based on specialization rules?

The queries in this article show the actual execution history.

A program can be eligible for multiple managers, but each individual request is processed by one Concurrent Manager process. The relationship is captured through:

FND_CONCURRENT_REQUESTS.CONTROLLING_MANAGER
        ↓
FND_CONCURRENT_PROCESSES.CONCURRENT_PROCESS_ID
        ↓
FND_CONCURRENT_QUEUES

Query 1: Programs executed by each custom Concurrent Manager

The following query returns a distinct list of programs historically executed by custom Concurrent Managers:

SELECT DISTINCT
       fcq.user_concurrent_queue_name manager_name,
       fcq.concurrent_queue_name manager_short_name,
       fav.application_name,
       fcp.concurrent_program_name program_short_name,
       fcp.user_concurrent_program_name program_name
FROM apps.fnd_concurrent_requests fcr
JOIN apps.fnd_concurrent_processes fpr
  ON fpr.concurrent_process_id = fcr.controlling_manager
JOIN apps.fnd_concurrent_queues_vl fcq
  ON fcq.application_id = fpr.queue_application_id
 AND fcq.concurrent_queue_id = fpr.concurrent_queue_id
JOIN apps.fnd_concurrent_programs_vl fcp
  ON fcp.application_id = fcr.program_application_id
 AND fcp.concurrent_program_id = fcr.concurrent_program_id
JOIN apps.fnd_application_vl fav
  ON fav.application_id = fcr.program_application_id
WHERE fcr.actual_start_date IS NOT NULL
  AND fcq.concurrent_queue_name NOT IN
      ('FNDICM', 'STANDARD', 'FNDCRM', 'FNDIM', 'FNDSM')
ORDER BY
       fcq.user_concurrent_queue_name,
       fav.application_name,
       fcp.user_concurrent_program_name;

Output columns

MANAGER_NAME: User-friendly Concurrent Manager name
MANAGER_SHORT_NAME: Internal Concurrent Manager short name
APPLICATION_NAME: Application that owns the program
PROGRAM_SHORT_NAME: Internal concurrent program name
PROGRAM_NAME: User concurrent program name

The standard Oracle managers are excluded so that the output focuses on custom managers.

Query 2: Detailed request execution history

Use the following query to display the request ID, manager, program, phase, status, start time, completion time, and runtime:

SELECT fcr.request_id,
       fcq.user_concurrent_queue_name manager_name,
       fcq.concurrent_queue_name manager_short_name,
       fav.application_name,
       fcp.concurrent_program_name program_short_name,
       fcp.user_concurrent_program_name program_name,
       fcr.actual_start_date,
       fcr.actual_completion_date,
       ROUND(
           (NVL(fcr.actual_completion_date, SYSDATE) -
            fcr.actual_start_date) * 24 * 60,
           2
       ) runtime_minutes,
       DECODE(fcr.phase_code,
              'P', 'Pending',
              'R', 'Running',
              'C', 'Completed',
              'I', 'Inactive',
              fcr.phase_code) phase,
       DECODE(fcr.status_code,
              'A', 'Waiting',
              'B', 'Resuming',
              'C', 'Normal',
              'D', 'Cancelled',
              'E', 'Error',
              'G', 'Warning',
              'H', 'On Hold',
              'I', 'Normal',
              'M', 'No Manager',
              'Q', 'Standby',
              'R', 'Normal',
              'S', 'Suspended',
              'T', 'Terminating',
              'U', 'Disabled',
              'W', 'Paused',
              'X', 'Terminated',
              'Z', 'Waiting',
              fcr.status_code) status
FROM apps.fnd_concurrent_requests fcr
JOIN apps.fnd_concurrent_processes fpr
  ON fpr.concurrent_process_id = fcr.controlling_manager
JOIN apps.fnd_concurrent_queues_vl fcq
  ON fcq.application_id = fpr.queue_application_id
 AND fcq.concurrent_queue_id = fpr.concurrent_queue_id
JOIN apps.fnd_concurrent_programs_vl fcp
  ON fcp.application_id = fcr.program_application_id
 AND fcp.concurrent_program_id = fcr.concurrent_program_id
JOIN apps.fnd_application_vl fav
  ON fav.application_id = fcr.program_application_id
WHERE fcr.actual_start_date >= SYSDATE - 30
  AND fcq.concurrent_queue_name NOT IN
      ('FNDICM', 'STANDARD', 'FNDCRM', 'FNDIM', 'FNDSM')
ORDER BY fcr.actual_start_date DESC;

This query displays the executions from the last 30 days.

Changing the reporting period

Last 24 hours

WHERE fcr.actual_start_date >= SYSDATE - 1

Last seven days

WHERE fcr.actual_start_date >= SYSDATE - 7

Last 30 days

WHERE fcr.actual_start_date >= SYSDATE - 30

Last 90 days

WHERE fcr.actual_start_date >= SYSDATE - 90

Last year

WHERE fcr.actual_start_date >= ADD_MONTHS(SYSDATE, -12)

Query 3: Manager-wise program execution count

The following query shows how many times each program was executed by each custom manager during the last 90 days:

SELECT fcq.user_concurrent_queue_name manager_name,
       fcq.concurrent_queue_name manager_short_name,
       fav.application_name,
       fcp.concurrent_program_name program_short_name,
       fcp.user_concurrent_program_name program_name,
       COUNT(*) execution_count,
       MIN(fcr.actual_start_date) first_execution,
       MAX(fcr.actual_start_date) last_execution,
       ROUND(
           AVG(
               (NVL(fcr.actual_completion_date, SYSDATE) -
                fcr.actual_start_date) * 24 * 60
           ),
           2
       ) average_runtime_minutes
FROM apps.fnd_concurrent_requests fcr
JOIN apps.fnd_concurrent_processes fpr
  ON fpr.concurrent_process_id = fcr.controlling_manager
JOIN apps.fnd_concurrent_queues_vl fcq
  ON fcq.application_id = fpr.queue_application_id
 AND fcq.concurrent_queue_id = fpr.concurrent_queue_id
JOIN apps.fnd_concurrent_programs_vl fcp
  ON fcp.application_id = fcr.program_application_id
 AND fcp.concurrent_program_id = fcr.concurrent_program_id
JOIN apps.fnd_application_vl fav
  ON fav.application_id = fcr.program_application_id
WHERE fcr.actual_start_date >= SYSDATE - 90
  AND fcq.concurrent_queue_name NOT IN
      ('FNDICM', 'STANDARD', 'FNDCRM', 'FNDIM', 'FNDSM')
GROUP BY
       fcq.user_concurrent_queue_name,
       fcq.concurrent_queue_name,
       fav.application_name,
       fcp.concurrent_program_name,
       fcp.user_concurrent_program_name
ORDER BY
       fcq.user_concurrent_queue_name,
       execution_count DESC;

This report is useful for:

Identifying which programs are processed by each custom manager
Understanding manager workload
Finding frequently executed programs
Reviewing average program runtime
Supporting Concurrent Manager capacity planning
Validating custom manager utilization before migration or cloning

Query 4: Show all managers, including the Standard Manager

Remove the custom-manager exclusion condition when you need the complete manager mapping.

Remove:

AND fcq.concurrent_queue_name NOT IN
    ('FNDICM', 'STANDARD', 'FNDCRM', 'FNDIM', 'FNDSM')

The output will then include requests processed by both seeded and custom Concurrent Managers.

Query 5: Find the manager for a specific request ID

When troubleshooting an individual concurrent request, use:

SELECT fcr.request_id,
       fcq.user_concurrent_queue_name manager_name,
       fcq.concurrent_queue_name manager_short_name,
       fpr.concurrent_process_id,
       fpr.os_process_id,
       fcp.user_concurrent_program_name program_name,
       fcr.actual_start_date,
       fcr.actual_completion_date
FROM apps.fnd_concurrent_requests fcr
JOIN apps.fnd_concurrent_processes fpr
  ON fpr.concurrent_process_id = fcr.controlling_manager
JOIN apps.fnd_concurrent_queues_vl fcq
  ON fcq.application_id = fpr.queue_application_id
 AND fcq.concurrent_queue_id = fpr.concurrent_queue_id
JOIN apps.fnd_concurrent_programs_vl fcp
  ON fcp.application_id = fcr.program_application_id
 AND fcp.concurrent_program_id = fcr.concurrent_program_id
WHERE fcr.request_id = &request_id;

The query prompts for the concurrent request ID and returns the manager and operating-system process information.

Why pending requests may not appear

Pending requests may not yet have a value in CONTROLLING_MANAGER. The manager is normally identified after the request is selected and processed.

Therefore, these queries are intended primarily for:

Running requests
Completed requests
Historical execution analysis

To determine which managers could potentially execute a pending request, the Concurrent Manager specialization rules must be evaluated separately.

Production safety

All queries in this article are read-only. They do not update Concurrent Manager definitions, concurrent requests, or specialization rules.

Before running a large historical query in production:

Restrict the query using ACTUAL_START_DATE.
Start with the last one, seven, or 30 days.
Avoid querying the complete request history during peak hours.
Export large results using SQL Developer, SQLcl, SQL*Plus, or an approved reporting tool.
Review the execution plan if the request tables contain substantial historical data.

Conclusion

It is possible to identify which concurrent program was executed by which custom Concurrent Manager in Oracle EBS 12.2.

The most reliable historical relationship is:

Concurrent Request
→ Controlling Manager Process
→ Concurrent Manager Queue
→ Concurrent Program

These queries show the manager that actually executed each request. They should not be interpreted as the complete specialization-rule configuration because a program can be eligible for multiple managers while its individual request is executed by only one manager.

How to Export Completed Oracle EBS Concurrent Programs by Daily, Weekly, Monthly, Quarterly, Half-Yearly, and Yearly Periods

Oracle E-Business Suite stores concurrent request execution details in the FND_CONCURRENT_REQUESTS table. Using a read-only SQL query, an Apps DBA can extract completed concurrent programs for a selected reporting period and export the results to Excel.

The report includes:

Request ID
Application and concurrent program
Requested-by user
Request and start times
Completion time
Execution duration
Completion status
Request arguments
Oracle session and operating system process IDs
Log and output file locations

Important distinction

This report returns concurrent requests completed during a selected period.

For example:

DAILY returns requests completed today.
WEEKLY returns requests completed during the current ISO week.
MONTHLY returns requests completed during the current month.
QUARTERLY returns requests completed during the current quarter.
HALFYEARLY returns requests completed during the current six-month period.
YEARLY returns requests completed during the current year.

It does not determine whether a concurrent program is itself scheduled to run daily, weekly, or monthly. Schedule-frequency analysis requires a separate query using request scheduling information and execution history.

Completed Concurrent Requests Query

Run the following query as the Oracle EBS APPS user:

DEFINE p_period = 'DAILY';

WITH period_dates AS
(
    SELECT
        CASE UPPER('&p_period')
            WHEN 'DAILY' THEN
                TRUNC(SYSDATE)
            WHEN 'WEEKLY' THEN
                TRUNC(SYSDATE, 'IW')
            WHEN 'MONTHLY' THEN
                TRUNC(SYSDATE, 'MM')
            WHEN 'QUARTERLY' THEN
                TRUNC(SYSDATE, 'Q')
            WHEN 'HALFYEARLY' THEN
                ADD_MONTHS(
                    TRUNC(SYSDATE, 'YYYY'),
                    CASE
                        WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) <= 6
                        THEN 0
                        ELSE 6
                    END
                )
            WHEN 'YEARLY' THEN
                TRUNC(SYSDATE, 'YYYY')
        END AS start_date,
        SYSDATE AS end_date
    FROM dual
)
SELECT
    UPPER('&p_period') AS report_period,
    d.start_date AS period_start,
    d.end_date AS period_end,
    r.request_id,
    a.application_name,
    p.concurrent_program_name AS program_short_name,
    p.user_concurrent_program_name,
    u.user_name AS requested_by,
    r.request_date,
    r.requested_start_date,
    r.actual_start_date,
    r.actual_completion_date,
    ROUND(
        (r.actual_completion_date - r.actual_start_date) * 24,
        2
    ) AS elapsed_hours,
    TRUNC(
        (r.actual_completion_date - r.actual_start_date) * 24
    ) || ':' ||
    LPAD(
        TRUNC(
            MOD(
                (r.actual_completion_date - r.actual_start_date) * 1440,
                60
            )
        ),
        2,
        '0'
    ) || ':' ||
    LPAD(
        TRUNC(
            MOD(
                (r.actual_completion_date - r.actual_start_date) * 86400,
                60
            )
        ),
        2,
        '0'
    ) AS elapsed_hh_mm_ss,
    DECODE(
        r.status_code,
        'C', 'Normal',
        'G', 'Warning',
        'E', 'Error',
        'X', 'Terminated',
        'D', 'Cancelled',
        r.status_code
    ) AS completion_status,
    r.argument_text,
    r.oracle_process_id AS os_process_id,
    r.oracle_session_id,
    r.logfile_name,
    r.outfile_name
FROM fnd_concurrent_requests r
JOIN fnd_concurrent_programs_vl p
  ON p.application_id = r.program_application_id
 AND p.concurrent_program_id = r.concurrent_program_id
JOIN fnd_application_vl a
  ON a.application_id = r.program_application_id
JOIN fnd_user u
  ON u.user_id = r.requested_by
CROSS JOIN period_dates d
WHERE r.phase_code = 'C'
  AND r.actual_completion_date >= d.start_date
  AND r.actual_completion_date <= d.end_date
ORDER BY r.actual_completion_date DESC;

Selecting the Reporting Period

Change the value of p_period before executing the query.

Daily report

DEFINE p_period = 'DAILY';

This returns requests completed from midnight today until the current time.

Weekly report

DEFINE p_period = 'WEEKLY';

This uses the ISO week, starting on Monday.

Monthly report

DEFINE p_period = 'MONTHLY';

This returns requests completed from the first day of the current month.

Quarterly report

DEFINE p_period = 'QUARTERLY';

This returns requests completed from the beginning of the current calendar quarter.

Half-yearly report

DEFINE p_period = 'HALFYEARLY';

The reporting periods are:

January through June
July through December

Yearly report

DEFINE p_period = 'YEARLY';

This returns requests completed from January 1 of the current year.

Completion Status Filters

The condition below includes every request whose phase is Completed:

WHERE r.phase_code = 'C'

A completed phase can contain requests that ended with Normal, Warning, Error, Terminated, or Cancelled status.

Successfully completed requests only

Add the following condition:

AND r.status_code = 'C'

Normal, Warning, and Error requests

Use:

AND r.status_code IN ('C', 'G', 'E')

Error requests only

Use:

AND r.status_code = 'E'

Warning requests only

Use:

AND r.status_code = 'G'

Understanding the Execution Duration

The report provides duration in two formats.

Decimal hours

The ELAPSED_HOURS column displays the runtime in hours:

2.50

This represents two hours and thirty minutes.

Hours, minutes, and seconds

The ELAPSED_HH_MM_SS column displays duration as:

02:30:00

This format is useful when reviewing long-running concurrent requests.

Exporting the Results to Excel

In Oracle SQL Developer:

Execute the query.
Right-click anywhere in the query result grid.
Select Export.
Select Excel 2007+ (.xlsx) as the output format.
Enable Include Column Headers.
Select the destination file.
Click Next, followed by Finish.

The results can be maintained in separate Excel worksheets:

Daily
Weekly
Monthly
Quarterly
Half-Yearly
Yearly

Production Safety

The query is read-only and does not update Oracle EBS data.

However, yearly reports may retrieve a large number of records from a busy production environment. Consider the following precautions:

Run large reports outside peak business hours.
Test the query in a non-production environment first.
Use a specific date range when the request history is extensive.
Avoid opening millions of rows directly in Excel.
Export large results to CSV when necessary.

An Excel worksheet supports a maximum of 1,048,576 rows. If the report exceeds this limit, use CSV files or divide the report into smaller date ranges.

Conclusion

This query provides a production-safe method to extract completed Oracle EBS concurrent requests for daily, weekly, monthly, quarterly, half-yearly, and yearly reporting periods. The output can be exported directly to Excel for operational reporting, performance analysis, audit review, and identification of long-running or failed concurrent requests.

Thursday, September 3, 2026

Sunday, August 30, 2026

Workflow at a Glance

Prerequisites & Pre-Checks

Phase 1 — Source Database: Capture and Export

Step 1: Identify the Good Plan

Step 2: Create and Populate the SQL Tuning Set

Step 3: Pack the STS into a Staging Table

Step 4: Export and Transfer

Phase 2 — Target Database: Import and Enforce

Step 5: Import the Staging Table

Step 6: Unpack the SQL Tuning Set

Step 7: Load the Plan as an Accepted Baseline

Step 8: Purge the Bad Cursor from the Shared Pool

Step 9: Verify the Baseline Is Actually Used

Practical Notes

Summary

Wednesday, August 26, 2026

1. List All EBS Tables with Supplemental Log Groups

2. Detailed Column-Level Supplemental Logging Breakdown

3. Aggregated Summary by EBS Schema / Module

Quick Reference: Understanding LOG_GROUP_TYPE Values

Tuesday, August 25, 2026

Oracle EBS Concurrent Request Stuck in Pending Standby – Conflict Resolution Manager Troubleshooting

1. Typical Symptom

2. What Does Pending / Standby Mean?

3. Check the Concurrent Request

4. Identify the Concurrent Program

5. Check Conflict Resolution Manager Status

6. Check How Many Requests Are in Pending / Standby

7. List All Pending / Standby Requests

8. Find the Oldest Standby Request

9. Check Concurrent Program Incompatibilities

10. Find Currently Running Incompatible Requests

11. Check Previous Executions of the Same Program

12. Check Whether the Request Is on Hold

13. Check Concurrent Manager Capacity

14. Check Conflict Resolution Manager at OS Level

15. Locate the CRM Log

16. Check ICM and CRM Together

17. Useful Summary Query

18. Troubleshooting Decision Tree

19. Scenario 1 – Running Incompatible Request Found

20. Scenario 2 – No Incompatible Request Found

21. Scenario 3 – Many Requests in Pending / Standby

22. Scenario 4 – Only a Few Standby Requests

Oracle E-Business Suite 12.2: Concurrent Request Performance Troubleshooting and Essential Diagnostic SQL

1. Investigation Flow for a Slow or Hanging Concurrent Request

2. Check the Current Concurrent Request Status

3. Map the Request to Its Database Session and SQL

4. Review the Current Wait Event

5. Identify Blocking Sessions

6. Compare Runtime with Previous Executions

7. Tracing a Concurrent Request

Option A: Enable Trace on the Concurrent Program

Find the Trace File on Oracle 19c

Option B: Initialization SQL Statement – Custom

Option C: Trace a Reproducible SQL*Plus Test

Format the Trace with TKPROF

8. Patch and File-Version Diagnostics

Identify the Patch That Delivered a Specific File Version

Check Whether an EBS Patch Is Recorded

Check the EBS Release

Check Database Component Versions

Inspect Package Header and Selected Source Lines

9. Index and Statistics Checks

List Index Columns

Check When Table Statistics Were Gathered

Gather EBS Table Statistics

10. Retrieve Profile Option Values

11. Materialized View Refresh

12. Useful UNIX Commands

Find a File Version Embedded in an Executable

List All Embedded File Headers

Check Soft and Hard Resource Limits

Compare an ODF Object with the Database

13. Relinking: Use Only Through an Approved Change

Recommended Evidence to Capture for an RCA

Conclusion

Saturday, August 22, 2026

Quick Reference: Understanding `LOG_GROUP_TYPE` Values