Tuesday, September 8, 2026

Oracle EBS Pending / Standby Concurrent Requests – Deep Dive Troubleshooting, SQL Queries and RCA

Oracle EBS Pending / Standby Concurrent Requests – Deep Dive Troubleshooting, SQL Queries and RCA

Oracle E-Business Suite concurrent requests can sometimes remain in Pending / Standby status even when the Standard Concurrent Managers are healthy and sufficient manager processes are available.

The most important point when troubleshooting this condition is:

Pending / Standby is usually a Concurrent Processing conflict-resolution condition, not a database blocking condition.

A request may be placed into Standby because of:

  • Concurrent program incompatibility
  • Global or conflict-domain incompatibility
  • Run Alone program rules
  • Self-incompatibility
  • A long-running incompatible request
  • A stale Running request
  • Conflict Resolution Manager / Internal Concurrent Manager problems

This article provides a production-friendly, read-only diagnostic workflow for identifying the actual root cause.

1. Understanding Pending / Standby

The main table used to troubleshoot Concurrent Processing requests is: 

FND_CONCURRENT_REQUESTS

A classic Pending / Standby request normally looks like:

Column Value Meaning
PHASE_CODE P Pending
STATUS_CODE Q Standby
HOLD_FLAG N Request is not manually held
ACTUAL_START_DATE NULL Request has never started

Therefore, one of the most important signatures is: 

PHASE_CODE  = 'P'
STATUS_CODE = 'Q'
HOLD_FLAG   = 'N'

2. Important RCA Principle

Pending Request
PHASE_CODE       = P
STATUS_CODE      = Q
ACTUAL_START_DATE = NULL

The request has never started.

Therefore:

The Pending request itself cannot currently be blocked
by a database lock because it has no executing DB session.

The first investigation belongs in:

Oracle EBS Concurrent Processing

not:

DBA_BLOCKERS / GV$LOCK / database locking.

Database troubleshooting becomes relevant only after identifying the currently Running request that is preventing the Standby request from starting.

3. Phase and Status Decode Reference

Phase Code Meaning Status Code Meaning
P Pending Q Standby
R Running R Normal
C Completed C Normal
I Inactive F Scheduled
H On Hold
M No Manager
E Error
G Warning

For an environment-specific lookup, the following queries can also be used.

Concurrent Request Status Codes

SELECT lookup_code,
       meaning
FROM   fnd_lookup_values
WHERE  lookup_type = 'CP_STATUS_CODE'
AND    language = USERENV('LANG')
AND    enabled_flag = 'Y'
AND    view_application_id = 0
ORDER BY lookup_code;

Concurrent Request Phase Codes

SELECT lookup_code,
       meaning
FROM   fnd_lookup_values
WHERE  lookup_type = 'CP_PHASE_CODE'
AND    language = USERENV('LANG')
AND    enabled_flag = 'Y'
AND    view_application_id = 0
ORDER BY lookup_code;

4. Step 1 – Complete Snapshot of the Pending Request

Start every investigation with the affected Request ID. 

SELECT r.request_id,
       r.phase_code,
       DECODE(r.phase_code,
              'P','Pending',
              'R','Running',
              'C','Completed',
              'I','Inactive',
              r.phase_code) phase,
       r.status_code,
       DECODE(r.status_code,
              'A','Waiting',
              'B','Resuming',
              'C','Normal',
              'D','Cancelled',
              'E','Error',
              'F','Scheduled',
              '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',
              r.status_code) status,
       r.hold_flag,
       r.queue_method_code,
       r.single_thread_flag,
       cp.run_alone_flag,
       r.cd_id,
       fcd.cd_name conflict_domain,
       r.parent_request_id,
       r.controlling_manager,
       r.oracle_session_id,
       r.oracle_process_id,
       r.os_process_id,
       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,
       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
       JOIN fnd_concurrent_programs cp
         ON cp.application_id = r.program_application_id
        AND cp.concurrent_program_id = r.concurrent_program_id
       LEFT JOIN fnd_conflicts_domain fcd
         ON fcd.cd_id = r.cd_id
WHERE  r.request_id = :REQUEST_ID;

5. Understanding QUEUE_METHOD_CODE

This column is extremely important for Pending / Standby troubleshooting.

QUEUE_METHOD_CODE Meaning Interpretation
I Unconstrained Normally does not require Conflict Resolution processing before being eligible for an appropriate Concurrent Manager.
B Constrained Request is subject to Concurrent Processing conflict resolution.
B + STATUS Q Constrained + Standby Classic conflict-resolution troubleshooting branch.
Important: Do not reverse these values. B represents a constrained request and I represents an unconstrained request.

6. Run Alone vs SINGLE_THREAD_FLAG

Another important distinction:

Column Purpose
FND_CONCURRENT_PROGRAMS.RUN_ALONE_FLAG Program-level Run Alone definition
FND_CONCURRENT_REQUESTS.SINGLE_THREAD_FLAG Separate request-level serialization characteristic

Therefore: 

RUN_ALONE_FLAG = Y

means:

Program is defined as Run Alone.

Do not treat SINGLE_THREAD_FLAG as a synonym for Run Alone.

7. Step 2 – Identify the Program Definition

SELECT r.request_id,
       r.program_application_id,
       r.concurrent_program_id,
       cp.concurrent_program_name short_name,
       cpt.user_concurrent_program_name program_name,
       r.queue_method_code request_queue_method,
       cp.queue_method_code program_queue_method,
       cp.run_alone_flag,
       r.single_thread_flag,
       cp.enabled_flag,
       r.cd_id
FROM   fnd_concurrent_requests r
       JOIN fnd_concurrent_programs cp
         ON cp.application_id = r.program_application_id
        AND cp.concurrent_program_id = r.concurrent_program_id
       JOIN fnd_concurrent_programs_tl cpt
         ON cpt.application_id = cp.application_id
        AND cpt.concurrent_program_id = cp.concurrent_program_id
        AND cpt.language = USERENV('LANG')
WHERE  r.request_id = :REQUEST_ID;

Record:

  • PROGRAM_APPLICATION_ID
  • CONCURRENT_PROGRAM_ID
  • QUEUE_METHOD_CODE
  • RUN_ALONE_FLAG
  • SINGLE_THREAD_FLAG
  • CD_ID

8. What Is a Conflict Domain?

Oracle EBS uses Conflict Domains to limit certain incompatibility rules to related groups of requests.

The request's conflict domain is represented by: 

FND_CONCURRENT_REQUESTS.CD_ID

Domain information can be obtained from: 

FND_CONFLICTS_DOMAIN

This is important because a Domain-scoped incompatibility does not necessarily block requests running in a different conflict domain.

9. Where Are Concurrent Program Incompatibilities Stored?

Program incompatibilities are recorded primarily in: 

FND_CONCURRENT_PROGRAM_SERIAL

Important columns include:

  • RUNNING_APPLICATION_ID
  • RUNNING_CONCURRENT_PROGRAM_ID
  • TO_RUN_APPLICATION_ID
  • TO_RUN_CONCURRENT_PROGRAM_ID
  • INCOMPATIBILITY_TYPE

10. Global vs Domain Incompatibility

INCOMPATIBILITY_TYPE Meaning Blocking Rule
G Global Conflict applies regardless of conflict domain
D Domain-specific Conflict applies when the relevant requests share the conflict domain

11. Step 3 – Find Incompatibility Definitions

The relationship should be checked in both directions. 

