in

SELECT
    sql_id,
    executions,
    elapsed_time/1000000 elapsed_sec,
    elapsed_time/1000/NULLIF(executions,0) ms_per_exec,
    cpu_time/1000000 cpu_sec,
    buffer_gets,
    disk_reads,
    rows_processed
FROM v$sql
WHERE sql_id = '0bujgc94rg3fj';

And find whether it is executing right now:

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.event,
    s.wait_class,
    s.seconds_in_wait,
    s.sql_id,
    s.module,
    s.action,
    s.program,
    s.machine
FROM v$session s
WHERE s.sql_id = '0bujgc94rg3fj';



What is this SQL waiting on when it becomes slow?
That is where ASH/AWR becomes important.
Run this first:
SELECT
    sql_id,
    plan_hash_value,
    executions,
    elapsed_time/1000000 elapsed_sec,
    cpu_time/1000000 cpu_sec,
    buffer_gets,
    disk_reads,
    rows_processed
FROM v$sql
WHERE sql_id = '0bujgc94rg3fj';

Then check whether there are multiple child cursors:
SELECT
    child_number,
    plan_hash_value,
    executions,
    elapsed_time/1000000 elapsed_sec,
    cpu_time/1000000 cpu_sec,
    buffer_gets,
    disk_reads,
    loads,
    invalidations,
    parse_calls
FROM v$sql
WHERE sql_id = '0bujgc94rg3fj'
ORDER BY child_number;

More importantly, use ASH:
SELECT
    event,
    wait_class,
    session_state,
    COUNT(*) samples
FROM v$active_session_history
WHERE sql_id = '0bujgc94rg3fj'
GROUP BY
    event,
    wait_class,
    session_state
ORDER BY samples DESC;

If Diagnostic Pack/AWR is available, check historical ASH:
SELECT
    event,
    wait_class,
    session_state,
    COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sql_id = '0bujgc94rg3fj'
GROUP BY
    event,
    wait_class,
    session_state
ORDER BY samples DESC;

Also check SQL performance over time:
SELECT
    sn.begin_interval_time,
    ss.plan_hash_value,
    ss.executions_delta,
    ROUND(ss.elapsed_time_delta/1000000,2) elapsed_sec,
    ROUND(
        ss.elapsed_time_delta /
        NULLIF(ss.executions_delta,0) / 1000,
        2
    ) ms_per_exec,
    ss.buffer_gets_delta,
    ss.disk_reads_delta
FROM dba_hist_sqlstat ss,
     dba_hist_snapshot sn
WHERE ss.snap_id = sn.snap_id
AND ss.dbid = sn.dbid
AND ss.instance_number = sn.instance_number
AND ss.sql_id = '0bujgc94rg3fj'
ORDER BY sn.begin_interval_time DESC;



++++++++++++++


SELECT
       NVL(sql_id,'NO_SQL_ID') sql_id,
       NVL(event,'ON CPU') event,
       wait_class,
       session_state,
       COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE top_level_sql_id = '0bujgc94rg3fj'
GROUP BY
       sql_id,
       event,
       wait_class,
       session_state
ORDER BY samples DESC;

This is probably the most valuable query at this point.
It may reveal something like:
TOP LEVEL
WF_EVENT.LISTEN

        |
        +--> SQL A against WF_EVENT_SUBSCRIPTIONS
        |
        +--> SQL B against WF_EVENTS
        |
        +--> SQL C against AQ queue
        |
        +--> SQL D ...

Then we can identify which one is burning the CPU and logical reads.
Also run this version
This will give us the internal SQL IDs ranked by activity:
SELECT
       sql_id,
       COUNT(*) samples,
       SUM(CASE
             WHEN session_state = 'ON CPU'
             THEN 1
             ELSE 0
           END) cpu_samples,
       SUM(CASE
             WHEN session_state = 'WAITING'
             THEN 1
             ELSE 0
           END) wait_samples
FROM dba_hist_active_sess_history
WHERE top_level_sql_id = '0bujgc94rg3fj'
GROUP BY sql_id
ORDER BY samples DESC;

Send me that result.

One more thing I noticed in your AWR screenshot
You have identical BEGIN_INTERVAL_TIME values appearing more than once, for example around 04-Aug.
That could be because the database has multiple instances. Your query currently doesn't display INSTANCE_NUMBER.
If this is RAC, we absolutely need to know which instance is experiencing the problem.
Modify the AWR query to:
SELECT
       sn.begin_interval_time,
       ss.instance_number,
       ss.plan_hash_value,
       ss.executions_delta,

       ROUND(
         ss.elapsed_time_delta / 1000000,
         2
       ) elapsed_sec,

       ROUND(
         ss.elapsed_time_delta /
         NULLIF(ss.executions_delta,0) /
         1000,
         2
       ) ms_per_exec,

       ss.buffer_gets_delta,

       ROUND(
         ss.buffer_gets_delta /
         NULLIF(ss.executions_delta,0)
       ) buffer_gets_per_exec,

       ss.disk_reads_delta

FROM dba_hist_sqlstat ss,
     dba_hist_snapshot sn

WHERE ss.snap_id = sn.snap_id
AND ss.dbid = sn.dbid
AND ss.instance_number = sn.instance_number
AND ss.sql_id = '0bujgc94rg3fj'

ORDER BY sn.begin_interval_time DESC,
         ss.instance_number;

++++++++++++++++++


Find the exact execution-plan operation where ASH is accumulating

SELECT
       sql_plan_line_id,
       sql_plan_operation,
       sql_plan_options,
       event,
       session_state,
       COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE top_level_sql_id = '0bujgc94rg3fj'
AND sql_id = 'b2jckdvl94knx'
GROUP BY
       sql_plan_line_id,
       sql_plan_operation,
       sql_plan_options,
       event,
       session_state
ORDER BY samples DESC;



 Find which Oracle object is generating the reads
Run this one as well:
SELECT
       ash.current_obj#,
       obj.owner,
       obj.object_name,
       obj.object_type,
       ash.event,
       COUNT(*) samples
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_objects obj
       ON obj.object_id = ash.current_obj#
WHERE ash.top_level_sql_id = '0bujgc94rg3fj'
AND ash.sql_id = 'b2jckdvl94knx'
AND ash.current_obj# > 0
GROUP BY
       ash.current_obj#,
       obj.owner,
       obj.object_name,
       obj.object_type,
       ash.event
ORDER BY samples DESC;



       sql_id,
       child_number,
       plan_hash_value,
       executions,
       ROUND(elapsed_time/1000000,2) elapsed_sec,
       ROUND(cpu_time/1000000,2) cpu_sec,
       buffer_gets,
       disk_reads,
       rows_processed,
       sql_fulltext
FROM v$sql
WHERE sql_id = 'b2jckdvl94knx';

If it is no longer in shared pool:
SELECT
       sql_id,
       DBMS_LOB.SUBSTR(sql_text,4000,1) sql_text
