What factors should I consider when I perform a major version upgrade in Amazon RDS for Oracle?

Short description

When Amazon RDS starts to support a new version of the Oracle database, you can upgrade your existing Oracle database version by choosing a higher version and then performing a major version upgrade. A major version might include updates, new features, security fixes, optimizer enhancements, and performance improvements. It's a best practice to test your application's functionality, compatibility, and performance against the new version of the Oracle database in non-production environments before upgrading your production database.

Note: If you receive errors when running AWS CLI commands, make sure that you’re using the most recent version of the AWS CLI.

Resolution

Before the upgrade

Here are a few things to keep in mind before performing the major version upgrade:

Upgrade Path: Verify the supported upgrade path from your current Oracle database version to the intended major version of Oracle database. You can check the valid upgrade paths by running the following AWS Command Line Interface (AWS CLI) command.

For Windows:

aws rds describe-db-engine-versions --engine engine-edition --engine-version current-engine-version --query "DBEngineVersions[*].ValidUpgradeTarget[?IsMajorVersionUpgrade==`true`].EngineVersion"

For Linux, macOS, or Unix:

aws rds describe-db-engine-versions --engine engine-edition --engine-version current-engine-version --query 'DBEngineVersions[*].ValidUpgradeTarget[?IsMajorVersionUpgrade==`true`].EngineVersion'

Be sure to replace the following values in the preceding commands:

• engine-edition with the edition of the database engine.
• current-engine-version with the current version of the database engine.

Suppose that you have an Amazon RDS for Oracle 12.1.0.2.v10 instance. To learn about all the valid major versions that you can upgrade your RDS for Oracle instances to, run the following command.

For Windows:

aws rds describe-db-engine-versions --engine oracle-ee --engine-version 12.1.0.2.v10 --query "DBEngineVersions[*].ValidUpgradeTarget[?IsMajorVersionUpgrade==`true`].EngineVersion"

For Linux, macOS, or Unix:

aws rds describe-db-engine-versions --engine oracle-ee --engine-version 12.1.0.2.v10 --query 'DBEngineVersions[*].ValidUpgradeTarget[?IsMajorVersionUpgrade==`true`].EngineVersion'

Instance class: Verify that your current instance class is supported for the major version you are going to upgrade. For information on the instance classes that are supported by RDS for Oracle, see Supported Oracle DB instance classes. You can also check the supported instance class by running the following AWS CLI command.

For Windows:

aws rds describe-orderable-db-instance-options --engine engine-edition --engine-version new-engine-version --region example-region --query "OrderableDBInstanceOptions[*].DBInstanceClass"

For Linux, macOS, or Unix:

aws rds describe-orderable-db-instance-options --engine engine-edition --engine-version new-engine-version --region example-region --query 'OrderableDBInstanceOptions[*].DBInstanceClass'

Be sure to replace the following values in the preceding commands:

• engine-edition with the edition of the database engine.
• new-engine-version with the new version that you plan to upgrade to.
• example-region with the Region that you are using.

For example, suppose that you want to upgrade to an RDS for Oracle 12.2.0.1.ru-2020-10.r1 instance. To learn about the instance classes that support the major version in a specific Region, run the following command:

aws rds describe-orderable-db-instance-options --engine oracle-ee --engine-version 12.2.0.1.ru-2020-10.rur-2020-10.r1 --region us-east-1 --query "OrderableDBInstanceOptions[*].DBInstanceClass"

Be sure to replace us-east-1 in the command with the Region of your choice.

Client compatibility: Verify that the Oracle client/driver versions are compatible with the new major version. Check if the drivers must be upgraded along with the major version upgrade. For information on interoperability, see Oracle documentation for Oracle Database Client and Oracle Database Interoperability.

Upgrade method: You can perform the major version upgrade in either of the following ways:

• Modify the RDS instance and apply the new major version.
  Note: This method involves some downtime.
