Oracle Database Upgrade to 19c on Linux

A complete production-ready SOP for upgrading Oracle Database from 11g/12c/18c to 19c on Linux. Covers pre-upgrade checks, preupgrade utility, upgrade methods, post-upgrade tasks, timezone upgrade, optimizer statistics, and full validation — with real commands, expected outputs, and consultant-level notes for both standard OFA and enterprise custom path conventions.

1. Document Info

2. Upgrade — Concepts You Must Know First

📝 What is a database upgrade? Upgrading moves an Oracle database from one major version to another — for example from 12.2 to 19c. The upgrade modifies the data dictionary, system objects, and internal structures to match the new version. The actual datafiles, schemas, and application data are preserved.

📝 Why upgrade to 19c? Oracle 19c is the Long Term Support Release (LTS) of the Oracle Database 12c family. Oracle provides premier support for 19c until April 2024 and extended support until April 2027. Many older versions (11g, 12.1) are already out of premier support. 19c is the current production standard.

Supported Upgrade Paths to 19c

⚠️ IMPORTANT: If your source is 11.2.0.3 or below, you must first upgrade to 11.2.0.4, then upgrade from 11.2.0.4 to 19c. Never skip intermediate versions.

Upgrade Methods

📝 This SOP covers Manual Upgrade (full control) and notes where DBUA differs. Manual upgrade is what consultants must know — DBUA hides too much and is not suitable for scripted production upgrades.

Upgrade Phases Overview

Phase 1 — Pre-Upgrade (source DB running)
  Install 19c software in new ORACLE_HOME
  Run preupgrade.jar (pre-upgrade utility)
  Fix all pre-upgrade issues
  Take full RMAN backup
  Gather optimizer statistics

Phase 2 — Upgrade (source DB shut down)
  Shut down source database
  Start database in upgrade mode using NEW 19c home
  Run catupgrd.sql (upgrade script) or DBUA
  Monitor upgrade progress

Phase 3 — Post-Upgrade (database on 19c)
  Run post-upgrade scripts
  Upgrade timezone
  Compile invalid objects
  Gather dictionary statistics
  Re-run post-upgrade checks
  Apply latest 19c RU patch

3. Path Conventions and Environment Details

📝 Both ORACLE_HOMEs (source and target) must exist simultaneously during the upgrade. The source home contains the database that needs to be upgraded. The target 19c home contains the new Oracle software that will run the database after upgrade.

4. Pre-Upgrade Checks and Preparation

⚠️ IMPORTANT: Pre-upgrade is the most critical phase. Every issue you find and fix before starting the upgrade is one less problem during the upgrade window. Most upgrade failures are caused by skipping or rushing pre-upgrade checks.

4.1 — Confirm Source Database Version and Status

-- Connect to SOURCE database (still running on old home)
sqlplus / as sysdba

set linesize 200
set pagesize 50
col banner for a80

-- Full version string
SELECT banner FROM v$version;

-- Detailed version
SELECT version, version_full FROM v$instance;

-- DB status
SELECT name, db_unique_name, open_mode,
       log_mode, database_role
FROM   v$database;

⚠️ IMPORTANT: Database must be in ARCHIVELOG mode and fully OPEN before starting upgrade. If not in ARCHIVELOG mode — enable it now.

4.2 — Install Oracle 19c Software in New Home

📝 Why install first? The preupgrade utility (preupgrade.jar) ships with 19c software and must be run from the 19c home against the source database. You need the 19c software installed before you can run pre-upgrade checks.

# Install 19c software (refer to SOP 01 for full installation steps)
# Quick reference:
su - oracle

# Create 19c home directory
mkdir -p /u01/app/oracle/product/19.3.0/dbhome_1
# Convention B: mkdir -p /oracle/RDBMS/19.31

# Unzip 19c software into new home
cd /u01/app/oracle/product/19.3.0/dbhome_1
unzip -q /stage/19c/LINUX.X64_193000_db_home.zip

# Run silent software-only installation
./runInstaller -silent \
  oracle.install.option=INSTALL_DB_SWONLY \
  UNIX_GROUP_NAME=oinstall \
  INVENTORY_LOCATION=/u01/app/oraInventory \
  ORACLE_BASE=/u01/app/oracle \
  oracle.install.db.InstallEdition=EE \
  oracle.install.db.OSDBA_GROUP=dba \
  oracle.install.db.OSOPER_GROUP=oper \
  oracle.install.db.OSBACKUPDBA_GROUP=backupdba \
  oracle.install.db.OSDGDBA_GROUP=dgdba \
  oracle.install.db.OSKMDBA_GROUP=kmdba \
  oracle.install.db.OSRACDBA_GROUP=racdba \
  SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
  DECLINE_SECURITY_UPDATES=true

# Run root scripts when prompted (as root)
/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/19.3.0/dbhome_1/root.sh