WITH target AS
(
    SELECT request_id,
           program_application_id app_id,
           concurrent_program_id prog_id,
           cd_id
    FROM   fnd_concurrent_requests
    WHERE  request_id = :REQUEST_ID
),
incompat AS
(
    SELECT s.running_application_id app_id,
           s.running_concurrent_program_id prog_id,
           s.incompatibility_type
    FROM   fnd_concurrent_program_serial s
           JOIN target t
             ON s.to_run_application_id = t.app_id
            AND s.to_run_concurrent_program_id = t.prog_id

    UNION

    SELECT s.to_run_application_id,
           s.to_run_concurrent_program_id,
           s.incompatibility_type
    FROM   fnd_concurrent_program_serial s
           JOIN target t
             ON s.running_application_id = t.app_id
            AND s.running_concurrent_program_id = t.prog_id
)
SELECT i.app_id,
       i.prog_id,
       cpt.user_concurrent_program_name incompatible_program,
       i.incompatibility_type,
       DECODE(i.incompatibility_type,
              'G','Global',
              'D','Domain',
              i.incompatibility_type) scope,
       CASE
         WHEN i.app_id = t.app_id
          AND i.prog_id = t.prog_id
         THEN 'SELF-INCOMPATIBLE'
       END self_incompatibility
FROM   incompat i
       CROSS JOIN target t
       JOIN fnd_concurrent_programs_tl cpt
         ON cpt.application_id = i.app_id
        AND cpt.concurrent_program_id = i.prog_id
        AND cpt.language = USERENV('LANG')
ORDER BY cpt.user_concurrent_program_name;
Important: An incompatibility definition alone does not prove that the program is currently blocking the request. The incompatible program must also be in an applicable Running state.

12. Step 4 – Core RCA Query: Find the Actual Running Blocker

This is the most important query in the troubleshooting workflow. 

WITH target AS
(
    SELECT request_id,
           program_application_id app_id,
           concurrent_program_id prog_id,
           cd_id
    FROM   fnd_concurrent_requests
    WHERE  request_id = :REQUEST_ID
),
incompat AS
(
    SELECT s.running_application_id app_id,
           s.running_concurrent_program_id prog_id,
           s.incompatibility_type
    FROM   fnd_concurrent_program_serial s
           JOIN target t
             ON s.to_run_application_id = t.app_id
            AND s.to_run_concurrent_program_id = t.prog_id

    UNION

    SELECT s.to_run_application_id,
           s.to_run_concurrent_program_id,
           s.incompatibility_type
    FROM   fnd_concurrent_program_serial s
           JOIN target t
             ON s.running_application_id = t.app_id
            AND s.running_concurrent_program_id = t.prog_id
)
SELECT r.request_id blocking_request_id,
       cpt.user_concurrent_program_name blocking_program,
       fu.user_name submitted_by,
       DECODE(i.incompatibility_type,
              'G','Global',
              'D','Domain',
              i.incompatibility_type) scope,
       r.cd_id,
       fcd.cd_name conflict_domain,
       cp.run_alone_flag,
       r.single_thread_flag,
       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,
       ROUND((SYSDATE-r.actual_start_date)*24,2)
           running_hours,
       ROUND((SYSDATE-r.actual_start_date),2)
           running_days,
       r.oracle_session_id,
       r.oracle_process_id,
       r.os_process_id
FROM   fnd_concurrent_requests r
       JOIN incompat i
         ON r.program_application_id = i.app_id
        AND r.concurrent_program_id = i.prog_id
       CROSS JOIN target t
       JOIN fnd_concurrent_programs cp
         ON cp.application_id = r.program_application_id
        AND cp.concurrent_program_id = r.concurrent_program_id
       JOIN fnd_concurrent_programs_tl cpt
         ON cpt.application_id = r.program_application_id
        AND cpt.concurrent_program_id = r.concurrent_program_id
        AND cpt.language = USERENV('LANG')
       JOIN fnd_user fu
         ON fu.user_id = r.requested_by
       LEFT JOIN fnd_conflicts_domain fcd
         ON fcd.cd_id = r.cd_id
WHERE  r.phase_code = 'R'
AND    r.request_id <> t.request_id
AND   (
          i.incompatibility_type = 'G'
          OR
          (
             i.incompatibility_type = 'D'
             AND r.cd_id = t.cd_id
          )
      )
ORDER BY r.actual_start_date;

How to Read the Output

Rows returned

    An incompatible request is currently Running.
    Investigate that request.

Same program returned

    Possible self-incompatibility.

No rows returned

    No explicit incompatible request is currently blocking.

    Next check:
       Run Alone
       CRM / ICM
       stale conflict state
       self-incompatibility
       other serialization rules

13. Example RCA Pattern

Target Request
--------------
Request ID : 900200100
Program    : AP/PO Purge Abort Routine
Phase      : P
Status     : Q
Hold       : N

             |
             | incompatibility
             v

Running Request
---------------
Request ID : 900180001
Program    : Payables Approval
Phase      : R
Status     : R
Runtime    : 26 Days

The correct RCA direction is therefore: 

Do NOT primarily troubleshoot the Pending request.

Investigate the RUNNING incompatible request.

14. Step 5 – Detect Run Alone Programs

Check whether the target program itself is defined as Run Alone: 

SELECT r.request_id,
       cpt.user_concurrent_program_name program_name,
       cp.run_alone_flag,
       r.cd_id,
       fcd.cd_name conflict_domain
FROM   fnd_concurrent_requests r
       JOIN fnd_concurrent_programs cp
         ON cp.application_id = r.program_application_id
        AND cp.concurrent_program_id = r.concurrent_program_id
       JOIN fnd_concurrent_programs_tl cpt
         ON cpt.application_id = cp.application_id
        AND cpt.concurrent_program_id = cp.concurrent_program_id
        AND cpt.language = USERENV('LANG')
       LEFT JOIN fnd_conflicts_domain fcd
         ON fcd.cd_id = r.cd_id
WHERE  r.request_id = :REQUEST_ID;

If: 

RUN_ALONE_FLAG = Y

investigate other currently running requests in the applicable conflict domain.

15. Find Running Run Alone Requests in the Target Domain

WITH target AS
(
    SELECT request_id,
           cd_id
    FROM   fnd_concurrent_requests
    WHERE  request_id = :REQUEST_ID
)
SELECT r.request_id,
       cpt.user_concurrent_program_name program_name,
       fu.user_name submitted_by,
       cp.run_alone_flag,
       r.cd_id,
       fcd.cd_name conflict_domain,
       TO_CHAR(r.actual_start_date,
               'DD-MON-YYYY HH24:MI:SS') actual_start_date,
       ROUND((SYSDATE-r.actual_start_date)*24,2)
           running_hours
FROM   fnd_concurrent_requests r
       JOIN fnd_concurrent_programs cp
         ON cp.application_id = r.program_application_id
        AND cp.concurrent_program_id = r.concurrent_program_id
       JOIN fnd_concurrent_programs_tl cpt
         ON cpt.application_id = cp.application_id
        AND cpt.concurrent_program_id = cp.concurrent_program_id
        AND cpt.language = USERENV('LANG')
       JOIN fnd_user fu
         ON fu.user_id = r.requested_by
       LEFT JOIN fnd_conflicts_domain fcd
         ON fcd.cd_id = r.cd_id
       CROSS JOIN target t
WHERE  r.phase_code = 'R'
AND    cp.run_alone_flag = 'Y'
AND    r.cd_id = t.cd_id
AND    r.request_id <> t.request_id
ORDER BY r.actual_start_date;

16. Step 6 – Self-Incompatibility Pile-Up

