What must I know when I perform a major version upgrade in Amazon RDS for Oracle?

Short description

When Amazon RDS supports a new version of the Oracle database, you can upgrade your current Oracle database version to a later version. After you do this, you can then perform 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 you upgrade your production database.

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshoot AWS CLI errors. Also, make sure that you're using the most recent AWS CLI version.

Before the upgrade

Note the following before you perform the major version upgrade:

Upgrade path

Verify the supported upgrade path from your current Oracle database version to the later major version of Oracle database. To check the valid upgrade paths, run the following 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'

Note: Be sure to replace the following values in the preceding commands: engine-edition with the edition of the database engine and current-engine-version with the current version of the database engine.

For example, suppose that you have an Amazon RDS for Oracle 19.0.0.0.ru-2024-04.rur-2024-04.r1 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-cdb --engine-version 19.0.0.0.ru-2024-04.rur-2024-04.r1 --query "DBEngineVersions[*].ValidUpgradeTarget[?
IsMajorVersionUpgrade==`true`].EngineVersion"

For Linux, macOS, or Unix:

aws rds describe-db-engine-versions --engine oracle-ee-cdb --engine-version 19.0.0.0.ru-2024-04.rur-2024-04.r1 --query 'DBEngineVersions[*].ValidUpgradeTarget[?
IsMajorVersionUpgrade==`true`].EngineVersion'

Note: Amazon RDS for Oracle supports major version upgrades from 19c CDB to 21c. Amazon RDS for Oracle doesn't support major version upgrades from non-CDB (12.1, 12.2, 19c) to 21c. To work around this, first convert an Oracle Database 19c non-CDB to an Oracle Database 19c CDB. Then, upgrade the Oracle Database 19c CDB to an Oracle Database 21c CDB. For more information, See Considerations for Oracle DB upgrades and Converting an RDS for Oracle non-CDB to a CDB.

Instance class

Verify that your current instance class is supported for the major version that you plan to upgrade to. For information on the instance classes that are supported by RDS for Oracle, see Supported RDS for Oracle DB instance classes. To check the supported instance class, run 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 previous 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 19.0.0.0.ru-2024-04.rur-2024-04.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-cdb --engine-version 19.0.0.0.ru-2024-04.rur-2024-04.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 or driver versions are compatible with the new major version. Check whether the drivers must be upgraded along with the major version upgrade. For information on interoperability, see Oracle Database Client and Oracle Database interoperability on the Oracle website.

Upgrade method

You can use either of these methods to perform the major version upgrade:

• 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 then migrate the data with 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. However, it doesn't create any other objects that aren't 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.

Custom parameter group

If your instance has a custom parameter group, first create a new parameter group for the new major version. Then, set the custom parameters as needed. To identify the custom parameters that you currently use, compare the current 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 in your option group, then the same options must be included in the new custom option group. To upgrade the time zone file version to the latest available version, add the TIMEZONE_FILE_AUTOUPGRADE option to the new custom option group.

Dictionary statistics

Dictionary statistics and fixed object statistics can reduce instance downtime during the upgrade. To gather the statistics, run the following command:

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

Objects that aren't valid

Make sure that your database doesn't contain objects that aren't valid. To check for objects that aren't valid, run the following command:

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

If the output shows objects that aren't valid, check the object that's not valid, and then run the following command:

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

To compile a list of the objects that aren't valid in the schema, run the following command:

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 when you have lengthy audit trails. For more information, see How do I truncate the sys.aud$ table on my Amazon RDS DB instance that's running Oracle? Use the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure to delete the audit trails. For more information, see CLEAN_AUDIT_TRAIL Procedure on the Oracle website.

Passwords

To verify that you don't use earlier versions of your passwords, run the following command:

SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

If the user uses a 10g version of a password, then check whether you can recreate the user with a newer version of the password. It's a best practice to use a later password version. If you can't fix the user's password version, then set the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter as needed to avoid connection errors.

The parameter sqlnetora.sqlnet.allowed_logon_version_server denotes the minimum version of the authentication protocol that's allowed when you connect to the database server. For more information, see SQLNET.ALLOWED_LOGON_VERSION_SERVER on the Oracle website. A setting of 8 permits most password versions and also allows any combination of the DBA_USERS.PASSWORD_VERSIONS values 10G, 11G, and12C.

The parameter sqlnetora.sqlnet.allowed_logon_version_client denotes the minimum authentication protocol that's used when the database acts as the client. For more information, see SQLNET.ALLOWED_LOGON_VERSION_CLIENT and Checking for accounts using case-insensitive password version on the Oracle website.

DBMS_JOB

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

FreeStorageSpace

Verify that your instance isn't close to its storage capacity. To confirm that you have enough free storage space available for the upgrade, check 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 whether your instance has pending maintenance actions in the Amazon RDS console. These pending maintenance 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

It's a best practice to create a manual snapshot of your RDS for Oracle database instance:

• You can use the snapshot to roll back to the previous version, as long as the version is supported by Amazon RDS.
• A major version upgrade usually creates an automated snapshot. Because Amazon Elastic Block Store (Amazon EBS) snapshots are incremental, the new snapshot has fewer changes to back up. Create a manual snapshot to reduce the time required to create the automated snapshot and the total time required to complete the upgrade.

Current configuration

To view the current configuration of your instance and save the output, run the following command. The output shows you information about the option group, parameter group, security groups, and tags that are attached to the current RDS for Oracle instance. To roll back and perform a restore from a snapshot or perform a point-in-time recovery, use the information that you retrieve from the following 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 command 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 that 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 whether there are any logon, logoff, or startup triggers that throw an error when they run.

To deactivate these triggers, run the following commands:

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

Be sure to replace the following values in the command:

• 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 start the upgrade, monitor the progress of the upgrade and check the following in the Amazon RDS console:

• The alert log that is located under the Logs & events tab of your instance
• Recent events that are located under the Logs & events tab of your instance

After the upgrade

1. To verify the patch version after you connect to the database, run the following commands:

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

2. Amazon RDS updates the lsinventory-dbv.txt files within an hour of applying the patch. Download this file from the Logs & events tab of your instance in the Amazon RDS console. To verify the applied patches, read the Isinventory-dbv.txt file.

3. To gather dictionary and fixed object statistics, run the following commands:

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

4. To verify that the number of objects that are valid or not valid matches the number of objects before the upgrade, run the following command:

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

5. To compile all the objects in the schema that are not valid, run the following command:

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

6. If you have command performance issues after the upgrade because the optimizer features of the new major version, you might want to use the OPTIMIZER_FEATURES_ENABLE parameter. For more information, see OPTIMIZER_FEATURES_ENABLE on the Oracle website. You can alter this parameter at the session level and system level. For example, suppose you upgrade your database from release 18.1 to release 19.1 but you want to keep the release 18.1 optimizer behavior. To do this, set the value of the OPTIMIZER_FEATURES_ENABLE parameter to 18.1.0..

Related information

Upgrading the RDS for Oracle DB engine