• Create a new RDS instance with the major version and migrate the data using AWS Data Migration Service (AWS DMS).
  Note: AWS DMS uses a minimalistic approach to migrate data and creates only those objects required to efficiently migrate the data. AWS DMS creates tables, primary keys, and in some cases unique indexes, but doesn't create any other objects that are not required to efficiently migrate the data from the source. For example, AWS DMS doesn't create secondary indexes, nonprimary key constraints, or data defaults. For more information, see High-level view of AWS DMS.

Choose one of these methods to perform the upgrade based on your use case.

Custom parameter group: If your instance has a custom parameter group, then create a new parameter group for the new major version and set the custom parameters appropriately. To identify the custom parameters that you currently use, compare the existing custom parameter group with the current version's default parameter group.

Custom option group: If your instance has a custom option group, then create a new custom option group for the major version. If you have persistent or permanent options, such as time zone or Oracle Transparent Data Encryption, in your option group, then the same persistent or permanent options must be included in the new custom option group. To upgrade the time zone file version to the latest available version in the database instance along with your major version upgrade, add the TIMEZONE_FILE_AUTOUPGRADE option to the new custom option group.

Dictionary statistics: Gathering dictionary statistics and fixed object statistics can reduce instance downtime during the upgrade. You can gather the statistics by running the following queries:

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Invalid objects: Verify and make sure that your database doesn't contain invalid objects. You can do so by running the following query:

SQL> SELECT OWNER, STATUS, COUNT (*) FROM DBA_OBJECTS GROUP BY OWNER, STATUS;

If invalid objects are found, check which object is invalid by running the following query:

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE STATUS != 'VALID';

You can compile all the invalid objects in the schema by running the following query:

SQL> EXEC DBMS_UTILITY.compile_schema(schema => 'ADMIN', compile_all => false);

Be sure to replace ADMIN in the query with the name of your schema.

Audit trails: Be sure that the audit trails aren’t lengthy. Pre-upgrade checks and upgrades might take longer with lengthy audit trails. To truncate audit trails, see How do I truncate the sys.aud$ table on my Amazon RDS DB instance that is running Oracle? You can also use the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure to delete the audit trails.

Passwords: Verify you are not using older version of passwords by running the following query:

SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

From the query results, if you find that a user uses only the 10g version of passwords, check if the user can be recreated with a newer version of password. If you can't fix the user password version, then set the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter appropriately to avoid connection errors.

When the parameter sqlnetora.sqlnet.allowed_logon_version_server is defined in an RDS for Oracle instance, the parameter denotes the minimum version of authentication protocol that's allowed when connecting to the database server. A setting of 8 permits most password versions and allows any combination of the DBA_USERS.PASSWORD_VERSIONS values 10G, 11G, and12C.

When the parameter sqlnetora.sqlnet.allowed_logon_version_client is defined in an RDS for Oracle instance, the parameter denotes the minimum authentication protocol to be used when the database acts as the client.

For more information, see Oracle documentation for Checking for accounts using case-insensitive password version.

DBMS_JOB: The DBMS_JOB package is discontinued with the upgrade of Oracle database 12c Release 2. If you are upgrading to version 19c, it's a best practice to convert all DBMS_JOB jobs to DBMS_SCHEDULER jobs before the upgrade. During the upgrade, Oracle converts the DBMS_JOB jobs to DBMS_SCHEDULER jobs. For more information, see Oracle documentation for Support for DBMS_JOB. If you have a large number of DBMS_JOB entries, the upgrade might take longer.

FreeStorageSpace: Verify that your instance is not nearing its storage capacity. Be sure that you have enough free storage space available for the upgrade to complete successfully by checking the FreeStorageSpace CloudWatch metric. For more information, see How can I create CloudWatch alarms to monitor the Amazon RDS free storage space and prevent storage full issues?

Maintenance actions: Check if your instance has any pending maintenance actions in the Amazon RDS console. These actions are applied during the upgrade window. For Multi-AZ instances, if no operating system updates are required, then the primary and standby upgrades occur at the same time. If operating system updates are required, then Amazon RDS applies the upgrade as outlined in Oracle upgrades in a Multi-AZ deployment.

