Best practices for upgrading Amazon RDS for Oracle DB instances from 12c to 19c

Amazon Relational Database Service (Amazon RDS) for Oracle provides newer versions of databases as they are introduced by Oracle so you can keep your DB instances up to date. These versions can include bug fixes, security enhancements, and other improvements. When Amazon RDS for Oracle supports a new version, you can choose how and when to upgrade your DB instances.

Amazon RDS for Oracle supports two types of upgrades: major version and minor version. In general, a major engine version upgrade can introduce changes that aren’t compatible with existing applications. In contrast, a minor version upgrade in Oracle generally only includes changes that are backward-compatible with existing applications. In other words, a minor version upgrade applies an Oracle Database Patch Set Update (PSU) or Release Update (RU) to a major version. For example, upgrading from 12c to 19c (19.0.0) is a major version upgrade, whereas going from 12.2.0.1.ru-2021-01.rur-2021-01.r1 to 12.2.0.1.ru-2021-04.rur-2021-04.r1 is considered a minor version upgrade.

Starting in 2018, Oracle has introduced a new version numbering schema that coincides with the year of the database software release. Instead of a legacy nomenclature such as 12.2.0.2, a three-field format consisting of Year.Update.Revision is used, such as 19.1.0. For consistency with previous releases, Amazon RDS for Oracle lists these versions as major version 19.0.0.0, with the update and revision included in the full version name.

Amazon RDS for Oracle provides the option to automate some of the minor version patching decisions through automatic minor version upgrade, although you can still perform Minor Version Upgrades manually. In major version upgrade, due to the changes from the prior version, you will make [SG1] all the patching decisions. Therefore, it’s critical to be aware of common issues, steps involved, and best practices to upgrade with the least impact on your business.

In this post, we look at the 12c (12.2.0.1 the 12.1.0.2) End of Support timeline in Amazon RDS for Oracle, study the version upgrade choices available to you, and dive deep into the best practices to follow during the upgrade process.

This post is relevant to database administrators running their DB instances on Amazon RDS for Oracle. Most pre-upgrade and post-upgrade steps in this post are general guidelines from Oracle support and upgrade documentation relevant to Amazon RDS for Oracle. This post is written considering the various workloads on Amazon RDS for Oracle that could be impacted by upgrading a 12c (12.2.0.1 and 12.1.0.2) DB to 19c. Not all guidelines mentioned here are relevant to all customers. We strongly recommend that you use your judgment to see which one is suitable for your database based on your options and parameters used. Prior knowledge of Oracle database administration tasks and the configured environment including schemas, options etc. should be used for performing the upgrade. It’s also highly recommended that you test the upgrade in a non-production environment before performing the upgrade in a production environment.

Amazon RDS for Oracle: 12c (12.2.0.1 and 12.1.0.2) version End of Support timeline

Oracle has announced the end date of support for Oracle Database version 12.1.0.2 as July 31, 2022, after which Oracle Support will no longer release Critical Patch Updates for this database version. Amazon RDS for Oracle plans to support this version for both LI and BYOL for all editions until July 31, 2022.

Oracle has announced the end date of support for Oracle Database version 12.2.0.1 as March 31, 2022, after which Oracle Support will no longer release Critical Patch Updates for this database version. Amazon RDS for Oracle plans to support this version for both LI and BYOL for all editions until March 31, 2022.

Amazon RDS for Oracle will begin auto upgrading all 12.1.0.2 DB instances to 19c starting on August 1, 2022, and all 12.2.0.1 DB instances to 19c starting on April 1, 2022. We highly recommend you upgrade your existing RDS for Oracle 12.1.0.2 and 12.2.0.1 DB instances and validate your applications before the automatic upgrades begin.

The following tables summarize the timeline for Oracle 12c (12.1.0.2 & 12.2.0.1).

12.1.0.2 deprecation timeline by Amazon RDS for Oracle (for both BYOL and LI)

12.2.0.1 deprecation timeline by Amazon RDS for Oracle (for both BYOL and LI)

For up-to-date information about the End of Support for the 12c version by Amazon RDS for Oracle, see Amazon RDS for Oracle – End of Support Timeline for 12.1.0.2 and 12.2.0.1 Major Versions.

