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.

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;

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.

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

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

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

---

1. List All EBS Tables with Supplemental Log Groups

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

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

---

2. Detailed Column-Level Supplemental Logging Breakdown

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

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

---

3. Aggregated Summary by EBS Schema / Module

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

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

---

Quick Reference: Understanding LOG_GROUP_TYPE Values

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

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

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

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

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

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

---

1. Typical Symptom

A concurrent request may appear as follows:

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

The Request Diagnostics window may display a message similar to:

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

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

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

No action required. This is a normal condition.

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

---

2. What Does Pending / Standby Mean?

Internally the concurrent request normally has:

PHASE_CODE  = P
STATUS_CODE = Q

Which represents:

P = Pending
Q = Standby

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

The processing flow is approximately:

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

---

3. Check the Concurrent Request

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

set lines 220
set pages 100

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

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

Typical output:

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

This confirms:

Pending / Standby

---

4. Identify the Concurrent Program

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

set lines 220
set pages 100

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

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

Pay particular attention to:

RUN_ALONE_FLAG
QUEUE_METHOD_CODE

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

---

5. Check Conflict Resolution Manager Status

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

set lines 200
set pages 100

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

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

A healthy CRM would normally show something similar to:

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

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

---

6. Check How Many Requests Are in Pending / Standby

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

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

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

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

---

7. List All Pending / Standby Requests

set lines 220
set pages 500

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

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

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

---

8. Find the Oldest Standby Request

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

An old Standby request may indicate either:

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

---

9. Check Concurrent Program Incompatibilities

Oracle EBS stores concurrent program incompatibility definitions in FND_CONCURRENT_PROGRAM_SERIAL.

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

set lines 220
set pages 200

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

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

       OR

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

---

10. Find Currently Running Incompatible Requests

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

set lines 220
set pages 200

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

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

    UNION

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

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

---

11. Check Previous Executions of the Same Program

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

set lines 220
set pages 100

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

This helps answer:

Is only one submission stuck?

OR

Does every execution of this program enter Pending / Standby?

---

12. Check Whether the Request Is on Hold

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

Normally:

HOLD_FLAG = N

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

---

13. Check Concurrent Manager Capacity

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

set lines 220
set pages 200

column manager_name format a50
column target_node format a30

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

For example, a Standard Manager may have:

Standard Manager
Maximum Processes : 40
Running Processes : 40

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

---

14. Check Conflict Resolution Manager at OS Level

Source the Oracle EBS application environment first.

. ./EBSapps.env run

Then check the application processes:

ps -ef | grep FNDLIBR | grep -v grep

Also check Service Manager processes:

ps -ef | grep FNDSM | grep -v grep

---

15. Locate the CRM Log

Concurrent Manager logs are normally available below:

$APPLCSF/$APPLLOG

For example:

cd $APPLCSF/$APPLLOG

ls -ltr | grep -i FNDCRM

If the exact file name is unknown:

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

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

---

16. Check ICM and CRM Together

set lines 200
set pages 100

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

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

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

---

17. Useful Summary Query

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

set lines 220
set pages 500

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

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

---

18. Troubleshooting Decision Tree

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

---

19. Scenario 1 – Running Incompatible Request Found

Example:

Request 123456789
Pending / Standby

Waiting because:

Request 123450001
Program: Payables Purge
Phase  : Running

In this situation the CRM is functioning correctly.

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

---

20. Scenario 2 – No Incompatible Request Found

Suppose:

Request 123456789
Pending / Standby

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

This situation requires deeper investigation.

Check:

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

---

21. Scenario 3 – Many Requests in Pending / Standby

For example:

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

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

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

---

22. Scenario 4 – Only a Few Standby Requests

For example:

Standby Requests : 3
FNDCRM Processes : 1

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

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

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

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

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

1. Investigation Flow for a Slow or Hanging Concurrent Request

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

2. Check the Current Concurrent Request Status

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

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

3. Map the Request to Its Database Session and SQL

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

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

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

4. Review the Current Wait Event

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

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

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

5. Identify Blocking Sessions

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

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

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

6. Compare Runtime with Previous Executions

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

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

7. Tracing a Concurrent Request

Option A: Enable Trace on the Concurrent Program

Navigate to:

System Administrator → Concurrent → Program → Define

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

Find the Trace File on Oracle 19c

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

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

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

Option B: Initialization SQL Statement – Custom

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

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

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

Option C: Trace a Reproducible SQL*Plus Test

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

-- Execute only the problematic SQL here.

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

Format the Trace with TKPROF

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