A very common operational issue occurs when a concurrent program is incompatible with itself and is submitted more frequently than it can complete.

Example:

Program scheduled every 15 minutes

Typical runtime = 45 minutes

Result:

Request 1 = Running
Request 2 = Pending / Standby
Request 3 = Pending / Standby
Request 4 = Pending / Standby

Query

SELECT r.request_id,
       r.phase_code,
       r.status_code,
       fu.user_name submitted_by,
       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,
       r.resubmit_interval,
       r.resubmit_interval_unit_code
FROM   fnd_concurrent_requests r
       JOIN fnd_user fu
         ON fu.user_id = r.requested_by
WHERE  (r.program_application_id,
        r.concurrent_program_id) =
       (
          SELECT program_application_id,
                 concurrent_program_id
          FROM   fnd_concurrent_requests
          WHERE  request_id = :REQUEST_ID
       )
AND    r.phase_code IN ('P','R')
ORDER BY DECODE(r.phase_code,'R',1,2),
         r.request_date;

Typical RCA

1 request = Running

Multiple requests = Pending / Standby

Schedule interval < actual runtime

Root Cause:
Scheduling frequency does not match program runtime.

Possible corrective actions:
Tune program performance
or
Increase scheduling interval.

17. Step 7 – Investigate the Running Blocking Request

SELECT request_id,
       phase_code,
       status_code,
       oracle_session_id,
       oracle_process_id,
       os_process_id,
       TO_CHAR(request_date,
               'DD-MON-YYYY HH24:MI:SS') request_date,
       TO_CHAR(actual_start_date,
               'DD-MON-YYYY HH24:MI:SS') actual_start_date,
       ROUND((SYSDATE-actual_start_date)*24*60,2)
           running_minutes,
       ROUND((SYSDATE-actual_start_date)*24,2)
           running_hours,
       ROUND((SYSDATE-actual_start_date),2)
           running_days
FROM   fnd_concurrent_requests
WHERE  request_id = :BLOCKING_REQUEST_ID;

A long-running request does not automatically mean it is hung.

Before any cancellation, determine:

  • Does the database session still exist?
  • Does the Apps-tier OS process still exist?
  • Is SQL actively executing?
  • What SQL_ID is running?
  • What wait event is occurring?
  • Is there a database blocker?
  • Is the program intentionally long-running?

18. Step 8 – Conflict Resolution Manager Health

The internal Concurrent Queue name is: 

FNDCRM

Using the internal name avoids dependency on translated display names. 

SELECT q.concurrent_queue_name,
       q.user_concurrent_queue_name,
       q.enabled_flag,
       q.max_processes,
       q.running_processes,
       q.control_code,
       p.concurrent_process_id,
       p.process_status_code,
       p.node_name,
       p.os_process_id,
       TO_CHAR(p.process_start_date,
               'DD-MON-YYYY HH24:MI:SS') process_start_date
FROM   fnd_concurrent_queues q
       LEFT JOIN fnd_concurrent_processes p
         ON p.concurrent_queue_id = q.concurrent_queue_id
        AND p.queue_application_id = q.application_id
        AND p.process_status_code = 'A'
WHERE  q.application_id = 0
AND    q.concurrent_queue_name = 'FNDCRM';

Interpretation

Observation Interpretation
Active FNDCRM process exists Dedicated CRM is active
MAX_PROCESSES > 0 but no active process Investigate CRM health
MAX_PROCESSES = 0 Check whether conflict resolution is delegated to ICM
Large number of P/Q requests with no active blockers Possible conflict reevaluation / CRM / ICM problem

19. Check Concurrent: Use ICM

The profile determines whether conflict resolution may be handled by the Internal Concurrent Manager instead of a dedicated CRM. 

SELECT fpo.profile_option_name,
       fpot.user_profile_option_name,
       fpov.level_id,
       fpov.level_value,
       fpov.profile_option_value
FROM   fnd_profile_options fpo
       JOIN fnd_profile_options_tl fpot
         ON fpot.profile_option_name = fpo.profile_option_name
        AND fpot.language = USERENV('LANG')
       JOIN fnd_profile_option_values fpov
         ON fpov.profile_option_id = fpo.profile_option_id
WHERE  fpot.user_profile_option_name =
       'Concurrent: Use ICM';

20. Step 9 – RAC-Safe EBS Request to Database Session Mapping

SELECT fcr.request_id,
       s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.status session_status,
       s.sql_id,
       s.prev_sql_id,
       s.event,
       s.wait_class,
       s.state,
       s.seconds_in_wait,
       s.last_call_et,
       s.blocking_instance,
       s.blocking_session,
       p.spid db_os_pid
FROM   fnd_concurrent_requests fcr
       LEFT JOIN gv$session s
         ON s.audsid = fcr.oracle_session_id
       LEFT JOIN gv$process p
         ON p.addr = s.paddr
        AND p.inst_id = s.inst_id
WHERE  fcr.request_id = :BLOCKING_REQUEST_ID;

Important Process Mapping

Column Meaning
ORACLE_SESSION_ID Used to correlate the request with the Oracle database session
ORACLE_PROCESS_ID Database-tier Oracle process information
OS_PROCESS_ID Apps-tier OS process associated with Concurrent Processing

21. How to Interpret the Session

Observation Meaning Next Action
ACTIVE + SQL_ID Program is actively executing SQL Investigate SQL
Session waiting Database/resource wait Check event and wait class
BLOCKING_SESSION populated Database blocking exists Investigate blocking chain
Long idle time Possible application wait/hang Review log and OS process
No database session Possible stale request Validate DB and Apps-tier processes

22. Step 10 – Current SQL of the Blocking Request

SELECT inst_id,
       sql_id,
       child_number,
       plan_hash_value,
       executions,
       ROUND(elapsed_time/1000000,2) elapsed_seconds,
       ROUND(cpu_time/1000000,2) cpu_seconds,
       buffer_gets,
       disk_reads,
       rows_processed,
       SUBSTR(sql_text,1,1000) sql_text
FROM   gv$sql
WHERE  sql_id = :SQL_ID
ORDER BY inst_id,
         child_number;

For long-running requests, compare the SQL plan, execution statistics, I/O and historical runtime with normal executions.

23. Step 11 – Check the Database Wait Event

SELECT inst_id,
       sid,
       serial#,
       status,
       sql_id,
       event,
       wait_class,
       state,
       seconds_in_wait,
       blocking_instance,
       blocking_session
FROM   gv$session
WHERE  sid = :SID
AND    inst_id = :INST_ID;

Typical wait classes that may require investigation include:

  • User I/O
  • System I/O
  • Application
  • Concurrency
  • Commit
  • Network
  • Configuration

24. Step 12 – Check Database Blocking

SELECT inst_id,
       sid,
       serial#,
       username,
       status,
       sql_id,
       event,
       blocking_instance,
       blocking_session
FROM   gv$session
WHERE  blocking_session IS NOT NULL
ORDER BY inst_id,
         sid;
Remember:

Concurrent Program Incompatibility is not the same as Database Locking.

A request can be Pending / Standby even when there are absolutely no database blocking sessions.

25. Step 13 – Validate a Possible Stale / Ghost Running Request

A serious condition occurs when EBS continues to show a request as Running even though the underlying DB or Apps-tier process has disappeared.

Check Database Process

SELECT inst_id,
       spid,
       program,
       tracefile
FROM   gv$process
WHERE  spid = TO_CHAR(:ORACLE_PROCESS_ID)
AND   (:INST_ID IS NULL OR inst_id = :INST_ID);