Refer to MOS note #742060.1 for the latest status of Oracle Database releases and support coverage. For License Included customers, create a support case for this document with AWS.

Version upgrade choices in Amazon RDS for Oracle

Amazon RDS for Oracle supports upgrading major versions 12.1.0.2 and 12.2.0.1 to major version 19.0.0.0. Premier Support for Oracle Database 19c ends on April 30, 2024, and Extended Support ends on April 30, 2027. Amazon RDS for Oracle plans to support Oracle Database 19c until April 30, 2027.

When upgrading any software, checking for compatibility of the new version and its features plays a crucial role in the upgrade’s overall success. Oracle Database versions and releases can have differences in how they work and interact with applications, which may result in compatibility issues. Although the way you interact with Amazon RDS for Oracle remains the same from the major version upgrade perspective, Oracle Database specific features across major versions may change or even become obsolete. For more information about major version upgrades, see Oracle database engine release notes.

Keep in mind the following:

• Major version downgrades aren’t supported by Amazon RDS for Oracle
• A major version upgrade from 12c (12.1.0.2 and 12.2.0.1) to 19c must upgrade to an Oracle Release Update (RU) released in the same month or later

For example, a major version upgrade from the 12.2 April 2020 RU (12.2.0.1.ru-2020-04.rur-2020-04.r1) to the 19c July 2020 RU (19.0.0.0.ru-2020-07.rur-2020-07.r1) is supported. However, a major version upgrade from the 12.2 April 2020 RU to the 19c January 2020 RU is not supported.

You can query the list of the valid target engine versions for upgrade with the AWS Command Line Interface (AWS CLI) based on your current database version. For example, the following AWS CLI command lists all the target engine versions for upgrade for the source Oracle 12.2 April 2020 RU (12.2.0.1.ru-2020-04.rur-2020-04.r1) with Oracle Enterprise Edition:

aws rds describe-db-engine-versions --engine oracle-ee --engine-version 12.2.0.1.ru-2020-04.rur-2020-04.r1 --query 'DBEngineVersions[].ValidUpgradeTarget' --output table

For information about the release date for each Oracle Release Updates(RUs), see Oracle database engine release notes.

Downtime considerations for a major version upgrade

Amazon RDS lets you manually initiate a major version upgrade by modifying the DB instance—either via the AWS Management Console, AWS CLI, or Amazon RDS API. This is an in-place upgrade and requires downtime for your applications during the upgrade process. In the event of any issues with the upgrade phase, you can restore the latest backup. The duration of the outage varies based on your engine version, parameters configured, number of objects in the database, option configured, and the DB instance class. You can determine the amount of time it takes by performing a test upgrade using a snapshot restore of the production database in a pre-production environment.

If performing the major version upgrade using the modify DB instance method isn’t desirable for your application, an alternative approach is using logical bi-directional replication with either AWS Database Migration Service (AWS DMS) or Oracle GoldenGate. This method can provide the least downtime for the upgrade. This method involves setting up logical replication between the source and target DB instances (both running on the same version). You then upgrade the target DB instance to 19c, while leaving the source to run on 12c (12.1.0.2 and 12.2.0.1). When the upgrade of the target DB instance is complete, you can point your application to the upgraded target DB instance. This method, which uses bi-directional replication between the source and target DB instances, can also be used as a fallback plan, should the upgrade break due to incompatibility.

This post covers the upgrade process using the modify DB instance method. The logical replication method of upgrading is out of scope of this post, learn more about this method on Upgrading Amazon RDS for Oracle database engine with minimal downtime using AWS DMS.

How Amazon RDS for Oracle performs a major version upgrade

When a major version upgrade is invoked on the console, AWS CLI, or Amazon RDS API, the automation completes the following steps:

1. Take a pre-upgrade snapshot (if configured for backups). You can use this snapshot to roll back to the previous engine version if needed.
2. Shut down the instance and prepare it for the upgrade.
3. Run Oracle upgrade scripts.
4. Take a post-upgrade snapshot.

When Amazon RDS initiates Step 1, the instance’s status changes from Available to Upgrading. After Step 4, it returns to Available. The actual downtime, however, is shorter; the database is shut down after the backup and starts back up when the upgrade is complete. The following diagram of an upgrade from version 12c to 19c illustrates the high-level steps when the modify-db-instance AWS CLI call is invoked.

