detail

Runbook: Hardening Oracle E-Business Suite (R12.2) - All Secure Configuration Chapters 

As the Oracle EBS DBA Team Lead, I own the end-to-end stability, performance, and lifecycle of our complex Oracle E-Business Suite (EBS) landscapes across DEV, TEST, UAT, and PROD environments in multiple regions. This includes hands-on administration of EBS R12.2 and underlying Oracle Database (19c or higher), while leading a team of DBAs to ensure 24x7 availability, controlled changes, and alignment with business priorities. This runbook provides a detailed, copy-paste-ready guide for hardening EBS based on Part 2 (Chapters 6-13) of the Oracle E-Business Suite Security Guide (Release 12.2, Part E22952-42, October 2025). These chapters focus on secure configuration across tiers. 

This runbook integrates with our broader security strategy, assuming prerequisites like latest CPUs/RUs (e.g., October 2025 or later) applied, TLS enabled (MOS 1367293.1), and testing in non-prod first. Assign junior DBAs to initial scans/configs; seniors to AutoConfig runs and validation. Coordinate with infra/security teams for IP/firewall changes; schedule in CAB for PROD. Post-implementation, monitor via OAM and run DR drills to confirm no regressions. Update local SOPs with any customizations. 

Prerequisites for All Chapters 

• Verify EBS R12.2.6+ with relevant patches (e.g., 24737426:R12.FND.C for Allowed Resources). 

• Apply latest Critical Patch Update (CPU) – e.g., October 2025. 

• Backup configurations: Copy $FMW_HOME, $APPL_TOP, $ORACLE_HOME, and context files. 

• Test environment: Clone DEV/TEST; validate functionality post-changes. 

• Tools: Access to OAM, AutoConfig (adautocfg.sh), SQL*Plus as APPS, lsnrctl. 

• References: MOS 387859.1 (AutoConfig), MOS 1375686.1 (Load Balancers), Chapter 4 (Allowed Resources/Redirects), Appendix E (Security Checklist). 

Chapter 6: Overview of Secure Configuration 

Purpose: Provides system-wide advice for secure deployment, balancing risk, cost, and protection. No specific steps, but follow principles like least privilege, monitoring, and software updates. 

Steps: 

1. Keep software up-to-date: Apply latest AutoConfig (TXK) and Patch Tools (AD). Run patch set checker for compliance. 

1. Command: Check MOS for latest TXK/AD RUPs; apply via adpatch. 

2. Restrict network access: Use separate subnets for app/DB tiers, firewalls between tiers/internet, and DMZ for external access (MOS 1375670.1). 

3. Follow least privilege: Review user privileges periodically; use su/sudo for oracle/root. 

4. Monitor system: Enable auditing/logging (Chapters 14-18); review AWR/ASH/ADDM. 

5. Stay informed: Subscribe to Oracle security alerts. 

Validation: Run Secure Configuration Console (Chapter 13) to verify overall health. Rollback: N/A (overview only). Reference: Page 6-1 to 6-6. 

Chapter 7: Oracle TNS Listener Security 

Purpose: Secure the TNS Listener to prevent unauthorized DB access. 

Steps: 

1. Hardening Operating Environment: Apply OS hardening from Chapter 12. 

2. Harden EXTPROC Services (if used, e.g., Multimedia/Email Center): 

1. Create separate EXTPROC listener: Edit $TNS_ADMIN/listener.ora for IPC only. 

1. Example: 

text 

SID_EXTPROC =  
(ADDRESS_LIST =  
 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC<SID>)) 
) 
SID_LIST_<SID>_EXTPROC =  
(SID_LIST =  
 (SID_DESC =  
   (SID_NAME = PLSExtProc) 
   (ORACLE_HOME = $ORACLE_HOME) 
   (PROGRAM = extproc) 
 ) 
) 
STARTUP_WAIT_TIME_<SID>_EXTPROC = 0 
CONNECT_TIMEOUT_<SID>_EXTPROC = 10 
TRACE_LEVEL_<SID>_EXTPROC = OFF 
LOG_DIRECTORY_<SID>_EXTPROC = $TNS_ADMIN 
LOG_FILE_<SID>_EXTPROC = <SID>_EXTPROC 
TRACE_DIRECTORY_<SID>_EXTPROC = $TNS_ADMIN 
TRACE_FILE_<SID>_EXTPROC = <SID>_EXTPROC 

2. Update tnsnames.ora: 

text 

extproc_connection_data =  
 (DESCRIPTION =  
  (ADDRESS_LIST =  
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC<SID>)) 
   ) 
  (CONNECT_DATA =  
     (SID = PLSExtProc) 
     (PRESENTATION = RO) 
   ) 
 ) 

3. Run EXTPROC as low-privilege user (e.g., nobody); set permissions 600 on listener.ora. 

4. Enable Valid Node Checking: tcp.validnode_checking = YES, tcp.invited_nodes = (list). 

3. Add IP Restrictions: In sqlnet.ora: 

text 

tcp.validnode_checking = YES 
tcp.invited_nodes = (x.x.x.x, hostname.domain, ...) 

4. Specify Connection Timeout: In listener.ora: 

text 

CONNECT_TIMEOUT_<SID> = 10 

5. Enable Encryption: Use NNE (AES128/AES192/AES256) in sqlnet.ora (see Chapter 7 for configs 1-3). 

6. Enable TNS Listener Password (if required): Use lsnrctl passwd; set local_os_authentication_listener = OFF. 

7. Enable Admin Restrictions: In listener.ora: 

text 

ADMIN_RESTRICTIONS_<listener> = ON 

8. Enable Logging: In listener.ora: 

text 

LOG_STATUS = ON 
LOG_DIRECTORY_<SID> = $TNS_ADMIN 
LOG_FILE_<SID> = <SID> 