FROM dba_hist_sqltext
WHERE sql_id = 'b2jckdvl94knx';






The next query should therefore combine everything into one result.
SELECT
       sql_id,
       SUM(executions) executions,
       ROUND(SUM(elapsed_time)/1000000,2) elapsed_sec,
       ROUND(SUM(cpu_time)/1000000,2) cpu_sec,
       SUM(buffer_gets) buffer_gets,
       SUM(disk_reads) disk_reads,

       ROUND(
          SUM(elapsed_time) /
          NULLIF(SUM(executions),0) /
          1000,
          2
       ) ms_per_exec,

       ROUND(
          SUM(buffer_gets) /
          NULLIF(SUM(executions),0),
          2
       ) buffer_gets_per_exec,

       ROUND(
          SUM(disk_reads) /
          NULLIF(SUM(executions),0),
          2
       ) disk_reads_per_exec

FROM gv$sql

WHERE sql_id IN
(
 '8nzx90zdhgfgc',
 '63v1yg88zt6gs',
 'd119xbzfdqyar',
 'cyly9yv9y91hb'
)

GROUP BY sql_id
ORDER BY elapsed_sec DESC;

That result is much more meaningful.
Also determine why there are multiple rows
Run:
SELECT
       inst_id,
       sql_id,
       child_number,
       plan_hash_value,
       executions,
       ROUND(elapsed_time/1000000,2) elapsed_sec,
       ROUND(cpu_time/1000000,2) cpu_sec,
       buffer_gets,
       disk_reads
FROM gv$sql
WHERE sql_id IN
(
 '8nzx90zdhgfgc',
 '63v1yg88zt6gs',
 'd119xbzfdqyar',
 'cyly9yv9y91hb'
)
ORDER BY sql_id,
         inst_id,
         child_number;




Now get the SQL text
This is the most important next piece:
SELECT DISTINCT
       sql_id,
       DBMS_LOB.SUBSTR(sql_fulltext,4000,1) sql_text
FROM gv$sql
WHERE sql_id IN
(
 '8nzx90zdhgfgc',
 '63v1yg88zt6gs',
 'd119xbzfdqyar',
 'cyly9yv9y91hb'
);




SELECT
       event,
       wait_class,
       session_state,
       COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sql_id = '8nzx90zdhgfgc'
GROUP BY
       event,
       wait_class,
       session_state
ORDER BY samples DESC;

Then:
SELECT
       ash.current_obj#,
       obj.owner,
       obj.object_name,
       obj.object_type,
       ash.event,
       COUNT(*) samples
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_objects obj
       ON obj.object_id = ash.current_obj#
WHERE ash.sql_id = '8nzx90zdhgfgc'
AND ash.current_obj# > 0
GROUP BY
       ash.current_obj#,
       obj.owner,
       obj.object_name,
       obj.object_type,
       ash.event
ORDER BY samples DESC;

Diagnosing Workflow Mailer Email Storms and Delayed PO Email Approvals — A Read-Only Production RCA Runbook

Oracle EBS 12.2: Diagnosing Workflow Mailer Email Storms and Delayed PO Email Approvals — A Read-Only Production RCA Runbook

A common production scenario in Oracle E-Business Suite 12.2: the Workflow Notification Mailer starts bouncing under a flood of notification emails, and users complain that Purchase Orders approved via email take a long time to reflect in EBS. This post is a complete, production-safe, READ-ONLY investigation runbook — every query and OS check needed to find exactly where the email approval is spending its time, before touching anything.

Ground rules: no purges, no updates to Workflow tables, no mailer bounces, no queue cleanup — diagnostics only. Baseline is EBS 12.2.x on Oracle 19c with APPS access and OS access to the Concurrent Manager node.

---

Architecture recap — why an outbound email storm delays inbound PO approvals

The Notification Mailer is one Java service container (WFMLRSVC / FNDCPGSC) that runs both the outbound processors (dequeue WF_NOTIFICATION_OUT → SMTP) and the inbound processors (poll IMAP → enqueue WF_NOTIFICATION_IN). Therefore:

1. An outbound storm can saturate the mailer's threads/heap → OutOfMemory / hang → GSM restarts the container ("bouncing").
2. Every bounce interrupts IMAP polling → user approval replies sit unread in the INBOX.
3. Even after the reply is enqueued to WF_NOTIFICATION_IN, a separate component — the Workflow Inbound Notifications Agent Listener — must dequeue it and call the respond API.
4. The post-response PO workflow activities may be deferred, i.e. parked on WF_DEFERRED for the Workflow Deferred Agent Listener (and, for timed-out/stuck activities, the Workflow Background Process).

So the end-to-end delay can accumulate in four independent places: mailer-outbound, mailer-inbound (IMAP), WF_NOTIFICATION_IN listener, WF_DEFERRED engine processing. The phases below measure each one.

---

PHASE 1 — Immediate 5-minute production health check

1.1 Service component status

Purpose: Confirm the four critical components are RUNNING right now.

SELECT fsc.component_id,
       fsc.component_name,
       fsc.component_status,
       fsc.component_type,
       fsc.startup_mode,
       fcq.concurrent_queue_name AS container
FROM   fnd_svc_components     fsc,
       fnd_concurrent_queues  fcq
WHERE  fsc.concurrent_queue_id = fcq.concurrent_queue_id (+)
AND    fsc.component_type LIKE 'WF_%'
ORDER  BY fsc.component_type, fsc.component_name;

Expected normal: Workflow Notification Mailer, Workflow Agent Listener components (Workflow Deferred Agent Listener, Workflow Deferred Notification Agent Listener, Workflow Inbound Notifications Agent Listener, Workflow Error Agent Listener, Java listeners) all RUNNING with startup_mode = AUTOMATIC.

Problem indicators:

component_status	Meaning	Next step
STOPPED / DEACTIVATED_USER	Someone stopped it	Find who/when in mailer log; do NOT restart yet
STOPPED_ERROR	Crashed after max auto-restarts	Phase 7 log analysis — this is your smoking gun
SUSPENDED	Manually or schedule-suspended	Check OAM schedule
STARTING (persistently)	Container thrashing / can't initialize	Phase 7 — IMAP/SMTP connect errors, OOM

1.2 Are the service containers themselves alive, and are they bouncing?

Purpose: WFMLRSVC (Mailer container) and WFALSNRSVC (Agent Listener container) restarts show up as multiple recent process rows. Frequent restarts = "mailer bouncing" confirmed with timestamps.

SELECT q.concurrent_queue_name,
       p.concurrent_process_id,
       p.process_status_code,
       TO_CHAR(p.process_start_date,'DD-MON HH24:MI:SS') AS started,
       p.node_name,
       p.logfile_name
FROM   fnd_concurrent_queues    q,
       fnd_concurrent_processes p
WHERE  q.concurrent_queue_id = p.concurrent_queue_id
AND    q.concurrent_queue_name IN ('WFMLRSVC','WFALSNRSVC')
AND    p.process_start_date > SYSDATE - 2
ORDER  BY q.concurrent_queue_name, p.process_start_date DESC;