Apps-Tier Validation

ps -ef | grep <OS_PROCESS_ID>

Potential Ghost Request Signature

EBS Request = Running

but

No GV$SESSION
No GV$PROCESS
No Apps-tier OS process

Potential Result:

Stale / Ghost Concurrent Request

Do not manually update FND_CONCURRENT_REQUESTS to change the status. Follow approved Oracle EBS Concurrent Processing recovery procedures.

26. Parent / Child Request Investigation

Some Pending conditions are associated with parent request sets or child dependencies. 

SELECT request_id,
       parent_request_id,
       phase_code,
       status_code,
       hold_flag,
       requested_start_date,
       actual_start_date
FROM   fnd_concurrent_requests
WHERE  request_id = :REQUEST_ID
OR     parent_request_id = :REQUEST_ID
ORDER BY request_id;

27. Check All Requests for the Same Program

SELECT r.request_id,
       r.phase_code,
       r.status_code,
       fu.user_name,
       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
       JOIN fnd_user fu
         ON fu.user_id = r.requested_by
WHERE  (r.program_application_id,
        r.concurrent_program_id) =
       (
          SELECT program_application_id,
                 concurrent_program_id
          FROM   fnd_concurrent_requests
          WHERE  request_id = :REQUEST_ID
       )
ORDER BY r.request_id DESC;

This helps identify:

  • Repeated submissions
  • A self-incompatible running request
  • Scheduler pile-ups
  • Multiple Abort requests
  • Unexpected overlapping jobs

28. Find All Pending / Standby Requests

SELECT r.request_id,
       cpt.user_concurrent_program_name program_name,
       fu.user_name submitted_by,
       r.queue_method_code,
       cp.run_alone_flag,
       r.single_thread_flag,
       r.cd_id,
       fcd.cd_name conflict_domain,
       r.parent_request_id,
       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,2)
           waiting_minutes,
       ROUND((SYSDATE-r.request_date)*24,2)
           waiting_hours
FROM   fnd_concurrent_requests r
       JOIN fnd_concurrent_programs cp
         ON cp.application_id = r.program_application_id
        AND cp.concurrent_program_id = r.concurrent_program_id
       JOIN fnd_concurrent_programs_tl cpt
         ON cpt.application_id = r.program_application_id
        AND cpt.concurrent_program_id = r.concurrent_program_id
        AND cpt.language = USERENV('LANG')
       JOIN fnd_user fu
         ON fu.user_id = r.requested_by
       LEFT JOIN fnd_conflicts_domain fcd
         ON fcd.cd_id = r.cd_id
WHERE  r.phase_code = 'P'
AND    r.status_code = 'Q'
AND    r.hold_flag = 'N'
ORDER BY r.request_date;

29. Monitoring Query – Pending / Standby Older Than Two Hours

SELECT r.request_id,
       cpt.user_concurrent_program_name program_name,
       fu.user_name submitted_by,
       r.queue_method_code,
       cp.run_alone_flag,
       r.single_thread_flag,
       r.cd_id,
       fcd.cd_name conflict_domain,
       TO_CHAR(r.request_date,
               'DD-MON-YYYY HH24:MI:SS') request_date,
       ROUND((SYSDATE-r.request_date)*24*60,2)
           waiting_minutes,
       ROUND((SYSDATE-r.request_date)*24,2)
           waiting_hours
FROM   fnd_concurrent_requests r
       JOIN fnd_concurrent_programs cp
         ON cp.application_id = r.program_application_id
        AND cp.concurrent_program_id = r.concurrent_program_id
       JOIN fnd_concurrent_programs_tl cpt
         ON cpt.application_id = r.program_application_id
        AND cpt.concurrent_program_id = r.concurrent_program_id
        AND cpt.language = USERENV('LANG')
       JOIN fnd_user fu
         ON fu.user_id = r.requested_by
       LEFT JOIN fnd_conflicts_domain fcd
         ON fcd.cd_id = r.cd_id
WHERE  r.phase_code = 'P'
AND    r.status_code = 'Q'
AND    r.hold_flag = 'N'
AND    r.request_date < SYSDATE - (2/24)
ORDER BY r.request_date;

30. Fleet-Level Pattern Recognition

Pattern Likely RCA Direction
One Pending request + one Running incompatible request Normal incompatibility or long-running blocker
Many instances of same program in P/Q Self-incompatibility / scheduler pile-up
Many different programs in same CD_ID Run Alone or long-running domain blocker
Many constrained requests stuck across multiple domains Check CRM / ICM health
P/Q request but no current blocker exists Conflict reevaluation, CRM/ICM or stale state investigation
EBS blocker shows Running but no DB/OS process exists Potential ghost/stale request

31. Full Pending / Standby Decision Tree

Concurrent Request Not Starting
             |
             v
Check FND_CONCURRENT_REQUESTS
             |
             v
PHASE_CODE = P ?
             |
             v
STATUS_CODE = Q ?
             |
        +----+----+
        |         |
       No        Yes
        |         |
        |         v
        |    HOLD_FLAG = N ?
        |         |
        |         v
        |   QUEUE_METHOD_CODE
        |         |
        |    +----+----+
        |    |         |
        |    I         B
        |    |         |
        |    v         v
        | Unexpected  Constrained Request
        | P/Q state       |
        |                 v
        |        Check Program Incompatibility
        |                 |
        |                 v
        |        Find Running Blocker
        |                 |
        |            +----+----+
        |            |         |
        |          Found      None
        |            |         |
        |            v         v
        |      Same program?   Check Run Alone
        |        /       \          |
        |      Yes        No         v
        |       |          |       CRM / ICM
        |       v          v         |
        | Self-incompat  Investigate |
        | scheduling     blocker     |
        |                  |         |
        |                  v         |
        |            GV$SESSION      |
        |                  |         |
        |          +-------+-------+ |
        |          |       |       | |
        |        SQL     WAIT    No Session
        |          |       |       |
        |          v       v       v
        |      SQL RCA  DB RCA   Ghost/Stale
        |
        v
Other Pending Status RCA

32. Pending Status Troubleshooting Matrix

Phase Status Primary Investigation
Pending Standby Conflict resolution, incompatibility, Run Alone, CRM/ICM
Pending Normal Manager availability, specialization, work shift
Pending Scheduled Requested Start Date
Pending On Hold Request hold or parent request
Pending No Manager Manager definition, specialization or availability
Running Normal DB session, SQL, waits and runtime if abnormal

33. Why Restarting Standard Concurrent Managers May Not Fix P/Q

A common response to any Pending request is restarting Concurrent Managers.

However: 

Program A = Running

Program B = incompatible with Program A

Program B = Pending / Standby

Restart Standard Managers

Program A is still logically blocking Program B

Result:

Program B can remain Pending / Standby.

Therefore, determine the conflict before restarting managers.

34. Why Adding More Manager Processes May Not Fix P/Q

If the problem is an incompatibility: 

Current Standard Manager Processes = 10

Increase to 20

Does this remove an incompatibility?

NO.

Manager capacity and conflict resolution are different problems.

35. Why Repeatedly Submitting the Program May Make Things Worse

Request 1 = Pending / Standby

User submits Request 2

Request 2 = Pending / Standby

User submits Request 3

Request 3 = Pending / Standby

This increases the queue without removing the root cause.

Correct approach: 

Find the Running incompatible request
              |
              v
Determine why it is still Running
              |
              v
Resolve the blocker
              |
              v
Allow Pending requests to be reevaluated

36. Sample RCA – Pending / Standby Due to Long-Running Incompatible Program

