Oracle Database Upgrade: 12cR2 → 19c Using AutoUpgrade

Why AutoUpgrade?

• Time‑saving: automates most steps and completes faster than manual upgrades.
• Multi‑DB upgrades: a single config file can upgrade many databases in parallel.
• Issue prevention: prechecks catch blockers early; fixups can auto‑remediate.
• End‑to‑end flow: runs pre‑ and post‑upgrade checks and copies network configs.

AutoUpgrade Processing Modes

• Analyze — read‑only pre‑upgrade analysis on the source home.
• Fixups — runs Analyze plus automatic pre‑upgrade fixups (flags manual items).
• Deploy — full flow: GRP, preupgrade, checks, fixups, drain, upgrade, postchecks, post‑fixups, postupgrade (copies sqlnet.ora, listener.ora, tnsnames.ora).
• Upgrade — run upgrade actions when source home is not available.

Internal Stages (high level)

SETUP → PREUPGRADE → PRECHECKS → GRP → PREFIXUPS → DRAIN → DBUPGRADE → POSTCHECKS → POSTFIXUPS → POSTUPGRADE

Requirements (summary)

• 19c software installed only in a new ORACLE_HOME.
• Java (JDK 8) available on the source server.
• Archivelog mode enabled.
• Free space for FRA, datafiles, and archived logs (see preupgrade report).
• Backups completed and verified.

Pre‑Upgrade Tasks (Do Before AutoUpgrade)

1) Backups & critical files

# Example: run your RMAN script (sample rmanbackup.sh)
chmod 775 rmanbackup.sh
nohup ./rmanbackup.sh &

# Save network files from the source home
cp -p $ORACLE_HOME/network/admin/{listener.ora,sqlnet.ora,tnsnames.ora} /u01/app/backup/

# Save spfile/pwd file
cp -p $ORACLE_HOME/dbs/spfile<SID>.ora /u01/app/backup/
cp -p $ORACLE_HOME/dbs/orapw<SID>     /u01/app/backup/

2) Clean up invalids, gather dictionary stats, purge recyclebin

-- as SYS from the source home
@$ORACLE_HOME/rdbms/admin/utlrp.sql
SELECT count(*) FROM dba_objects WHERE status='INVALID';  -- expect 0
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PURGE DBA_RECYCLEBIN;

3) Verify materialized views are refreshed (no long refreshes pending)

SELECT o.name
FROM sys.obj$ o, sys.user$ u, sys.sum$ s
WHERE o.type# = 42 AND bitand(s.mflags,8)=8;
-- No rows expected. If any, refresh MVs before upgrading.

4) Generate a preupgrade report (optional but recommended)

. oraenv  # set to source SID
/u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/<SID>/preupgrade
# Outputs: preupgrade.log, preupgrade_fixups.sql, postupgrade_fixups.sql

5) Verify tablespace headroom & archive destination space

Review preupgrade.log minimum sizes (SYSTEM, SYSAUX, TEMP, UNDO) and ensure archive destination has ample free space (df -h <dest>).

6) Create working dirs

mkdir -p /u02/upgrade_to_19c/global_logs
mkdir -p /u02/upgrade_to_19c/<SID>_upgrade_logs

Install 19c Software Only

Extract/place the 19c software into new target home, e.g. /u02/app/oracle/product/19.0.0/dbhome_1 (do not create a database).

Create AutoUpgrade Config

Generate a template and edit it:

java -jar /u02/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/autoupgrade.jar -create_sample_file config
# Creates sample_config.cfg

Example sha_config.cfg (adjust names/paths):

global.autoupg_log_dir=/u02/upgrade_to_19c/global_logs

upg1.dbname=sha
upg1.sid=sha
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/12.2.0/dbhome_1
upg1.target_home=/u02/app/oracle/product/19.0.0/dbhome_1
upg1.log_dir=/u02/upgrade_to_19c/sha_upgrade_logs
upg1.upgrade_node=<host FQDN>
upg1.target_version=19
# Optional helpers
# upg1.run_utlrp=yes
# upg1.timezone_upg=yes

You can list multiple upgN.* blocks to upgrade many databases at once.

Run AutoUpgrade

From the working directory (e.g., /u02/upgrade_to_19c):

Analyze

java -jar /u02/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/autoupgrade.jar   -config sha_config.cfg -mode ANALYZE
# Monitor with: lsj | status -job <id> | tasks

Fix any items reported. You may also run -mode FIXUPS to apply supported fixes automatically.

Deploy (full flow)