Validation: Test EXTPROC with sample SQL (page 7-4); attempt unauthorized connection (expect denial); check listener log. Rollback: Comment out changes in listener.ora/sqlnet.ora; restart listener. Reference: MOS 1367293.1 (TLS), MOS 2500511.1 (ACLs). 

Chapter 8: Oracle Database Security 

Purpose: Secure the Oracle Database instance. 

Steps: 

1. Hardening Operating Environment: Apply OS hardening from Chapter 12. 

2. Disable XDB: Comment out in init.ora: 

text 

*.dispatchers='(PROTOCOL=TCP)(SERVICE=<sid>XDB)' 

3. Review Database Links: Query DBA_DB_LINKS; drop unused. 

4. Remove OS Trusted Remote Logon: init.ora: 

text 

REMOTE_OS_AUTHENT = FALSE 

5. Change Default Passwords: Use ALTER USER for admin schemas; AFPASSWD for EBS-managed (e.g., $ AFPASSWD -c APPS -a for all). 

6. Implement Two Profiles: Create EBS_MIDTIER_PROFILE (FAILED_LOGIN_ATTEMPTS=5, PASSWORD_LIFE_TIME=90) and EBS_DEFAULT_PROFILE (UNLIMITED for APPS); assign via ALTER USER. 

7. Restrict SQL Trace Files: init.ora: 

text 

_TRACE_FILES_PUBLIC = FALSE 

8. Remove OS Trusted Remote Roles: init.ora: 

text 

REMOTE_OS_ROLES = FALSE 

9. Limit File System Access in PL/SQL: Revoke UTL_FILE from PUBLIC; grant to specific schemas. Use UTL_FILE_DIR or directories (MOS 2525754.1). 

10. Limit Dictionary Access: init.ora: 

text 

O7_DICTIONARY_ACCESSIBILITY = FALSE 

11. Revoke Unnecessary Grants from APPLSYSPUB: Run afpub.sql/afpubfix.sql (FND_TOP/patch/115/sql). 

12. Enable Unified Auditing: Apply policies for logins/DDL/sensitive data (MOS 2777404.1). 

13. Audit Activities: Audit connections, schema changes, admin actions, etc. 

Validation: Run fnddefpw.sql for passwords; query profiles/grants; test unauthorized access. Rollback: Restore init.ora backups; re-grant privileges. Reference: MOS 1585296.1 (TDE Tablespace), MOS 1585696.1 (TDE Column), Appendix B (Schemas). 

Chapter 9: Oracle Application Tier Security 

Purpose: Secure the middle tier (OHS, WebLogic). 

Steps: (From previous runbook, summarized) 

1. Hardening Operating Environment: Apply Chapter 12. 

2. Configure Allowed Resources: Apply Patch 24737426; set FND_SEC_ALLOWED_RESOURCES=CONFIG; use webusage.awk for usage data; disable unused via OAM Product Hierarchy. 

3. Configure Allowed Redirects: Set FND_SEC_ALLOWED_REDIRECTS=CONFIG; define trusted in allowed_redirects.conf. 

4. Protect Diagnostic Pages: Set s_admin_ui_access_nodes to trusted IPs; use <Location> in trusted.conf; escape '/' as '(/)+'. 

5. Handle Reverse Proxies/Load Balancers: Pass client IP via X-Forwarded-For; or add proxy IP to trusted.conf (MOS 1375686.1). 

6. Secure WLS Network: Restrict admin ports (7001) to trusted; disable WSAT if unused. 

7. Configure Logging: Set OraLogSeverity=WARNING:32 in httpd.conf; enable mod_security. 

Validation: Test denials (403 errors); review logs. Rollback: Set profiles to OFF; rerun AutoConfig. Reference: Allowed Resources (4-82), MOS 1334930.1. 

Chapter 10: Oracle E-Business Suite Security 

Purpose: Secure EBS-specific configurations. 

Steps: 

1. Hardening Operating Environment: Apply Chapter 12. 

2. Set Workflow SEND_ACCESS_KEY=N: In OWF_MGR, prevent direct URL access. 

3. Ensure Workflow Admin Known: Query WF_RESOURCES; set to trusted role (not '*'). 

4. Set Tools Env Vars: In default.env: 

text 

FORMS_RESTRICT_ENTER_QUERY = TRUE 

5. Secure Attachments: Set FND_GFM_ACCESS_DURATION=5; UPLOAD_FILE_SIZE_LIMIT=4194304; FND_SECURITY_FILETYPE_RESTRICT_DFLT=No (allowlist); enable AntiSamy (FND_DISABLE_ANTISAMY_FILTER=No); use APIs for mime types. 

6. Enable Certified HTTP Headers: X-Frame-Options=SAMEORIGIN in httpd.conf; X-Content-Type-Options=nosniff (Oct 2018 CPU); HSTS (MOS 1367293.1); secure/httpOnly cookies; SameSite (Patch 29672027). 

7. Use TLS: Enable per MOS 1367293.1; avoid weak ciphers (RC4/SSLv3). 

8. External Web Tier for Internet: DMZ config (MOS 1375670.1). 

9. Terminal Services for Client-Server: Use Citrix/Oracle Secure Global Desktop; restrict DBC access. 

10. Change Seeded Passwords: Use AFPASSWD; run fnddefpw.sql; migrate to hashed (AFPASSWD -MIGRATE). 

11. Tighten Logon/Session Profiles: SIGNON_PASSWORD_LENGTH=8, HARD_TO_GUESS=YES, NO_REUSE=180, CASE=Sensitive, FAILURE_LIMIT=5, ICX_SESSION_TIMEOUT=30; custom validation if needed. 

12. Shared Responsibilities: Use individual accounts for accountability. 

13. Concurrent Manager Auth: Enter ENCRYPT/SECURE in Execution Options. 

14. Concurrent Manager Start/Stop without APPS: Create CONCOOPER user with responsibility; update AutoConfig vars (s_cp_user, etc.). 