Best practices for major version upgrades

In this section, we dive deep into the recommended best practices during various phases of the upgrade process: pre-upgrade, upgrade, and post-upgrade.

We discuss the common steps typically completed in most Oracle Database upgrades. For a complete list, see the Oracle Database Upgrade Guide.

Pre-upgrade phase

Oracle uses the following terminology when upgrading to a higher release (or major version upgrade in Amazon RDS terminology):

• Deprecated features – Features that are no longer being enhanced, but are still supported for the full life of this release of Oracle Database.
• Desupported features – Those that are no longer supported by fixing bugs related to that feature. Oracle can choose to remove the code required to use the feature.

Where indicated, a deprecated feature can be desupported in a future major release. When moving across multiple major releases during the upgrade process, it’s highly recommended to consult Oracle documentation for deprecated and desupported features, options, and parameters in the intermediary releases as well.

You should consider the following factors in the pre-upgrade phase.

Downtime considerations

A typical upgrade with all possible options in the option group might take 1–2 hours. To reduce downtime, consider the following:

• Take a manual snapshot using the create-db-snapshot AWS CLI call prior to starting the upgrade phase. This speeds up the time taken for a pre-upgrade snapshot (which is automatically invoked during the upgrade phase). In a Multi-AZ configuration, snapshots are taken from the standby instance and in case of a failover after the last snapshot or if there is large number of changed blocks since the last snapshot; this can lead to a longer time to complete the snapshot.
• When upgrading to 19c, it’s recommended to convert all DBMS_JOB entries to DBMS_SCHEDULER before the upgrade. During the upgrade, Oracle tries to convert DBMS_JOB to DBMS_SCHEDULER. If you have a large number of DBMS_JOB entries, the upgrade takes longer.
• Make sure the audit trails aren’t very large. Pre-upgrade checks and upgrades can take longer with large audit trails.
• Gather optimizer statistics before the upgrade using DBMS_STATS.GATHER_DICTIONARY_STATS. Also gather fixed and dictionary stats.
• Remove options that aren’t used. It’s a good practice to remove unused options to speed up the upgrades and have fewer issues and conflicts when moving from one major version to another.
• Remove or reset parameters that aren’t used for the same preceding reasons.
• If necessary, you might also have to upgrade the instance class based on the target instance choice. For example, instance classes like T2, R3, M3, and so on aren’t supported for Amazon RDS for Oracle 19c, and you need to modify the instance as well if using one of these older instance types. This may impact your Reserved Instances (RI) as well. If you have RIs for these older instance types, log a support ticket if you have questions on these. For more information, see Oracle on Amazon RDS.

Multi-AZ considerations

If your DB instance is in a Multi-AZ deployment, Amazon RDS for Oracle upgrades both the primary and Multi-AZ standby simultaneously.

Option groups considerations

Consider the following for option groups:

• By default, when one engine is upgraded to the next higher major version, Amazon RDS for Oracle chooses the default option group if the new corresponding custom option group for the target version isn’t chosen. For example, when upgrading from 12c to 19c, you should create a new option group that’s compatible with the new version 19c and matches closest to the 12.1 and 12.2 options, and use it during upgrade process. You should also consider factors such as APEX upgrade, which is recommended to be handled as a separate preparatory process, to speed up the upgrade. This also applies to OEM agents. In some cases, options are uninstalled and reinstalled as you move from one version to another. For example, SQLT is freshly installed, which deletes any old metadata stored by the option.
• When choosing the version of the OEM agent, consider the compatibility with the OMS.
• Note the option group and the VPC. The option group associated with the instance is linked to that VPC, which means that you can’t use the option group assigned to the DB instance if you try to restore the instance to a different VPC (for example, when you use the OEM option). When you upgrade a major version, understand the changes Oracle makes to the options in that change. For example, Oracle Multimedia is de-supported in Oracle Database 19c. However, it separated the MDSYS functionality into a separate option called Locator. Oracle also recommends you move from Locator to Spatial and Graph prior to the upgrade. All MDSYS functionality is available with Spatial and Graph, which is now available to all editions without a separate license. So, when you upgrade to 19c and install Spatial and Graph, all stored procedures work fine. For more information, see “My Oracle support” article 2347372.1. For License Included customers, create a support case for this document with AWS.