Expected normal: exactly one A (Active) row per container, started days/weeks ago. Problem: several rows in the last 24 h (statuses K/S/T followed by new A rows) = container is being killed/restarted repeatedly. Save the logfile_name values — they are the Phase 7 inputs.

1.3 30-second queue snapshot

Purpose: Instant view of the two queues that gate email approvals.

SELECT 'WF_NOTIFICATION_OUT' q, msg_state, COUNT(*) cnt,
       TO_CHAR(CAST(MIN(enq_time) AS DATE),'DD-MON HH24:MI') oldest
FROM   applsys.aq$wf_notification_out GROUP BY msg_state
UNION ALL
SELECT 'WF_NOTIFICATION_IN', msg_state, COUNT(*),
       TO_CHAR(CAST(MIN(enq_time) AS DATE),'DD-MON HH24:MI')
FROM   applsys.aq$wf_notification_in GROUP BY msg_state
UNION ALL
SELECT 'WF_DEFERRED', msg_state, COUNT(*),
       TO_CHAR(CAST(MIN(enq_time) AS DATE),'DD-MON HH24:MI')
FROM   applsys.aq$wf_deferred GROUP BY msg_state
UNION ALL
SELECT 'WF_ERROR', msg_state, COUNT(*),
       TO_CHAR(CAST(MIN(enq_time) AS DATE),'DD-MON HH24:MI')
FROM   applsys.aq$wf_error GROUP BY msg_state;

Expected normal: READY counts low (tens–low hundreds) with oldest READY only minutes old. Problem: thousands of READY and/or oldest READY hours old. A big count with a young oldest-READY is throughput pressure, not a stall; a stall is proven by AGE, not count — Phase 2 measures both.

1.4 New-notification arrival rate (storm confirmation, 10 seconds)

SELECT COUNT(*) notifications_last_hour
FROM   wf_notifications
WHERE  begin_date > SYSDATE - 1/24;

Compare against your known baseline (pull the same hour yesterday/last week in Phase 3). 5–10× baseline = storm.

---

PHASE 2 — Workflow AQ queue backlog analysis

AQ view column notes (applies to all AQ$<queue_table> views): msg_state: READY (waiting to be dequeued), WAIT (delay not yet elapsed), PROCESSED (dequeued, retained until retention time expires), EXPIRED (exceeded max retries → moved to the exception queue). enq_time/deq_time are TIMESTAMP WITH TIME ZONE — CAST(... AS DATE) before date arithmetic. PROCESSED rows exist only while queue retention keeps them; treat their absence as "retention=0", not "no throughput".

2.1 WF_NOTIFICATION_IN — inbound approval emails

What it does: the Mailer enqueues every valid inbound email response here; the Workflow Inbound Notifications Agent Listener dequeues it and executes the response (calls the Respond API → Workflow Engine).

2.1a State summary, oldest/newest, age

SELECT msg_state,
       COUNT(*)                                          cnt,
       TO_CHAR(CAST(MIN(enq_time) AS DATE),'DD-MON-YYYY HH24:MI:SS') oldest,
       TO_CHAR(CAST(MAX(enq_time) AS DATE),'DD-MON-YYYY HH24:MI:SS') newest,
       ROUND((SYSDATE - CAST(MIN(enq_time) AS DATE))*24*60,1)        oldest_age_min
FROM   applsys.aq$wf_notification_in
GROUP  BY msg_state
ORDER  BY msg_state;

2.1b Expired / retried messages (poison messages)

SELECT msg_state, retry_count, COUNT(*) cnt
FROM   applsys.aq$wf_notification_in
GROUP  BY msg_state, retry_count
ORDER  BY retry_count DESC;

2.1c Arrival vs processing rate per hour (this is the "faster in than out?" answer)

-- Arrivals per hour (enqueue by mailer)
SELECT TO_CHAR(CAST(enq_time AS DATE),'DD-MON HH24') hr, COUNT(*) enqueued
FROM   applsys.aq$wf_notification_in
WHERE  CAST(enq_time AS DATE) > SYSDATE - 1
GROUP  BY TO_CHAR(CAST(enq_time AS DATE),'DD-MON HH24')
ORDER  BY 1;

-- Dequeues per hour (inbound listener throughput) — needs retention > 0
SELECT TO_CHAR(CAST(deq_time AS DATE),'DD-MON HH24') hr, COUNT(*) dequeued
FROM   applsys.aq$wf_notification_in
WHERE  deq_time IS NOT NULL
AND    CAST(deq_time AS DATE) > SYSDATE - 1
GROUP  BY TO_CHAR(CAST(deq_time AS DATE),'DD-MON HH24')
ORDER  BY 1;

Result pattern	Interpretation
READY ≈ 0, oldest READY < 5 min	Inbound listener healthy — delay is upstream (IMAP/mailer/mail server)
READY large, oldest READY old, dequeues/hr ≈ 0	Cause E — Inbound Agent Listener down/stuck (check 1.1, Phase 6 sessions, Phase 7 listener log)
READY large but dequeues/hr healthy and enq/hr larger	Cause D-as-symptom — listener keeping up but flooded; look at what's flooding it
enq/hr near zero while users report replying	Emails never reaching WF_NOTIFICATION_IN — Cause B/C/J (mailer down, IMAP backlog, mail-server delay) → Phase 4/7/10

2.2 WF_DEFERRED — deferred Workflow Engine work

What it does: activities the engine defers (cost above threshold) plus most Business Events, including oracle.apps.wf.notification.send (which stages outbound notifications). Processed by the Workflow Deferred Agent Listener (and Java counterpart on WF_JAVA_DEFERRED).

SELECT msg_state,
       COUNT(*) cnt,
       TO_CHAR(CAST(MIN(enq_time) AS DATE),'DD-MON-YYYY HH24:MI:SS') oldest,
       TO_CHAR(CAST(MAX(enq_time) AS DATE),'DD-MON-YYYY HH24:MI:SS') newest,
       ROUND((SYSDATE - CAST(MIN(enq_time) AS DATE))*24*60,1)        oldest_age_min
FROM   applsys.aq$wf_deferred
GROUP  BY msg_state;

Which events are backlogged (corr_id = event name):

SELECT corr_id, msg_state, COUNT(*) cnt,
       TO_CHAR(CAST(MIN(enq_time) AS DATE),'DD-MON HH24:MI') oldest
FROM   applsys.aq$wf_deferred
WHERE  msg_state IN ('READY','WAIT')
GROUP  BY corr_id, msg_state
ORDER  BY cnt DESC
FETCH FIRST 25 ROWS ONLY;

Expected normal: READY drains within minutes. Problem: oldest READY > 30–60 min = Cause F (deferred backlog). If the dominant corr_id is APPS:oracle.apps.wf.notification.send, the storm is choking notification staging — outbound and engine progress both suffer. If it's an application event, that names your storm source.