Incident Concurrent request remained Pending / Standby
Phase / Status P / Q
Hold Flag N
Actual Start Date NULL
Immediate Cause A configured incompatible concurrent program was still Running
Contributing Condition The incompatible request had been Running significantly longer than expected
Database Blocking Must be investigated against the Running blocker, not the Pending request
Concurrent Manager Capacity Not the primary root cause
Resolution Investigate and resolve the Running incompatible request using approved procedures
Preventive Action Monitor aged Running blockers and aged Pending / Standby requests

37. Sample RCA Statement

The affected Oracle EBS concurrent request remained in Pending / Standby because it was subject to Concurrent Processing conflict resolution. Investigation identified another concurrently running program that was configured as incompatible with the target program. Oracle EBS therefore prevented the target request from starting. The investigation was subsequently redirected to the long-running incompatible request to determine whether it was actively processing, waiting on a database resource, blocked by another session, or represented a stale Concurrent Processing state.

38. Production Apps DBA Checklist

  1. Check Request ID.
  2. Confirm PHASE_CODE.
  3. Confirm STATUS_CODE.
  4. Check HOLD_FLAG.
  5. Check REQUESTED_START_DATE.
  6. Check ACTUAL_START_DATE.
  7. Check QUEUE_METHOD_CODE.
  8. Check RUN_ALONE_FLAG.
  9. Check SINGLE_THREAD_FLAG separately.
  10. Check CD_ID.
  11. Identify incompatibility definitions.
  12. Check Global vs Domain incompatibility.
  13. Find the currently Running incompatible request.
  14. Check self-incompatibility.
  15. Check scheduler pile-up.
  16. Check Run Alone programs.
  17. Check CRM / ICM health.
  18. Check runtime of the blocker.
  19. Map the blocker to GV$SESSION.
  20. Check SQL_ID.
  21. Check Wait Event.
  22. Check database blocking.
  23. Check DB process.
  24. Check Apps-tier OS process.
  25. Review Concurrent Request log.
  26. Obtain functional/business approval before cancellation.

39. Actions to Avoid

Do not immediately:

  • Kill a database session
  • Kill an Apps-tier OS process
  • Restart all Concurrent Managers
  • Add more manager processes
  • Cancel a business-critical request
  • Update FND_CONCURRENT_REQUESTS directly
  • Delete rows from Concurrent Processing tables
  • Remove incompatibility definitions without functional approval
  • Repeatedly submit the same request

40. Recommended Troubleshooting Sequence

1. Confirm P / Q
        |
2. Verify HOLD_FLAG = N
        |
3. Check QUEUE_METHOD_CODE
        |
4. Identify program and CD_ID
        |
5. Check incompatibility definitions
        |
6. Apply Global / Domain rules
        |
7. Find actual Running blocker
        |
8. Check Run Alone
        |
9. Check self-incompatibility
        |
10. Check CRM / ICM
        |
11. Investigate blocking request
        |
12. Map to GV$SESSION
        |
13. Check SQL_ID
        |
14. Check Wait Event
        |
15. Check database blocking
        |
16. Validate DB and Apps OS processes
        |
17. Determine actual RCA
        |
18. Take approved corrective action
        |
19. Verify Standby request is released

41. Post-Resolution Validation

After the blocking condition is resolved: 

SELECT request_id,
       phase_code,
       status_code,
       queue_method_code,
       controlling_manager,
       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,
       TO_CHAR(actual_completion_date,
               'DD-MON-YYYY HH24:MI:SS') completion_date
FROM   fnd_concurrent_requests
WHERE  request_id = :REQUEST_ID;

Expected sequence: 

Pending / Standby
        |
        v
Conflict removed
        |
        v
Conflict Resolution reevaluates request
        |
        v
Request becomes eligible
        |
        v
Concurrent Manager picks request
        |
        v
Running
        |
        v
Completed

42. Final Technical Takeaway

For Oracle EBS Pending / Standby: 
PHASE_CODE  = P
STATUS_CODE = Q
        |
        v
Pending / Standby
        |
        v
Check QUEUE_METHOD_CODE
        |
        v
Constrained request?
        |
        v
Check incompatibilities
        |
        v
Apply Global / Domain rules
        |
        v
Find Running blocker
        |
        v
Check Run Alone / Self-Incompatibility
        |
        v
Check CRM / ICM
        |
        v
Investigate Running blocker
        |
        v
GV$SESSION / GV$SQL / Wait Events / Blocking
        |
        v
Determine Root Cause

Conclusion

Pending / Standby should not automatically be treated as a Concurrent Manager capacity problem or a database blocking problem.

In many cases, Oracle EBS is intentionally preventing a concurrent request from starting because Concurrent Processing has identified a conflict.

The key tables and views used during the RCA are:

  • FND_CONCURRENT_REQUESTS
  • FND_CONCURRENT_PROGRAMS
  • FND_CONCURRENT_PROGRAMS_TL
  • FND_CONCURRENT_PROGRAM_SERIAL
  • FND_CONFLICTS_DOMAIN
  • FND_CONCURRENT_QUEUES
  • FND_CONCURRENT_PROCESSES
  • FND_USER
  • GV$SESSION
  • GV$PROCESS
  • GV$SQL

The most important troubleshooting principle is simple:

Do not ask only, "Why is this request Pending?"

Ask:

"What exact Concurrent Processing rule is preventing this request from becoming eligible, and which request or process currently owns that conflict?"

Production Safety Note: All SQL statements in this article are intended for read-only diagnostic use. Validate object and column availability against your Oracle E-Business Suite and Oracle Database release. Do not manually update FND Concurrent Processing tables, terminate database sessions, kill operating-system processes, cancel business-critical requests, or modify Concurrent Program incompatibility definitions without following your organization's approved application, functional, incident and change-management procedures.

Posted by at No comments:

Oracle EBS Concurrent Request Stuck in Pending Standby (P/Q) – How to Find the Blocking Incompatible Program


Oracle EBS Concurrent Request Stuck in Pending Standby (P/Q) – How to Find the Blocking Incompatible Program

In Oracle E-Business Suite, a concurrent request may remain in Pending / Standby even though the Concurrent Managers are running normally and the request is not manually placed on hold.

A common reason is Concurrent Program Incompatibility. Oracle EBS prevents the request from starting while another incompatible concurrent program is running.

This article provides a production-friendly, read-only troubleshooting procedure to identify the exact request causing the Pending / Standby condition.

1. Understanding Pending / Standby

The important values in FND_CONCURRENT_REQUESTS are:

Column Value Meaning
PHASE_CODE P Pending
STATUS_CODE Q Standby
HOLD_FLAG N Request is not manually held
ACTUAL_START_DATE NULL Request has not started

When a request shows P / Q, one of the first things to investigate is concurrent program incompatibility.

2. Check the Concurrent Request

Replace :REQUEST_ID with the affected concurrent request ID.

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 = :REQUEST_ID;

Typical output for an incompatibility-related problem may look like:

PHASE_CODE       : P
STATUS_CODE      : Q
HOLD_FLAG        : N
ACTUAL_START_DATE: NULL

3. Find the Concurrent Program Name

SELECT r.request_id,
       cp.user_concurrent_program_name,
       r.program_application_id,
       r.concurrent_program_id,
       r.phase_code,
       r.status_code
FROM   fnd_concurrent_requests r,
       fnd_concurrent_programs_tl cp
WHERE  r.request_id = :REQUEST_ID
AND    cp.application_id = r.program_application_id
AND    cp.concurrent_program_id = r.concurrent_program_id
AND    cp.language = USERENV('LANG');

