Amazon Relational Database Service (Amazon RDS) for Oracle provides new engine versions of databases and Oracle Critical Patch Updates for existing versions of databases, so you can keep your database instances up to date. These versions include bug fixes, security updates, and other enhancements.
In this post, we show you how to perform an upgrade of Amazon RDS for Oracle with minimal downtime using Oracle GoldenGate. You can use this solution for Amazon RDS for Oracle major version upgrades or minor version upgrades with Oracle Patch Set Update (PSU) or Release Update (RU) and major database reorganization on Amazon RDS for Oracle with minimal impact on the primary database.
Understanding Amazon RDS major and minor version upgrades
Amazon RDS for Oracle supports the following upgrades to an Oracle DB instance:
Major version upgrades – A major version upgrade for a database engine can introduce changes that aren’t compatible with existing applications. To upgrade your DB instance to a major version, you must perform the action manually. For this post, we demonstrate upgrading Oracle DB version 12.1.0.2 to 19.0.0.0. For more information, visit Oracle database engine release notes.
Minor version upgrades – A minor version upgrade includes only changes that are backward-compatible with existing applications. Upgrading the minor version of an RDS for Oracle database applies additional fixes and new features to an existing database. If you enable auto minor version upgrades on your DB instance, the minor version upgrade occurs automatically. In all other cases, you upgrade the DB instance manually. For this post, we demonstrate upgrading Oracle 12.1.0.2.v24 to 12.1.0.2.v25.
The duration of the outage from the upgrade depends on your engine version and instance size. Amazon RDS for Oracle provides an option to manually initiate a major version upgrade by modifying your DB instance. This is known as an in-place upgrade and requires downtime for your applications during the upgrade process. Additionally, you must restore the latest backup in the event of any issues with the upgrade.
Alternatively, you can use logical replication with either AWS Database Migration Service (AWS DMS) or Oracle GoldenGate. This method is known as an out-of-place upgrade and provides the least downtime for the DB engine version upgrade. For information about upgrading using AWS DMS, see Upgrading Amazon RDS for Oracle database engine with minimal downtime using AWS DMS.
Oracle GoldenGate
Oracle GoldenGate is a tool for real-time change data capture (CDC) and replication, and supports replicating data between Oracle database versions. For more information, visit Overview of the Oracle GoldenGate Architecture. Oracle GoldenGate creates trail files that contain the most recently changed data from the source database, and pushes these files to the destination database. You can use Oracle GoldenGate to perform minimal downtime data migration or nearly continuous data replication. Amazon RDS for Oracle is compatible with Oracle GoldenGate, and Oracle GoldenGate is a licensed software from Oracle.
Blue/green deployment
Blue/green deployment requires two identical environments. The blue environment represents the current database version serving production traffic, and the upgrade is performed on the green environment to the new database version without impacting the blue environment. The bi-directional replication between the blue and green environments allows you to perform database cutover or failback, if needed, with minimum downtime. When testing is complete on the green environment, live application traffic is directed to the green environment and the blue environment is deprecated. The desired change is applied on the green database environment only.
To avoid possible data inconsistency and a split-brain situation, never write to both blue and green environments at the same time. Replication doesn’t automatically recover the data changes, and it may be difficult to recover manually.
You can use blue/green deployment for database change management processes such as major and minor version upgrades, application changes, schema changes like creating new indexes or modifying indexes on a very large table, and data reorganization. The primary benefit of blue/green deployment is the ability to perform these maintenance activities with minimal downtime, while having the capability to fail back to the blue environment in case the upgrade doesn’t go well.
Solution overview
This post shows how you can perform a major and minor version upgrade of the RDS for Oracle database with minimal downtime using a blue/green deployment. The blue environment represents the current RDS for Oracle database version serving production traffic. We use an Amazon Elastic Compute Cloud (Amazon EC2) instance as the Oracle GoldenGate hub and start the Oracle GoldenGate Extract process for the blue environment.
The RDS for Oracle replica instance in the blue environment is promoted to a new standalone database when there is no replication lag. We perform an in-place major or minor version upgrade of the new standalone RDS for Oracle database in the green environment by modifying the database, and start the replication using Oracle GoldenGate from the blue to green environment to continuously replicate the data changes.
Make sure all the changes are replicated from the blue to the green environment. After the testing is complete on the green environment, application traffic is routed from the blue to the green environment.
The following diagram depicts the order of events, showing the steps to implement the solution.
Promote the replica of the RDS for Oracle database in the blue environment to the new standalone database in the green environment.
Perform an in-place upgrade of the new standalone RDS for Oracle database in the green environment from database version1 to version2.
Set up replication using Oracle GoldenGate on the EC2 hub instance in the blue/green environment to replicate data changes.
To implement this solution, you complete the following high-level steps:
Configure Oracle GoldenGate on an Amazon RDS for Oracle database in the blue environment.
Configure Oracle GoldenGate on Amazon EC2 for the Amazon RDS for Oracle database in the blue environment.
Start the Oracle GoldenGate Extract process in the blue environment.
Promote the replica of the database in the blue environment.
Upgrade the newly promoted database in the green environment.
Configure Oracle GoldenGate on an Amazon RDS for Oracle database in the green environment.
Configure Oracle GoldenGate on Amazon EC2 for the database in the green environment.
Start the Oracle GoldenGate Replicat process for the green environment.
Test the DDL and DML replication setup.
Cut over from the blue to green environment.
Prerequisites
Before you get started, make sure you complete the following prerequisites:
Create an RDS for Oracle database with a replica in the same AWS Region as the primary DB instance. For more information, check the following AWS CloudFormation sample template and Working with Oracle replicas for Amazon RDS. For this post, we create an RDS for Oracle database with a replica on version 12.1.0.2.
Install and configure the Oracle GoldenGate hub on your EC2 instance (for this post, Oracle GoldenGate 19.1.0.0.0+ release version). For more information, see Oracle GoldenGate Certification Matrix 19c (19.1.*).
Configure Oracle GoldenGate on an Amazon RDS for Oracle database in the blue environment
Connect to the RDS for Oracle database in the blue environment as an Amazon RDS primary user (for this post, the user is admin).
We start by enabling the Oracle GoldenGate replication parameter on the source RDS for Oracle database.
Create a new custom database parameter group or use an existing one with enable_goldengate_replication set to true in the source database:
Enable supplemental logging and increase the archive log retention on the source database with the following code:
Check that supplemental logging is enabled on the source database with the following code:
Create the Oracle GoldenGate replication user (for more information, visit Using Oracle GoldenGate with Amazon RDS):
Grant the necessary account privileges to the GoldenGate user (for this post, the user is oggadmin) in the blue environment.
A best practice is to grant least privileges for the Oracle GoldenGate user for the RDS for Oracle database. For more information, visit Granting the Appropriate User Privileges.
Configure Oracle GoldenGate on Amazon EC2 for the database in the blue environment
The next step is to create an Oracle GoldenGate hub instance on Amazon EC2. Connect to Oracle GoldenGate on Amazon EC2 using the GGSCI utility and create subdirectories. For more information, visit Implement Oracle GoldenGate high availability in the AWS Cloud.
On the EC2 instance running the Oracle GoldenGate setup, add TNS entries for the source in the blue environment and target in the green environment for the RDS for Oracle database. For example, see the following code:
Configure an Oracle GoldenGate credential store to maintain encrypted database passwords and user IDs:
Create an Oracle GoldenGate checkpoint table using the following code:
In this post, we replicate the schema SOE from the source RDS for Oracle database in the blue environment to the green environment.
ADD SCHEMATRANDATA acts on all current and future tables in a given schema to automatically enable schema-level supplemental logging. ADD TRANDATA for Oracle enables the unconditional logging of the primary key and the conditional supplemental logging of all unique keys and foreign keys of the specified table.
To use the Oracle GoldenGate DDL replication feature, use the ADD SCHEMATRANDATA command to log the required supplemental data:
Start the Oracle GoldenGate Extract process in the blue environment
We configure the source data capture using Integrated Extract for the RDS for Oracle database in the blue environment to extract the changes on the RDS for Oracle DB instance in the blue environment. Start the Extract process using the following code:
Promote the replica of the database in the blue environment
Wait until the replication lag between your primary RDS for Oracle DB instance and its replica in the blue environment is zero, then create a green environment by promoting the replica of the RDS for Oracle database to a new standalone database. You can also restore a standalone RDS for Oracle DB instance in the green environment from a snapshot of the database in the blue environment. This post demonstrates the former method (promoting a replica).
Alternatively, you can promote a mounted replica, which doesn’t use Active Data Guard. For more information about read-only and mounted replicas, see Working with Oracle replicas for Amazon RDS.
To promote the replica to the new RDS for Oracle database, use the following AWS Command Line Interface (AWS CLI) command:
The DB instance promotion process takes a few minutes. You can verify the status of the newly promoted database in the green environment changes to Available. For more information, see Viewing DB instance status. The RDS for Oracle database in the blue and green environments are on same DB version in this step (for this post, 12.1.0.2).
Note down the Oracle Sequence Change Number (SCN) when the standby becomes the primary from the alert log of the newly promoted DB instance. We use this SCN to start the replication from the blue to green environment.
For example, the following is the snippet from the alert log of when the newly promoted RDS for Oracle database and standby became primary (SCN number is 537939). The Oracle GoldenGate replication from the blue to green environment should be started from SCN number 537939 (your SCN number will be different!).
Upgrade the newly promoted database in the green environment
Before you upgrade the newly promoted RDS for Oracle DB instance, we recommend reviewing the Oracle documentation for information about behavior changes and deprecated and desupported features, and also referring to Upgrading the Oracle DB engine for other considerations.
In this step, you can choose to perform either a major version upgrade or minor version upgrade of the RDS for Oracle database in the green environment. To upgrade the instance in the green environment, you modify the instance via one of the following methods:
The Amazon RDS Console (see Console)
The AWS CLI (see AWS CLI)
The Amazon RDS API (see RDS API)
The following code is an example of an Amazon RDS for Oracle in-place major version upgrade in the green environment from Oracle database version 12.1.0.2 to 19.0.0.0 using the AWS CLI modify-db-instance command:
The following code is an example of an Amazon RDS for Oracle in-place minor version upgrade in the green environment from Oracle database version 12.1.0.2.v24 to 12.1.0.2.v25 using the AWS CLI modify-db-instance command:
When the upgrade process is complete, verify that the status of the standalone RDS for Oracle database in the green environment is in an Available state.
Configure Oracle GoldenGate on an RDS for Oracle database in the green environment
Connect to the target RDS for Oracle database as an Amazon RDS primary user (for this post, the user is admin).
Verify the Oracle GoldenGate replication parameter is set to true on the RDS for Oracle database in the green environment using the following code:
Verify the Oracle GoldenGate replication user created on the RDS for Oracle database and grant the necessary privileges to the GoldenGate user (for this post, the user is oggadmin) in the green environment.
Configure Oracle GoldenGate on Amazon EC2 for the database in the green environment
Configure Oracle GoldenGate on Amazon EC2 for the RDS for Oracle database in the green environment using the following code:
Start the replication process
Start the replication using Oracle GoldenGate from the SCN number you noted from the alert log (537939 in our case):
Connect to the RDS for Oracle database in the green environment to check the status of the Oracle GoldenGate replication:
Test the DDL and DML replication setup
To test the replication setup, connect to the RDS for Oracle database in the blue environment and create sample data in the SOE schema using the following code:
Connect to the RDS for Oracle database in the green environment to verify the replication using the following code:
Cut over from the blue environment to the green environment
After the testing is complete from both a functional and nonfunctional standpoint, you’re ready to point your applications to the new major application version in the green environment. This post provides the following steps as a reference; you should add additional steps as required to tailor the solution to your environment.
Complete the steps in the following order:
Make sure that all users and roles are set up with appropriate permissions in the green environment.
Verify the green environment is configured with the correct instance type, custom DB parameters groups, and security groups.
This is the point at which your downtime starts.
Check for any long-running transactions in the blue environment to stop or delete.
Stop all applications that write to the blue environment. To stop the application writes on the RDS for Oracle database, complete the following steps:
Stop all the applications and make sure all the write activities are stopped on the database and there is no application database connection.
If you can’t easily stop application writes, you can work around it by blocking application access to the database. For example, modify security groups to block access to the database, and revoke write access to users on the database.
Verify the data integrity, database object counts match (such as indexes and functions), and row counts match in the blue and green environment.
When the data is in sync with zero replication lag between the blue and green environment, stop the Oracle GoldenGate Replicat process using the following code:
Manually sync the sequences on the RDS for Oracle database in the blue and green environments (you can also automate this process using scripts).
Modify your application to point to the green environment and start the application to route production traffic to the green environment.
This is the point at which your downtime stops.
Conduct necessary tests to verify that the application is working correctly.
After the switchover to the green environment, you can use the blue environment to perform post-cutover tasks like roll back to a previous database version if needed, or delete it to minimize costs.
Rollback plan
When migrating a database to a new version, it’s prudent to have a fallback strategy if the new system doesn’t work as expected. Make sure you have defined a success criteria for cutover along with a rollback plan before enabling application writes on the green environment. The rollback plan should include setting up Oracle GoldenGate replication from the old green (new blue) and old blue (new green) environment. Note down the Oracle SCN of the old green environment to start replication of data changes in the blue/green environment. For more information on setting up Oracle GoldenGate replication with an RDS for Oracle database, see Implement Oracle GoldenGate high availability in the AWS Cloud.
To avoid data inconsistency issues, never perform writes to both the blue and green environment at the same time. During the cutover, we start replication from the old green to blue environment for failback, before routing application write traffic to the green environment.
Clean up
To clean up the Oracle GoldenGate installation, see Uninstalling Oracle GoldenGate with Oracle Database. To delete the RDS for Oracle database, see Deleting a DB instance. Finally, terminate your Amazon EC2 instance.
Conclusion
In this post, I showed you how to upgrade your RDS for Oracle database version using Oracle GoldenGate with a blue/green deployment strategy with replicas to achieve minimal downtime. You can use this method for major version upgrades, minor version upgrades, major database reorganization, and schema changes on Amazon RDS for Oracle with minimal impact on the primary database.
We welcome your feedback. Share your experience and any questions in the comments.
About the Author
Nethravathi Muddarajaiah is a Database Specialist Solutions Architect. She works with AWS customers to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.
Read MoreAWS Database Blog