2.3 WF_NOTIFICATION_OUT — outbound emails awaiting SMTP send

What it does: the Mailer's outbound feed. One message per email to be sent. This is where an email storm physically piles up.

SELECT msg_state,
       COUNT(*) cnt,
       TO_CHAR(CAST(MIN(enq_time) AS DATE),'DD-MON-YYYY HH24:MI:SS') oldest,
       ROUND((SYSDATE - CAST(MIN(enq_time) AS DATE))*24*60,1)        oldest_age_min
FROM   applsys.aq$wf_notification_out
GROUP  BY msg_state;

-- Mailer send throughput per hour (needs retention > 0)
SELECT TO_CHAR(CAST(deq_time AS DATE),'DD-MON HH24') hr, COUNT(*) sent
FROM   applsys.aq$wf_notification_out
WHERE  deq_time IS NOT NULL
AND    CAST(deq_time AS DATE) > SYSDATE - 1
GROUP  BY TO_CHAR(CAST(deq_time AS DATE),'DD-MON HH24')
ORDER  BY 1;

Problem: tens of thousands READY + mailer restarts in 1.2 = Causes A + B + K interacting. A WAIT state spike here usually means failed sends waiting for retry (SMTP trouble).

2.4 Other relevant queues

Queue	Role	Why it matters here
WF_JAVA_DEFERRED	Java-subscription deferred events	Java listeners stuck → some notifications never stage
WF_ERROR / WF_JAVA_ERROR	Errored activities/events → WFERROR notifications	An error loop generates emails — classic storm engine
WF_IN / WF_OUT	Legacy external agent queues	Usually idle; backlog = old integrations misbehaving
WF_CONTROL	Container control messages (GSM ↔ components)	Don't judge by counts; messages expire by design

SELECT 'WF_JAVA_DEFERRED' q, msg_state, COUNT(*) cnt,
       ROUND((SYSDATE - CAST(MIN(enq_time) AS DATE))*24*60,1) oldest_age_min
FROM applsys.aq$wf_java_deferred GROUP BY msg_state
UNION ALL
SELECT 'WF_JAVA_ERROR', msg_state, COUNT(*),
       ROUND((SYSDATE - CAST(MIN(enq_time) AS DATE))*24*60,1)
FROM applsys.aq$wf_java_error GROUP BY msg_state
UNION ALL
SELECT 'WF_ERROR', msg_state, COUNT(*),
       ROUND((SYSDATE - CAST(MIN(enq_time) AS DATE))*24*60,1)
FROM applsys.aq$wf_error GROUP BY msg_state;

Interpretation: large/growing WF_ERROR READY → find the failing item type (Phase 3.4) — an ERROR retry loop mailing SYSADMIN is one of the most common EBS email storms.

---

PHASE 3 — Email-volume / notification-storm analysis

All queries here are on WF_NOTIFICATIONS (one row per notification, begin_date = creation time). Change the window (SYSDATE - 1/24, - 6/24, - 1) to get the 1 h / 6 h / 24 h views.

3.1 STATUS and MAIL_STATUS distribution (last 24 h)

SELECT status, mail_status, COUNT(*) cnt
FROM   wf_notifications
WHERE  begin_date > SYSDATE - 1
GROUP  BY status, mail_status
ORDER  BY cnt DESC;

Status meanings — read these before concluding anything:

Column	Value	Meaning	Caution
STATUS	OPEN	Awaiting response/close	Normal for FYI + pending approvals
STATUS	CLOSED	Responded/closed
STATUS	CANCELED	Canceled (e.g., re-approval reset)	Mass CANCELED bursts can themselves send "canceled" mails
MAIL_STATUS	MAIL	Queued for the mailer, email not yet confirmed sent	Big MAIL count = outbound backlog, says nothing about inbound
MAIL_STATUS	SENT	Mailer sent the email	No timestamp of send stored here
MAIL_STATUS	ERROR	Send failed	Check mailer log for the SMTP error
MAIL_STATUS	WAIT	Awaiting retry/more info
MAIL_STATUS	INVALID	Bad/unresolvable address	Spikes after HR/user changes
MAIL_STATUS	NULL	No email required (recipient preference, or closed before send)	NULL/SENT on an approved-but-stuck PO does NOT mean the inbound response arrived — inbound progress is proven only by WF_COMMENTS / activity end_date (Phase 5)

3.2 Notifications per hour (find the storm start time)

SELECT TO_CHAR(begin_date,'DD-MON HH24') hr, COUNT(*) cnt
FROM   wf_notifications
WHERE  begin_date > SYSDATE - 2
GROUP  BY TO_CHAR(begin_date,'DD-MON HH24')
ORDER  BY 1;

Problem indicator: a step-change hour. That timestamp is your correlation anchor for mailer restarts (1.2), queue growth (Phase 2) and log errors (Phase 7).

3.3 Top generators — by type, message, recipient

-- Top 30 by item type + message (change window: 1/24, 6/24, 1)
SELECT message_type, message_name, COUNT(*) cnt,
       MIN(begin_date) first_seen, MAX(begin_date) last_seen
FROM   wf_notifications
WHERE  begin_date > SYSDATE - 1/24
GROUP  BY message_type, message_name
ORDER  BY cnt DESC
FETCH FIRST 30 ROWS ONLY;

-- Top 30 recipients (a single role/user drowning in mail = loop or bad routing rule)
SELECT recipient_role, COUNT(*) cnt
FROM   wf_notifications
WHERE  begin_date > SYSDATE - 1/24
GROUP  BY recipient_role
ORDER  BY cnt DESC
FETCH FIRST 30 ROWS ONLY;

-- Hour x type matrix for the top offenders
SELECT TO_CHAR(begin_date,'DD-MON HH24') hr, message_type, COUNT(*) cnt
FROM   wf_notifications
WHERE  begin_date > SYSDATE - 1
GROUP  BY TO_CHAR(begin_date,'DD-MON HH24'), message_type
HAVING COUNT(*) > 100
ORDER  BY 1, 3 DESC;

Typical storm signatures:

Signature	Likely source
message_type = WFERROR, recipient SYSADMIN	Error retry loop (check WF_ERROR queue + the erroring item type)
ALR-prefixed / Alert messages	Oracle Alert firing per-row instead of per-batch
Same message_name + same recipient_role every few seconds	Looping workflow / bad activity transition
POAPPRV surge	PO mass interface/requisition import or approval-hierarchy misconfig
Huge counts with STATUS=OPEN forever on FYI messages	Notifications not being closed → re-send/reminder logic piling up

3.4 Workflow item creation rate (catches loops even before notifications)

SELECT item_type, COUNT(*) cnt, MIN(begin_date) first_seen
FROM   wf_items
WHERE  begin_date > SYSDATE - 1
GROUP  BY item_type
ORDER  BY cnt DESC
FETCH FIRST 20 ROWS ONLY;

3.5 Open notifications and aging