# Verify 19c home is installed
/u01/app/oracle/product/19.3.0/dbhome_1/OPatch/opatch lsinventory | \
    grep "Oracle Database"

4.3 — Apply Latest Release Update to 19c Home

📝 Why patch before upgrade? Apply the latest 19c RU to the new Oracle Home BEFORE upgrading the database. This means your database goes directly to the latest patched version in one operation instead of upgrading to base 19.3 and then patching afterward.

# Apply latest 19c RU to the NEW 19c home (refer to SOP 02 for full steps)
# Database is still running on OLD home at this point

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
$ORACLE_HOME/OPatch/opatch apply \
    -oh $ORACLE_HOME \
    -silent \
    /stage/patches/unzipped/<LATEST_RU_PATCHNUM>

# Verify patch applied
$ORACLE_HOME/OPatch/opatch lsinventory | grep -E "Oracle Database|Patch"

4.4 — Run preupgrade.jar — The Pre-Upgrade Utility

📝 What is preupgrade.jar? preupgrade.jar is Oracle’s official pre-upgrade checker. It is located in the 19c Oracle Home and is run against the source database (still on old home). It checks hundreds of conditions and generates:

• A list of ERRORS that MUST be fixed before upgrade
• A list of WARNINGS that should be reviewed
• Fix scripts that can automatically resolve many issues
• A preupgrade_fixups.sql script for remaining manual fixes

⚠️ IMPORTANT: You MUST fix all ERROR items before starting the upgrade. WARNING items should be reviewed and fixed where possible.

# Run preupgrade.jar from 19c home against source database
# Source DB must be OPEN and running on OLD home
su - oracle

# Set environment to OLD source home
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH

# Run preupgrade.jar from NEW 19c home
java -jar /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar \
    FILE DIR /tmp/preupgrade_output \
    TERMINAL TEXT

# Review the output files
ls -lh /tmp/preupgrade_output/

Files generated by preupgrade.jar:

# Read the preupgrade log carefully
cat /tmp/preupgrade_output/preupgrade.log

# Check specifically for ERROR items
grep -E "ERROR|WARNING|RECOMMEND" /tmp/preupgrade_output/preupgrade.log

4.5 — Review and Understand preupgrade.jar Output

📝 Common items found by preupgrade.jar and what to do:

4.6 — Run preupgrade_fixups.sql on Source Database

📝 What does fixups.sql do? It automatically fixes many of the issues identified by preupgrade.jar — gathers statistics, purges recyclebin, removes invalid objects, adjusts parameters etc. Run it on the SOURCE database while it is still on the OLD home.

# Connect to SOURCE database (on OLD home)
su - oracle
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus / as sysdba

-- Run the pre-upgrade fixup script
-- This script is generated by preupgrade.jar
@/tmp/preupgrade_output/preupgrade_fixups.sql

-- Review the output carefully
-- It will show what was fixed and what still needs manual attention

4.7 — Manual Pre-Upgrade Fixes

📝 Fix these manually after running fixups.sql if still required:

-- On SOURCE database as SYSDBA

-- 1. Purge recyclebin
PURGE DBA_RECYCLEBIN;

-- 2. Gather dictionary statistics (critical for upgrade performance)
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

-- 3. Gather fixed object statistics
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

-- 4. Compile invalid objects
@?/rdbms/admin/utlrp.sql

-- Verify invalid objects after recompile
SELECT COUNT(*) invalid_count
FROM   dba_objects
WHERE  status = 'INVALID';

-- 5. Check and remove deprecated parameters
-- Review spfile for any deprecated parameters
set linesize 200
set pagesize 100
col name  for a40
col value for a60
col description for a60

-- Parameters deprecated in 19c
SELECT name, value
FROM   v$parameter
WHERE  name IN (
    'sec_case_sensitive_logon',
    'o7_dictionary_accessibility',
    'optimizer_adaptive_features',
    'parallel_automatic_tuning',
    'log_archive_local_first',
    'standby_archive_dest'
)
AND    value IS NOT NULL;

-- 6. Disable any database triggers that might interfere
-- Re-enable after upgrade
SELECT owner, trigger_name, status
FROM   dba_triggers
WHERE  triggering_event LIKE '%LOGON%'
OR     triggering_event LIKE '%STARTUP%'
ORDER BY owner;

-- Disable triggers temporarily
-- ALTER TRIGGER <trigger_name> DISABLE;

4.8 — Check and Fix Timezone Version

-- Check current timezone version on source database
SELECT version FROM v$timezone_file;

-- Check what version 19c ships with
-- (Run from 19c home sqlplus)
-- The version will be shown during upgrade -- note it for post-upgrade

4.9 — Gather Optimizer Statistics Before Upgrade

