Best practices for maintenance activities in Amazon RDS for Oracle

Customers prefer to host their Oracle database workloads in a managed service such as Amazon Relational Database Service (Amazon RDS) for Oracle because of the benefits offered by managed services. One of the major benefits that customers like about Amazon RDS for Oracle is the ease of performing maintenance activities for their databases. Amazon RDS for Oracle automates most of the maintenance activities, such as operating system (OS) maintenance, database upgrades, scaling of database storage, time zone file upgrades, and more. It saves lot of time and resources spent on such activities that don’t directly add value to your business.

As in the case of maintenance activities for on-premises Oracle databases, most of these maintenance activities need proper planning and testing in Amazon RDS as well, to provide smooth operation of your business. The Amazon RDS for Oracle User Guide provides comprehensive coverage of the maintenance activities in Amazon RDS for Oracle. However, it could be cumbersome to quickly learn about the best practices around various maintenance activities in Amazon RDS for Oracle from the user guide. In this post, we describe the key maintenance activities and the best practices to be followed for each of them.

Maintenance windows

Amazon RDS performs maintenance on Amazon RDS resources to fix issues related to security and instance reliability. The Amazon RDS maintenance window is your opportunity to control when DB instance modifications, database engine version upgrades, and software patching occurs, in the event they are requested or required. During the maintenance window, Amazon RDS applies updates related to hardware, underlying OS, or database engine minor version. Some of these maintenance operations, such as OS updates and database patching, cause downtime on your RDS instance. It is usually recommended that you define your RDS maintenance windows to cover a lean period in your business to minimize the impact of maintenance activities on your applications. Customers are notified about scheduled maintenance activities through various AWS channels, encouraging proactive planning and minimal surprises. The OS updates, primarily driven by security considerations, underscore the importance of promptly keeping systems up to date.

If a maintenance event is scheduled for a given week, it’s initiated during the 30-minute maintenance window you identify. Most maintenance events also complete during the 30-minute maintenance window, although larger maintenance events may take more than 30 minutes to complete.

There might be a requirement to change the current maintenance window or defer maintenance actions to minimize the impact to your application. Changing the maintenance window for an RDS instance doesn’t require downtime. However, if there are one or more pending actions that cause downtime, and the maintenance window is changed to include the current time, then the pending actions are applied immediately, resulting in downtime. You can’t defer a maintenance action that has already started. However, you can defer a maintenance action that’s scheduled for the next maintenance window.

Also, it is important to understand the difference between pending modifications and pending maintenance. Pending modifications refer to the changes that are deferred to the next maintenance window, requested manually using modify-db-instance API or from the console without –apply-immediately option. Alternatively, a pending maintenance  refers to a maintenance activity that is suggested by RDS automation (not manually initiated by a modify-db-instance API call).

For information about pending maintenance events for your RDS DB instances, check the Events page on the Amazon RDS console. Then, check for engine-specific maintenance events. You can use the AWS Command Line Interface (AWS CLI) to run describe-pending-maintenance-actions or the Amazon RDS API for DescribeDBInstances. For more information, see Viewing pending maintenance updates.

Infrastructure Maintenance

AWS regularly performs routine maintenance with minimal disruption to RDS instances. Scheduled events are AWS initiated stops and reboots for planned maintenance. In some cases, scheduled events may be needed when we detect a hardware degradation. In other cases, scheduled events may be needed to retire older hardware. In rare cases, you may experience a network maintenance event. Before Amazon RDS schedules maintenance, you receive an email notification about the scheduled maintenance windows. This includes the time of the maintenance and the availability zones (AZs) that are affected. If you have been notified that there is a hardware maintenance, it is always advisable to schedule a restart of the instance as soon as possible during the maintenance window. The purpose is to prevent a planned maintenance from turning into an unplanned downtime. During hardware maintenance, Single-AZ deployments are unavailable for a few minutes. For Multi-AZ deployments with an affected AZ, your instance is unavailable for the time it takes to fail over to the secondary AZ, usually 60–120 seconds. If maintenance affects only the secondary AZ, then there’s no failover or downtime.

