⬆️ Database Upgrade

# Oracle Database Upgrade Triage Runbook

⏱ 1–8 hrs (depending on failure point) 12c → 19c · 19c → 23ai Updated 2026-05-15
🔴 Did you create a Guaranteed Restore Point before starting?
If yes, you can flashback the database to pre-upgrade state in under 30 minutes at any point. If no, jump to Section 9: Rollback Options immediately to understand your recovery path before taking any further action.

## 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

⚠️ WARNING vs ERROR distinction
Warnings can be deferred; ERRORs in preupgrade.jar output block the upgrade and must be resolved first.

    -- 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

#### 📋 Decision Tree: DBUA or Manual?

- Use DBUA if: first upgrade attempt, non-CDB/standard environment, less than 500GB, DBA comfort with GUI
- Use Manual (catctl.pl) if: DBUA previously failed, need parallelism control, automating in a script, large CDB with many PDBs, need to capture detailed logs
- DBUA advantage: pre/post checks built in, progress UI, automatic log management
- Manual advantage: restart capability, granular error visibility, scriptable, works over SSH without X11

    -- 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

ℹ️ Restart, don't re-run from scratch
catctl.pl tracks completed phases in `catctl_p*.sql` files and a restart marker. Adding `-p <phase>` or just re-running catctl.pl resumes from the failure point.

    -- 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);

⚠️ SYS/SYSTEM invalids after two utlrp runs = problem
If you have invalid objects in SYS or SYSTEM after running utlrp.sql twice, this indicates a failed component upgrade. Check `dba_registry` for INVALID status components and address Section 6 (Component Upgrade Failures).

## 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

🔴 You MUST create the GRP before the upgrade begins
A Guaranteed Restore Point (GRP) created before upgrade lets you flashback the database to pre-upgrade state in 15–30 minutes regardless of how far the upgrade progressed. No GRP = your only option is an RMAN restore, which takes hours.

    -- === 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

### Validate your upgrade readiness before the maintenance window

TuneVault health checks surface upgrade blockers — invalid objects, unsupported parameters, component status, archive log space — before they halt your upgrade at 2am.

Run a Free Health Check →