📝 Why? Fresh optimizer statistics ensure the upgrade scripts run efficiently. Stale statistics can cause upgrade scripts to choose bad execution plans resulting in a very slow upgrade.

-- On SOURCE database
-- Gather schema statistics for SYS and SYSTEM
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM');

-- Gather dictionary statistics
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

-- Gather fixed objects statistics
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

4.10 — Check Component Status Before Upgrade

-- On SOURCE database
set linesize 200
set pagesize 100
col comp_name for a50
col version   for a15
col status    for a12

SELECT comp_name, version, status
FROM   dba_registry
ORDER BY comp_name;

⚠️ IMPORTANT: All components must be VALID before starting the upgrade. If any component shows INVALID — fix it first. An invalid component before upgrade becomes a failed component after upgrade.

4.11 — Check for Long Running Transactions

📝 Why? The upgrade shuts down the database. Any uncommitted transactions at shutdown time will be rolled back. For very large transactions this can take a long time. Coordinate with application teams to ensure no long-running transactions are in progress when you shut down.

-- On SOURCE database
set linesize 200
set pagesize 100
col username  for a20
col machine   for a25
col program   for a30
col status    for a12
col start_time for a25
col minutes   for 9999

SELECT s.username,
       s.machine,
       s.program,
       s.status,
       TO_CHAR(t.start_time,'YYYY-MM-DD HH24:MI:SS') start_time,
       ROUND((SYSDATE - CAST(t.start_time AS DATE)) * 24 * 60) minutes
FROM   v$transaction t,
       v$session     s
WHERE  t.ses_addr = s.saddr
ORDER BY t.start_time;

4.12 — Take Full RMAN Backup Before Upgrade

⚠️ IMPORTANT: This is your last chance to take a backup of the database in its pre-upgrade state. If the upgrade fails and you need to roll back, this backup is your recovery point. Do NOT skip this step.

su - oracle
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH

rman target /

RMAN> BACKUP AS COMPRESSED BACKUPSET
        DATABASE
        FORMAT '/u01/app/oracle/fast_recovery_area/ORCL/pre_upgrade/full_%d_%T_%s_%p'
        TAG 'PRE_UPGRADE_BACKUP'
        PLUS ARCHIVELOG
        FORMAT '/u01/app/oracle/fast_recovery_area/ORCL/pre_upgrade/arch_%d_%T_%s_%p';

RMAN> LIST BACKUP SUMMARY;
RMAN> EXIT;

4.13 — Record Pre-Upgrade Baseline

-- Record current state for comparison after upgrade
sqlplus / as sysdba

-- Save current component versions
set linesize 200
set pagesize 100
col comp_name for a50
col version   for a15
col status    for a12

SELECT comp_name, version, status
FROM   dba_registry
ORDER BY comp_name;

-- Save current object counts
set linesize 200
set pagesize 50
col object_type for a25
col count       for 9999999

SELECT object_type, COUNT(*) count
FROM   dba_objects
GROUP BY object_type
ORDER BY object_type;

-- Save invalid object count
SELECT COUNT(*) pre_upgrade_invalids
FROM   dba_objects
WHERE  status = 'INVALID';

-- Save parameter file content
SELECT name, value
FROM   v$parameter
WHERE  isdefault = 'FALSE'
ORDER BY name;

# Save to file for reference
sqlplus -s / as sysdba <<EOF > /tmp/pre_upgrade_baseline.txt
SET LINESIZE 200 PAGESIZE 100
SELECT comp_name, version, status FROM dba_registry ORDER BY comp_name;
SELECT object_type, COUNT(*) FROM dba_objects GROUP BY object_type ORDER BY 1;
SELECT name, value FROM v\$parameter WHERE isdefault='FALSE' ORDER BY name;
EXIT;
EOF

cat /tmp/pre_upgrade_baseline.txt

5. Upgrade Execution — Manual Method

⚠️ IMPORTANT: From this point the database will be unavailable to users. Ensure maintenance window is active, OEM blackout is created, application teams are notified, and all necessary personnel are available.

5.1 — Shut Down Source Database

# Set environment to OLD source home
su - oracle
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus / as sysdba

-- Graceful shutdown
-- IMMEDIATE = active transactions rolled back, DB cleanly shut down
SHUTDOWN IMMEDIATE;

EXIT;

# Verify database is down
ps -ef | grep ora_pmon_ORCL | grep -v grep
# Should return no output

5.2 — Switch Environment to 19c Oracle Home

# CRITICAL STEP -- switch to NEW 19c Oracle Home
su - oracle

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

# Convention B
# export ORACLE_HOME=/oracle/RDBMS/19.31

# Verify you are now on 19c home
echo $ORACLE_HOME
$ORACLE_HOME/bin/sqlplus -V
# Must show: SQL*Plus: Release 19.x.x.x.x