If the maintenance is scheduled for the primary instance in a multi-AZ instance, then you can perform a manual failover. The primary instance becomes the standby instance and then that instance will undergo maintenance on the scheduled date. This way, the primary instance won’t go down for maintenance. To see the RDS DB instances that are scheduled to receive hardware maintenance during your maintenance window, review the DB instances that are listed on the Open and recent issues tab on your AWS Health Dashboard. For more information, see the maintenance notification email that’s sent to your account.

Scaling the instance class

You can modify the RDS instance to change its instance class or size to accommodate dynamic resource requirements of your workload. Most modifications to a DB instance can be applied either immediately or defer until the next maintenance window. Changing the DB instance class incurs downtime. The downtime required for this change is minimal when scaling RDS instances in a Multi-AZ environment because the standby instance gets scaled first, then a failover occurs to the newly sized database followed by scaling of the former primary instance. Failover time usually takes about 60–120 seconds. It’s important to consider that a multi-AZ instance will be running in a different AZ (compared to where it was running when the modification request was initiated) at the end of the scaling activity. If your workload has any AZ affinity and it must run in the original AZ, you may need to do a manual failover at the end of the scaling activity.

A Single-AZ RDS instance is unavailable during the scaling operation, which takes approximately 10 minutes to complete.

Default Oracle memory-related parameters are automatically taken care of by Amazon RDS during the scaling activities; however, for custom-defined memory allocations, it is advised that those settings be reviewed after the scaling operation. For instance, if you have a parameter setting (such as db_16k_cache_size) that is conflicting with the resources available in the target instance class, the instance can go to incompatible-parameters status during the scaling activity.

Amazon RDS read replicas provide enhanced performance and durability for RDS DB instances. They make it straightforward to elastically scale out beyond the capacity constraints of a single DB instance for read-heavy database workloads. You can scale read replicas independent of the primary RDS instance. Refer to Scaling Your Amazon RDS Instance Vertically and Horizontally for more details.

Scaling storage

With Amazon RDS, storage and compute layers are decoupled. Scaling storage usually doesn’t cause any outage or performance degradation of the DB instance. If your workload is predictable, you can separately modify your DB instance to increase the allocated storage space or improve performance by changing the storage type (such as to General Purpose SSD to Provisioned IOPS SSD).

You can modify the storage settings by using the AWS Management Console, AWS CLI, or Amazon RDS API. Specify the storage type, allocated storage, and the amount of Provisioned IOPS that you require. The range depends on the storage type and instance class. The effective throughput and IOPs supported by your deployment depend on the Amazon Elastic Block Store (Amazon EBS) bandwidth offered by your instance class and your Amazon EBS configuration. Make sure your instance class supports the intended I/O metrics when you are adjusting the configurations at the EBS layer. Although you can reduce the amount of IOPS provisioned for your instance, you can’t shrink the allocated storage.

In most cases, scaling storage doesn’t require an outage and doesn’t degrade performance of the instance. However, this operation might take several hours. You can’t make further storage modifications for either 6 hours or until storage optimization completes on the instance, whichever is longer. But you can perform other instance modifications, such as instance size scaling or rebooting. Your application availability isn’t affected during the storage scaling activity. Amazon RDS offers a progress indicator for improved visibility into the storage optimization process. With the progress indicator, you get better visibility into the progress of storage optimization process, such as scaling up of storage sizes and changing the storage volumes.

Amazon RDS for Oracle supports storage auto scaling, which automatically scales storage capacity in response to growing database workloads, with zero downtime. With storage auto scaling, you simply set your desired maximum storage limit, and auto scaling takes care of the rest. Storage auto scaling continuously monitors actual storage consumption, and scales capacity up automatically when actual utilization approaches provisioned storage capacity. The storage auto scaling feature is targeted for addressing organic growth of your database, in which case the database doesn’t need to scale storage again within 6 hours (or until storage optimization is complete) after a storage scaling activity. In case of bulk data load, such as in the case of initial data migration, it is recommended to allocate required storage manually by modifying the storage configuration instead of relying on the auto scaling feature.

