A Professional's Guide to Upgrading to Oracle Database 19c

A Professional's Guide to Upgrading to Oracle Database 19c

Oracle Database 19c, as the long-term support release of the Oracle 12c, 18c, and 21c family, represents a significant milestone in database management. It offers extended stability, improved security, and a host of new features that enhance performance and scalability. For experienced professionals, upgrading to 19c is not just a technical task; it's a strategic move to future-proof your infrastructure. This guide will walk you through the essential steps and best practices for a successful upgrade.

---

Understanding Your Upgrade Path

Before you begin, it's critical to understand your starting point. Oracle supports direct upgrades to 19c from several previous versions, including 11.2.0.4, 12.1.0.2, 12.2.0.1, and 18c.¹ If your database is an older version, you must first upgrade to a supported release before moving to 19c. You also need to choose your upgrade method:

• Database Upgrade Assistant (DBUA): A graphical tool that simplifies the upgrade process, making it ideal for single-instance databases and those less familiar with command-line operations.

• Manual Upgrade: A command-line approach that offers more control and is preferred for complex environments, such as Oracle Real Application Clusters (RAC), or for a scripted, repeatable process.

• AutoUpgrade: A powerful, command-line utility that automates most of the upgrade process, handling multiple databases simultaneously and automatically managing pre- and post-upgrade tasks. It's the recommended tool for most upgrades, especially in large-scale or multi-tenant environments.

---

The Pre-Upgrade Checklist: Meticulous Preparation is Key

A successful upgrade is 90% preparation. Skipping steps here can lead to significant issues and downtime.

1. Back up your database. This is the most crucial step. A full RMAN backup, or a guaranteed restore point, provides a safety net to revert to in case of an unforeseen failure.

2. Install the Oracle 19c software. Install the 19c binaries into a new, separate Oracle Home directory. This is known as an "out-of-place" upgrade and is the recommended approach as it simplifies fallback and minimizes downtime. Do not perform an in-place upgrade. After installation, apply the latest Release Updates (RUs) and other necessary patches to the new 19c home.²

3. Run the Pre-Upgrade Utility.

This utility, included with the 19c software, analyzes your database for potential issues that could prevent a successful upgrade. It generates a report with a list of required and recommended actions.

• Required Actions: These are critical fixes that you must apply before the upgrade.³ They might include dropping invalid objects, resolving dictionary issues, or addressing tablespace sizing.⁴

• Recommended Actions: These are suggestions for best practices and performance improvements, such as gathering dictionary statistics or purging the recycle bin.⁵ While not strictly mandatory, they are highly advised. The utility will also generate preupgrade_fixups.sql and postupgrade_fixups.sql scripts to help you automate these steps.⁶

4. Address invalid objects and gather statistics.⁷ Prior to the upgrade, compile any invalid objects in the source database using the utlrp.sql script.⁸ Additionally, it is a best practice to gather dictionary statistics to ensure the optimizer has accurate information.

---

Executing the Upgrade

Once your preparation is complete, the actual upgrade process can be executed.

1. Set up the environment.

Ensure your environment variables, specifically ORACLE_HOME and ORACLE_SID, point to the new 19c home and the database you are upgrading.9

2. Start the upgrade.

• Using AutoUpgrade: This is the preferred method. Create a configuration file (.properties) that specifies your source and target homes and the databases to be upgraded. Then, run AutoUpgrade in "analyze" mode to get a final check of your plan before executing the "deploy" command.

• Using DBUA: Launch the DBUA utility from the 19c Oracle Home.¹⁰ The wizard will guide you through the process, providing options to perform a pre-upgrade check, create a restore point, and configure post-upgrade steps.

• Manual Upgrade: If you're opting for the manual route, you will need to perform a series of steps, including shutting down the database, starting it in upgrade mode, and running the dbupgrade script. This method requires a deep understanding of the process and careful attention to detail.

---

Post-Upgrade Validation and Optimization

The upgrade is not complete until you have validated the new environment.

1. Run post-upgrade scripts and checks.

Execute the postupgrade_fixups.sql script generated earlier to address any remaining issues.11 Also, compile any invalid objects that may have appeared after the upgrade by running the utlrp.sql script from the new Oracle Home.12

2. Update the COMPATIBLE parameter. This parameter, which controls the database's compatibility level, should be set to '19.0.0'.¹³ This enables new features and behaviors of Oracle 19c. You will need to bounce the database for this change to take effect.

3. Verify database health and functionality.

Check the dba_registry view to ensure all database components are valid. Test application connectivity and functionality to confirm that the upgrade has not introduced any regressions.

4. Perform a full backup.

With the upgrade successfully completed, perform a new full backup of your 19c database. This establishes a new baseline for your recovery strategy.

---

Video Resource

This video provides an overview of how to upgrade an Oracle database from 12c to 19c using the manual method, demonstrating the key steps involved.

Oracle 12c to 19c manual upgrade steps