⚠️ IMPORTANT: This is the most common mistake during upgrades. Verify you are using the 19c home BEFORE starting the database in upgrade mode. If you start the database with the OLD home, the upgrade will not proceed.

5.3 — Update oratab to Point to New Home

# Update /etc/oratab to point ORCL to the new 19c home
# This ensures all tools use the correct home
su - root

# Show current oratab entry
grep ORCL /etc/oratab

# Update the entry
sed -i 's|ORCL:/u01/app/oracle/product/12.2.0.1/dbhome_1|ORCL:/u01/app/oracle/product/19.3.0/dbhome_1|' \
    /etc/oratab

# Convention B:
# sed -i 's|ORCL:/oracle/RDBMS/12.2|ORCL:/oracle/RDBMS/19.31|' /etc/oratab

# Verify
grep ORCL /etc/oratab

5.4 — Update Listener to Use New Oracle Home

# Update listener.ora to point to new 19c home
su - oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1

vi $ORACLE_HOME/network/admin/listener.ora

Verify listener.ora references 19c home:

# listener.ora on 19c home
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver01.company.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME   = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME      = ORCL)
    )
  )

# Stop listener from OLD home
/u01/app/oracle/product/12.2.0.1/dbhome_1/bin/lsnrctl stop

# Start listener from NEW 19c home
$ORACLE_HOME/bin/lsnrctl start
$ORACLE_HOME/bin/lsnrctl status

5.5 — Copy SPFILE or Create pfile for 19c

📝 Why? The database spfile/pfile must be accessible from the new 19c home. Oracle 19c can use the existing spfile — just ensure the 19c home can find it.

su - oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1

# Option A: Create a pfile pointing to spfile location
# (If spfile is in default location under OLD home)
cat > $ORACLE_HOME/dbs/initORCL.ora << EOF
SPFILE='/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/spfileORCL.ora'
EOF

# Option B: Copy spfile to new home
cp /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/spfileORCL.ora \
   /u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileORCL.ora

# Option C: If password file is in old home -- copy to new home
cp /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwORCL \
   /u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwORCL

# Verify
ls -lh $ORACLE_HOME/dbs/

5.6 — Start Database in UPGRADE Mode

📝 What is UPGRADE mode? Starting the database with the UPGRADE option opens it in a special restricted mode that allows the Oracle internal upgrade scripts to modify the data dictionary. Regular user connections are not allowed in UPGRADE mode.

# On 19c home -- start in upgrade mode
su - oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus / as sysdba

-- Start in upgrade mode
-- This opens the database with the new 19c binaries
-- but the data dictionary is still at the old version
STARTUP UPGRADE;

-- Verify database is in upgrade mode
SELECT status, logins FROM v$instance;
-- STATUS must show: OPEN
-- LOGINS must show: RESTRICTED

Expected output:

INSTANCE_NAME   STATUS       LOGINS
--------------- ------------ ------------
ORCL            OPEN         RESTRICTED

⚠️ IMPORTANT: If database fails to start with UPGRADE, check the alert log immediately. Common causes: spfile cannot be found, incompatible parameters, missing password file. Fix and retry before proceeding.

5.7 — Create Upgrade Log Directory

# Create directory for upgrade logs
mkdir -p /u01/app/oracle/cfgtoollogs/ORCL/upgrade
chown oracle:oinstall /u01/app/oracle/cfgtoollogs/ORCL/upgrade

# Convention B
# mkdir -p /oracle/cfgtoollogs/ORCL/upgrade

5.8 — Run the Database Upgrade Script (catupgrd.sql)

📝 What is catupgrd.sql? This is Oracle’s main upgrade script. It upgrades the data dictionary from the source version to 19c. It runs hundreds of SQL scripts that modify system tables, recompile packages, create new dictionary objects, and update internal metadata. This is the core of the upgrade process.

📝 How long does it take? Typically 30-90 minutes for a small to medium database. Large databases with many schemas can take 2-4 hours. The time depends on database size and server performance.

# Open a separate terminal to monitor upgrade progress
# (Do NOT close the main upgrade terminal)

# Terminal 2 -- monitor upgrade progress
tail -100f /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/log/catupgrd*.log

# Terminal 1 -- run the upgrade (this is the main upgrade command)
# Connect as sysdba from 19c home
su - oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus / as sysdba

-- Run the upgrade script
-- This takes 30-90 minutes
-- DO NOT interrupt or close this session
@?/rdbms/admin/catupgrd.sql

📝 Monitor upgrade progress while catupgrd.sql is running — in another terminal:

-- In a separate sqlplus session -- check upgrade progress
-- This view shows component-by-component upgrade status
set linesize 200
set pagesize 100
col comp_name   for a45
col version     for a15
col status      for a15
col start_time  for a25
col end_time    for a25

SELECT comp_name, version, status,
       TO_CHAR(modified,'YYYY-MM-DD HH24:MI:SS') modified
