Monday, November 16, 2026

Essential Oracle EBS R12.2 Health Check Queries: Context Files & Edition Ports


Managing an Oracle E-Business Suite (EBS) R12.2 environment requires keeping close tabs on your application tier configuration, especially across the Run and Patch editions. Whether you are preparing for an Online Patching (adop) cycle, troubleshooting clone issues, or performing routine DBA health checks, verifying the status of your context files and managed server ports directly from the database can save hours of manual file-system digging.

Below are three essential SQL queries targeting the apps.fnd_oam_context_files table to validate your context file versions and verify port allocations across both editions.

1. Check Context File Status and Versions in the Database

Before running AutoConfig or starting an adop phase, ensure there are no invalid or stale context files registered in the database. This query filters out template/metadata entries and flags any context files where the status is NULL or not marked as VALID.

SQL
SELECT 
    path,
    status,
    serial_number,
    version 
FROM 
    apps.fnd_oam_context_files 
WHERE 
    name NOT IN ('TEMPLATE', 'METADATA') 
    AND (status IS NULL OR status != 'VALID')
ORDER BY 
    path, version;

What to look for:

  • Ideally, this query should return no rows (or only historical/expected entries), confirming that active context files across all nodes are valid and synchronized.

  • If you see duplicate paths with conflicting versions, you may need to clean up fnd_oam_context_files and re-run AutoConfig.

2. Check Managed Server Ports in the Run Edition

In Oracle EBS R12.2, the Run and Patch editions run on distinct port pools. Rather than logging into every application node and grepping through XML files, you can extract the exact WebLogic managed server ports (oacore, forms, oafm, forms-c4ws, and oaea) directly from the XML stored in the database for the Run Edition.

SQL
SELECT 
    EXTRACTVALUE(XMLType(TEXT), '//host[@oa_var="s_hostname"]') AS hostname,
    EXTRACTVALUE(XMLType(TEXT), '//oacore_server_ports') AS oacore_ports,
    EXTRACTVALUE(XMLType(TEXT), '//forms_server_ports') AS forms_ports,
    EXTRACTVALUE(XMLType(TEXT), '//oafm_server_ports') AS oafm_ports,
    EXTRACTVALUE(XMLType(TEXT), '//forms-c4ws_server_ports') AS forms_c4ws_ports,
    EXTRACTVALUE(XMLType(TEXT), '//oaea_server_ports') AS oaea_ports
FROM 
    apps.fnd_oam_context_files
WHERE 
    name NOT IN ('TEMPLATE', 'METADATA')
    AND (status IS NULL OR status != 'H')
    AND EXTRACTVALUE(XMLType(TEXT), '//file_edition_type') = 'run'
    AND CTX_TYPE = 'A';

Query Breakdown:

  • CTX_TYPE = 'A' filters specifically for Application Tier context files.

  • status != 'H' excludes historical context file records.

  • file_edition_type = 'run' isolates the currently active Run file system.

3. Check Managed Server Ports in the Patch Edition

To verify that your Patch Edition is properly configured on its alternate port pool—and ensure there are no port conflicts with the Run Edition—run the exact same XML extraction targeting the Patch Edition:

SQL
SELECT 
    EXTRACTVALUE(XMLType(TEXT), '//host[@oa_var="s_hostname"]') AS hostname,
    EXTRACTVALUE(XMLType(TEXT), '//oacore_server_ports') AS oacore_ports,
    EXTRACTVALUE(XMLType(TEXT), '//forms_server_ports') AS forms_ports,
    EXTRACTVALUE(XMLType(TEXT), '//oafm_server_ports') AS oafm_ports,
    EXTRACTVALUE(XMLType(TEXT), '//forms-c4ws_server_ports') AS forms_c4ws_ports,
    EXTRACTVALUE(XMLType(TEXT), '//oaea_server_ports') AS oaea_ports
FROM 
    apps.fnd_oam_context_files
WHERE 
    name NOT IN ('TEMPLATE', 'METADATA')
    AND (status IS NULL OR status != 'H')
    AND EXTRACTVALUE(XMLType(TEXT), '//file_edition_type') = 'patch'
    AND CTX_TYPE = 'A';

Pro Tip: Compare the output of Queries 2 and 3 side-by-side after a fresh fs_clone or Rapid Clone to guarantee that the port pools shifted cleanly between your fs1 and fs2 file systems.

Suggested Blogspot Labels: Oracle EBS, EBS R12.2, Oracle Apps DBA, SQL Scripts, ADOP, AutoConfig

No comments:

Post a Comment