4. Check Program Incompatibility Definitions

Oracle EBS stores concurrent program incompatibility information in FND_CONCURRENT_PROGRAM_SERIAL.

The following query shows programs configured as incompatible with the target request.

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,
       fnd_concurrent_requests r
WHERE  r.request_id = :REQUEST_ID
AND    s.to_run_application_id = r.program_application_id
AND    s.to_run_concurrent_program_id = r.concurrent_program_id
AND    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')
ORDER BY cp2.user_concurrent_program_name;

A program can potentially have many incompatibility definitions. The existence of an incompatibility definition itself does not mean there is currently a problem. The important step is identifying whether one of those programs is actually running.

5. Find the Exact Running Request Blocking the Target Request

This is the most useful query in the investigation.

It takes the target request, determines its incompatible programs, and checks whether any of those programs are currently running. 

WITH target_request AS
(
    SELECT request_id,
           program_application_id,
           concurrent_program_id
    FROM   fnd_concurrent_requests
    WHERE  request_id = :REQUEST_ID
),
incompatible_programs AS
(
    SELECT s.running_application_id application_id,
           s.running_concurrent_program_id concurrent_program_id
    FROM   fnd_concurrent_program_serial s,
           target_request 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
    FROM   fnd_concurrent_program_serial s,
           target_request t
    WHERE  s.running_application_id =
           t.program_application_id
    AND    s.running_concurrent_program_id =
           t.concurrent_program_id
)
SELECT r.request_id,
       cp.user_concurrent_program_name program_name,
       fu.user_name submitted_by,
       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,
       r.oracle_process_id,
       r.os_process_id
FROM   fnd_concurrent_requests r,
       fnd_concurrent_programs_tl cp,
       fnd_user fu,
       incompatible_programs i
WHERE  r.program_application_id = i.application_id
AND    r.concurrent_program_id = i.concurrent_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')
AND    fu.user_id = r.requested_by
ORDER BY r.actual_start_date;

Example Result

REQUEST_ID   PROGRAM_NAME          PHASE   STATUS   RUNNING_MINUTES
----------   -------------------   -----   ------   ---------------
123456789    Payables Approval       R       R          38000

If this query returns a running request, that request is a strong candidate for the request holding the target concurrent program in Pending / Standby.

6. Check How Long the Blocking Request Has Been Running

Use the Request ID returned by the previous query.

SELECT request_id,
       phase_code,
       status_code,
       oracle_session_id,
       oracle_process_id,
       os_process_id,
       TO_CHAR(request_date,
               'DD-MON-YYYY HH24:MI:SS') request_date,
       TO_CHAR(actual_start_date,
               'DD-MON-YYYY HH24:MI:SS') actual_start_date,
       ROUND((SYSDATE-actual_start_date)*24*60,2)
           running_minutes,
       ROUND((SYSDATE-actual_start_date)*24,2)
           running_hours,
       ROUND((SYSDATE-actual_start_date),2)
           running_days
FROM   fnd_concurrent_requests
WHERE  request_id = :BLOCKING_REQUEST_ID;

A request showing as Running for several days or weeks should be investigated carefully before cancelling it.

7. Check Whether the Database Session Still Exists

The next step is determining whether the EBS request is genuinely running at the database level or whether the EBS request status may be stale. 

SELECT fcr.request_id,
       s.inst_id,
       s.sid,
       s.serial#,
       s.status session_status,
       s.username,
       s.sql_id,
       s.event,
       s.wait_class,
       s.seconds_in_wait,
       s.last_call_et,
       p.spid db_os_pid
FROM   fnd_concurrent_requests fcr
       LEFT JOIN gv$session s
              ON s.audsid = fcr.oracle_session_id
       LEFT JOIN gv$process p
              ON p.addr = s.paddr
             AND p.inst_id = s.inst_id
WHERE  fcr.request_id = :BLOCKING_REQUEST_ID;

8. Interpret the Database Session

Result Meaning Action
Active session with SQL_ID The concurrent request is actively executing SQL Investigate SQL performance before taking action
Session exists and is waiting The request may be waiting on DB, I/O, locks or another resource Investigate wait event and blocking session
Session exists but appears idle for an unusually long period Program may be waiting internally or hung Review concurrent log and application behaviour
No database session exists EBS may still show the concurrent request as Running although its DB process is gone Investigate as a possible stale/ghost concurrent request

9. Check the SQL Being Executed

If the running concurrent request has a SQL_ID:

SELECT inst_id,
       sql_id,
       child_number,
       executions,
       ROUND(elapsed_time/1000000,2) elapsed_seconds,
       ROUND(cpu_time/1000000,2) cpu_seconds,
       buffer_gets,
       disk_reads,
       rows_processed,
       SUBSTR(sql_text,1,1000) sql_text
FROM   gv$sql
WHERE  sql_id = :SQL_ID
ORDER BY inst_id,
         child_number;

10. Check Current Database Wait Event

SELECT inst_id,
       sid,
       serial#,
       status,
       sql_id,
       event,
       wait_class,
       state,
       seconds_in_wait,
       blocking_instance,
       blocking_session
FROM   gv$session
WHERE  sid = :SID
AND    inst_id = :INST_ID;

This is useful for identifying whether the concurrent request is waiting for:

  • Database locks
  • I/O
  • Network activity
  • Application-level waits
  • Another database session

11. Check Whether the Session Is Database Blocked

SELECT inst_id,
       sid,
       serial#,
       username,
       sql_id,
       event,
       blocking_instance,
       blocking_session
FROM   gv$session
WHERE  blocking_session IS NOT NULL
ORDER BY inst_id,
         sid;

It is important to distinguish a database blocking problem from a concurrent program incompatibility problem.

A request can remain in Pending / Standby without having any Oracle database lock at all.

12. Find All Pending / Standby Requests

The following query can be useful during general Concurrent Manager troubleshooting. 

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,
       TO_CHAR(r.requested_start_date,
               'DD-MON-YYYY HH24:MI:SS') requested_start_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;

13. Important Difference: Standby vs No Manager Available

Do not automatically assume that every Pending request is caused by insufficient Concurrent Manager processes.

Condition Typical Investigation
Pending / Standby Concurrent program incompatibility / serialization
Pending / Normal Manager availability, specialization rules, work shifts and target processes
Pending / Scheduled Requested start date has not arrived
Pending / On Hold Request or parent request may be on hold

14. Example Troubleshooting Flow

Concurrent Request Not Starting
            |
            v
Check FND_CONCURRENT_REQUESTS
            |
            v
PHASE_CODE = P ?
            |
            v
STATUS_CODE = Q ?
            |
            +---- No ----> Investigate other pending conditions
            |
           Yes
            |
            v
Check FND_CONCURRENT_PROGRAM_SERIAL
            |
            v
Find incompatible running programs
            |
            v
Blocking concurrent request found?
            |
       +----+----+
       |         |
      Yes        No
       |         |
       v         v
Check DB       Check CM / ICM
session        and queue state
       |
       v
Active / Waiting / Missing Session
       |
       v
Review Log + SQL + Wait Event
       |
       v
Take application-approved action

15. Common Scenario

Consider the following situation:

Target Request
--------------
Request ID : 987654321
Program    : AP/PO Purge Abort Routine
Phase      : Pending
Status     : Standby

                 |
                 | Incompatibility
                 v

Blocking Request
----------------
Request ID : 987650000
Program    : Payables Approval
Phase      : Running
Status     : Normal
Runtime    : Several days