FROM   dba_registry
ORDER BY modified;

Expected final message from catupgrd.sql:

Oracle Database instance restarted...
...
Upgrade Summary Report Located in:
/u01/app/oracle/cfgtoollogs/ORCL/upgrade/upg_summary.log

5.9 — Shut Down and Restart Database Normally After Upgrade

📝 Why restart after upgrade? After catupgrd.sql completes, the database is still in UPGRADE/RESTRICTED mode. You must restart it normally to open it for users.

-- Shut down after upgrade script completes
SHUTDOWN IMMEDIATE;

-- Start normally (not in upgrade mode)
STARTUP;

-- Verify database is open normally
SELECT status, logins FROM v$instance;
-- STATUS = OPEN, LOGINS = ALLOWED (not RESTRICTED)

SELECT name, open_mode FROM v$database;
-- open_mode = READ WRITE

6. Post-Upgrade Tasks

⚠️ IMPORTANT: Post-upgrade tasks are NOT optional. An upgrade is not complete until every post-upgrade task is done. Many DBAs consider the database upgraded when catupgrd.sql finishes — this is wrong. The database is only fully upgraded and stable after all post-upgrade tasks complete.

6.1 — Run Post-Upgrade Status Script

📝 What is utlu19s.sql? This script checks the upgrade status of all components and generates a summary report. Run it immediately after the first normal startup post-upgrade.

-- Connect to upgraded database (now on 19c)
sqlplus / as sysdba

-- Run post-upgrade status check
@?/rdbms/admin/utlu19s.sql

What to look for in output:

Oracle Database 19.0 Post-Upgrade Status Tool
Component Status     Version  HH:MM:SS
Oracle Server        VALID    19.x.x.x  00:45:23
REQUIRED COMPONENTS VALID
...
Elapsed Time:    45:23
...
STATUS: SUCCESSFUL

⚠️ IMPORTANT: If any component shows INVALID or UPGRADED status — do NOT proceed. Investigate the specific component and fix it before continuing.

6.2 — Run Post-Upgrade Fixups Script

📝 What is postupgrade_fixups.sql? Generated by preupgrade.jar earlier. Contains fixes that must be applied AFTER the upgrade completes — timezone adjustments, parameter updates, deprecated object removal etc.

-- Run the post-upgrade fixup script
-- This was generated by preupgrade.jar in Section 4.4
@/tmp/preupgrade_output/postupgrade_fixups.sql

6.3 — Run catcon.pl to Recompile Invalid Objects

📝 What is utlrp.sql in 19c? In 19c, Oracle recommends using utlrp.sql which internally uses catcon.pl for parallel recompilation. This recompiles all invalid PL/SQL objects in the database using multiple CPUs — much faster than serial recompilation.

-- Recompile all invalid objects
-- This MUST be run after upgrade
@?/rdbms/admin/utlrp.sql

-- Monitor recompilation progress
SELECT count(*) remaining_invalids
FROM   dba_objects
WHERE  status = 'INVALID';
-- Run this repeatedly until count reaches 0 or stable minimum

6.4 — Check Component Status After Upgrade

set linesize 200
set pagesize 100
col comp_name for a50
col version   for a15
col status    for a12

SELECT comp_name, version, status
FROM   dba_registry
ORDER BY comp_name;

⚠️ IMPORTANT: ALL components must show VALID and version must be 19.x.x.x.x. Any component showing INVALID must be reinitiated using its specific catalog script.

6.5 — Check for INVALID Objects After Upgrade

set linesize 200
set pagesize 100
col owner       for a20
col object_name for a45
col object_type for a25
col status      for a10

-- List invalid objects
SELECT owner, object_name, object_type, status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

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

📝 Some invalid objects in USER schemas immediately after upgrade is normal — applications often have compiled code that references internal Oracle packages that changed version. Have application teams recompile their code after upgrade.

6.6 — Upgrade Timezone File

📝 Why upgrade timezone? Oracle ships updated timezone data files with each release. The timezone file version in the database must be upgraded to match the new Oracle Home. Without this, timezone-dependent operations may behave incorrectly.

-- Check current timezone version in database
SELECT version FROM v$timezone_file;

-- Check what timezone version 19c home provides
-- (Check the file in 19c home)

# Check timezone version in 19c Oracle Home
ls -la $ORACLE_HOME/oracore/zoneinfo/timezlrg_*.dat | tail -1
# The number in the filename is the version

-- Upgrade timezone (if database version is lower than 19c home version)
-- Step 1: Run the timezone upgrade script
@?/rdbms/admin/utltz_upg_check.sql

-- Step 2: Run the actual upgrade
@?/rdbms/admin/utltz_upg_apply.sql

-- Step 3: Restart the database
SHUTDOWN IMMEDIATE;
STARTUP;