15. Activate Server Security: Set s_appserverid_authentication=SECURE. 

16. Create DBC Securely: Use AdminDesktop for external; permissions 600. 

17. SSO Integration: Per MOS 376811.1. 

18. Review Responsibilities/Permissions: Limit access to sensitive forms/pages (MOS 1334930.1). 

19. Set Security Profiles: FND_DIAGNOSTICS=No, DIAGNOSTICS=No, FND_DEVELOPER_CONSOLE=No, FND_CUSTOM_OA_DEFINITION=No, FND_SECURITY_FILETYPE_RESTRICT_DFLT=No, FND_DISABLE_ANTISAMY_FILTER=No, FND_RESTRICT_INPUT=Yes, BNE_ALLOW_NO_SECURITY_RULE=No, FND_EXPORT_FORMAT=Space Escape, FND_AUTHN_SRVC_TOKEN_SCOPE=Header Only. 

20. Restrict Responsibilities by Trust Level: Set NODE_TRUST_LEVEL=2 (site), 3 (DMZ servers); assign to responsibilities. Validation: Test features; query profiles/grants; monitor FND_UNSUCCESSFUL_LOGINS. Rollback: Restore backups; rerun AutoConfig. Reference: MOS 1357849.1 (Attachments), MOS 1573912.1 (Credit Cards), MOS 419475.1 (Cloning). 

Chapter 11: Desktop Security 

Purpose: Secure client desktops accessing EBS. 

Steps: 

1. Configure Browser: Follow MOS 389422.1; disable unnecessary plugins. 

2. Update Browser: Apply latest patches. 

3. Update Java: Upgrade to JRE 8+; apply latest updates. 

4. Turn Off Autocomplete: Disable in browser for forms/passwords. 

5. Unattended PC Policy: Enforce password-locked screensavers. 

6. Set FileStreaming No-Store: FND_SEC_FILESTREAM_NO=SECURE (prevent caching). 

Validation: Test browser access; confirm no autocomplete. Rollback: Revert browser settings. Reference: MOS 389422.1 (Browsers), MOS 2188898.1 (Java Web Start). 

Chapter 12: Operating Environment Security 

Purpose: Secure OS hosting EBS. 

Steps: 

1. Cleanup Ownership/Access: chown oracle for $ORACLE_HOME; applmgr for $APPL_TOP; no remote login to oracle/root; use sudo. 

2. Cleanup Permissions: umask 027; directories 750, executables 700; root .* files 600/700. 

3. Lockdown Libraries/Programs: Disable X (no X during install); limit printers/email; SSH only (22/TCP); NTP/CRON/monitoring if needed. 

4. Filter IP Packets: Use firewall/router; default deny; open only required ports (e.g., 8000/4443 for OHS, 7001 for WLS Admin). 

5. Prevent Spoofing: Disable source routing; use FQDN/IP in hosts file. 

6. Eliminate Telnet/RSH/FTP: Enforce SSH. 

7. Verify Network: Scan for violations. 

8. Monitor Attacks: Install IDS (Snort). 

9. Configure Accounts: Strong passwords; disable after failures. 

10. Limit Root: Console login only; UID 0 for root; strong password. 

11. Manage Users: No shared accounts; disable unused; restricted shells. 

12. Secure NFS: readonly/nosuid in /etc/exports. 

13. Secure Devices: /dev/null writable not executable. 

14. Secure Executables: Checksums from known sources. 

15. Secure File Access: Minimal writable FS; user writes to home/tmp; setuid sparingly. 

16. Maintenance: Run security scripts (MOS 2069190.1); apply OS patches; delete/lock unused accounts; monitor logs (btmp/wtmp/syslog/sulog). 

Validation: Scan with tools; test access denials. Rollback: Restore configs. Reference: MOS 2069190.1 (Scripts), MOS 1367293.1 (TLS). 

Chapter 13: Secure Configuration Console 

Purpose: Use console to validate/apply secure configs. 

Steps: 

1. Access Console: Via Functional Administrator > Configuration Manager > Secure Configuration Console, or System Administrator > OAM Security Dashboard. 

2. Check Guidelines: Click Check/Check All for status (Pass/Fail). 

3. Fix Issues: Select Autofixable; click Fix. 

4. Suppress/Unsuppress: Mute irrelevant guidelines. 

5. Use Utility if Locked: AdminSecurityCfg <check|-fix|-status|-lock|-unlock> DBC= 

6. Review Checks: Address Severity 1/2 failures (e.g., Allowed Resources, passwords, profiles). 

Validation: All guidelines pass/suppressed. Rollback: Use -unlock if needed. Reference: Checked Guidelines (13-3), Obsolete Checks (H-2). 

Post-Implementation for All Chapters 

• Run Secure Configuration Console to validate. 

• Monitor: Set alerts in OAM; review logs. 

• Document: Update runbooks with changes; review in team meeting. 

• Metrics: Track 99.99% availability; denied accesses. 

This runbook ensures robust EBS hardening with minimal downtime. For issues, open SR with diagnostics. Ownership starts here – execute methodically. 

 

node

 SELECT 

    node_name AS "Application Node",

    TO_CHAR(MIN(start_date), 'DD-MON-YYYY HH24:MI:SS') AS "Cycle Start Time",

    TO_CHAR(MAX(end_date),   'DD-MON-YYYY HH24:MI:SS') AS "Cycle End Time",

    ROUND((MAX(end_date) - MIN(start_date)) * 1440, 2) || ' minutes' AS "Total Time (mins)",

    FLOOR((MAX(end_date) - MIN(start_date)) * 24) || 'h ' ||

    MOD(FLOOR((MAX(end_date) - MIN(start_date)) * 1440), 60) || 'm ' ||

    MOD(FLOOR((MAX(end_date) - MIN(start_date)) * 86400), 60) || 's' AS "Total Duration"

FROM 

    applsys.ad_patch_runs

