Oracle 19c - The Different Methods of Upgrade Process - Never Upgrade Your Production Database Directly - Overview!

November 02, 2021  

 

Step 1: Prepare to Upgrade Oracle Database:

Become familiar with the features of the new release of Oracle Database.

Determine the upgrade path to the new release.

Select an upgrade method.

Select an Oracle home directory for the new release.

Develop a testing plan.

Prepare a backup strategy.

Follow preupgrade recommendations.

Run preupgrade fixup scripts, or carry out manual preupgrade system updates.

Note:

During the upgrade, consider running multiple releases of the database software, so that you can use the existing release as your production environment while you test the new release.

Consider completing a software-only installation to the new Oracle Database release. In a software-only installation, you install the Oracle Database software but do not create a database as part of the installation process.

Step 2: Test the Upgrade Process for Oracle Database:

Perform a test upgrade using a test database. Conduct the test upgrade in an environment created for testing that does not interfere with the production database. Oracle recommends that your test environment is on a server that is, as much as possible, a replica of your production environment. For example: Oracle recommends that the server not only uses the same operating system, but that runs the same patch level, with the same packages, and matches other details of your production system configuration.

Step 3: Test the Upgraded Test Oracle Database:

Perform the tests that you planned in Step 1 on the test database that you upgraded to the new release of Oracle Database.

Review the results, noting anomalies in the tests.

Investigate ways to correct any anomalies that you find and then implement the corrections.

Repeat Step 1, Step 2, and the first parts of Step 3, as necessary, until the test upgrade is successful and works with any required applications.

To test for anomalies and determine potential support questions, carry out SQL plan management. 

SQL plan management includes the following steps:

Before the upgrade, capture baselines and plans on the earlier release Oracle Database, and store those plans.

Oracle recommends that you store the plans on staging tables, and then run the Data Pump Export utility expdp for those tables.

After the upgrade, in the event of a regression or a performance issue, apply (load/accept/evolve) an old plan that you know is good, based on the plans you captured from the previous release Oracle Database.

Step 4: Prepare and Preserve the Production Oracle Database:

Complete these tasks before you upgrade your existing production database:

Prepare the current production database as appropriate to ensure that the upgrade to the new release of Oracle Database is successful.

Schedule the downtime required for backing up and upgrading the production database.

Back up the current production database.

Before you carry out a major change to a system, Oracle recommends that you make sure that you have a fallback strategy implemented. 

Oracle recommends that your fallback strategy includes the following preparations:

Test your backup strategy, and ensure that it works.

If you need a backup strategy, then plan for the time required to apply it during your maintenance window.

To perform plan stability checks in preparation for upgrade, carry out SQL plan management. Raise a service request if you need assistance.

Note:

A database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements.

Most plan changes result in either improvement or no performance change. However, some plan changes may cause performance regressions. SQL plan baselines significantly minimize potential regressions resulting from an upgrade.

When you upgrade, the database only uses plans from the plan baseline. The database puts new plans that are not in the current baseline into a holding area, and later evaluates them to determine whether they use fewer resources than the current plan in the baseline. If the plans perform better, then the database promotes them into the baseline; otherwise, the database does not promote them.

Step 5: Upgrade the Production Oracle Database:

Upgrade the production database to the new release of Oracle Database.

After the upgrade, perform a full backup of the production database and perform other post-upgrade tasks.

Step 6: Tune and Adjust the New Production Oracle Database:

Tune the new production database for the new release. Typically, the new production Oracle Database performs to the same standards, or better, than the database before the upgrade.

Determine which features of the new Oracle Database release that you want to use, and update your applications accordingly.

Develop new database administration procedures as needed.

Do not upgrade your production Oracle Database release to the new release until all applications you must use in the upgraded database have been tested and operate properly.

Example of Upgrading with the Database Upgrade Assistant

Below is an overview of the upgrade process using DBUA.

#1 - Backup Your Database:

Before the upgrade starts, take a full backup of the database, including archive logs, and ensure that you have enough downtime arranged with the Application team to revert to this backup if the upgrade fails or if you encounter any issues. The downtime should have already been estimated based on the upgrade of your Test/Dev instances.

#2 - Empty Recycle Bin & Gather Stats:

Before the upgrade process, empty the recycle bin and gather stats. One of the recommendations is to export the stats as well.

Ensure you have enough free space in db_recovery_file_dest and make changes to db_recovery_file_dest_size if needed.

#3 - Run Oracle’s Database Pre-Upgrade Utility:

Run the pre-upgrade script using the below guide from Oracle support:

How to Download and Run Oracle's Database Pre-Upgrade Utility (Doc ID 884522.1)

The script output will provide you with two additional scripts:

preupgrade_fixups.sql (Must be run before the upgrade)

postupgrade_fixups.sql (Must be run after the upgrade)

#4 - Install the Oracle 19c Database Software:

Install the Oracle 19c Database software (You can do this before Step 1 since you must install it in a different Oracle Home location from your Oracle 12c Home.)

#5 - Run the DBUA Utility

Run the DBUA utility to upgrade the database. The utility provides options for gathering stats before the upgrade, taking a database backup, creating a restore point, restoring the database to rollback, and creating custom scripts. 

#6 - Run Post-Upgrade Script & Restart Database:

After the upgrade is complete, run the postupgrade_fixups.sql and restart the database.

After completing the above steps, here are some post-upgrade steps:

Ensure the application connects to the database (Move your SQLNet.ora, Listener.ora). The listener can be reconfigured during the DBUA process itself.

2. Initiate a full backup of the database along with archive logs.

References:

https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/major-steps-upgrade-process-oracle-database.html#GUID-EE26CF0A-1B77-43D7-A613-9C6D0BF42DBB

Hope this helps!

Cheers!

Ramesh.