8. Patch and File-Version Diagnostics

Identify the Patch That Delivered a Specific File Version

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

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

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

Check Whether an EBS Patch Is Recorded

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

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

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

Check the EBS Release

SELECT release_name
FROM   apps.fnd_product_groups;

Check Database Component Versions

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

Inspect Package Header and Selected Source Lines

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

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

9. Index and Statistics Checks

List Index Columns

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

Check When Table Statistics Were Gathered

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

Gather EBS Table Statistics

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

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

10. Retrieve Profile Option Values

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

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

11. Materialized View Refresh

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

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

12. Useful UNIX Commands

Find a File Version Embedded in an Executable

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

List All Embedded File Headers

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

Check Soft and Hard Resource Limits

ulimit -aS
ulimit -aH

Compare an ODF Object with the Database

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

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

13. Relinking: Use Only Through an Approved Change

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

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

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

Recommended Evidence to Capture for an RCA

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

Conclusion

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

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

 

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

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

A reliable investigation must answer four questions:

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

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

---

1. Understand the ADOP Failure Boundary

An online patching cycle normally contains the following phases:

prepare → apply → finalize → cutover → cleanup

Each phase has a different failure profile.

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

Always identify the failing phase before attempting recovery.

---

2. Confirm the Correct EBS Environment

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

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

Expected considerations:

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

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

---

3. Check the Current ADOP Session

Start with the session status:

adop -status

This identifies whether:

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

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

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

adop -validate

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

---

4. Scan the Logs Before Opening Individual Files

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

Scan the latest session:

adopscanlog

Display error-level messages:

adopscanlog loglevel=error

Scan all available sessions:

adopscanlog session_id=0

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

adopscanlog -help

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

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

Worker failed
Phase failed
ADOP exiting with status 1

The real cause may appear earlier:

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

Always investigate upward from the final failure message.

---

5. Locate the ADOP Log Directory

The non-editioned file system stores ADOP logs beneath:

$NE_BASE/EBSapps/log/adop

List the most recently updated files:

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

A typical structure separates logs by:

• ADOP session
• Timestamp
• Phase
• Application node
• Patch number
• Worker

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

Useful searches include:

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

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

---

6. Monitor a Running Session with ADOPMON

For long-running phases, use:

adopmon

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

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

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

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

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

---

7. Investigate Failed Patch Workers

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

Check the worker status using the AD Controller utility:

adctrl

Typical actions include:

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

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

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

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

---

8. Query AD_ZD_LOGS for Database-Side Evidence

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

Connect using an approved secure method:

sqlplus /nolog

CONNECT apps

Review recent entries:

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

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

Search for likely failure messages:

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

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

SELECT MAX(log_sequence)
FROM   ad_zd_logs;

After reproducing the failure:

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

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

---

9. Use ADZDSHOWLOG.sql

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

sqlplus /nolog

CONNECT apps
@$AD_TOP/sql/ADZDSHOWLOG.sql

This is particularly helpful when:

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

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

---

10. Generate an Online-Patching Diagnostic Report

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

Check the local usage first:

$AD_TOP/bin/adzdreport.pl -help

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

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

The report is useful for identifying:

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

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

---

11. Perform Database Health Checks

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

Check invalid objects

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

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

Check tablespace usage

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

Also verify:

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

Check blocking sessions

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

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

---

12. Check File-System and Node Health

On every application node, verify:

df -g

On Linux, use:

df -h

Also verify:

hostname
date
ulimit -a

Check the ownership and permissions of:

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

In multi-node systems, confirm:

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

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

---

13. Build a Failure Timeline

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

Capture:

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

Correlate events across:

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

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

---

14. Restart or Abort: Make the Correct Decision

Restart the existing session when:

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

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

Consider abort only when:

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

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

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

Abort and cleanup may also be combined where appropriate:

adop phase=abort,cleanup cleanup_mode=full

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

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

---

15. Special Caution After Cutover Starts

Once cutover begins, the operational situation changes significantly.

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

If cutover fails:

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

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

---

16. Post-Recovery Validation

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

Run:

adop -status
adop -validate

Then verify:

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

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

---

17. Production-Safe Command Checklist

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

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

df -g
ulimit -a

Database-side tools:

@$AD_TOP/sql/ADZDSHOWLOG.sql

Diagnostic utility:

$AD_TOP/bin/adzdreport.pl -help

Recovery commands—use only after diagnosis and approval:

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

---

18. Common Troubleshooting Mistakes

Avoid the following practices:

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

---

Conclusion

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

A strong Apps DBA workflow is:

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

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