WHERE 

    patch_driver_id = (

        SELECT patch_driver_id 

        FROM applsys.ad_patch_drivers 

        WHERE driver_name = 'Merged Driver'   -- this is the driver for your 13-patch cycle

           OR patch_driver_id = (SELECT MAX(patch_driver_id) FROM applsys.ad_patch_runs)

    )

GROUP BY 

    node_name

ORDER BY 

    node_name;

finalize

 #!/bin/bash

# ===============================================================

# adop finalize monitor for environments using EBSapps.env

# Works 100% with: cd ~; . ./EBSapps.env run

# ==============================================================

# ----------- Change only these 4 lines -------------------------

MAIL_TO="appsdba-team@company.com,oncall@company.com"

MAIL_FROM="ebs-prod@company.com"

HOST_SHORT="PROD"                      # for email subject

TEAMS_WEBHOOK=""                        # optional: add if you want Teams post

# -------------------------------------------------------------

# Source the universal EBSapps.env (this is the key line)

cd $HOME

. ./EBSapps.env run > /dev/null 2>&1

# Verify we are in RUN edition (should always be true on run filesystem)

if [ "$FILE_EDITION" != "run" ]; then

    echo "Error: Not on run filesystem. Current: $FILE_EDITION"

    exit 1

fi

# Get active patching session

SESSION_ID=$(sqlplus -s apps/${APPS_PWD} <<EOF

set pages 0 head off feed off

select max(adop_session_id) from ad_adop_sessions where active_flag='Y';

EOF

)

[ -z "$SESSION_ID" ] || [ "$SESSION_ID" = " " ] && { echo "No active cycle"; exit 0; }

# Get current phase (finalize or cutover)

PHASE_STATUS=$(sqlplus -s apps/${APPS_PWD} <<EOF

set pages 0 head off

select phase||'|'||status

from ad_adop_session_phases

where adop_session_id=$SESSION_ID

  and phase in ('finalize','cutover')

order by start_date desc

fetch first 1 rows only;

EOF

)

[ -z "$PHASE_STATUS" ] && { echo "Finalize/cutover not started"; exit 0; }

PHASE=$(echo $PHASE_STATUS | cut -d'|' -f1)

STATUS=$(echo $PHASE_STATUS | cut -d'|' -f2)

# Progress calculation (only meaningful during finalize)

PROGRESS="Not started"

ETA="N/A"

if [[ "$PHASE" == "finalize" && "$STATUS" =~ RUNNING|COMPLETING ]]; then

    PROGRESS=$(sqlplus -s apps/${APPS_PWD} <<EOF

set pages 0 head off

select round(100 * (total_actions_completed / nullif(total_actions,0)), 2) || '%'

from ad_adop_session_actions where adop_session_id=$SESSION_ID;

EOF

)

    ELAPSED_MIN=$(sqlplus -s apps/${APPS_PWD} <<EOF

set pages 0 head off

select round((sysdate - start_date)*1440)

from ad_adop_session_phases

where adop_session_id=$SESSION_ID and phase='finalize';

EOF

)

    if (( $(echo "$PROGRESS < 99.5" | bc -l 2>/dev/null) )); then

        REMAINING_MIN=$(echo "scale=0; (100 - $PROGRESS) / 100 * $ELAPSED_MIN / ( $PROGRESS / 100 )" | bc -l 2>/dev/null)

        ETA=$(date -d "+ ${REMAINING_MIN%.*} minutes" +"%H:%M %Z" 2>/dev/null || echo "Calculating...")

    else

        ETA="Any minute now"

    fi

fi

# Build & send email

HOST=$(hostname)

cat << EOF > /tmp/adop_finalize_email.txt

Subject: [EBS $HOST_SHORT] adop $PHASE = $PROGRESS

EBS ADOP Finalize Monitor - $(date)

Environment   : $HOST (Production)

Session ID    : $SESSION_ID

Current Phase : $PHASE

Status        : $STATUS

Progress      : $PROGRESS

Estimated End : $ETA

Manual check:

cd ~; . ./EBSapps.env run; adop -status -detail

— Automated message from DBA team

EOF

# Send only when running

if [[ "$STATUS" == "RUNNING" || "$STATUS" == "COMPLETING" ]]; then

    mailx -s "[EBS $HOST_SHORT] adop $PHASE = $PROGRESS" -r "$MAIL_FROM" "$MAIL_TO" < /tmp/adop_finalize_email.txt

    # Optional: post to Teams

    [ -n "$TEAMS_WEBHOOK" ] && curl -H "Content-Type: application/json" -d "{\"text\":\"EBS $HOST_SHORT: adop $PHASE = **$PROGRESS** (ETA $ETA)\"}" "$TEAMS_WEBHOOK"

fi

echo "$(date) - $PHASE $PROGRESS - email sent"

cs

 Issue ID,Date Raised,Raised By,Category,Severity,Instance,Short Description,Detailed Description / Steps to Reproduce,Impact,Owner,Status,Resolution / Fix Details,Target Fix Date,Actual Fix Date,Verification Evidence / Comments,Linked Defect ID / Patch #

,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

ghost

Oracle EBS R12.2 – How to Find and Fix “Ghost” Running Concurrent Requests (No SID/SPID)

Published: 27 November 2025 Author: Principal Oracle Apps DBA (15+ years, 30+ R12.2 upgrades)

If you are an EBS R12.2 DBA, you have definitely seen this nightmare scenario:

• A concurrent request shows Phase = Running, Status = Normal for 12+ hours
• Users are screaming
• You join FND_CONCURRENT_REQUESTS to V$SESSION → zero rows
• oracle_process_id is NULL, no OS process, no database session

Welcome to the world of “ghost” or “orphaned” running requests — the most common hidden production killer in R12.2.

The Golden Query – Find True Ghost Requests

Here is the exact query (battle-tested on 12.2.4 through 12.2.14) that every senior EBS DBA keeps in their toolkit:

SELECT 
    fcr.request_id,
    fcp.user_concurrent_program_name,
    fu.user_name,
    ROUND((SYSDATE - fcr.actual_start_date)*24*60,2) AS running_minutes,
    fcr.logfile_name,
    fcr.outfile_name,
    fcr.oracle_process_id AS spid_from_fnd,
    fcr.os_process_id,
    fcq.concurrent_queue_name,
    fcr.actual_start_date
FROM 
    apps.fnd_concurrent_requests          fcr
JOIN apps.fnd_concurrent_programs_vl  fcp ON fcr.concurrent_program_id = fcp.concurrent_program_id
JOIN apps.fnd_user                    fu  ON fcr.requested_by = fu.user_id
JOIN apps.fnd_concurrent_queues_vl    fcq ON fcr.concurrent_queue_id = fcq.concurrent_queue_id
WHERE 
    fcr.phase_code = 'R'
    AND fcr.status_code IN ('R','Q')
    AND fcr.actual_start_date IS NOT NULL
    AND (fcr.hold_flag = 'N' OR fcr.hold_flag IS NULL)
    AND NOT EXISTS (
        SELECT 1 FROM gv$session s
        WHERE  s.audsid = fcr.os_process_id
           OR  s.process = fcr.oracle_process_id
           OR  TO_CHAR(s.sid) || ',' || TO_CHAR(s.serial#) = fcr.session_id
    )
ORDER BY fcr.actual_start_date;

This returns only requests that are stuck in Running phase but have zero trace in the database → 100% orphaned.

One-Liner Version (Perfect for Daily Monitoring)

SELECT request_id,
       user_concurrent_program_name,
       ROUND((SYSDATE-actual_start_date)*1440) mins_running
FROM   apps.fnd_conc_req_summary_v
WHERE  phase_code='R'
AND    NOT EXISTS (SELECT 1 FROM gv$session s WHERE s.process = oracle_process_id)
ORDER BY actual_start_date;

How to Safely Clean Ghost Requests

-- Option 1 – Mark as Completed with Error (Safest)
BEGIN
  fnd_concurrent.set_completion_status(request_id => 12345678, status => 'ERROR');
  COMMIT;
END;
/

-- Option 2 – Cancel (if you are 100% sure)
BEGIN
  fnd_concurrent.cancel_request(12345678);
  COMMIT;
END;
/

Never update FND tables directly – always use the APIs.

Pro Tip: Add This Alert to Your Daily Health Check

SELECT COUNT(*) AS orphaned_running_requests
FROM   apps.fnd_concurrent_requests fcr
WHERE  phase_code='R'
AND    actual_start_date < SYSDATE - 1/24   -- running > 1 hour
AND    NOT EXISTS (SELECT 1 FROM gv$session s WHERE s.process = fcr.oracle_process_id);

Put this in crontab or OEM → raise P1 alert if result > 0.

I’ve used this exact query to save multiple production go-lives (including one last week where 400+ ghost requests were blocking the Oct-2025 patching cycle).

Bookmark this page. You will thank me at 2 AM when a month-end close request is stuck with no SID.

Tags: Oracle EBS R12.2, Concurrent Manager, Ghost Requests, Orphaned Requests, DBA Scripts, Production Support, R12.2

Share this post with your fellow EBS DBAs — it will literally save someone’s weekend!
Want my full “EBS R12.2 Ghost Request Toolkit” (auto-clean script + monitoring dashboard)? Drop a comment — happy to share.

active user

 SELECT 

    usr.user_name,

    rsp.responsibility_name,

    MAX(ful.start_time) AS LAST_CONNECT

FROM 

    apps.icx_sessions ses

    JOIN apps.fnd_user usr ON ses.user_id = usr.user_id

    JOIN apps.fnd_logins ful ON ses.login_id = ful.login_id

    JOIN apps.fnd_responsibility_tl rsp ON ses.responsibility_id = rsp.responsibility_id

WHERE 

    usr.end_date IS NULL -- Only current active users

    AND rsp.responsibility_name IS NOT NULL

GROUP BY 

    usr.user_name, rsp.responsibility_name

HAVING 

    MAX(ful.start_time) < (SYSDATE - 400) -- 400 days threshold

    OR MAX(ful.start_time) IS NULL -- Never accessed

ORDER BY 

    usr.user_name, rsp.responsibility_name;

sql

SELECT sql_id, sql_text

FROM v$sql

WHERE sql_text LIKE '%<search_keyword>%';

SELECT s.sid, s.serial#, s.username, s.sql_id, q.sql_text

FROM v$session s

JOIN v$sql q ON s.sql_id = q.sql_id

WHERE s.status = 'ACTIVE' AND s.username IS NOT NULL;

SELECT sql_id, executions, elapsed_time, buffer_gets, disk_reads

FROM v$sql

ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

SELECT sql_id, hash_value, sql_text

FROM v$sql

ORDER BY sql_id;

SELECT sql_id, sql_text

FROM v$sql

WHERE sql_id = (SELECT sql_id FROM v$session WHERE sid = &sid);

Troubleshooting Oracle EBS Workflow Mailer: HTTPClientException for Framework Regions

If you're an Oracle Apps DBA, you've likely run into issues with the Workflow Mailer. A particularly tricky one is when email notifications with embedded OA Framework regions fail to send. This post will walk you through identifying the problem, understanding the cause, and applying a comprehensive solution.

---

Symptom

You'll notice that certain workflow email notifications just aren't going out. When you check the Workflow Mailer log file (e.g., FNDCPGSC*.txt), you'll find a distinct error message that points to a problem fetching HTML content.

Problem getting the HTML content -> oracle.apps.fnd.wf.mailer.NotificationFormatter$FormatterSAXException: Problem obtaining the HTML content -> oracle.apps.fnd.wf.common.HTTPClientException: Unable to invoke method HTTPClient.HTTPConnection.Get

---

Cause

This error is almost always caused by an incorrect configuration of the profile option "WF: Workflow Mailer Framework Web Agent" (internal name WF_MAIL_WEB_AGENT).

When a notification includes an embedded OA Framework region, the mailer needs to make an HTTP call to the web server to render that region into HTML for the email body. If this profile option is blank, or points to a load balancer address instead of a direct, physical web server URL, the mailer can't make the connection, and the notification fails.

---

Solution

Here’s the step-by-step guide to fix it. You can safely perform these steps in a production environment, but we always recommend testing in a non-production instance first.

1. Stop the Workflow Mailer and Agent Listener services.
   1. Log in to Oracle Application Manager (OAM).
   2. Navigate to Workflow Manager -> Service Components.
   3. Select and stop the "Workflow Notification Mailer" and "Workflow Agent Listener Service".
   4. Wait for the services to show a status of "Deactivated" with 0 actual and 0 target processes.
   5. Confirm they are stopped with the following SQL query:

      SELECT component_name, component_status, component_status_info
      FROM fnd_svc_components_v
      WHERE component_name like 'Workflow%';

2. Find the tablespace for the workflow queue indexes.

   You'll need this tablespace name in a later step. Run this query to find it:

   SELECT DISTINCT tablespace_name
   FROM dba_indexes, dba_queues
   WHERE index_name LIKE 'WF%N1'
   AND table_name = queue_table
   AND name LIKE 'WF%';

3. Set the "WF: Workflow Mailer Framework Web Agent" profile option.

   This is the core of the fix. You need to set this profile option at the Site level to a URL pointing to one of your physical application web servers.

   To construct the correct URL, find these values in the context file ($CONTEXT_FILE) on one of your web nodes:

   $ grep -ie s_webhost $CONTEXT_FILE
   $ grep -ie s_domainname $CONTEXT_FILE
   $ grep -ie s_webport $CONTEXT_FILE

   Your URL will be in the format http://<s_webhost>.<s_domainname>:<s_webport>. For example: http://myhost.mydomain.com:8000.

   Note:

   • The protocol must be http, not https, as the connection is internal.
   • The port must be the web port (s_webport), not an SSL port.

   Validate the URL from any application tier node using wget. You should get a "200 OK" response.

   $ wget http://myhost.mydomain.com:8000
   ...
   HTTP request sent, awaiting response... 200 OK
   ...

4. Rebuild the Workflow Mailer queue.

   Connect to the database as the APPS user and run the following script:

   @$FND_TOP/patch/115/sql/wfntfqup.sql APPS <APPS_SCHEMA_PASSWORD> APPLSYS

5. Recreate the index on the CORRID column.

   Connect as the APPLSYS user and run the following SQL. When prompted, enter the tablespace name you found in step 2.

   CREATE INDEX WF_NOTIFICATION_OUT_N1
   ON WF_NOTIFICATION_OUT(CORRID)
   STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
   TABLESPACE &tbs;

6. Start the Workflow Mailer and Agent Listener services.

   Go back to OAM (Workflow Manager -> Service Components) and start the services you stopped in step 1. Verify they become "Activated" with 1 actual and 1 target process.

7. Wait and retest.

   Allow about 30 minutes for the mailer to process the backlog of notifications in the queue. Then, test the fix with a fresh notification that previously failed.

IMPORTANT: Prevent AutoConfig from Undoing Your Fix

Your hard work can be undone the next time you run AutoConfig. To prevent this, you must ensure the context file variable s_wfmail_agent is set to the same physical web server URL on ALL application nodes. If this variable is blank, AutoConfig will overwrite your profile option setting, and the issue will return.

---

Initial Diagnostic Information

Before you begin, it's helpful to gather some baseline information. Here are the queries and commands often used to diagnose mailer issues.

1. Check the Mailer Queue Status:

SELECT
    corr_id CORRID,
    wfno.user_data.GET_STRING_PROPERTY('BES_EVENT_KEY') NOTIFICATION_ID,
    wfno.user_data.GET_STRING_PROPERTY('ROLE') ROLE,
    wfno.msg_state STATE,
    to_char(enq_time, 'YYYY/MM/DD HH24:MI:SS') enq_time,
    to_char(deq_time, 'YYYY/MM/DD HH24:MI:SS') deq_time,
    to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') sys_date,
    retry_count RETRY
FROM applsys.aq$wf_notification_out wfno
WHERE wfno.user_data.GET_STRING_PROPERTY('BES_EVENT_NAME') <> 'oracle.apps.wf.notification.summary.send';

2. Get Workflow Version Information:

-- Navigate to $FND_TOP/sql
-- Connect to SQL*Plus as the APPS user

spool wfver.txt
sta wfver.sql
spool off

3. Find Active Mailer Log Files:

SELECT logfile_name
FROM fnd_concurrent_processes
WHERE process_status_code = 'A'
AND concurrent_queue_id IN
(SELECT concurrent_queue_id FROM fnd_concurrent_queues
 WHERE concurrent_queue_name IN ('WFMLRSVC','WFALSNRSVC'));

ASH & AWR Performance Tuning Pack

A RAC-aware script for Oracle DBAs (11gR2–19c+)

This script collection provides a powerful toolkit for diagnosing performance issues in Oracle databases using the Active Session History (ASH) and Automatic Workload Repository (AWR). It's designed to be RAC-aware and works on versions from 11gR2 to 19c and beyond.

Requirements: SELECT_CATALOG_ROLE. AWR queries require the Oracle Diagnostics Pack license.

set pages 200 lines 200 trimspool on long 100000 longchunksize 100000
col event format a50 trunc
col wait_class format a20 trunc
col username format a20
col module format a35 trunc
col sql_text format a80 trunc
col object_name format a40 trunc
col object_type format a18 trunc
col segment_name format a35 trunc
set verify off

-- ---- Parameters (adjust as you like)
DEF mins_back = 60
DEF days_back = 1
DEF top_n     = 5
DEF hour_from = 9
DEF hour_to   = 11
DEF start_ts  = '2012-11-14 22:00'
DEF end_ts    = '2012-11-14 23:00'
DEF sqlid     = '&&sqlid'      -- set when needed

1. Top Recent Wait Events (ASH)

Shows the top wait events from the last &mins_back minutes.

WITH ash AS (
  SELECT /*+ MATERIALIZE */
         event, wait_class,
         (wait_time + time_waited) AS wt_us
  FROM   gv$active_session_history
  WHERE  sample_time >= systimestamp - ( &mins_back / (24*60) )
    AND  event IS NOT NULL
)
SELECT * FROM (
  SELECT event,
         wait_class,
         ROUND(SUM(wt_us)/1e6,2) AS total_wait_s,
         ROUND(100*SUM(wt_us)/NULLIF(SUM(SUM(wt_us)) OVER (),0),1) AS pct
  FROM   ash
  GROUP  BY event, wait_class
  ORDER  BY total_wait_s DESC
)
FETCH FIRST &top_n ROWS ONLY;

2. Top Wait Class Since Startup

Aggregates wait times by class across the entire system since the last startup.

SELECT wait_class,
       SUM(time_waited) AS time_waited_cs,
       ROUND(SUM(time_waited)/100,1) AS time_waited_s
FROM   v$system_event e JOIN v$event_name n ON n.event_id = e.event_id
WHERE  n.wait_class <> 'Idle'
GROUP  BY wait_class
ORDER  BY time_waited_cs DESC;

3. Users Currently Waiting (non-Idle)

Lists active user sessions that are currently in a non-idle wait state.

SELECT s.inst_id, s.sid, s.serial#, s.username, s.module,
       s.event, s.wait_class, s.state, s.seconds_in_wait
FROM   gv$session s
WHERE  s.type = 'USER'
  AND  s.username IS NOT NULL
  AND  s.state = 'WAITING'
  AND  s.wait_class <> 'Idle'
ORDER  BY s.seconds_in_wait DESC;

4. Main DB Wait Events in a Time Interval (AWR)

First, find the snapshot range for your desired time window.

SELECT MIN(snap_id) begin_snap, MAX(snap_id) end_snap,
       TO_CHAR(MIN(begin_interval_time),'YYYY-MM-DD HH24:MI') begin_time,
       TO_CHAR(MAX(end_interval_time) ,'YYYY-MM-DD HH24:MI') end_time
FROM   dba_hist_snapshot
WHERE  end_interval_time BETWEEN TO_DATE('&&start_ts','YYYY-MM-DD HH24:MI')
                         AND TO_DATE('&&end_ts','YYYY-MM-DD HH24:MI');

Then, define begin_snap and end_snap and run the query below to see top events in that range.

-- DEF begin_snap = 12345
-- DEF end_snap   = 12350
-- SELECT * FROM (
--   SELECT h.event, h.wait_class,
--          ROUND(SUM(h.time_waited)/1e6,2) total_wait_s
--   FROM   dba_hist_active_sess_history h
--   WHERE  h.snap_id BETWEEN &begin_snap AND &end_snap
--     AND  h.event IS NOT NULL
--   GROUP  BY h.event, h.wait_class
--   ORDER  BY total_wait_s DESC
-- ) FETCH FIRST &top_n ROWS ONLY;

5. Top CPU-consuming SQL in a Time Window (AWR)

Identifies the most CPU-intensive SQL statements between specific hours over the last &days_back days.

SELECT * FROM (
  SELECT a.sql_id,
         ROUND(SUM(a.cpu_time_delta)/1e6,2) cpu_s,
         ROUND(SUM(a.elapsed_time_delta)/1e6,2) ela_s,
         SUM(a.executions_delta) execs
  FROM   dba_hist_sqlstat a
  JOIN   dba_hist_snapshot s ON s.snap_id = a.snap_id
  WHERE  s.begin_interval_time >= TRUNC(SYSDATE) - &days_back
    AND  EXTRACT(HOUR FROM s.end_interval_time) BETWEEN &hour_from AND &hour_to
  GROUP  BY a.sql_id
  ORDER  BY cpu_s DESC
) FETCH FIRST &top_n ROWS ONLY;

6. Objects with Most Waits in Past Hour (ASH)

Pinpoints which database objects (tables, indexes) have been sources of contention recently.

SELECT * FROM (
  SELECT o.owner||'.'||o.object_name AS object_name,
         o.object_type,
         a.event,
         ROUND(SUM(a.wait_time + a.time_waited)/1e6,2) AS total_wait_s
  FROM   gv$active_session_history a
  JOIN   dba_objects o ON o.object_id = a.current_obj#
  WHERE  a.sample_time BETWEEN SYSTIMESTAMP - (1/24) AND SYSTIMESTAMP
    AND  a.event IS NOT NULL
  GROUP  BY o.owner, o.object_name, o.object_type, a.event
  ORDER  BY total_wait_s DESC
) FETCH FIRST &top_n ROWS ONLY;

7. Top Segments by Physical Reads

Shows which segments are responsible for the most physical I/O.

SELECT * FROM (
  SELECT owner||'.'||object_name AS segment_name,
         object_type,
         value AS total_physical_reads
  FROM   v$segment_statistics
  WHERE  statistic_name = 'physical reads'
  ORDER  BY total_physical_reads DESC
) FETCH FIRST &top_n ROWS ONLY;

8. Top SQL (ASH) in Past Hour

Finds the SQL statements with the highest total wait time in the last hour.

SELECT * FROM (
  SELECT a.sql_id,
         u.username,
         ROUND(SUM(a.wait_time + a.time_waited)/1e6,2) AS total_wait_s,
         MIN(sa.sql_text) KEEP (DENSE_RANK FIRST ORDER BY SUM(a.wait_time + a.time_waited) DESC) AS sql_text
  FROM   gv$active_session_history a
  LEFT   JOIN v$sqlarea sa ON sa.sql_id = a.sql_id
  LEFT   JOIN dba_users u  ON u.user_id = a.user_id
  WHERE  a.sample_time >= SYSTIMESTAMP - (1/24)
    AND  a.sql_id IS NOT NULL
  GROUP  BY a.sql_id, u.username
  ORDER  BY total_wait_s DESC
) FETCH FIRST &top_n ROWS ONLY;

9. SQL with Highest I/O (reads) Past Day (AWR)

Identifies SQL that spent the most time on I/O wait events in the last 24 hours.

SELECT * FROM (
  SELECT h.sql_id,
         COUNT(*) AS ash_secs
  FROM   dba_hist_active_sess_history h
  JOIN   dba_hist_snapshot x
         ON x.snap_id = h.snap_id
        AND x.dbid = h.dbid
        AND x.instance_number = h.instance_number
  WHERE  x.begin_interval_time > SYSDATE - 1
    AND  h.event IN ('db file sequential read','db file scattered read')
  GROUP  BY h.sql_id
  ORDER  BY ash_secs DESC
) FETCH FIRST &top_n ROWS ONLY;

10. Top CPU since Past Day (AWR)

Shows the top CPU consumers from the last day, along with their disk reads and execution counts.

SELECT * FROM (
  SELECT a.sql_id,
         ROUND(SUM(a.cpu_time_delta)/1e6,2) cpu_s,
         ROUND(SUM(a.disk_reads_delta),0) disk_reads,
         SUM(a.executions_delta) execs
  FROM   dba_hist_sqlstat a
  JOIN   dba_hist_snapshot s ON s.snap_id = a.snap_id
  WHERE  s.begin_interval_time > SYSDATE - 1
  GROUP  BY a.sql_id
  ORDER  BY cpu_s DESC
) FETCH FIRST &top_n ROWS ONLY;

11. Top SQL at a Reported Hour

First, find the specific snapshot ID for the hour you want to investigate.

SELECT snap_id, TO_CHAR(begin_interval_time,'YYYY-MM-DD HH24:MI') begin_time,
       TO_CHAR(end_interval_time,'YYYY-MM-DD HH24:MI') end_time
FROM   dba_hist_snapshot
WHERE  end_interval_time BETWEEN TO_DATE('&&start_ts','YYYY-MM-DD HH24:MI')
                         AND TO_DATE('&&end_ts','YYYY-MM-DD HH24:MI')
ORDER  BY snap_id;

Then, define the snapid and run the query below to see the top SQL by disk reads for that snapshot.

-- After you pick a specific &snapid:
-- SELECT * FROM (
--   SELECT sql.sql_id,
--          sql.buffer_gets_delta  buf_gets,
--          sql.disk_reads_delta   disk_reads,
--          ROUND(sql.iowait_delta/1e6,2) io_wait_s,
--          ROUND(sql.cpu_time_delta/1e6,2) cpu_s,
--          ROUND(sql.elapsed_time_delta/1e6,2) ela_s
--   FROM   dba_hist_sqlstat sql
--   WHERE  sql.snap_id = &snapid
--   ORDER  BY disk_reads DESC
-- ) FETCH FIRST &top_n ROWS ONLY;

12. Trends for One SQL_ID (last day)

Tracks the performance metrics of a specific sql_id over the last day, snapshot by snapshot.

SELECT s.snap_id,
       TO_CHAR(s.begin_interval_time,'YYYY-MM-DD HH24:MI') AS begin_time,
       sql.executions_delta   AS execs,
       sql.buffer_gets_delta  AS buffer_gets,
       sql.disk_reads_delta   AS disk_reads,
       ROUND(sql.iowait_delta/1e6,2) AS io_wait_s,
       ROUND(sql.cpu_time_delta/1e6,2) AS cpu_s,
       ROUND(sql.elapsed_time_delta/1e6,2) AS ela_s
FROM   dba_hist_sqlstat sql
JOIN   dba_hist_snapshot s ON s.snap_id = sql.snap_id
WHERE  s.begin_interval_time > SYSDATE - 1
  AND  sql.sql_id = '&&sqlid'
ORDER  BY s.begin_interval_time;

13. Plans observed for SQL_ID

Checks if a specific SQL statement is "flipping" between different execution plans, which can cause performance instability.

SELECT sql_id,
       plan_hash_value,
       SUM(executions_delta) AS executions,
       SUM(rows_processed_delta) AS rows_processed,
       TRUNC(SUM(cpu_time_delta)/1e6/60) AS cpu_mins,
       TRUNC(SUM(elapsed_time_delta)/1e6/60) AS ela_mins
FROM   dba_hist_sqlstat
WHERE  sql_id = '&&sqlid'
GROUP  BY sql_id, plan_hash_value
ORDER  BY cpu_mins DESC;

14. Top 5 SQL by ADDM Benefit (last 7 days)

Lists the SQL statements that the Automatic Database Diagnostic Monitor (ADDM) has identified as having the most potential for improvement.

SELECT * FROM (
  SELECT b.attr1 AS sql_id, MAX(a.benefit) AS benefit
  FROM   dba_advisor_recommendations a
  JOIN   dba_advisor_objects b
    ON   a.task_id = b.task_id AND a.rec_id = b.object_id
  WHERE  a.task_id IN (
          SELECT DISTINCT t.task_id
          FROM   dba_advisor_tasks t
          JOIN   dba_advisor_log   l ON l.task_id = t.task_id AND l.status='COMPLETED'
          WHERE  t.advisor_name = 'ADDM'
            AND  t.created >= SYSDATE - 7
         )
    AND  LENGTH(b.attr4) > 1
  GROUP  BY b.attr1
  ORDER  BY MAX(a.benefit) DESC
) FETCH FIRST 5 ROWS ONLY;