Step‑by‑Step Guide to Upgrading Oracle Database 19c with Hands‑On Labs

Overview

The article provides a comprehensive, hands‑on tutorial for upgrading Oracle Database 19c, starting from environment preparation to post‑upgrade performance comparison. It uses a pre‑built virtual‑machine image (downloadable via the provided URL) and Oracle Cloud Infrastructure (OCI) as optional execution platforms.

1. Environment Setup

Download and import the virtual‑machine zip files into your preferred hypervisor (Oracle VM VirtualBox, OCI, or any compatible VM platform). The VM contains multiple Oracle database versions, including an 11.2.0.4 instance named upgr that will be upgraded to 19.3.

2. Generate Workload and AWR Snapshots

Use HammerDB to create a benchmark workload for the upgr database and capture an AWR snapshot. The steps are:

Set environment variables and create the first snapshot (snapshot ID 92).

Start HammerDB with ./hammer.sh and load the predefined workload script.

Run the workload, monitor until the status shows all workers completed (value 1), then close HammerDB.

Return to SQL*Plus, verify the capture script has finished, and create a second snapshot (snapshot ID 93).

3. Capture and Export SQL Tuning Set

After the workload runs, query the generated SQL Tuning Set (STS) and its associated snapshots:

Execute the provided script (shown in the red box of the image) to list STS information.

Run the displayed SQL statements to retrieve detailed STS data.

4. Export AWR Report

Export the AWR report to the directory object DATA_PUMP_DIR:

Run the script that prompts for the DBID (press Enter for default).

Enter the number of days for snapshot listing (e.g., 1).

Provide the start and end snapshot numbers (ensure they are from the same database instance).

Select the output directory (the pre‑created DATA_PUMP_DIR).

Accept the default report name or type a custom one.

5. Manual Database Upgrade (11.2.0.4 → 19.3)

The upgrade is divided into eight steps:

Pre‑upgrade preparation : Run the provided script to display current database information and perform prerequisite checks.

Run OLS PRE‑UPGRADE script : Execute the script in SQL*Plus and verify no errors appear.

Adjust parameters and run pre‑upgrade fix script : Modify processes and SGA (e.g., set SGA to 1 GB for a laptop), export the spfile, then run the fix script.

Shutdown database, copy init.ora, create password file : Follow the commands shown in the red box of the image.

Create spfile and start database in UPGRADE mode : Launch sqlplus, start the instance, and wait for the upgrade to begin (20‑50 minutes).

Clean invalid objects and run post‑upgrade fix script : Set the upgr19 environment, start the database, and execute the cleanup script (5‑10 min) followed by the post‑upgrade script (≈5 min).

Change time zone : Update the time‑zone version from TZV14 to TZV32, which may require multiple database restarts.

Update compatible version and oratab : Use ALTER SYSTEM SET COMPATIBLE='19.0.0', restart the database, and modify /etc/oratab to point to the new Oracle home.

6. Post‑Upgrade Validation

After the upgrade, verify that the database is running in the new version, re‑run AWR export, and compare performance metrics with the pre‑upgrade baseline captured earlier.

Key Takeaways

This tutorial demonstrates a full end‑to‑end upgrade workflow, including workload generation, snapshot management, SQL tuning set handling, AWR reporting, and a manual upgrade process with detailed command‑line steps, making it a practical reference for Oracle DBAs preparing for 19c migrations.