In case of RDS for Oracle replicas, which are initially created from a snapshot of the primary instance, it’s a good practice to scale the storage in the replica matching the primary configuration. Storage scaling in replicas happens independently of the primary instance and storage shortage in the replica can lead to replication lag.

If your workload has a dependency on statistics gathered by the DBMS_STATS.GATHER_SYSTEM_STATS package, you may need to reconsider re-gathering those stats after major changes that alter the performance characteristics of the instance (for example, moving to io2 Block Express storage or moving to instances that support faster clock speed).

Database upgrades

A database upgrade transforms an existing Oracle Database environment into a new release of Oracle Database. The upgrade can be either a major or minor version upgrade.

The following figure depicts the interpretation of Oracle release numbers.

Oracle provides quarterly updates in the form of Release Updates (RUs). Applying an RU in Amazon RDS for Oracle is referred to as a minor version upgrade. In minor version upgrades, the changes are backward-compatible with an existing application and typically don’t impact existing functionality.

In contrast, major version upgrades involve changing the Oracle version, such as Oracle 19c to Oracle 21c, which introduces several new features, enhances existing features, or deprecates certain functionality. Therefore, it’s vital to perform thorough testing to verify application functionality and performance with a new major version.

For more information, refer to the release notes for Amazon RDS for Oracle.

Database minor version upgrades

Amazon RDS for Oracle automates patching of instances to provide security and stability. The patching encompasses applying operating system updates, such as security fixes, and Oracle’s quarterly RUs. The Amazon RDS for Oracle release notes provide details about the Oracle Database versions that are supported by Amazon RDS. Minor versions are typically released by Oracle every quarter. Minor versions contain minor feature enhancements and bug fixes. Examples of minor versions are 21.0.0.0.ru-2024-01.rur-2024-01.r1 and 19.0.0.0.ru-2024-01.rur-2024-01.r1.

Amazon RDS for Oracle doesn’t enforce minor version upgrades except when Auto Minor Version Upgrade (AmVU) is enabled or certain Oracle options like Oracle Spatial, Oracle JVM, Oracle Multimedia, and Oracle Locator are used. Therefore, you have the option to upgrade your database engine to a minor version either manually or automatically using the automatic version upgrade. For manual upgrades, refer to Manually upgrading the engine version. To set up automatic upgrades, refer to Automatically upgrading the minor engine version. Regardless of your chosen method, manual or automatic, a minor version upgrade will incur downtime.

The following are considerations and best practices for minor version upgrades:

• A minor version upgrade introduces changes that are backward-compatible with existing applications.
• Test the minor version update in a snapshot restore copy of production to determine the required downtime for the upgrade and validate application functionality and performance.
• Although during the database upgrade, the automation takes a pre-upgrade backup, having a manual backup before starting the database upgrade can shorten the total backup time of pre-upgrade backup and overall activity time. This will make sure only delta changes need to be backed up when automated backup happens during the database upgrade.
• The source DB instance and its read replicas must share the same Oracle Database engine version. Amazon RDS conducts upgrades in two stages:
  • First, the primary DB instance is upgraded while read replicas get disconnected but stay available in the older version.
  • When the primary database upgrade is complete, read replicas are upgraded simultaneously while the source DB remains accessible.
• The upgrade activity involves various phases and the instance remains accessible most of the time. RDS automatically blocks the connectivity to the instance when the database needs to go down for the upgrade activity. You can enable event notifications to track the progress of the upgrade process. You can subscribe to the Amazon RDS event RDS-EVENT-0268 emitted by the upgrade process to release the database to applications and end-users rather than waiting for the instance to become available, which can take a longer time.
• Refer to Time Zone upgrade for more details on how to handle upgrading time zone files in Amazon RDS for Oracle.

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