The purge request itself may be perfectly healthy. Oracle EBS is intentionally preventing it from starting because the incompatible Payables program is still considered Running.

The correct troubleshooting focus therefore shifts from the Pending request to the running incompatible request.

16. Before Cancelling a Long-Running Concurrent Request

Never cancel a production concurrent request only because it has been running for a long time. First verify:

  • Whether the database session still exists
  • Current SQL_ID
  • Current wait event
  • Whether database blocking exists
  • Concurrent request log
  • Whether the program is designed to run continuously
  • Business impact of terminating the request
  • Whether the application functional team approves cancellation

17. Key Takeaway

For Oracle EBS Concurrent Manager troubleshooting: 

PHASE_CODE  = 'P'
STATUS_CODE = 'Q'

        usually means

Pending / Standby

        investigate

Concurrent Program Incompatibility

        then identify

The currently running incompatible request

Do not immediately restart Concurrent Managers or terminate database sessions. First identify the exact reason Oracle EBS is keeping the request in Standby.

Using FND_CONCURRENT_REQUESTS, FND_CONCURRENT_PROGRAM_SERIAL, FND_CONCURRENT_PROGRAMS_TL, GV$SESSION and GV$SQL provides a systematic way to determine whether the issue is:

  • Concurrent program incompatibility
  • A genuinely long-running request
  • A database wait
  • A blocking session
  • Or a stale/ghost concurrent request

Note: The SQL statements in this article are intended for diagnostic and read-only troubleshooting. Validate queries in your environment and follow your organization's production change and incident-management procedures before cancelling requests, terminating sessions, or modifying Concurrent Manager configuration.

Posted by at No comments:

Friday, September 4, 2026

Oracle EBS R12.2 Workflow Notification Mailer Down — A Step-by-Step Root Cause Analysis Guide


A production-safe, read-only-first troubleshooting methodology for Oracle E-Business Suite R12.2 Workflow Notification Mailer failures. No blind restarts — find the real root cause first.

One of the most common production incidents an Oracle Apps DBA faces is:

“Workflow Notification Mailer is DOWN / Stopped with Error — users are not receiving workflow emails.”

The typical (wrong) reaction is to restart the mailer immediately. It may come back up for a few minutes and stop again — because the real root cause (an expired password, a renewed SSL certificate, a firewall change) was never fixed. This post walks through a structured RCA approach: collect evidence first, classify the failure, fix the cause, and only then restart.

Environment assumed: Oracle EBS R12.2, Oracle Database 19c, Linux/UNIX application tier, Workflow Mailer managed through Oracle Applications Manager (OAM) / Generic Service Management (GSM).

The Notification Flow (Know What You Are Debugging)

Business Event
   → WF_DEFERRED queue
      → Workflow Agent Listener
         → WF_NOTIFICATION_OUT queue
            → Workflow Notification Mailer
               → SMTP Server / Mail Relay
                  → User Mailbox

A failure at any stage produces “no emails” — but each stage leaves different evidence. Our job is to find where the chain is broken before touching anything.

Phase 1 — Establish Current State (Read-Only)

1.1 Status of all Workflow service components

Run as APPS:

SET LINESIZE 200 PAGESIZE 100
COL component_name FORMAT A45
COL component_status FORMAT A18

SELECT component_id,
       component_name,
       component_status,
       component_status_info,
       startup_mode
FROM   fnd_svc_components
ORDER  BY component_id;

Expected: Notification Mailer and all Agent Listeners = RUNNING, startup mode AUTOMATIC.

Abnormal: STOPPED_ERROR, DEACTIVATED_SYSTEM, or stuck in STARTING. Note the mailer's COMPONENT_ID, and read COMPONENT_STATUS_INFO carefully — it often contains the actual Java exception text.

1.2 Container status and hosting node (multi-node aware)

SELECT cq.concurrent_queue_name,
       cq.target_node,
       DECODE(cp.process_status_code,'A','ACTIVE','R','RUNNING',
              'T','TERMINATING','K','TERMINATED','S','STOPPED',
              cp.process_status_code) status,
       cp.concurrent_process_id,
       cp.logfile_name
FROM   fnd_concurrent_queues cq,
       fnd_concurrent_processes cp
WHERE  cp.concurrent_queue_id (+) = cq.concurrent_queue_id
AND    cp.process_status_code (+) = 'A'
AND    cq.concurrent_queue_name IN ('WFMLRSVC','WFALSNRSVC');

WFMLRSVC = Mailer Service container, WFALSNRSVC = Agent Listener container. If there is no active process for the container, the problem is at the Java/GSM level — not the mail server.

1.3 Identify the exact current Mailer log (never guess the filename)

SELECT fcp.concurrent_process_id,
       fcp.logfile_name,
       fcp.node_name,
       fcp.process_start_date
FROM   fnd_concurrent_processes fcp,
       fnd_concurrent_queues fcq
WHERE  fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND    fcq.concurrent_queue_name = 'WFMLRSVC'
ORDER  BY fcp.process_start_date DESC
FETCH FIRST 3 ROWS ONLY;

Then confirm on the OS (as the applmgr user, on the node returned above):

cd $APPLCSF/$APPLLOG
ls -ltr FNDCPGSC*.txt | tail -5

The most recently modified FNDCPGSC*.txt matching the process ID is your live mailer log. You can also cross-check in OAM → Workflow Manager → Notification Mailer → View Log.

Phase 2 — Extract the Failure Signature

On the application node, sweep the log for classic failure patterns using a portable shell loop:

LOGF=FNDCPGSC<nnnnn>.txt   # from Phase 1.3

tail -200 $LOGF

for p in "ERROR" "Exception" "SMTP" "IMAP" \
         "AuthenticationFailedException" "Connection refused" \
         "Connection timed out" "UnknownHostException" \
         "SSLHandshakeException" "PKIX" \
         "unable to find valid certification path" \
         "MessagingException" "javax.mail"
do
  echo "===== $p ====="
  grep -n "$p" $LOGF | tail -10
done

To see the context around a hit at line N:

sed -n '<N-20>,<N+20>p' $LOGF

Phase 3 — Database Evidence: Configuration, Backlog, Queues

3.1 Mailer configuration parameters

SELECT p.parameter_name,
       DECODE(p.parameter_name,'MAILPASSWORD','*****',
              'INBOUND_PASSWORD','*****', v.parameter_value) parameter_value
FROM   fnd_svc_comp_param_vals v,
       fnd_svc_comp_params_b  p,
       fnd_svc_components     c
WHERE  c.component_type = 'WF_MAILER'
AND    v.component_id   = c.component_id
AND    v.parameter_id   = p.parameter_id
AND    p.parameter_name IN
       ('OUTBOUND_SERVER','SMTP_OUT_PORT','OUTBOUND_SSL_ENABLED',
        'SSL_TRUSTSTORE','INBOUND_SERVER','INBOUND_PORT',
        'INBOUND_SSL_ENABLED','ACCOUNT','FROM','REPLYTO',
        'NODENAME','TEST_ADDRESS','MAX_ERROR_COUNT')
ORDER  BY p.parameter_name;

Tip: If TEST_ADDRESS is populated, all notifications are silently diverted to that single address — a classic “mailer is RUNNING but nobody gets email” cause (typically left over from a clone).

3.2 Notification backlog

