Syed Anwar Ahmed – Oracle DBA Blog

Tag: ADOP PATCHING

  • When ADOP Remembers Too Much: Fixing Patch Failures Caused by Stale Metadata in Oracle EBS

    During an Oracle E-Business Suite ADOP patching cycle in a multi-node environment, the apply phase failed on one node while completing successfully on others. Despite retries — including downtime mode — the issue persisted, pointing to a deeper inconsistency within the patching framework.

    Symptoms Observed

    • ADOP session status: FAILED
    • Patch applied successfully on some nodes, failed on admin node
    • Repeated failures even with restart=no, abandon=yes, and downtime mode
    • No immediate actionable error from standard logs

    Timeline of Events

    T0 -- Patch execution initiated (ADOP apply phase)
T1 -- Failure observed on admin node
T2 -- Retry using downtime mode -- Failure persists
T3 -- ADOP session review shows inconsistent state
T4 -- Internal metadata tables analyzed
T5 -- Cleanup performed (tables + restart directory)
T6 -- Patch re-executed -- Success across all nodes

    Investigation

    Step 1: Check ADOP Session State

    Query the ADOP session status to understand the current state across all nodes:

    -- Check current ADOP session status
SELECT session_id, node_name, phase, status,
       start_date, end_date
FROM applsys.ad_adop_sessions
ORDER BY start_date DESC;

-- Check apply phase status per node
SELECT s.session_id, n.node_name, p.phase_code,
       p.status, p.start_date, p.end_date
FROM applsys.ad_adop_sessions s,
     applsys.ad_adop_session_phases p,
     applsys.fnd_nodes n
WHERE s.session_id = p.session_id
AND p.node_id = n.node_id
ORDER BY p.start_date DESC;

    The existing session showed status FAILED with the apply phase partially completed — a clear indicator of inconsistent execution state across nodes.

    Step 2: Check adalldefaults.txt

    Reviewed the defaults file for any relevant configuration:

    cat $APPL_TOP/admin/$TWO_TASK/adalldefaults.txt | grep -i missing
-- Key parameter found:
-- MISSING_TRANSLATED_VERSION = No

    Modifying and retrying with this parameter had no impact, confirming the issue was not translation-related.

    Step 3: Check Install Processes Table

    -- Check for stale entries in FND_INSTALL_PROCESSES
SELECT COUNT(*) FROM applsys.fnd_install_processes;

-- View stale entries in detail
SELECT process_status, process_name, last_update_date
FROM applsys.fnd_install_processes
ORDER BY last_update_date DESC;

-- Check AD_DEFERRED_JOBS
SELECT COUNT(*) FROM applsys.ad_deferred_jobs;
SELECT * FROM applsys.ad_deferred_jobs;

    Observation: FND_INSTALL_PROCESSES contained stale entries from the failed session. AD_DEFERRED_JOBS was empty.

    Root Cause

    The failure was caused by stale and inconsistent ADOP metadata tables — specifically APPLSYS.FND_INSTALL_PROCESSES and APPLSYS.AD_DEFERRED_JOBS. ADOP internally relies on these tables to track patch progress checkpoints, deferred job execution, and restart state management. When these tables retain entries from failed or incomplete sessions, ADOP assumes an incorrect execution state, leading to patch reconciliation failure, apply phase breakdown, and node-level inconsistencies.

    Resolution Steps

    Step 1: Backup Critical Tables

    -- Always backup before any cleanup
CREATE TABLE applsys.fnd_install_processes_bak AS
SELECT * FROM applsys.fnd_install_processes;

CREATE TABLE applsys.ad_deferred_jobs_bak AS
SELECT * FROM applsys.ad_deferred_jobs;

-- Verify backups
SELECT COUNT(*) FROM applsys.fnd_install_processes_bak;
SELECT COUNT(*) FROM applsys.ad_deferred_jobs_bak;

    Step 2: Drop Stale Metadata Tables

    Dropping these tables forces ADOP to rebuild clean metadata during the next run:

    DROP TABLE applsys.fnd_install_processes;
DROP TABLE applsys.ad_deferred_jobs;

    Step 3: Reset the Restart Directory

    The restart directory can silently preserve failure states. Back it up and create a fresh one:

    cd $APPL_TOP/admin/$TWO_TASK

-- Backup existing restart directory
mv restart restart_bkp_$(date +%Y%m%d)

-- Create fresh restart directory
mkdir restart

-- Verify
ls -la | grep restart

    Step 4: Re-run the Patch

    adop phase=apply \
     patches=<patch_id> \
     restart=no \
     abandon=yes \
     apply_mode=downtime

    The patch completed successfully across all nodes after the metadata cleanup.

    Before vs After

    ComponentBefore FixAfter Fix
    ADOP SessionFailedSuccessful
    Node ConsistencyPartialFull
    Restart BehaviorStuckClean
    Patch ExecutionIncompleteCompleted

    Key Takeaways

    • ADOP is state-driven — even when logs appear clean, internal metadata drives execution decisions
    • Partial success is a clue — if some nodes succeed and one fails, focus on local metadata, not the patch itself
    • The restart directory matters — it can silently preserve failure states and must be validated before retrying
    • Downtime mode is not a fix-all — even in downtime, ADOP still reads metadata tables; corruption persists unless cleaned
    • Always backup before cleanup — never drop tables without creating a backup first

    When NOT to Use This Approach

    Avoid applying this fix if the issue is caused by missing database patches (ETCC warnings), file system or permission issues, incorrect patch sequencing, or environment misconfiguration. Always validate the root cause before performing any metadata cleanup.

    This scenario highlights a subtle but critical behavior in ADOP — sometimes patch failures are not caused by the patch itself, but by what the system remembers about past attempts. By resetting stale metadata, we allow ADOP to re-evaluate the environment cleanly, leading to successful execution.

    Have questions or faced a similar issue? Reach out at sdanwarahmed@gmail.com.