-- Open > 30 minutes, overall and PO-only
SELECT message_type, COUNT(*) cnt,
       ROUND((SYSDATE - MIN(begin_date))*24,1) oldest_open_hrs
FROM   wf_notifications
WHERE  status = 'OPEN'
AND    begin_date < SYSDATE - 30/1440
GROUP  BY message_type
ORDER  BY cnt DESC;

SELECT notification_id, recipient_role, subject, begin_date, mail_status
FROM   wf_notifications
WHERE  status = 'OPEN'
AND    message_type = 'POAPPRV'
AND    begin_date < SYSDATE - 30/1440
ORDER  BY begin_date
FETCH FIRST 50 ROWS ONLY;

Caution: OPEN + old is normal for approvals humans haven't answered. It only indicates a system problem when the user says "I already replied" — then trace that specific NID in Phase 5.

---

PHASE 4 — Workflow Mailer and Agent Listener analysis

4.1 Mailer configuration snapshot (READ-ONLY)

Purpose: capture thread counts, polling frequency, IMAP/SMTP hosts before touching anything.

SELECT c.component_name, p.parameter_name, v.parameter_value
FROM   fnd_svc_components        c,
       fnd_svc_comp_param_vals   v,
       fnd_svc_comp_params_b     p
WHERE  c.component_id = v.component_id
AND    v.parameter_id = p.parameter_id
AND    c.component_type = 'WF_MAILER'
AND    p.parameter_name NOT LIKE '%PASSWORD%'
ORDER  BY p.parameter_name;

Key parameters to note down:

Parameter	Meaning	Storm relevance
PROCESSOR_OUT_THREAD_COUNT	Outbound sender threads	1 thread vs storm volume = Cause K
PROCESSOR_IN_THREAD_COUNT	Inbound IMAP processing threads	0/low = inbound starvation
PROCESSOR_READ_TIMEOUT / PROCESSOR_MAX_LOOP_SLEEP	Polling cadence	Long sleeps add fixed latency
INBOUND_SERVER / ACCOUNT / OUTBOUND_SERVER	IMAP/SMTP endpoints	For Phase 10 checks
MAX_INVALID_ADDR_LIST_SIZE, EXPUNGE_ON_CLOSE	Inbox hygiene	Giant unexpunged INBOX slows IMAP polls

4.2 Listener/mailer processing evidence in the DB

Throughput per hour was already measured in 2.1c / 2.3 (deq_time). Combine:

Evidence	Healthy	Unhealthy
WF_NOTIFICATION_OUT deq/hr vs WF_NOTIFICATIONS created/hr	Roughly matching	Created ≫ sent → outbound falling behind (B/K)
WF_NOTIFICATION_IN enq/hr vs user replies expected	Matching	Near zero → replies not reaching EBS (C/J or mailer down)
WF_NOTIFICATION_IN deq lag (deq_time − enq_time)	Seconds	See 4.3

4.3 Inbound listener latency distribution (the single most useful inbound metric)

SELECT ROUND(AVG((CAST(deq_time AS DATE) - CAST(enq_time AS DATE))*24*60),1) avg_min,
       ROUND(MAX((CAST(deq_time AS DATE) - CAST(enq_time AS DATE))*24*60),1) max_min,
       COUNT(*) sample
FROM   applsys.aq$wf_notification_in
WHERE  deq_time IS NOT NULL
AND    CAST(enq_time AS DATE) > SYSDATE - 1;

Expected normal: avg well under a minute. Problem: minutes/hours → the listener (E) or its downstream engine work (F/G/H) is the bottleneck — Phase 6 tells you which (waiting session vs blocked session vs no session).

---

PHASE 5 — Trace one delayed PO approval end-to-end

Inputs: PO number (+ org), NID if known, user, approx time of email approval. ⚠ Joins marked [impl-varies] can differ if the PO approval workflow is customized (custom item type, custom notification, AME). Verify item_type before trusting results.

5.1 PO → workflow item key

SELECT poh.po_header_id, poh.segment1 po_number, poh.org_id,
       poh.authorization_status, poh.approved_flag, poh.approved_date,
       poh.wf_item_type, poh.wf_item_key,
       poh.last_update_date, poh.last_updated_by
FROM   po_headers_all poh
WHERE  poh.segment1 = '&po_number'
AND    poh.org_id   = &org_id;      -- omit org_id only if segment1 is globally unique

5.2 Full activity history for that item (current + history)

SELECT ias.item_type, ias.item_key,
       pa.instance_label            activity,
       ias.activity_status,
       ias.activity_result_code,
       ias.notification_id          group_id,        -- joins wf_notifications.group_id
       TO_CHAR(ias.begin_date,'DD-MON HH24:MI:SS') act_begin,
       TO_CHAR(ias.end_date,  'DD-MON HH24:MI:SS') act_end,
       ias.error_name
FROM   wf_item_activity_statuses ias,
       wf_process_activities     pa
WHERE  ias.item_type = 'POAPPRV'                 -- [impl-varies] custom item types exist
AND    ias.item_key  = '&wf_item_key'
AND    ias.process_activity = pa.instance_id
UNION ALL
SELECT h.item_type, h.item_key, pa.instance_label, h.activity_status,
       h.activity_result_code, h.notification_id,
       TO_CHAR(h.begin_date,'DD-MON HH24:MI:SS'),
       TO_CHAR(h.end_date,  'DD-MON HH24:MI:SS'),
       h.error_name
FROM   wf_item_activity_statuses_h h,
       wf_process_activities       pa
WHERE  h.item_type = 'POAPPRV'
AND    h.item_key  = '&wf_item_key'
AND    h.process_activity = pa.instance_id
ORDER  BY act_begin;

What to look for: the notification activity should show NOTIFIED while waiting, then COMPLETE with result (e.g., APPROVED) once the response is processed. The gap between the user's email-send time and act_end of the notification activity IS the system delay you're hunting. A row stuck in DEFERRED afterwards points to F; ERROR points to I (check error_name + WF_ERROR queue).

5.3 The notification itself + the recorded response

-- Notification (join by GROUP_ID — an activity NID is the group id, not always the row NID)
SELECT n.notification_id, n.group_id, n.recipient_role, n.status, n.mail_status,
       TO_CHAR(n.begin_date,'DD-MON HH24:MI:SS') created,
       TO_CHAR(n.end_date,  'DD-MON HH24:MI:SS') closed,
       n.original_recipient, n.responder, n.subject
FROM   wf_notifications n
WHERE  n.group_id = &group_id_from_5_2
ORDER  BY n.notification_id;

-- Response values captured on the notification
SELECT na.name, na.text_value, na.number_value, na.date_value
FROM   wf_notification_attributes na
WHERE  na.notification_id = &notification_id
AND    na.name IN ('RESULT','RESPONDER','#FROM_ROLE');   -- RESULT = APPROVED/REJECTED

-- Response arrival record (12.2 stores responses/actions in WF_COMMENTS)
SELECT wc.notification_id, wc.from_role, wc.to_role, wc.action, wc.action_type,
       TO_CHAR(wc.comment_date,'DD-MON HH24:MI:SS') comment_time,
       SUBSTR(wc.user_comment,1,200) user_comment
