ADOP Cutover and Rollback Runbook

1. ADOP Phase Overview

ADOP runs Oracle EBS patching across five phases. Each phase must complete before the next begins. The patching happens on the patch edition (run filesystem), keeping the run edition (production filesystem) live throughout apply and finalize.

• prepare: Creates the patch edition. Copies the run filesystem to the patch filesystem. Activates the patch edition in the database.
• apply: Applies patches to the patch edition only. Production users are completely unaffected.
• finalize: Compiles and generates objects in the patch edition. The longest phase for large patches.
• cutover: Switches the database from run edition to patch edition. Requires a brief outage (typically 5–30 minutes). All current sessions must be terminated.
• cleanup: Drops the old run edition. Reclaims disk space on the old patch filesystem. Can run after cutover while users are active.

2. Check Current ADOP Session Status

-- Current ADOP session and phase status
SELECT
  s.adop_session_id,
  s.prepare_status,
  s.apply_status,
  s.finalize_status,
  s.cutover_status,
  s.cleanup_status,
  s.abandon_status,
  s.start_date,
  s.end_date
FROM apps.ad_adop_sessions s
WHERE s.adop_session_id = (
  SELECT MAX(adop_session_id)
  FROM apps.ad_adop_sessions
);

-- Check active workers (should be 0 if no ADOP is running)
SELECT COUNT(*) AS active_workers
FROM apps.ad_adop_session_patches sp
JOIN apps.ad_adop_sessions s ON s.adop_session_id = sp.adop_session_id
WHERE sp.apply_status = 'R'; -- R = Running

-- Get the edition names (run vs patch)
SELECT edition_name, edition_type
FROM dba_editions
ORDER BY edition_name;

3. Recover from Stuck Phases

3a. Resume a failed or interrupted ADOP session

If ADOP was interrupted (SSH disconnect, server restart, OOM), you can resume from where it stopped:

-- As applmgr OS user, source EBS environment
-- . /u01/oracle/ebs/EBSapps.env run

-- Resume the most recent session
-- adop phase=apply patches=<patch_number> resume=yes
-- adop phase=finalize resume=yes
-- adop phase=cutover resume=yes

3b. Prepare phase stuck

-- Check if the patch edition was created
SELECT edition_name, edition_type, parent_edition_name
FROM dba_editions;

-- If prepare is stuck: check for locked sessions preventing edition creation
SELECT s.sid, s.serial#, s.username, s.status, s.sql_id,
  ROUND((SYSDATE - s.logon_time)*24, 1) AS hours_connected
FROM v$session s
WHERE s.status = 'ACTIVE'
  AND s.username IS NOT NULL
ORDER BY s.logon_time;

3c. Finalize phase hung on object compilation

-- Check what's being compiled
SELECT object_name, object_type, status, last_ddl_time
FROM dba_objects
WHERE status = 'INVALID'
  AND owner = 'APPS'
ORDER BY last_ddl_time DESC
FETCH FIRST 20 ROWS ONLY;

-- If specific objects keep failing, compile manually in the patch edition
-- ALTER SESSION SET EDITION = &PATCH_EDITION_NAME;
-- ALTER PACKAGE APPS.PROBLEM_PACKAGE COMPILE;
-- ALTER VIEW APPS.PROBLEM_VIEW COMPILE;

4. When Cutover Fails or Stalls

4a. Terminate blocking sessions preventing cutover

-- Find sessions blocking cutover (non-ADOP sessions in APPS)
SELECT
  s.sid, s.serial#, s.username, s.status,
  s.program, s.machine,
  ROUND((SYSDATE - s.logon_time) * 60, 0) AS mins_connected
FROM v$session s
WHERE s.username IS NOT NULL
  AND s.username != 'SYS'
  AND s.program NOT LIKE '%adop%'
ORDER BY s.logon_time;

-- Terminate blocking sessions (use with caution)
-- ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

-- If OS-level signal is needed (for disconnected sessions)
-- SELECT s.sid, s.serial#, p.spid AS os_pid
-- FROM v$session s JOIN v$process p ON p.addr = s.paddr
-- WHERE s.sid = &YOUR_SID;
-- Send SIGTERM to &OS_PID (as oracle OS user)

4b. Cutover hung on “Waiting for database edition switch”

-- Check if the database edition switch is actually happening
SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'DEFAULT_EDITION';

-- If switch is hung, check for edition-blocking locks
SELECT l.sid, s.username, l.type, l.lmode, l.request
FROM v$lock l
JOIN v$session s ON s.sid = l.sid
WHERE l.type = 'ED';  -- ED = Edition lock

5. Rollback a Patch Cycle

-- Rollback is possible only if cutover_status != 'C' (Completed)
SELECT cutover_status, abandon_status
FROM apps.ad_adop_sessions
WHERE adop_session_id = (
  SELECT MAX(adop_session_id) FROM apps.ad_adop_sessions
);

-- Rollback the patch (as applmgr, EBS env sourced)
-- adop phase=abandon

-- After abandon completes, start cleanup to reclaim disk space
-- adop phase=cleanup

6. Emergency adop cleanup

If a previous ADOP session ended abnormally and left the system in an inconsistent state (no active ADOP running but adop phase=prepare fails with “active session exists”), you may need to force-abandon and cleanup:

-- Check for orphaned ADOP session
SELECT
  adop_session_id,
  prepare_status, apply_status, finalize_status,
  cutover_status, cleanup_status, abandon_status
FROM apps.ad_adop_sessions
ORDER BY adop_session_id DESC
FETCH FIRST 3 ROWS ONLY;

-- Force-mark orphaned session as abandoned (if truly no ADOP is running)
UPDATE apps.ad_adop_sessions
SET abandon_status = 'C'
WHERE adop_session_id = &SESSION_ID
  AND abandon_status IS NULL;
COMMIT;

-- Then run cleanup to drop old edition and reclaim space
-- adop phase=cleanup

-- If cleanup itself fails, force cleanup
-- adop phase=cleanup force=yes

7. Post-Cleanup Verification

-- Verify only one edition exists post-cleanup
SELECT edition_name, edition_type, parent_edition_name
FROM dba_editions;

-- Verify default edition is the run edition
SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'DEFAULT_EDITION';

-- Check invalid objects after cleanup
SELECT COUNT(*) AS invalid_objects
FROM dba_objects
WHERE status = 'INVALID'
  AND owner IN ('APPS', 'APPLSYS');

-- Recompile invalid objects
-- Run as APPS: EXEC UTL_RECOMP.recomp_parallel(4);

-- Verify disk space was reclaimed
-- du -sh $PATCH_TOP (should be smaller or gone)