1. During the minor version upgrade, Amazon RDS takes two DB snapshots during the upgrade process (one before and one after), but only if the backup retention period is more than zero. In Amazon RDS, you can’t roll back the engine upgrade. Instead, you can use the snapshot taken before the upgrade and restore it to roll back to the previous engine version if needed.
2. The pre-check process begins, which checks for issues such as user-defined triggers that can prevent login from Amazon RDS automation and space availability in the sysaux and system tablespaces that can cause the upgrade fail. If checks fail at this stage, the engine upgrade is not initiated, and the instance continues to remain available. Refer to the Log & events tab for details of any failures during the pre-check.
3. When the pre-check is completed successfully, the process shuts down the instance and prepares it for the upgrade. Downtime of the instance starts from this phase.
4. The engine version upgrade activity gets invoked, which runs Oracle upgrade scripts.
5. When the upgrade process is complete, the Log & events tab shows “The engine version upgrade finished.” Amazon RDS for Oracle introduced new events in the maintenance category to provide more visibility during engine version upgrades. Track engine version upgrades in Amazon RDS for Oracle using the customer visible events
6. A post-upgrade backup is initiated and the instance comes back to an available state when the backup is complete.

The following diagram shows the various phases involved in a database upgrade activity.

Planning minor version upgrades in Amazon RDS for Oracle

You may choose to apply a patch manually or rely on Auto Minor Version Upgrade (AmVU). Upgrades to minor versions are voluntary, with the exception of AmVU-enabled instances. When Oracle publishes RUs, it usually takes Amazon RDS for Oracle 2–5 weeks to make them accessible for the instance.

The RUs will be released to different Regions in waves and RDS for Oracle Release Notes will be updated once the RU is released to all Regions. You can run describe-db-engine-versions APIs manually or automatically to get notified as soon as the RU is available in your Region of interest.

You can get early notification by building an automation using AWS Lambda and Amazon Simple Notification Service (Amazon SNS) based on the output of the aws rds describe-db-engine-versions –engine oracle-ee –output table command or using the RSS feed on the release notes page.

To get more control of the minor version update for your instances, you can access these early notifications through event notifications and listed events to prepare and test the changes in lower environments.

The event RDS-EVENT-0155 comes into play only when Amazon RDS schedules the minor version to be applied on to a DB instance (with AmVU). It’s not mandatory for AmVU-enabled instances to be patched automatically by Amazon RDS. Instead, it’s recommended to take advantage of early warnings and finish all testing and change management procedures and manually complete the upgrade before AmVU impacts your instances.

You will be informed at least 3 weeks in advance of the AmVU schedule for instances with AmVU enabled. Notifications are sent to the email address linked to the account as well as on the console. Amazon RDS will automatically apply the RU on AmVU-enabled instances during the next maintenance window after the date mentioned in the schedule. If this schedule conflicts with other planned activities on your instance, raise a support case with AWS as soon as you get notified of the AmVU schedule.

Operating system patching

Amazon RDS for Oracle manages OS patching as part of its managed experience. These updates, which are vital for enhancing and fortifying the security of the OS, are typically applied during the maintenance window to minimize disruption.

Although OS maintenance requires downtime, OS updates are specific to the database engine version and DB instance class. Therefore, instances in your fleet may get scheduled for OS patching at different times. Automatic scheduling prioritizes patches that are critical for security and instance reliability, occurring only once every few months and consuming only a fraction of the maintenance window. It can also be viewed by running the AWS CLI describe-pending-maintenance-actions command or by calling the Amazon RDS DescribePendingMaintenanceActions API operation.

OS updates can be either optional or mandatory:

• An optional update can be applied at any time. Although these updates are optional, we recommend that you apply them periodically to keep your Amazon RDS fleet up to date. Amazon RDS does not apply these updates automatically.
• A mandatory update is required and has an apply date. Plan to apply those OS updates before this apply date. After the specified apply date, Amazon RDS automatically applies the OS update to your instance in the maintenance window.

For RDS Single-AZ instances, OS patching directly impacts availability, given the absence of a failover mechanism. During the maintenance window, the system undergoes patching, rendering it temporarily unavailable, which can take a few minutes.

For optimal uptime, Multi-AZ instances adopt a strategic approach to OS patching. Initially, the standby instance undergoes patching, followed by the primary instance. The downtime required includes the duration of a failover completion, typically 60–120 seconds. For more information, see Multi-AZ deployments.

In Multi-AZ setups, backups stem from the standby instance to minimize the impact to the primary. During OS patching, a failover occurs, swapping primary and standby roles, which makes the next backup happen from the new standby instance.

Further planned maintenance activities, like version upgrades that invoke a full backup, can potentially take longer to complete due to the increased backup time.

For applications that are sensitive to cross-AZ latency, manual intervention aligns the database and application layers within the same AZ during automatic failovers. Optimal efficiency entails running a re-failover for incremental backups from the subsequent standby, minimizing disruption.

To stay informed about newly released optional OS patches, you can subscribe to the RDS-EVENT-0230 event in the security patching event category. For comprehensive guidance on subscribing to Amazon RDS events, refer to Subscribing to Amazon RDS event notification.

If your instance has both an Amazon RDS minor version upgrade and OS patching in the list of pending maintenance activities, you should only apply the database minor version upgrade because it will automatically combine the OS update through an optimized workflow to reduce the outage required, as discussed in the next section.

Combined OS and DB patching

Upgrades to the database engine level require downtime. Even if your RDS DB instance uses a Multi-AZ deployment, both the primary and Multi-AZ standby DB instances upgrade at the same time. This causes downtime until the upgrade is complete, and the duration of the downtime varies based on various factors, such as instance and storage configuration, number of database objects, and database options. Similarly, OS patching also incurs downtime, though it’s much shorter compared to database patching. In some cases, your instance may have both OS patching and database minor version upgrades in the list of pending maintenance activities.

In the following example, the Amazon RDS console shows both the minor version and OS update are available for a Multi-AZ environment.

If you decide to start with the OS update before the database upgrade, the following happens:

• In a Multi-AZ configuration, Amazon RDS initiates the OS update on AZ2. After the OS update succeeds, AZ2 becomes the new primary as a result of a failover. The automation then updates the OS on the new standby in AZ1 (former primary).
• A pre-upgrade backup is required before initiating a database minor version upgrade. In a Multi-AZ environment, the backup is always taken from the standby database. Because AZ1, the new standby, was the former primary and had never been backed up, a full snapshot backup is invoked from the new standby in AZ1, which adds to the amount of time before the RDS instance upgrade begins.

To streamline operations and minimize the downtime for both of these maintenance activities, you can opt for combined OS and database upgrades.

The combined OS and database patching proves invaluable when both a database minor version upgrade and an OS update are available. Instead of applying them separately in two steps, which could prolong the downtime, they can be streamlined into a single automated workflow. To invoke this optimized workflow, you can choose to run the database minor version upgrade first, which will automatically combine both of the pending activities into a single workflow so you don’t have two separate downtimes and reduce the risk of extended activity time due to longer backup operations. The steps are as follows:

1. A backup is invoked from the current Multi-AZ standby DB instance.
2. The OS is updated on the current standby DB instance.
3. This initiates a failover, shifting the primary DB instance to the standby DB instance.
4. The database version is upgraded on the new primary DB instance (previously the standby).
5. The OS is updated on the new standby DB instance (previously the primary).
6. The database version is upgraded on the new standby DB instance.
7. A failover occurs, returning the original primary and standby instances to their respective roles. This restores the replication configuration to its initial state.