Manual snapshot: Create a manual snapshot of your RDS for Oracle database instance for the following reasons:

• The snapshot can be used to roll back to the previous version, as long as the version is supported by Amazon RDS.
• An automated snapshot is usually created as part of the major version upgrade process. As Amazon Elastic Block Store (Amazon EBS) snapshots are incremental, the new snapshot has fewer changes to back up. Therefore, creating a manual snapshot might reduce the time taken to create the automated snapshot and the total time spent in completing the upgrade.

Current configuration: Run the following query to view the current configuration of your instance and save the output. The output provides you with information on the option group, parameter group, security groups, and tags that are attached to the current RDS for Oracle instance. To rollback and perform a restore from snapshot or perform point-in-time recovery, use the information that you retrieve from this command:

> aws rds describe-db-instances --db-instance-identifier example-instance-name --region example-region

Be sure to replace the following values in the query:

• example-instance-name with the name of your RDS for Oracle instance.
• example-region with the Region of your choice.

Undo tablespace: Be sure that your Undo Tablespace is set to the right size to avoid resize operations during the upgrade.

Triggers: Run the following query to list your logon, logoff, and startup triggers:

SQL> SELECT OWNER, TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, STATUS, ACTION_TYPE, TRIGGER_BODY FROM DBA_TRIGGERS WHERE TRIGGERING_EVENT LIKE '%LOGO%' or TRIGGERING_EVENT LIKE '%STARTUP%';

Check if the triggers are valid and functional. Although the trigger is valid and compiles well, the trigger might throw an error at runtime and interfere with database reboots. Check if there are any logon, logoff, or startup triggers that throw an error when they run. You can deactivate these triggers by running the following queries:

SQL> ALTER TRIGGER EXAMPLE-OWNER.EXAMPLE-TRIGGER DISABLE;

Be sure to replace the following values in the query:

• EXAMPLE-OWNER with the name of the schema where you created the trigger.
• EXAMPLE-TRIGGER with the name of the trigger.

For example:

--To disable AUDIT_USERS trigger in MYADMIN schema
SQL> ALTER TRIGGER MYADMIN.AUDIT_USERS DISABLE;

During the upgrade

After you initiate the upgrade, you can monitor the progress of the upgrade by checking the following in the Amazon RDS console:

• Alert log located under the Logs & events tab of your instance
• Recent events located under the Logs & events tab of your instance

After the upgrade

• Run the following queries to verify the patch version after connecting to the database:

SQL> SELECT * FROM sys.registry$history;
SQL> SELECT INSTALL_ID,PATCH_ID,ACTION,STATUS,ACTION_TIME,DESCRIPTION FROM DBA_REGISTRY_SQLPATCH;

• Amazon RDS updates the lsinventory-dbv.txt files within an hour of applying the patch. You can download this file from the Logs & events tab of your instance in the Amazon RDS console. Verify the applied patches by reading the Isinventory-dbv.txt file.
• Run the following queries to gather dictionary statistics and fixed object statistics:

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

• Run the following query to verify that the number of valid and invalid objects match with those before the upgrade:

SQL> SELECT OWNER, STATUS, COUNT(*) from DBA_OBJECTS GROUP BY OWNER, STATUS;

• Run the following query to compile all the invalid objects in the schema:

SQL> EXEC DBMS_UTILITY.compile_schema(schema => 'ADMIN', compile_all => false);

• If you have query performance issues after the upgrade due to the optimizer features of the new major version, then consider using the OPTIMIZER_FEATURES_ENABLE parameter. You can alter this parameter at the session level and system level. For example, if you upgrade your database from release 18.1 to release 19.1, but you want to keep the release 18.1 optimizer behavior, then you can do so by setting the value of the OPTIMIZER_FEATURES_ENABLE parameter to 18.1.0.

---

Related information

Upgrading the Oracle DB engine