SELECT mail_status, status, COUNT(*)
FROM   wf_notifications
GROUP  BY mail_status, status
ORDER  BY 3 DESC;
Result Meaning
Large, growing MAIL_STATUS = 'MAIL' Notifications queued; mailer not draining them
MAIL_STATUS = 'FAILED' rows Delivery attempted and rejected by the mail server
MAIL_STATUS NULL for affected users User notification preference issue — not a mailer fault

3.3 Queue depths across the flow

SELECT 'WF_DEFERRED' q, msg_state, COUNT(*)
FROM applsys.aq$wf_deferred GROUP BY msg_state
UNION ALL
SELECT 'WF_NOTIFICATION_OUT', msg_state, COUNT(*)
FROM applsys.aq$wf_notification_out GROUP BY msg_state
UNION ALL
SELECT 'WF_ERROR', msg_state, COUNT(*)
FROM applsys.aq$wf_error GROUP BY msg_state;
Pattern Where the chain is broken
WF_DEFERRED READY huge, WF_NOTIFICATION_OUT small Agent Listener stage (upstream of mailer)
WF_NOTIFICATION_OUT READY growing Mailer stage — consistent with STOPPED_ERROR
Queues draining but no mail arrives Mail server / relay / quarantine (external)

Also verify no queue was accidentally disabled:

SELECT name, enqueue_enabled, dequeue_enabled
FROM   dba_queues
WHERE  owner = 'APPLSYS'
AND    name IN ('WF_DEFERRED','WF_NOTIFICATION_OUT','WF_NOTIFICATION_IN','WF_ERROR');

All should be YES / YES.

Phase 4 — Network and Mail Server Tests (From the App Node)

Use the exact host and port parameters retrieved in Phase 3.1:

# DNS resolution
host <smtp_server>
nslookup <smtp_server>

# TCP reachability
telnet <smtp_server> 25        # or 587 / 465

# Manual SMTP handshake — proves relay permission
telnet <smtp_server> 25
EHLO <appnode_fqdn>
MAIL FROM:<workflow_account@domain>
RCPT TO:<your_address@domain>
QUIT

# SSL/TLS certificate validation
openssl s_client -connect <smtp_server>:465
openssl s_client -connect <smtp_server>:587 -starttls smtp

# IMAP (if inbound processing is enabled)
telnet <imap_server> 143
openssl s_client -connect <imap_server>:993
Test result Meaning
DNS lookup fails DNS issue (check resolv.conf / name servers)
Connection refused Wrong port, or SMTP service down
Telnet hangs, then times out Firewall blocking (common after security policy changes)
550 relay denied on RCPT TO App server IP/name not whitelisted on the mail relay
openssl verify error / incomplete chain Certificate ↔ truststore problem
protocol_version / handshake alert TLS version mismatch (JDK runtime vs mail server profile)

Phase 5 — Root Cause Classification Matrix

Match the dominant log signature to the problem area:

Log signature Root cause area Typical real-world trigger
javax.mail.AuthenticationFailedException, 535 5.7.x Mail account / auth Password rotated or expired; basic auth disabled on Exchange Online
Connection refused SMTP service/port Relay down, port changed, wrong OUTBOUND_SERVER
Connection timed out Firewall / network New firewall rule; relay IP migrated
UnknownHostException DNS Hostname decommissioned; DNS server change
SSLHandshakeException, PKIX path building failed SSL cert ↔ Java truststore Mail server certificate renewed with a new CA chain
handshake_failure, protocol_version TLS/cipher mismatch Mail infra enforcing TLS 1.2+; older JDK on app tier
ORA-25xxx, dequeue errors Workflow AQ queues Queue disabled or subscriber issue (often post-clone)
Container won't start, OutOfMemoryError Java / GSM container JVM heap sizing; Service Manager (FNDSM) down on the node
Mailer RUNNING, backlog grows, no errors Mailer configuration TEST_ADDRESS set; wrong NODENAME
Mail accepted (250 OK) but never arrives External mail infra Anti-spam, SPF/DKIM policy, quarantine, journaling
WF_DEFERRED growing, listeners STOPPED_ERROR Workflow / Agent Listener Failing event subscription hitting max error count

Field observation: The two most frequent production root causes for a mailer that “worked fine for months and suddenly stopped” are:

  1. A renewed SSL certificate on the corporate mail relay causing SSLHandshakeException / PKIX errors.
  2. A rotated or expired mail account password causing AuthenticationFailedException.

Phase 6 — Safest Recovery Sequence

Fix the cause first, then recover with the smallest possible scope:

  1. Remediate the root cause:
    • Certificate/truststore: Import the new CA chain into the truststore the mailer uses (the SSL_TRUSTSTORE parameter location, otherwise the JDK cacerts on the application tier) using keytool -import. In R12.2, perform this on the run file system and remember to propagate or sync to the patch file system during the next fs_clone.
    • Password: Update it directly through OAM → Notification Mailer → Edit — never update FND tables manually.
    • Firewall/DNS/relay: Have the network or mail team fix it, then re-run the Phase 4 tests to confirm connectivity before restarting services.
  2. Restart only the Notification Mailer component via OAM Workflow Manager.
  3. If the component will not start cleanly, restart the WFMLRSVC container only.
  4. Only if GSM itself is implicated, bounce Concurrent Managers (adcmctl.sh) during an approved window.
  5. Queue rebuild scripts (e.g., the notification queue rebuild procedure) are a last resort — run only under Oracle Support guidance matching your exact symptoms. Never run destructive queue cleanup casually, and never update Workflow tables directly.

Phase 7 — Post-Recovery Verification (End to End)

-- 1. Mailer is RUNNING
SELECT component_name, component_status
FROM   fnd_svc_components
WHERE  component_type = 'WF_MAILER';

-- 2. Backlog is draining (run twice, 10 minutes apart — count must decrease)
SELECT COUNT(*) FROM wf_notifications WHERE mail_status = 'MAIL';

-- 3. Outbound queue is draining
SELECT msg_state, COUNT(*)
FROM   applsys.aq$wf_notification_out
GROUP  BY msg_state;

Then run a controlled end-to-end verification:

  1. OAM → Workflow Manager → Notification Mailer → Test Mailer (or trigger any standard workflow notification).
  2. Confirm the notification's MAIL_STATUS changes from MAIL to SENT.
  3. Confirm the email arrives in the destination inbox (check junk and quarantine folders too).
  4. Monitor the live log for 15–30 minutes: tail -f $APPLCSF/$APPLLOG/FNDCPGSC<new>.txt — verify there are no exceptions and only periodic processing messages appear.
  5. Confirm Agent Listeners remain RUNNING and WF_DEFERRED is not accumulating.

Key Takeaways

  • Never restart first. A restart destroys nothing but often hides critical runtime evidence and wastes outage windows.
  • The mailer log filename must be identified from the database, not guessed.
  • The queue-depth pattern tells you which stage of the flow is broken before you read a single log line.
  • 80% of sudden mailer failures trace back to changes outside EBS: passwords, certificates, firewalls, DNS, or mail platform migrations.
  • All fixes go through OAM / supported procedures — never direct table updates, never unsupported queue purges.

If this helped you resolve a production Workflow Mailer outage, share your failure signature in the comments — the classification matrix above grows with every real-world case.

Posted by at No comments:

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;

Posted by at No comments:

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

  1. Capture the optimal plan into a SQL Tuning Set on the source database.
  2. Pack the STS into a staging table.
  3. Export and transfer the staging table with Data Pump.
  4. Import and unpack the STS on the target database.
  5. Load the plan as an accepted SQL Plan Baseline.
  6. Purge the bad cursor from the shared pool.
  7. 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.

Posted by at No comments:
Subscribe to: Posts (Atom)