-- Step 4: Verify new timezone version
SELECT version FROM v$timezone_file;

6.7 — Run datapatch After Upgrade

📝 Why run datapatch after upgrade? If you applied a Release Update (RU) to the 19c home before upgrading (as recommended in Section 4.3), datapatch must be run to apply the SQL-level changes from that RU to the data dictionary. The upgrade scripts (catupgrd.sql) bring the dictionary to base 19.3 — datapatch brings it to the patched version.

su - oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export ORACLE_SID=ORCL

# Database must be open before running datapatch
cd $ORACLE_HOME/OPatch
./datapatch -verbose

# Monitor log
ls -lrt /u01/app/oracle/cfgtoollogs/sqlpatch/
tail -100f /u01/app/oracle/cfgtoollogs/sqlpatch/<latest_dir>/<latest_log>

Expected output:

Connecting to database...OK
Bootstrapping registry...done
datapatch: ALL PATCHES APPLIED SUCCESSFULLY.

6.8 — Gather Dictionary Statistics After Upgrade

📝 Why? The upgrade significantly changes the data dictionary. After upgrade, all optimizer statistics for dictionary objects are stale. Gathering fresh statistics ensures good query performance on the newly upgraded dictionary.

-- Gather dictionary statistics
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

-- Gather fixed objects statistics
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

-- Gather complete system statistics if using system statistics
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD');

6.9 — Review and Clean Up Deprecated Parameters

📝 Why? Some parameters valid in older Oracle versions are deprecated or obsolete in 19c. Having them in spfile generates warning messages in the alert log and may cause unexpected behavior. Remove or replace them.

-- Check for deprecated parameters
set linesize 200
set pagesize 100
col name        for a45
col value       for a40
col description for a60

SELECT name, value, description
FROM   v$parameter
WHERE  isdeprecated = 'TRUE'
AND    isdefault    = 'FALSE'
ORDER BY name;

# Remove deprecated parameters from spfile
# Example for commonly deprecated params in 19c upgrades:
sqlplus / as sysdba

-- Remove deprecated parameters (examples -- check your specific list)
ALTER SYSTEM RESET sec_case_sensitive_logon SCOPE=SPFILE;
ALTER SYSTEM RESET parallel_automatic_tuning SCOPE=SPFILE;
ALTER SYSTEM RESET log_archive_local_first SCOPE=SPFILE;

-- Restart to apply spfile changes
SHUTDOWN IMMEDIATE;
STARTUP;

6.10 — Set COMPATIBLE Parameter

📝 What is COMPATIBLE? The COMPATIBLE parameter controls which Oracle features are available and which file format is used. Increasing it enables new 19c features but makes the database incompatible with older Oracle versions. Once increased, you cannot downgrade below the COMPATIBLE setting.

⚠️ IMPORTANT: Do NOT increase COMPATIBLE immediately after upgrade. Wait until you have verified the application works correctly on 19c, all testing is complete, and the decision to stay on 19c is confirmed. Once you increase COMPATIBLE — there is no downgrade path.

-- Check current COMPATIBLE setting
SHOW PARAMETER compatible;

-- Only increase COMPATIBLE after full testing and sign-off
-- This is the one-way door -- no going back after this
-- ALTER SYSTEM SET COMPATIBLE='19.0.0' SCOPE=SPFILE;
-- SHUTDOWN IMMEDIATE;
-- STARTUP;

-- Verify after change
-- SHOW PARAMETER compatible;

6.11 — Upgrade Optimizer Statistics

📝 Why? After upgrading the optimizer version changes. Old optimizer statistics gathered under the previous version may cause suboptimal plans under the new optimizer. Run a comprehensive statistics gather after upgrade.

-- Gather statistics for all user schemas
-- Run this during off-peak hours -- can take significant time
EXEC DBMS_STATS.GATHER_DATABASE_STATS(
    options       => 'GATHER AUTO',
    degree        => 4,
    cascade       => TRUE
);

-- Or gather schema by schema for better control
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname => 'HR',
    degree  => 4,
    cascade => TRUE
);

7. Post-Upgrade Verification Checks

⚠️ IMPORTANT: Do not close the maintenance window or hand over the upgraded database without completing every check below. These checks confirm the upgrade was successful and the database is healthy on 19c.

7.1 — Verify Oracle Version is 19c

sqlplus / as sysdba

set linesize 200
set pagesize 50
col banner for a80

SELECT banner FROM v$version WHERE banner LIKE 'Oracle%';

SELECT version, version_full FROM v$instance;
-- Must show 19.x.x.x.x

7.2 — Verify Database is Open Normally

set linesize 150
set pagesize 50
col name           for a12
col db_unique_name for a15
col open_mode      for a15
col log_mode       for a15
col status         for a12

SELECT name, db_unique_name, open_mode, log_mode
FROM   v$database;

