Oracle Database Upgrade Triage Runbook

1. Pre-upgrade Validation Failures (preupgrade.jar)

Run preupgrade.jar from the new Oracle home against the source database. It generates preupgrade_fixups.sql (run before upgrade) and postupgrade_fixups.sql (run after). Never skip this step.

-- Run from NEW ORACLE_HOME against the source DB (still open)
java -jar $NEW_OH/rdbms/admin/preupgrade.jar FILE DIR /tmp/preupgrade_output

-- Review the output summary
cat /tmp/preupgrade_output/preupgrade.log

-- Execute pre-upgrade fixups (source DB, as SYSDBA)
sqlplus / as sysdba @/tmp/preupgrade_output/preupgrade_fixups.sql

Common preupgrade.jar warnings and how to handle them

-- Check for components in non-VALID status
SELECT comp_id, comp_name, status, version
FROM dba_registry
ORDER BY comp_id;

-- Purge recycle bin (often flagged by preupgrade)
PURGE DBA_RECYCLEBIN;

-- Gather dictionary stats (preupgrade will flag if stale)
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

-- Adjust compatible parameter if flagged
SHOW PARAMETER compatible;
-- Must be at minimum: 11.2.0 for 19c target, 12.2.0 for 23ai target

-- Check AWR retention (preupgrade may warn about large AWR)
SELECT retention FROM dba_hist_wr_control;

-- Check for XML DB issues (required for upgrade)
SELECT status FROM dba_registry WHERE comp_id = 'XDB';

2. DBUA vs Manual Upgrade Decision

-- Launch DBUA (requires DISPLAY for GUI, or use -silent for headless)
$NEW_OH/bin/dbua -silent -dbName ORCL \
  -oracleHome $NEW_OH \
  -sysDBAUserName sys \
  -sysDBAPassword <password>

-- DBUA log location (tail for progress)
tail -f $NEW_OH/cfgtoollogs/dbua/<dbname>/upgrade1/dbua*.log

3. catctl.pl / catupgrd.sql Failures and Restarts

catctl.pl is the parallel upgrade driver in 12.2+. It runs the upgrade in phases across parallel processes. If it fails mid-phase, it can be restarted from the failure point — you do not need to start over.

-- Manual upgrade sequence (new ORACLE_HOME)
-- Step 1: Shut down source DB cleanly
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;

-- Step 2: Start in upgrade mode under NEW ORACLE_HOME
export ORACLE_HOME=$NEW_OH
export PATH=$NEW_OH/bin:$PATH
sqlplus / as sysdba
STARTUP UPGRADE;

-- Step 3: Run catctl.pl (parallel upgrade, 4 processes)
$NEW_OH/perl/bin/perl $NEW_OH/rdbms/admin/catctl.pl \
  -n 4 \
  -d $NEW_OH/rdbms/admin \
  catupgrd.sql

-- Monitor progress in another session
sqlplus / as sysdba
SELECT time, message FROM sys.upgrade_progress_restart
ORDER BY time DESC
FETCH FIRST 20 ROWS ONLY;

Restarting catctl.pl after a failure