This optimized process also makes sure DB instances remain primary in the same AZ at the end of the activity, as they were when the maintenance was invoked.

Database major version upgrade

When Amazon RDS introduces support for a new major version of the Oracle database, Amazon RDS makes the upgrade straightforward with an API call or a few clicks on the Console. A major version upgrade typically brings new features, security fixes, optimizer enhancements, and performance improvements. It’s strongly recommended to conduct thorough testing of your application’s functionality, compatibility, and performance against the new Oracle version in non-production environments before proceeding with upgrading your production database.

When performing a major version upgrade of Oracle Database (for example, upgrading from 19c to 21c), it’s important to note that major version downgrades are not supported for RDS for Oracle database versions.

The following is a checklist for completing a major version upgrade:

• End-of-support date – Oracle periodically deprecates its versions, and Amazon RDS follows suit by aligning with Oracle’s deprecation schedule. As part of this, Amazon RDS establishes an end-of-support date for each major engine version, and customers will be notified well in advance, recommending them to upgrade to the next long-term supported major version. After this deadline, if you haven’t upgraded to a supported version, Amazon RDS will automatically perform the upgrade for you.
• Oracle Database releases – Review RDS for Oracle releases for feature or parameter deprecation between two major versions.
• Compatibility check – Make sure your current instance class is supported for the major version you are upgrading to. Refer to Supported RDS for Oracle DB instance classes for more details.
• Client compatibility – Verify that the Oracle client, driver, and SSL versions are compatible with the new major version. For SSL certificate upgrades, see Best practices for successful SSL connections to Amazon RDS for Oracle.
• Custom parameter group – Create a new parameter group for the new major version and set the custom parameters similar to the original custom parameters.
• Custom option group – Establish a new custom option group for the major version, including exiting necessary persistent and permanent options.
• Optimizer statistics – Gather dictionary statistics and fixed object statistics to minimize instance downtime during the upgrade.
• Audit trail management – Manage audit trails to minimize lengthy pre-upgrade checks and upgrades.
• Storage space – Confirm sufficient free storage space to prevent storage capacity issues during the upgrade.
• Maintenance actions – Check for pending maintenance actions on the Amazon RDS console before initiating the upgrade.
• Snapshot creation – When the upgrade starts, the automation will take a pre-upgrade backup. Creating a manual snapshot before starting the upgrade can expedite the backup because only delta changes are captured, which helps shorten the overall upgrade time.
• Upgrading with read replicas – The Oracle Database engine version of the primary DB instance and all its read replicas must be the same. Amazon RDS performs the upgrade in the stages. First, the primary DB instance is upgraded while read replicas get disconnected but stay available in the older version. When the primary database upgrade is complete, read replicas are upgraded simultaneously while the source DB remains accessible.
• Time zones – With each Oracle Database release, new Daylight Saving Time (DST) versions may be introduced. These updates reflect changes in transition rules across various time zone regions. Opting to upgrade an RDS for Oracle DB instance with the TIMEZONE and TIMEZONE_FILE_AUTOUPGRADE options enabled will extend downtime. If you’re not using the TIMEZONE option, make sure to exclude the TIMEZONE_FILE_AUTOUPGRADE option before starting the upgrade process. Alternatively, you can postpone the time zone file upgrade for a later downtime. Later in this post, we discuss how to upgrade time zone files in Amazon RDS for Oracle.
• Non-CDB to CDB conversion – Later in this post, we discuss various upgrade paths from a non-CDB architecture to CDB.

Instance class deprecation