SELECT instance_name, version_full, host_name, status
FROM   v$instance;

7.3 — Compare Component Versions Before and After

set linesize 200
set pagesize 100
col comp_name for a50
col version   for a15
col status    for a12

-- Post-upgrade component versions (compare with pre-upgrade baseline)
SELECT comp_name, version, status
FROM   dba_registry
ORDER BY comp_name;

7.4 — Check All Datafiles Online

sql

set linesize 200
set pagesize 100
col file#           for 999
col tablespace_name for a25
col status          for a12
col name            for a70

SELECT file#, tablespace_name, status, name
FROM   v$datafile
WHERE  status != 'ONLINE'
ORDER BY file#;
-- Should return no rows

7.5 — Check All Tablespaces

set linesize 200
set pagesize 100
col tablespace_name for a25
col status          for a12
col contents        for a12
col total_mb        for 9999999
col free_mb         for 9999999
col used_pct        for 999.99

SELECT df.tablespace_name,
       df.status,
       df.contents,
       ROUND(df.bytes/1024/1024,2)                  total_mb,
       ROUND(NVL(fs.bytes,0)/1024/1024,2)            free_mb,
       ROUND((1-NVL(fs.bytes,0)/df.bytes)*100,2)     used_pct
FROM  (SELECT tablespace_name, status, contents,
              SUM(bytes) bytes
       FROM   dba_data_files
       GROUP BY tablespace_name, status, contents) df,
      (SELECT tablespace_name, SUM(bytes) bytes
       FROM   dba_free_space
       GROUP BY tablespace_name) fs
WHERE  df.tablespace_name = fs.tablespace_name(+)
ORDER BY df.tablespace_name;

7.6 — Check Invalid Object Count vs Pre-Upgrade Baseline

-- Compare with pre-upgrade baseline recorded in Section 4.13
set linesize 180
set pagesize 100
col owner       for a20
col object_type for a25
col count       for 9999999

SELECT owner, object_type, COUNT(*) count
FROM   dba_objects
WHERE  status = 'INVALID'
GROUP BY owner, object_type
ORDER BY owner, count DESC;

SELECT COUNT(*) post_upgrade_invalids
FROM   dba_objects
WHERE  status = 'INVALID';

7.7 — Verify Redo Logs and Archiving

set linesize 180
set pagesize 50
col l#      for 999
col status  for a12
col members for 999

SELECT group# l#, members, bytes/1024/1024 size_mb,
       status, archived
FROM   v$log
ORDER BY group#;

ARCHIVE LOG LIST;

7.8 — Verify datapatch Applied Correctly

set linesize 200
set pagesize 100
col patch_id    for 9999999999
col version     for a15
col status      for a15
col description for a55
col action_time for a25

SELECT patch_id, version, status, description, action_time
FROM   dba_registry_sqlpatch
ORDER BY action_time DESC;

7.9 — Check Timezone Version

-- Verify timezone was upgraded
SELECT version FROM v$timezone_file;
-- Should match the version in 19c Oracle Home

7.10 — Run OPatch Inventory Check

# Verify 19c home and patches are correctly registered
su - oracle
$ORACLE_HOME/OPatch/opatch lsinventory | grep -E "Oracle Database|Patch"

7.11 — Check Alert Log for Upgrade Errors

# Scan alert log for any errors during upgrade
tail -500 /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log \
     | grep -E "ORA-|Error|WARNING|FAILED|CORRUPTED"

# Convention B
tail -500 /oracle/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log \
     | grep -E "ORA-|Error|WARNING|FAILED|CORRUPTED"

7.12 — Verify Listener is Running and Database Registered

# Listener check
lsnrctl status

# Test TNS connectivity
sqlplus system/Oracle_123@localhost:1521/ORCL

7.13 — Run Upgrade Summary Report

-- Review the upgrade summary that catupgrd.sql generated
-- This file contains the complete upgrade history

cat /u01/app/oracle/cfgtoollogs/ORCL/upgrade/upg_summary.log

7.14 — Take RMAN Backup After Upgrade

⚠️ IMPORTANT: Take a full backup IMMEDIATELY after the upgrade completes and all post-upgrade tasks are done. This is your first backup of the upgraded database. The pre-upgrade backup cannot be used to restore a 19c database — you need a post-upgrade backup.

su - oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export ORACLE_SID=ORCL

rman target /

RMAN> BACKUP AS COMPRESSED BACKUPSET
        DATABASE
        FORMAT '/u01/app/oracle/fast_recovery_area/ORCL/post_upgrade/full_%d_%T_%s_%p'
        TAG 'POST_UPGRADE_BACKUP'
        PLUS ARCHIVELOG
        FORMAT '/u01/app/oracle/fast_recovery_area/ORCL/post_upgrade/arch_%d_%T_%s_%p';