# If you previously ran a failed job, move/clean log dirs to avoid conflicts
mv global_logs /u01/ || true
mv sha_upgrade_logs /u01/ || true

java -jar /u02/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/autoupgrade.jar   -config sha_config.cfg -mode DEPLOY

AutoUpgrade will create a Guaranteed Restore Point (GRP) automatically (EE only), shut down the DB, perform the upgrade, and complete post steps.

Monitor anytime:

lsj          # list jobs
status -job <id>
tasks

If you hit FRA size errors, increase FRA (for example to 16000 MB), then rerun DEPLOY.

Post‑Upgrade Tasks

Ensure environment now points to the 19c ORACLE_HOME.

1) Network and init files

AutoUpgrade usually copies tnsnames.ora, sqlnet.ora, listener.ora to the target home. Verify and adjust as needed:

# 19c tns entries example
# listener.ora, tnsnames.ora, sqlnet.ora in $ORACLE_HOME/network/admin
lsnrctl start LISTENER_<SID>
tnsping <service>

Password file is typically migrated. Confirm:

ls -l $ORACLE_HOME/dbs/orapw<SID>
show parameter remote_login_passwordfile  -- expect EXCLUSIVE

2) Start database (normal)

export ORACLE_HOME=/u02/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=<SID>
sqlplus / as sysdba
STARTUP;

3) Verify component status and version

SELECT name, host_name, version, open_mode FROM v$database, v$instance;
SELECT comp_id, comp_name, version, status FROM dba_registry ORDER BY comp_id;

4) Recompile invalids & gather stats

@$ORACLE_HOME/rdbms/admin/utlrp.sql
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

5) Run pre/post fixup scripts (from preupgrade.jar run)

@/home/oracle/<SID>/preupgrade/preupgrade_fixups.sql
@/home/oracle/<SID>/preupgrade/postupgrade_fixups.sql

6) Time zone (if required)

If v$timezone_file is older than the latest (e.g., 26 → 32), run:

-- check
SELECT version FROM v$timezone_file;

-- upgrade (restarts DB twice)
@$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
@$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql

SELECT version FROM v$timezone_file;  -- expect latest

7) Post‑upgrade status report (19c)

@$ORACLE_HOME/rdbms/admin/utlusts.sql TEXT

8) catuppst.sql (normally run automatically)

@$ORACLE_HOME/rdbms/admin/catuppst.sql

9) Guaranteed Restore Point & COMPATIBLE

Keep the GRP until apps certify the upgrade. When approved:

-- drop GRP
SELECT name, guarantee_flashback_database, time FROM v$restore_point;
DROP RESTORE POINT <name>;  -- e.g., AUTOUPGRADE_* or PRE_UPGRADE

-- set COMPATIBLE (irreversible for downgrade)
ALTER SYSTEM SET compatible='19.0.0' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE; STARTUP;
SHOW PARAMETER compatible;  -- expect 19.0.0

10) Validate app objects

• Confirm critical schemas (e.g., SCOTT) are intact:
  SELECT COUNT(*) FROM scott.scott_test; -- expect 10000
• Check AWR/monitoring, backups, and jobs.

Sample SQL/Commands (from example)

Create a check table and data before upgrade (optional sanity test):

CREATE TABLE scott_test (id NUMBER, description VARCHAR2(50));
INSERT /*+ APPEND */ INTO scott_test
SELECT level, 'Description for ' || level FROM dual CONNECT BY level <= 10000;
COMMIT;
SELECT COUNT(id) FROM scott_test;  -- 10000

Example environment switch after upgrade:

echo $ORACLE_HOME    # old
export ORACLE_HOME=/u02/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

Logs to review during/after upgrade:

$TARGET_HOME/cfgtoollogs/<SID>/upgrade*/catupgrd*.log
$TARGET_HOME/cfgtoollogs/<SID>/upgrade*/upg_summary.log
nohup.out (if using dbupgrade wrapper)

Quick Troubleshooting

• FRA / archive destination too small → increase db_recovery_file_dest_size and filesystem space.
• Tablespace minimums not met → resize or add datafiles before retrying.
• Insufficient disk space for homes → add storage or grow LVM volumes.
• Job failed → review job logs under global.autoupg_log_dir and run status -job <id>.

Final Verification

SELECT banner FROM v$version;
SELECT comp_id, status FROM dba_registry ORDER BY comp_id;
SELECT COUNT(*) FROM dba_objects WHERE status='INVALID';  -- expect 0

✅ Your database is upgraded to 19c and validated.