To provide you with the latest instance classes that provide higher performance with the latest processors, operating at a higher clock speed at no additional cost, Amazon RDS deprecates old instance classes. If you’re running an RDS for Oracle instance that is reaching its end of life, you will be notified in advance—typically 6 months prior to the deadline—to upgrade your instance to one of the latest instance classes meeting your workload requirements, such as memory, vCPU, Oracle license footprint, and available Reserved Instances (RI) options. AWS sends an email to the email address that’s associated with your AWS account prior to the scheduled event. The email provides details about the event, including the start and end date.If there are instances still running in deprecated instance classes after the deadline, they will be forcefully upgraded to one of the new instance classes that best matches the instance class being deprecated during the configured maintenance window. For example, db.r4 instances upgrade to db.r5. In a Multi-AZ configuration, the upgrade uses the standby instance to reduce the downtime to complete a failover activity, which is typically under 2 minutes. In a Single-AZ configuration, the instance can remain unavailable for 10–15 minutes during the upgrade process. We recommend the following best practices for instance class deprecation:

• Start acting as soon as the deprecation notification is received to assess the existing RIs and identifying the best instance class to upgrade to.
• Choose the latest generation instance classes whenever possible. Refer to Amazon RDS for Oracle instance types for a list of current and latest generation instance classes for Amazon RDS for Oracle.
• Test the upgrade process in a snapshot restore copy of the production instance to assess the impact of the upgrade.
• Contact your AWS account team to discuss the reserved instances (RIs) migration process if you have RIs for the instance class being deprecated.

Time zone version upgrade

Oracle Database supplies multiple versions of time zone files to reflect the new rules of DST, which can be released independent of quarterly RUs. In Oracle Database, the TIMESTAMP WITH TIME ZONE data type stores time stamp and time zone data. If your database doesn’t contain any tables with TIMESTAMP WITH TIME ZONE, then you don’t have to worry about time zone versions and upgrades. Otherwise, you need to assess the impact of time zone version changes to your application and decide how to apply the latest time zone versions to your instances. Amazon RDS for Oracle provides TIMEZONE_FILE_AUTOUPGRADE as an option in the option group to allow instances to automatically upgrade to new time zone versions when available. Refer to Oracle time zone file autoupgrade for more information.

Each minor version provided by Amazon RDS for Oracle contains a specific DST version, which can be seen in the release notes for Amazon RDS for Oracle. Amazon RDS releases RU revisions if Oracle releases a new time zone version that needs to be incorporated to an existing RU. For example, version 19.0.0.0.ru-2023-01.rur-2023-01.r1 comes with DSTV40, whereas version 19.0.0.0.ru-2023-01.rur-2023-01.r2 comes with DSTV41. When you attach an option group that contains the TIMEZONE_FILE_AUTOUPGRADE option to your instance, the instance will automatically upgrade the time zone version of the instance when a new time zone file is available or when the instance undergoes minor version upgrades. A time zone version upgrade can be time-consuming and cause extended downtime for your instance based on various factors, such as:

• The amount of TIMESTAMP WITH TIME ZONE data in your database
• The DB instance configuration
• The DB instance class
• The storage configuration
• The database configuration
• The database parameter settings
• Number of objects in Recycle Bin

Follow these best practices regarding time zone version upgrades to reduce the impact to your instance:

• When you add the TIMEZONE_FILE_AUTOUPGRADE option to your option group, choose to add the option during the maintenance window. This is to avoid an immediate downtime in case your instance has newer time zone versions available. By choosing the immediate-apply option, the instance can experience unintended downtime.
• Always test the time zone upgrade on a snapshot restore copy of your production instance to assess the downtime requirements for the upgrade as well as to test the application functionality.
• If your database doesn’t have TIMESTAMP WITH TIME ZONE data types, do not attach TIMEZONE_FILE_AUTOUPGRADE to the option group used by that instance, because it will cause additional downtime during minor version upgrades.
• When your database contains time zone sensitive application data (for example, TIMESTAMP WITH TIME ZONE data types), you may still need to assess when to upgrade the time zone version. If your maintenance window is long enough to upgrade both the minor version and time zone version in the same maintenance, you can use the TIMEZONE_FILE_AUTOUPGRADE option enabled during the minor version upgrade. If that’s not feasible, you can complete the minor version upgrade without using TIMEZONE_FILE_AUTOUPGRADE and later enable that option to upgrade the time zone version in a later maintenance window. Refer to Strategies for updating your time zone file for various strategies to help meet your specific requirements.