-- Check which phase failed — review catupgrd*.log files
ls -lt $NEW_OH/cfgtoollogs/dbua/*/upgrade1/
-- Or if running manually:
ls -lt /tmp/catctl_*.log

-- Re-run catctl.pl to resume from failure point
$NEW_OH/perl/bin/perl $NEW_OH/rdbms/admin/catctl.pl \
  -n 4 \
  -d $NEW_OH/rdbms/admin \
  catupgrd.sql
-- catctl.pl automatically detects partial progress and resumes

-- If catctl.pl refuses to restart, check for leftover lock
SELECT * FROM sys.registry$history ORDER BY action_time DESC;

-- Force phase restart (use phase number from catctl log)
$NEW_OH/perl/bin/perl $NEW_OH/rdbms/admin/catctl.pl \
  -n 4 -p <failed_phase_number> \
  -d $NEW_OH/rdbms/admin \
  catupgrd.sql

catupgrd.sql on older releases (pre-12.2)

-- For 11g/12.1 source: catupgrd.sql runs directly in SQL*Plus
-- Startup upgrade first, then:
sqlplus / as sysdba @$NEW_OH/rdbms/admin/catupgrd.sql

-- Check for errors after completion
sqlplus / as sysdba @$NEW_OH/rdbms/admin/utlu122s.sql

4. Invalid Objects Post-Upgrade

Some number of invalid objects after upgrade is normal. The target: zero invalid objects in SYS/SYSTEM schemas. User schema invalids are expected and must be recompiled by the application owner.

-- Count invalid objects by schema
SELECT owner, object_type, COUNT(*) AS invalid_count
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type
ORDER BY owner, invalid_count DESC;

-- Step 1: Run utlrp.sql (recompiles invalid objects in parallel)
sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/utlrp.sql

-- Step 2: Run utlrp.sql a second time if count decreases
-- (dependency chains sometimes need two passes)
sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/utlrp.sql

-- After recompile: check remaining invalids in SYS/SYSTEM
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status = 'INVALID'
  AND owner IN ('SYS', 'SYSTEM')
ORDER BY object_type, object_name;

-- Manually recompile a specific object
ALTER PACKAGE sys.dbms_metadata COMPILE;
ALTER PACKAGE sys.dbms_metadata COMPILE BODY;

-- For user schema invalids — run as the schema owner
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema=>'SCOTT', compile_all=>FALSE);

5. Timezone File Version Mismatch (DST Patch)

Oracle maintains its own timezone data separate from the OS. The new Oracle home must have a timezone file version ≥ the source database's timezone version. Mismatches block upgrade startup or cause post-upgrade errors.

-- Check current timezone file version in source DB
SELECT version FROM v$timezone_file;

-- Check timezone version in NEW Oracle home (before upgrade)
-- grep TIMEZONE_FILE_VERSION $NEW_OH/oracore/zoneinfo/timezlrg_*.dat
-- Or: ls $NEW_OH/oracore/zoneinfo/

-- After upgrade: check if DB needs DST upgrade
SELECT
  property_name,
  property_value
FROM database_properties
WHERE property_name LIKE '%TIMEZONE%';

-- Run DBMS_DST to upgrade timezone data (if needed)
-- This is a two-step process: prepare + upgrade

-- Step 1: Begin prepare (check for affected data)
EXEC DBMS_DST.BEGIN_PREPARE(new_version => <target_tz_version>);

-- Step 2: Find tables with TIMESTAMP WITH TIME ZONE columns that need update
SELECT * FROM sys.dst$affected_tables;
SELECT * FROM sys.dst$error_table;

-- Step 3: End prepare
EXEC DBMS_DST.END_PREPARE;

-- Step 4: Upgrade (requires RESTRICTED mode)
STARTUP RESTRICT;
EXEC DBMS_DST.BEGIN_UPGRADE(new_version => <target_tz_version>);
EXEC DBMS_DST.UPGRADE_DATABASE(parallel => TRUE, parallel_degree => 4);
EXEC DBMS_DST.END_UPGRADE(parallel => TRUE, parallel_degree => 4);

6. Component Upgrade Failures (APEX, XML DB, JVM, Spatial)

-- Check registry for component status
SELECT comp_id, comp_name, status, version, modified
FROM dba_registry
ORDER BY comp_id;

-- Valid statuses after upgrade: VALID, OPTION OFF, REMOVED
-- Problem statuses: INVALID, LOADING, UPGRADING

XML DB (XDB) — INVALID after upgrade

-- XDB invalid is common, especially on 19c upgrades
sqlplus / as sysdba

-- Remove and reinstall XDB
@$ORACLE_HOME/rdbms/admin/catnoqm.sql
@$ORACLE_HOME/rdbms/admin/catqm.sql change_on_install SYSAUX TEMP YES

-- Revalidate
@$ORACLE_HOME/rdbms/admin/utlrp.sql

APEX — remove old version before upgrading

-- Check APEX version in dba_registry
SELECT comp_id, version, status FROM dba_registry
WHERE comp_id = 'APEX';

-- If old APEX is INVALID: remove it (APEX must be reinstalled separately)
sqlplus / as sysdba @$ORACLE_HOME/apex/apxremov.sql

-- Install matching version from APEX download for new Oracle home
-- cd $APEX_DIR; sqlplus / as sysdba @apexins.sql APEX APEX TEMP /i/

Oracle JVM (OJVM) — separate patch required

-- OJVM requires a SEPARATE quarterly patch (JVM patch)
-- It is NOT included in the database RU/RUR

-- Check JVM status
SELECT comp_id, version, status FROM dba_registry
WHERE comp_id = 'JAVAVM';

-- If INVALID: apply OJVM bundle patch from My Oracle Support
-- Patch number format: OJVM RELEASE UPDATE <version> (search MOS for your version)
-- Apply with: opatch apply -local <patch_number>

Spatial / SDO — revalidation

-- If MDSYS is invalid
sqlplus / as sysdba
@$ORACLE_HOME/md/admin/mdinst.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql

7. Common ORA Errors During Upgrade

ORA-39700: database must be opened with UPGRADE option

-- You're trying to open the DB normally when it needs to be in UPGRADE mode
-- or the catupgrd.sql hasn't run yet on a database opened normally

-- Correct sequence: shut down, start UPGRADE, run catupgrd/catctl
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
-- Now run catctl.pl or catupgrd.sql

ORA-01092: ORACLE instance terminated — disconnection forced

-- Session killed during upgrade, OR a background process crashed
-- Check alert log immediately
-- tail -200 $ORACLE_BASE/diag/rdbms/<dbname>/<sid>/trace/alert_<sid>.log

-- Check for ORA-00600 (internal error) in alert log
-- grep "ORA-00600\|ORA-07445\|ORA-01092" alert_*.log

-- If catctl.pl launched it, just restart catctl.pl — it will resume
$NEW_OH/perl/bin/perl $NEW_OH/rdbms/admin/catctl.pl -n 4 \
  -d $NEW_OH/rdbms/admin catupgrd.sql

ORA-04031: unable to allocate shared memory during upgrade

-- shared_pool_size is too small for upgrade operations
-- Set a minimum 1GB shared pool for upgrade

-- Add to init<sid>.ora or spfile before starting UPGRADE
ALTER SYSTEM SET shared_pool_size = 1G SCOPE = SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 1G SCOPE = SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;

8. Post-Upgrade Validation Checklist

-- After catctl.pl completes and DB is OPEN (normal mode)

-- 1. Run post-upgrade fixups (generated by preupgrade.jar earlier)
sqlplus / as sysdba @/tmp/preupgrade_output/postupgrade_fixups.sql

-- 2. Run utlu122s.sql — post-upgrade status utility
sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/utlu122s.sql

-- 3. Check dba_registry — all components must be VALID or OPTION OFF
SELECT comp_id, comp_name, status, version
FROM dba_registry
ORDER BY comp_id;

-- 4. Run utlrp.sql twice
sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/utlrp.sql
sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/utlrp.sql

-- 5. Check remaining invalids in SYS/SYSTEM
SELECT COUNT(*) FROM dba_objects
WHERE status = 'INVALID'
  AND owner IN ('SYS', 'SYSTEM');

-- 6. Verify DB version
SELECT version, version_full FROM v$instance;

-- 7. Gather dictionary and fixed object stats
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

-- 8. Run utluiobj.sql — checks for remaining user-facing invalids
sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/utluiobj.sql

-- 9. Verify timezone file version matches
SELECT version FROM v$timezone_file;

-- 10. Check alert log for errors after first full open
-- grep "ORA-\|Error\|error" $ORACLE_BASE/diag/rdbms/<db>/<sid>/trace/alert_*.log | tail -50

9. Rollback Strategy — Guaranteed Restore Point

-- === BEFORE UPGRADE: Create Guaranteed Restore Point ===

-- Step 1: Ensure DB is in ARCHIVELOG mode
SELECT log_mode FROM v$database;

-- Step 2: Enable flashback (if not already)
ALTER SYSTEM SET db_recovery_file_dest_size = 100G;
ALTER SYSTEM SET db_recovery_file_dest = '/u03/fra';
ALTER DATABASE FLASHBACK ON;

-- Step 3: Create the GRP (name it clearly)
CREATE RESTORE POINT pre_upgrade_19c GUARANTEE FLASHBACK DATABASE;

-- Verify it exists
SELECT name, guarantee_flashback_database, time
FROM v$restore_point;


-- === IF UPGRADE FAILS: Flashback to GRP ===

-- Step 1: Mount the database under the OLD Oracle home
export ORACLE_HOME=$OLD_OH
export PATH=$OLD_OH/bin:$PATH
sqlplus / as sysdba
SHUTDOWN ABORT;
STARTUP MOUNT;

-- Step 2: Flashback to the restore point
FLASHBACK DATABASE TO RESTORE POINT pre_upgrade_19c;

-- Step 3: Open with resetlogs
ALTER DATABASE OPEN RESETLOGS;

-- Step 4: Verify you're back on the old version
SELECT version FROM v$instance;

-- Step 5: Drop the restore point (it consumes FRA space)
DROP RESTORE POINT pre_upgrade_19c;

10. Version-Specific Notes

12c (12.1 / 12.2) → 19c

• Direct upgrade from 12.1.0.2 or 12.2.0.1 to 19c is supported. No intermediate hop required.
• Apply at minimum the latest 19c RU (Release Update) before running the upgrade — base 19.3 has known upgrade bugs.
• The compatible parameter cannot be downgraded after it's raised. Do not raise it until the upgrade is confirmed stable.
• APEX must be separately upgraded after the DB upgrade — it does not auto-upgrade with catctl.pl.
• Unified Auditing is the default in 19c. Mixed mode is on by default for compatibility, but plan to migrate audit policies.
• Check _allow_resetlogs_corruption — it should NOT be set in your spfile before upgrading.

-- Verify compatible is NOT raised before upgrade is confirmed
SHOW PARAMETER compatible;
-- Leave at 12.2.0.1 during upgrade; raise to 19.0.0 after stability window

-- Confirm minimum patch level on 19c home before upgrade
-- opatch lspatches | head -5
-- Should show at least 19.X.0.0.YYMMDD Release Update

19c → 23ai (23c)

• Requires 19c to be on a recent RU first (19.18+ recommended by Oracle).
• 23ai introduces SQL Domains, Property Graphs, and TRUE CACHE — none require action during upgrade but verify application SQL compatibility.
• The compatible minimum for 23ai target is 19.0.0.
• PDB upgrades in CDB: use catctl.pl -c <pdb_name> to upgrade individual PDBs or catctl.pl -C <pdb_list> for multiple in parallel.
• AutoUpgrade tool (Oracle-supplied) is the recommended path for 23ai — it handles pre-checks, fixups, upgrade, and post-upgrade in a single workflow.

-- AutoUpgrade tool (recommended for 23ai) — config file example
-- Create config.cfg:
--   global.autoupg_log_dir=/tmp/autoupg_logs
--   upg1.source_home=/u01/app/oracle/product/19.0.0/dbhome_1
--   upg1.target_home=/u01/app/oracle/product/23.0.0/dbhome_1
--   upg1.sid=ORCL
--   upg1.start_time=NOW
--   upg1.upgrade_node=localhost

java -jar $NEW_OH/rdbms/admin/autoupgrade.jar \
  -config config.cfg \
  -mode deploy

-- Monitor AutoUpgrade progress
java -jar $NEW_OH/rdbms/admin/autoupgrade.jar \
  -config config.cfg \
  -mode monitor