Parameter groups considerations

Consider the following regarding parameter groups:

• The continuous_mine functionality of the LogMine package DBMS_LOGMNR.START_LOGMNR is obsolete. It was deprecated in Oracle Database 12c Release 2 (12.2). There is no replacement functionality. If you use continuous_mine functionality, you need to use AWS DMS or Oracle GoldenGate solution while performing upgrade.
• The apply parameter OPTIMIZE_PROGRESS_TABLE for Oracle GoldenGate Integrated Replicat, XStream In, and Logical Standby, is desupported in Oracle Database 19c. Before you upgrade to Oracle Database 19c, you must turn off this parameter. If OPTIMIZE_PROGRESS_TABLE is set to ON, stop apply gracefully, turn off the parameter, and restart apply. For GoldenGate Apply and XStream, this parameter is set to OFF by default.

Security considerations

The following are some important security considerations:

• Oracle database accounts with password set to default or accounts that are locked due to password expiry lose the authentication method after the upgrade is complete. Although it could be reverted back, it’s recommended to validate the password for its strength before the upgrade and lock it.
• Check the accounts that use the case-insensitive password version. Log in to SQL*Plus as an administrative user, and enter the following SQL query. If there are any 10g versions, you should refer to the Oracle documentation to fix 10g versions, or user accounts with LOCKED after the upgrade is complete.

SELECT USERNAME, PASSWORD_VERSIONS FROM DBA_USERS;

• Make sure that you don’t have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE. Setting this parameter to FALSE prevents the use of the case-sensitive password versions (the 11g and 12c password versions) for authentication.

General considerations

Finally, you should consider the following general points:

• When upgrading to 19c, we recommend you look at the provisioned capacity and see if it will be met. Depending on the instance class your 12c database is running on, you may need to scale the compute to a newer generation of the instance class. For more information, see Oracle on Amazon RDS. This could also be an opportunity to evaluate Amazon RDS Reserved Instances, and you may want to purchase new leases for your version before performing the upgrade.
• Make sure that no objects are invalid before upgrading. It’s a good practice to keep a list of all objects with their respective count, type, and status prior to the upgrade and compare it after the upgrade.
• Take a backup of optimizer statistics for all application schemas using the DBMS_STATS package.
• Collect pre-upgrade AWR or Statspack snapshots prior to the upgrade. These are used during the post-upgrade performance validation. Even though you would have done this in the pre-production environment, it’s a good practice to do this comparison in the production environment.
• If you also have a DB maintenance task, such as adding partitions, it’s better to run them before upgrading.
• Disable scheduled database custom jobs or cron jobs.
• It’s recommended to disable any custom DDL triggers before upgrade and re-enable them after upgrade.
• If you’re using materialized views, check the status of all materialized views prior to the upgrade.

  SELECT * FROM DBA_MVIEWS;

  Check the size of the materialized view logs. If it’s non-zero, address them to make sure they are all in sync. It’s recommended to wait until all materialized views have completed refreshing. Make sure that any objects referencing remote databases across database links are accessible to reduce time spent on network timeouts. For more information, see “My Oracle support” Doc ID 1406586.1. For License Included customers, create a support case for this document with AWS.

• Keep a list of clients and its drivers used along with the version numbers. Identify the corresponding version that works with 19c.
• Review all hidden parameters and make sure they’re modified or removed to meet the target version. Include all features affected by these parameters as part of the functional and performance testing in the pre-production environment.
• Starting in Oracle Database 19c, the SQL*Plus table PRODUCT_USER_PROFILE (PUP table) is desupported. The SQL*Plus product-level security feature is unavailable in Oracle Database 19c. Oracle recommends that you protect data by using Oracle Database settings, so that you ensure consistent security across all client applications.

Upgrade phase

After you complete the pre-upgrade checks successfully, you can move on to the upgrade phase. If your instance has been using custom option group or parameter group configurations, you must specify new option and parameter groups for the upgrade to go through along with any other additional attributes.

In this section, we discuss how to perform the upgrade on the console or via the AWS CLI.

To upgrade the engine version of a DB instance on the console, complete the following steps:

1. On the Amazon RDS console, choose Databases.
2. Choose the DB instance that you want to upgrade.
3. Choose Modify.

The Modify DB Instance page appears.

4. For License model, choose the desired license type.
5. For DB engine version, choose the new version.
   
6. Choose Continue and check the summary of modifications.
7. To apply the changes immediately, choose Apply immediately.

Choosing this option can cause an outage in some cases. For more information, see Using the Apply Immediately setting. Otherwise, you can have the upgrade take place in your next weekly maintenance window.

8. On the confirmation page, review your changes and choose Modify DB Instance to save your changes.

To perform a major version upgrade in your next maintenance window using the AWS CLI modify-db-instance command, enter the following code:

aws rds modify-db-instance \
    --db-instance-identifier mydbinstance \
    --engine-version new_version \
    --allow-major-version-upgrade \
    --no-apply-immediately

For information about valid engine versions, use the AWS CLI describe-db-engine-versions command.

If you have multiple instances, you could use the AWS CLI combined with a shell script to upgrade multiple instances. For more information about upgrading the database engine for Amazon RDS for Oracle, see Upgrading the Oracle DB engine.

Post-upgrade phase

After the upgrade phase, you can complete the following checks and post-upgrade steps:

1. Recompile INVALID objects. As mentioned in the pre-upgrade phase section, checking for objects with INVALID status and fixing them is required before testing further. This is also a good time to compare the report (list of object count with status) gathered during the pre-upgrade phase.

   SELECT * FROM DBA_OBJECTS where STATUS = 'INVALID';

2. If Amazon RDS for Oracle is used as an RMAN catalog, upgrade the RMAN recovery catalog. Use the UPGRADE CATALOG command to upgrade the RMAN recovery catalog schema from an older version to the version required by the RMAN client.
3. Complete the following client connectivity steps:
   1. Make sure to set the ORACLE_HOME, PATH, LD_LIBRARY_PATH, LIBPATH, and more to 19.x in the client home.
   2. Make appropriate changes to tnsnames.ora and sqlnet.ora on the client side and incorporate changes related to 19c or the new endpoint if testing on a new RDS instance restored from the production.
   3. Test the client connectivity. Based on the identified target version of the driver, perform a regression test. Keep in mind there are cases where the choice of JDBC version might impact performance and change the process optimizer plan.
4. If you used the DBSM_STATS package to export the optimizer stats as mentioned in the pre-upgrade tasks, upgrade the stats table (using DBMS_STATS.UPGRADE_STAT_TABLE) where the optimizer statistics are backed up prior to the upgrade. Then restore statistics and run performance testing.
5. If you have database links to other RDS for Oracle DB instances or other Oracle instances on Amazon Elastic Compute Cloud (Amazon EC2), it’s recommended to test them from both a functionality and performance standpoint after the upgrade.
6. If you have any external scheduler jobs scheduled in Amazon CloudWatch or cron, ensure those are enabled and can still connect to the database after upgrade and run with no issues.
7. Also make sure to enable any DDL or system triggers disabled as part of the pre-upgrade check.
8. Make sure to have enough free space in SYSTEM and SYSAUX tablespaces. Having an additional 1–2 GB of space in each tablespace is recommended.
9. Validate your applications against the upgraded database. It’s vital that you look at the top SQL statements (either via AWR or Statspack) and verify that they’re using the desired plans.
10. After the upgrade, if the SQL statements perform poorly due to the change in the plans by the 19c optimizer, you can use OPTIMIZER_FEATURES_ENABLE. This parameter is alterable at the session level and system level. For example, if you upgrade your database from release 12c to release 19c, but you want to keep the release 12c optimizer behavior, you can do so by setting this parameter to 12c. At a later time, you can try the enhancements introduced in releases up to and including release 19c by setting the parameter to 19.0.0.
11. If you have an automated process of backup and restore, disaster recovery, or production to non-production refresh procedures, we strongly recommend you test them. You need to test any process that interacts with other databases that may or may not be at the same level.

Common issues

You must upgrade any manual snapshots you intend to retain, including the snapshots taken using AWS Backup or any third-party partner products. They are force-upgraded on restore, but longer-term they will eventually be deprecated. For example, when the backup is restored, the first thing the RDS does is force upgrade it to one of the supported versions and then complete the restore. If you want to keep it as a copy of the old version (for example, if the database is supporting an old version of a third-party application) you should plan on making a backup using native Oracle tools such as export with Data Pump or RMAN.