FROM   wf_comments wc
WHERE  wc.notification_id = &notification_id
ORDER  BY wc.comment_date;

wf_comments.comment_date for the RESPOND/email action is your best DB-side approximation of "when EBS processed the reply". Comparing it with the user's mail-client send time isolates the mail-server + IMAP + mailer-inbound legs (which the DB cannot see) from the DB-side legs (which it can).

5.4 PO approval action record [impl-varies]

SELECT pah.sequence_num, pah.action_code,
       TO_CHAR(pah.action_date,'DD-MON HH24:MI:SS') action_time,
       pah.employee_id, pah.note
FROM   po_action_history pah
WHERE  pah.object_id        = &po_header_id
AND    pah.object_type_code IN ('PO','PA')
ORDER  BY pah.sequence_num;

---

PHASE 6 — Notification response timing model

For one NID, assemble this table. Bold rows are stored in the database; the rest need logs.

#	Stage	Timestamp source	Stored in DB?
1	Notification created	wf_notifications.begin_date	Yes
2	Staged for mailer	aq$wf_notification_out.enq_time (while retained)	Partly
3	Email sent (SMTP)	Mailer log only (mail_status=SENT has no timestamp)	No
4	User clicked Approve / sent reply	User's mail client / mail-server logs	No
5	Reply landed in WF IMAP inbox	Mail-server logs / message Received: headers	No
6	Mailer enqueued reply	aq$wf_notification_in.enq_time	Yes
7	Listener dequeued reply	aq$wf_notification_in.deq_time (retention>0)	Yes
8	Response recorded	wf_comments.comment_date (RESPOND)	Yes
9	Notification closed	wf_notifications.end_date	Yes
10	Notification activity completed	wf_item_activity_statuses(.._h).end_date	Yes
11	PO approved/updated	po_action_history.action_date, po_headers_all.approved_date	Yes

Delay attribution:

Large gap between	Root-cause bucket
4 → 6	B / C / J (mailer down or slow IMAP polling, mailbox backlog, corporate mail routing)
6 → 7	D / E (WF_NOTIFICATION_IN backlog / inbound listener)
7 → 9/10	F / G / H / I (deferred backlog, engine, DB blocking, PO workflow logic)
10 → 11	I (post-approval PO activities, doc manager, custom code)

---

PHASE 7 — Database session / blocking / performance analysis

7.1 Workflow-related sessions

SELECT s.inst_id, s.sid, s.serial#, s.username, s.status,
       s.module, s.action, s.program, s.sql_id, s.event, s.wait_class,
       s.seconds_in_wait, s.blocking_session, s.last_call_et,
       TO_CHAR(s.logon_time,'DD-MON HH24:MI') logon
FROM   gv$session s
WHERE  s.username = 'APPS'
AND (  UPPER(s.module) LIKE '%WF%'
    OR UPPER(s.module) LIKE '%WORKFLOW%'
    OR UPPER(s.action) LIKE '%WF%'
    OR UPPER(s.program) LIKE '%FNDSM%'
    OR s.module LIKE 'e:FND:cp:%' )
ORDER  BY s.blocking_session NULLS LAST, s.last_call_et DESC;

(Single instance: use v$session and drop inst_id.) Mailer/listener JDBC sessions typically show program = JDBC Thin Client with WF modules/actions.

Expected normal: mostly INACTIVE (idle between polls) or short ACTIVE bursts; waits like AQ: ... idle waits are fine. Problem: ACTIVE with high last_call_et, non-idle waits (enq: TX - row lock contention, buffer busy waits, db file sequential read storms), or a populated blocking_session.

7.2 Blocking tree and locked WF/PO objects

-- Who blocks whom
SELECT LPAD(' ',2*(LEVEL-1)) || s.sid blocked_tree, s.serial#, s.username,
       s.event, s.sql_id, s.seconds_in_wait, s.module
FROM   gv$session s
WHERE  LEVEL > 1 OR EXISTS
       (SELECT 1 FROM gv$session x WHERE x.blocking_session = s.sid)
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;

-- Locks held on WF_/PO_ tables
SELECT o.owner, o.object_name, lo.session_id, lo.oracle_username,
       lo.locked_mode, s.module, s.event