RMAN> LIST BACKUP SUMMARY;
RMAN> EXIT;

8. AutoUpgrade Tool (Alternative Method)

📝 What is AutoUpgrade? AutoUpgrade is Oracle’s fully automated upgrade tool introduced in 19c. It handles the entire upgrade process including pre-checks, fixups, upgrade execution, post-upgrade tasks, and timezone upgrade — all in one tool with minimal DBA intervention. For multiple databases or standardized enterprise upgrades, AutoUpgrade is the most efficient method.

8.1 — Download and Verify AutoUpgrade

# AutoUpgrade is a JAR file -- download latest version from MOS
# MOS Doc ID 2485457.1 -- latest autoupgrade.jar download link
ls -lh /stage/upgrade/autoupgrade.jar

# Check version
java -jar /stage/upgrade/autoupgrade.jar -version

8.2 — Create AutoUpgrade Configuration File

vi /home/oracle/autoupgrade_ORCL.cfg

# -------------------------------------------------------
# AutoUpgrade Configuration File for ORCL database
# -------------------------------------------------------

# Global settings
global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade

# Database-specific settings (one section per database)
upg1.dbname=ORCL
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/12.2.0.1/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.sid=ORCL
upg1.log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade/ORCL
upg1.upgrade_node=dbserver01
upg1.target_version=19
upg1.run_utlrp=yes
upg1.timezone_upg=yes

8.3 — Run AutoUpgrade in Analyze Mode (Pre-Checks Only)

# Analyze mode -- runs pre-upgrade checks only, makes no changes
java -jar /stage/upgrade/autoupgrade.jar \
    -config /home/oracle/autoupgrade_ORCL.cfg \
    -mode analyze

8.4 — Run AutoUpgrade in Deploy Mode (Full Upgrade)

# Deploy mode -- runs complete upgrade end-to-end
# This handles everything: pre-checks, upgrade, post-upgrade tasks
java -jar /stage/upgrade/autoupgrade.jar \
    -config /home/oracle/autoupgrade_ORCL.cfg \
    -mode deploy

# Monitor AutoUpgrade progress in another terminal
tail -100f /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/autoupgrade_<date>.log

9. Upgrade with Data Guard (Transient Logical Standby Method)

📝 What is the Transient Logical Standby method? This is an advanced upgrade technique that provides near-zero downtime. The physical standby is temporarily converted to a logical standby, upgraded to 19c, then switched over to become the new primary. The old primary is then upgraded and becomes the standby. Total downtime is only the switchover time.

📝 This method is complex and requires Data Guard Broker to be configured. High-level steps only are shown here — refer to MOS Doc ID 2485457.1 for full details.

High Level Steps:

1. Primary on 12c, Standby on 12c -- both running
2. Convert standby to Logical Standby (DBMS_LOGSTDBY)
3. Upgrade Logical Standby to 19c (catupgrd.sql on logical standby)
4. Switchover -- Logical Standby (19c) becomes new Primary
5. Applications now connecting to 19c database (minimal downtime)
6. Convert old Primary (12c) to Physical Standby
7. Upgrade old Primary Oracle Home to 19c
8. Switchover back to original primary (now on 19c) -- optional

📎 Full procedure: MOS Doc ID 2485457.1 Section 4 — Using Data Guard Standby Database for Upgrade

10. Rollback Plan (If Upgrade Fails)

📝 Can an upgrade be rolled back? If the upgrade fails during catupgrd.sql before completing, you can restore the database from the pre-upgrade RMAN backup taken in Section 4.12 and restart the old version. After catupgrd.sql completes and you open the database normally, the upgrade is committed and the only rollback path is from backup.

⚠️ IMPORTANT: This is why the pre-upgrade backup is so critical. Plan a rollback procedure BEFORE starting the upgrade.

10.1 — Rollback Procedure (If Upgrade Fails Mid-Way)

# If upgrade fails during catupgrd.sql or database fails to open after upgrade
# Restore from pre-upgrade backup

su - oracle
# Switch back to OLD Oracle Home
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH

rman target /

-- Restore from pre-upgrade backup
RMAN> SHUTDOWN ABORT;
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE FROM TAG 'PRE_UPGRADE_BACKUP';
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;

-- Verify old version is restored
RMAN> EXIT;

sqlplus / as sysdba
SELECT banner FROM v$version;
-- Must show old version (12c etc.)

11. Quick Reference Card

This SOP covers everything you need to upgrade Oracle Database to 19c on Linux without referring to any other source. Always run preupgrade.jar and fix every ERROR before starting the upgrade, always take a full RMAN backup immediately before and after the upgrade, always run datapatch after upgrade if a Release Update was pre-applied to the 19c home, do not increase the COMPATIBLE parameter until testing is fully complete and signed off, and always take a post-upgrade backup before handing over the upgraded database.