After you upgrade the database, if you run into connectivity issues such as “ORA-28040: No matching authentication protocol“, you might have to upgrade the client version or the JDBC driver. In general, it’s best to match the client and server versions. The best client version for the database version 19c is client 19c. However, the 12c clients are also supported on 19c. You can set sqlnetora.sqlnet.allowed_logon_version_server on the RDS instance if a lower version client is still needed. We highly recommend testing for this combination. Refer to “My Oracle support” document 207303.1 for more information with AWS.

Oracle upgrades of read replicas

The Oracle DB engine version of the source DB instance and all its read replicas must be the same. Amazon RDS performs the upgrade in the following stages:

1. Upgrade the source DB instance. The read replicas are available during this stage.
2. Upgrade the read replicas in parallel, regardless of the replica maintenance windows. The source DB is available during this stage.

The following diagram illustrates the read replica upgrade process. Multi-AZ read replicas update at the same time.

For major version upgrades of cross-Region read replicas, Amazon RDS performs additional actions:

1. Generate an option group for the target version automatically.
2. Copy all options and option settings from the original option group to the new option group.
3. Associate the upgraded cross-Region read replica with the new option group.

Test the upgrade of your RDS for Oracle DB instance

Before you upgrade the production database to a new major version, we recommend you to validate your applications against the new version in a pre-production environment. This rehearsal of the upgrade process also allows you to gauge the time it takes to perform this major version upgrade.

The following diagram illustrates the steps to test your upgrade.

The process includes the following steps:

1. Take a snapshot of the existing DB instance using the create-db-snapshot AWS CLI call.
2. Restore the DB snapshot using the restore-db-instance-from-db-snapshot AWS CLI call to create a test DB instance on the same version.
3. Run the modify-db-instance AWS CLI call on the test DB instance to upgrade it to the new major version.
4. When the upgrade is complete, you can validate your application against the test DB instance.
5. After the testing is complete, you can schedule and perform the production database upgrade.

After the upgrade is complete, review the output of the file preupgrade.log in the BDUMP directory using the console or the rdsadmin.rds_file_util.read_text_file PL/SQL procedure:

select * from (table(rdsadmin.rds_file_util.read_text_file('BDUMP', 'preupgrade.log')));

Disregard any findings related to Amazon RDS-managed features of the database or system configuration—such as directories and symbolic links – that are handled by Amazon RDS automation.

Additional considerations

Finally, consider the following:

• The Amazon RDS Database Activity Streams feature supports Amazon RDS for Oracle starting from version 19c for customers who use any edition of Oracle Database with the License Included or Bring Your Own License models. When you upgrade your database from 12c to a higher version, you may want to evaluate this managed database auditing capability. For more information, see Monitoring Amazon RDS for Oracle using Database Activity Streams.
• From Release Update 19.0.0.0.ru-2021-04.rur-2021-04.r1 onwards, for customers who use any edition of Oracle Database with the License Included or Bring Your Own License models, Amazon RDS for Oracle supports the creation of a DB instance with a single pluggable database (PDB) using the Oracle multi-tenant architecture, which enables the DB instance to operate as a multi-tenant container database (CDB). A PDB is a collection of schemas, schema objects, and non-schema objects that appear as a non-CDB to a client. For more information, see RDS for Oracle architecture.

Conclusion

In this post, we reviewed the 12c End of Support timeline in Amazon RDS for Oracle, studied the version upgrade choices available to you, and covered the best practices during the phases of the upgrade process.

If you need additional assistance in planning and upgrading your Amazon RDS for Oracle DB instances running on 12c, you can reach out to the following resources:

• AWS Professional Services
• AWS Partners

For more information about the upgrade, visit Upgrading the Oracle DB engine.

About the Authors

Roneel Kumar is an Amazon Web Services Senior Database Specialist Solutions Architect who specializes in Relational Database Engines. He provides Technical Assistance, operational, and database practices to customers in APJ.

Manash Kalita is an Amazon Web Services Senior Database Specialist Solutions Architect for ASEAN, having extensive experience in Enterprise Cloud Architecture.