FROM   v$locked_object lo, dba_objects o, v$session s
WHERE  lo.object_id = o.object_id
AND    s.sid = lo.session_id
AND   (o.object_name LIKE 'WF\_%' ESCAPE '\' OR o.object_name LIKE 'PO\_%' ESCAPE '\');

Problem: row-lock contention on WF_NOTIFICATIONS / WF_ITEM_ACTIVITY_STATUSES / PO_HEADERS_ALL = Cause H. Note the blocker's module — a stuck form/user session or a batch job holding a PO row will serialize every approval behind it. Do not kill anything; record SID/SQL_ID.

7.3 What SQL the listener/engine is grinding on

SELECT sql_id, executions, ROUND(elapsed_time/1e6/NULLIF(executions,0),3) sec_per_exec,
       buffer_gets, disk_reads, SUBSTR(sql_text,1,120) sql_text
FROM   v$sqlarea
WHERE  (UPPER(sql_text) LIKE '%WF_NOTIFICATION%' OR UPPER(sql_text) LIKE '%WF_ITEM_ACTIVITY%')
AND    parsing_schema_name = 'APPS'
ORDER  BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

High sec_per_exec on WF queries often points at bloated WF tables/queues (millions of never-purged rows) degrading every dequeue/update — a capacity finding for the post-RCA remediation list, not for now.

---

PHASE 8 — Concurrent processing checks

SELECT r.request_id, t.user_concurrent_program_name prog, r.phase_code, r.status_code,
       TO_CHAR(r.actual_start_date,'DD-MON HH24:MI') started,
       TO_CHAR(r.actual_completion_date,'DD-MON HH24:MI') ended,
       ROUND((NVL(r.actual_completion_date,SYSDATE)-r.actual_start_date)*24*60) run_min,
       r.argument_text
FROM   fnd_concurrent_requests    r,
       fnd_concurrent_programs_tl t
WHERE  r.concurrent_program_id = t.concurrent_program_id
AND    r.program_application_id = t.application_id
AND    t.language = 'US'
AND    t.user_concurrent_program_name LIKE 'Workflow%'
AND    r.requested_start_date > SYSDATE - 1
ORDER  BY r.actual_start_date DESC;

Role clarification — Workflow Background Process (FNDWFBG): It is NOT in the email-response path. Inbound responses are processed online by the Inbound Notifications Agent Listener → Workflow Engine. FNDWFBG matters only indirectly:

• it processes deferred activities (if scheduled with deferred=Y) — so if the PO workflow defers activities after the response, a missing/slow FNDWFBG (or Deferred Agent Listener) delays the final PO status update;
• it processes timed-out and stuck items — relevant to cleanup, not to response latency.

Problem indicators: FNDWFBG not scheduled at all, erroring, or running for hours (a symptom of WF_DEFERRED bloat / storm volume, i.e., evidence for A/F, not a cause to fix by itself).

---

PHASE 9 — Workflow Mailer / Agent Listener log analysis

9.1 Which logs

Component	Log	Location
Notification Mailer (WFMLRSVC container)	FNDCPGSC<pid>.txt	$APPLCSF/$APPLLOG on the CM node (exact path = logfile_name from query 1.2)
Agent Listener service (WFALSNRSVC container)	FNDCPGSC<pid>.txt (separate pid)	same
GSM / Service Manager	FNDSM* / ICM log	$APPLCSF/$APPLLOG
DB-side AQ/WF errors	alert log + FND_LOG_MESSAGES (if AFLOG enabled)	DB node / query below

-- If FND logging was on for WF (module 'wf.%'):
SELECT TO_CHAR(timestamp,'DD-MON HH24:MI:SS') ts, module, SUBSTR(message_text,1,200) msg
FROM   fnd_log_messages
WHERE  module LIKE 'wf%'
AND    timestamp > SYSDATE - 1
ORDER  BY timestamp DESC FETCH FIRST 200 ROWS ONLY;

9.2 OS-level search (READ-ONLY)

cd $APPLCSF/$APPLLOG      # CM node from 1.2

# Errors and exceptions in mailer/listener container logs, last-modified first
ls -lt FNDCPGSC*.txt | head
egrep -in 'ORA-|WFMAIL|SQLException|OutOfMemory|Exception' FNDCPGSC<mailer_pid>.txt | tail -100
egrep -in 'imap|smtp|socket|timeout|authentication|unable to connect|connection re(set|fused)' \
      FNDCPGSC<mailer_pid>.txt | tail -100

# Inbound processing + a specific notification id (NIDs appear as e.g. 2846290/1)
egrep -in 'inbound|processing message|moved message|discard|unsolicited' FNDCPGSC<mailer_pid>.txt | tail -100
grep  -in '&NID' FNDCPGSC<mailer_pid>.txt

# Restart/bounce evidence
egrep -in 'shutting down|shutdown|starting|started|deactivat|restart' FNDCPGSC<mailer_pid>.txt
egrep -in 'WFMLRSVC|Workflow Mailer' $APPLCSF/$APPLLOG/FNDSM*.txt | tail -50

9.3 Correlating a delayed approval with the logs

1. From Phase 5/6 take: user reply time (T4), enq_time into WF_NOTIFICATION_IN (T6).
2. In the mailer log between T4 and T6, find the IMAP poll cycles: long silent gaps = mailer down or sleeping; repeated unable to connect/auth errors = IMAP problem; "processing message"/NID lines show exactly when the reply was read.
3. If the log shows the reply read at T4+minutes but enq_time is much later → DB-side enqueue stall (rare; check Phase 7 waits at that time).
4. If the log shows nothing until long after T4 and the container restarted repeatedly (9.2 restart grep) → the bounce loop is the inbound delay: every restart re-initializes IMAP and re-scans the inbox — with a storm-bloated inbox each scan is slow, compounding the loop.

If log level is too low to see message-level lines, the increase (Log Level = STATEMENT via OAM) is a config change — park it for the remediation phase, don't do it mid-RCA unless approved.

---

PHASE 10 — Mailbox / IMAP stage isolation

Leg	How to test (read-only)	Delay here means
User → corporate mail server	Received: headers of the reply message (mail team pulls one sample)	J — client/relay delay, not EBS
Corporate server → WF IMAP inbox	Mail-server delivery logs for the WF account; header timestamps	J/C
WF INBOX → Mailer read	Mailbox counts (below) + mailer log poll cycle	B/C/K
Mailer → WF_NOTIFICATION_IN	mailer log "processing" line vs enq_time	rare; DB waits
WF_NOTIFICATION_IN → Listener	4.3 latency query	D/E
Listener → Engine → PO	Phase 5/6 gaps 7→11	F/G/H/I

Mailbox counts (ask mail team, or view the WF account mailbox read-only): message counts and oldest-message age in INBOX, PROCESS, and DISCARD folders.

Observation	Interpretation
INBOX piling up, PROCESS moving	Mailer reading slower than arrival (threads/capacity — K)
INBOX piling up, PROCESS static	Mailer not polling at all (down/bouncing — B)
Thousands of old messages never expunged	Every poll rescans them → slow polls; also check DISCARD growth from auto-replies/bounces
Auto-reply/out-of-office storm to the WF account	Mail loop: outbound storm → OOO replies → inbound flood — A feeding C/D

---

PHASE 11 — Root-cause decision matrix

Correlate — never classify from one number:

#	Evidence combination	Classification
1	3.2 step-change + one dominant generator in 3.3	A — email storm (name the item/message)
2	NOTIFICATION_OUT huge/old + mailer OOM/restarts (1.2, 9.2)	B (+ K if threads minimal)
3	INBOX old messages + PROCESS static + mailer log gaps	C (driven by B)
4	NOTIFICATION_IN READY old + listener RUNNING + slow deq	D/E — listener capacity or stuck session (7.1)
5	4.3 latency high + listener STOPPED_ERROR	E
6	WF_DEFERRED oldest READY hours + 5.2 rows DEFERRED after respond	F
7	Sessions ACTIVE, non-idle waits, no blockers, all queues aging	G (engine/DB throughput)
8	blocking_session populated, TX locks on WF/PO tables	H
9	5.2 shows response processed fast but PO activities slow/ERROR	I
10	Header timestamps show delay before WF inbox	J
11	Thread counts=1, inbox never expunged, undersized JVM in log	K

Most common storyline matching your symptoms: A → B (+K) → C → intermittent D. The storm floods WF_NOTIFICATION_OUT, the mailer JVM thrashes and GSM bounces it, IMAP polling stops during every bounce, approval replies age in the INBOX, and each restart's inbox rescan is slower because of the storm's own bounce-backs. The inbound listener and engine are often healthy — verify with 4.3 before blaming them.

---

Final summary table (fill during execution)

Check	Current Evidence	Normal	Problem Indicator	Likely Cause	Next Action
Component status (1.1)		All RUNNING	STOPPED_ERROR / thrashing	B/E/K	Phase 9 logs
Container restarts (1.2)		1 old Active row	Multiple starts <24 h	B	Phase 9 restart grep
WF_NOTIFICATION_OUT (2.3)		READY low, young	Huge + old	A/B/K	3.3 top generators
WF_NOTIFICATION_IN age (2.1)		oldest READY < 5 min	Hours old	D/E	4.3 + 7.1
IN latency deq−enq (4.3)		< 1 min avg	Minutes+	E/G/H	7.1/7.2
WF_DEFERRED age (2.2)		Drains in minutes	Hours old	F	corr_id breakdown
WF_ERROR growth (2.4)		Stable	Growing fast	A (error loop)	Failing item type
Notifications/hr (3.2)		Baseline	Step change	A	Storm start anchor
Top generator (3.3)		Spread	One dominant	A	Owner of that workflow
Traced PO gap 4→6 (Ph.6)		< 2–3 min	Large	B/C/J	Mail logs / Phase 10
Traced PO gap 6→7		Seconds	Large	D/E	Listener
Traced PO gap 7→11		Seconds–min	Large	F/G/H/I	5.2 statuses + 7.2
Blocking (7.2)		None	TX locks on WF/PO	H	Record blocker
FNDWFBG (Ph.8)		Scheduled, minutes	Missing/erroring/hours	F symptom	After storm fixed

---

End of runbook. Corrective actions (mailer tuning, queue drain strategy, storm-source fix, inbox hygiene) to be designed only after the matrix above is filled in and the cause classified.

---

Disclaimer: run all queries with a read-only mindset on production; validate object names against your patch level. Corrective actions (mailer tuning, storm-source fixes, queue drain strategy, inbox hygiene) belong to a separate remediation phase — only after the root cause is classified.

Complete Step-by-Step Guide: Cloning Oracle E-Business Suite (EBS) with Enterprise Command Center (ECC)

 

Cloning an Oracle E-Business Suite (EBS) R12.2 environment is a standard task for any Oracle Apps DBA. However, when Enterprise Command Center (ECC) is integrated into the architecture, standard adcfgclone routines are not enough.

ECC operates on an independent WebLogic domain and uses specialized search cores (Solr/ZooKeeper). If you clone the EBS backend without cloning and synchronizing the ECC tier, your dashboards will fail due to broken token handshakes, dropped JDBC connection pools, or stale search indices. Below is the complete step-by-step runbook for cloning an integrated EBS and ECC stack.

---

📌 Architecture & Target Parameters

Before executing commands, determine your source and target mapping:

Parameter	Source Node	Target Node
EBS SID	PROD	TEST
Database Host	proddb.domain.com	testdb.domain.com
Apps Tier Host	prodapps.domain.com	testapps.domain.com
ECC Server Host	prodecc.domain.com	testecc.domain.com
ECC Base Path	/u01/oracle/ecc	/u01/oracle/ecc
WebLogic Port	7001 / 7002	7001 / 7002

---

Phase 1: Source Environment Preparation (Pre-Clone)

1. Execute Pre-Clone on EBS DB & Apps Tiers

On Database Tier (run as oracle):

cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME
perl adpreclone.pl dbTier

On Apps Tier Run File System (run as applmgr):

source <EBS_BASE>/EBSapps/env/env.env run
cd $ADMIN_SCRIPTS_HOME
perl adpreclone.pl appsTier

2. Take an ECC Tier Backup

Per MOS Doc ID 2495053.1 (Appendix D), use the packaged utility to archive metadata and domain configs:

cd $ECC_BASE/Oracle/quickInstall

# 1. Stop ECC services
./stopAllEcc.sh

# 2. Run backup archive
./backup.sh

# 3. Restart source services once completed
./startAllEcc.sh

Verification: Confirm that an archive named backup_<timestamp>.tar.gz has been generated in $ECC_BASE/Oracle/quickInstall/.

---

Phase 2: Replicate Files & Configure Target EBS

1. Restore or copy database files to testdb.domain.com.
2. Copy the run file system (EBSapps) to testapps.domain.com.
3. Copy the ECC backup archive to the target ECC node:

   rsync -avzP $ECC_BASE/Oracle/quickInstall/backup_* \
     oracle@testecc.domain.com:$ECC_BASE/Oracle/quickInstall/

Configure Cloned DB and Application Nodes

On Target DB Tier:

cd $ORACLE_HOME/appsutil/clone/bin
perl adcfgclone.pl dbTier

On Target Apps Tier:

cd $COMMON_TOP/clone/bin
perl adcfgclone.pl appsTier

Clean up obsolete FND nodes in Target DB:

sqlplus apps/<apps_password>
EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

Execute AutoConfig across all target EBS nodes.

---

Phase 3: Restore and Configure Target ECC Tier

1. Update ECC Connection Properties

Edit $ECC_BASE/Oracle/quickInstall/EccConfig.properties on testecc.domain.com to point to the new target database and ECC hostname:

# Target Database Details
DATABASE_HOSTNAME=testdb.domain.com
DATABASE_PORT=1521
DATABASE_SERVICE_NAME=TEST

# Target ECC Host Details
ECC_HOSTNAME=testecc.domain.com
ECC_MANAGED_SERVER_PORT=7001

2. Run Restore Scripts & Start Services

cd $ECC_BASE/Oracle/quickInstall
./createEnvFile.sh
./envSetup.sh
./startAllEcc.sh

Verification: Log into the WebLogic Admin Console at http://testecc.domain.com:7001/console. Go to Services > Data Sources > ebsDB > Monitoring > Testing and ensure Test Data Source returns Test Succeeded.

---

Phase 4: Establish EBS-to-ECC Trust Handshake

1. Generate Target DBC File

On the target EBS Apps Tier (run as applmgr):

source <EBS_BASE>/EBSapps/env/env.env run

java oracle.apps.fnd.security.AdminDesktop apps/<APPS_PASSWORD> \
  CREATE NODE_NAME=testecc.domain.com \
  DBC=$FND_SECURE/TEST.dbc

Transfer this file to the ECC server:

scp $FND_SECURE/TEST_testecc.domain.com.dbc \
  oracle@testecc.domain.com:$ECC_BASE/Oracle/quickInstall/connection.dbc

2. Update Context Variable & Profile Option

1. In the target EBS application context file, update:

   <s_ecc_web_host>http://testecc.domain.com:7001</s_ecc_web_host>

2. Run AutoConfig: $ADMIN_SCRIPTS_HOME/adautocfg.sh.
3. In EBS, verify the site-level profile option ECC: External Web URL points to http://testecc.domain.com:7001.

---

Phase 5: Reset Stale Metadata & Run Full Data Load

To avoid JobId -> -1 errors caused by source database tracking flags, reset the load metadata in SQL*Plus as APPS:

EXEC fnd_ecc_util.reset_data_load(p_application_short_name => 'ALL');
COMMIT;

Submit the concurrent request from EBS:

• Program: Enterprise Command Center Data Load
• System Name: EBS
• Application Name: ALL (or select a single application, e.g., AP)
• Load Type: FULL_LOAD
• Reset Data: True

---

🛠️ Common Post-Clone Troubleshooting

• OutOfMemoryError during full load: Increase Java heap settings (-Xms and -Xmx) in $ECC_BASE/Oracle/quickInstall/envSetup.sh and restart ECC.
• Invalid Security Handshake / Token Issues: Verify that FND_NODES has the target ECC hostname registered in uppercase format.
• Solr Lock Errors: Stop ECC, clear lingering core locks under $ECC_BASE/Oracle/ecc_server/solr/server/solr/cores/*, and restart the ECC services.

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.