Non-CDB to CDB conversion

Amazon RDS for Oracle supports three different architectures:

• Non-CDB architecture – A traditional architecture supported only until 19c.
• Single-tenant architecture – A multi-tenant architecture with only one tenant (PDB). For more details, see Single-tenant configuration of the CDB architecture.
• Multi-tenant architecture – A true multi-tenant architecture that supports more than one PDB. For more details, see Multi-tenant configuration of the CDB architecture.

Because Oracle doesn’t support non-CDB architecture from 21c onwards, you need to convert your existing instances from non-CDB to single-tenant or multi-tenant prior to the upgrade. This conversion to multi-tenant may also be needed if you’re planning to consolidate multiple databases as PDBs in a single RDS instance. The following table shows the supported conversions between the three architectures in RDS for Oracle.

The following table shows creation options, conversion options, and upgrade paths for various releases.

As shown in the table, there is no direct conversion possible from a non-CDB to multi-tenant architecture. If you have an instance that’s currently in a non-CDB architecture, you need to first convert it to a single-tenant architecture before it can be converted to a multi-tenant architecture.

During the first step, the instance changes to upgrading status and can take more than 30 minutes to return to an available state, which includes the downtime required for the database for this architecture conversion. Because the second step doesn’t involve changes on the database layer, it doesn’t cause a downtime. We recommend the following best practices for converting the instance from a non-CDB to multi-tenant or single-tenant architecture:

• Test the desired conversion or upgrade process in a copy of the production instance to assess the impact of this architecture change to your application as well as the downtime required.
• Refer to Working with CDBs in RDS for Oracle to learn the features supported and unsupported by single-tenant and multi-tenant architectures before you make the choice.
• The conversion process takes a snapshot backup of the instance, which can contribute to additional time to complete the activity. Taking a manual snapshot prior to the conversion can help reduce the overall activity time because the snapshot during the conversion process can finish faster.
• Depending on the features supported by the target architecture, you may need to disable certain features currently used by the instance. For example, you need to drop replicas before the conversion because a multi-tenant architecture doesn’t support replicas as of this writing. Refer to Limitations of RDS for Oracle CDBs to learn more about features supported by a CDB architecture.
• Conversion from a non-CDB architecture to a single-tenant architecture is a resource-intensive process. Make sure your instance class and storage configuration support the required resources for this conversion. You may scale up the instance for this activity and scale down to the original size once the maintenance is over.
• Pre-create custom option groups and parameter groups with the engine type as oracle-ee-cdb for the instance and choose those during the conversion process if the instance currently uses a custom parameter group or option group. Otherwise, you may experience errors or unintended results by choosing the default option groups and parameter groups during the conversion process.
• Gather dictionary stats prior to converting from a non-CDB to CDB architecture to make sure all DDLs run with optimal execution plans.

Conclusion

In this post, we discussed common maintenance activities in Amazon RDS for Oracle, highlighting various considerations and best practices to follow to provide maximum availability for your DB instance with the least impact on your application and no compromise on security.

Provide any feedback in the comments section.

About the Authors

Jobin Joseph is a Senior Database Specialist Solutions Architect based in Toronto. With a focus on relational database engines, he assists customers in migrating and modernizing their database workloads to AWS. He is an Oracle Certified Master with over 20 years of experience with Oracle databases.

Manish Bhasin is a Cloud Support Engineer II at Amazon Web Services. He is specialized in relational databases, especially Oracle database. Manish focuses on Amazon RDS, assisting customers with migrations, upgrades, and simplifying their cloud database journey.

Manash Kalita is a Senior Database Specialist Solutions Architect with Amazon Web Services. He works with AWS customers designing customer solutions on database projects, helping them migrate and modernize their existing databases to the AWS Cloud as well as orchestrate large-scale migrations in AWS.