AWS Database Blog

Upgrading Amazon RDS for Oracle database engine with minimal downtime using AWS DMS

Amazon Relational Database Service (Amazon RDS) for Oracle is a managed database service that makes it easier to set up, operate, and scale Oracle deployments on AWS. Amazon RDS for Oracle provides newer versions of Oracle Database so you can keep your DB instance up to date. Newer versions can include bug fixes, security enhancements, and other improvements to the database engine. Amazon RDS supports major and minor version upgrades. A minor version upgrade includes only changes that are backward-compatible with existing applications. A major version upgrade can introduce changes that aren’t compatible with existing applications, and you must perform the action manually. When Amazon RDS supports a new version of Oracle, you can upgrade your DB instance to the new version by modifying the DB instance via the AWS Management Console, AWS Command Line Interface (AWS CLI), or Amazon RDS API. For minor version upgrades, you can choose to enable automatic minor version upgrades.

When you upgrade an RDS for Oracle DB instance, this is an in-place upgrade and requires downtime for your applications during the upgrade process. The duration of the outage varies based on your engine version and the DB instance class. If the duration of the outage isn’t desirable for your application, an alternative approach is to use logical replication with AWS Database Migration Service (AWS DMS) to minimize downtime during the upgrade process. AWS DMS makes it easy to migrate data from a source data store to a target data store and replicate ongoing changes to keep sources and targets in sync.

This post provides an example and covers the steps to set up logical replication using AWS DMS to upgrade your RDS for Oracle DB instance with minimal downtime.

Considerations for version upgrade

Although Amazon RDS for Oracle provides the option to automate some of the patching decisions, the majority of patching decisions are up to you. Therefore, it’s critical to be aware of the implications and considerations for Oracle DB upgrades.

This post demonstrates how to use AWS DMS for logical replication during an upgrade. There are limitations for replicating data using Oracle as a source, including unsupported data types or data definition language (DDL) statements. If your applications use these features, you might still be able to use this approach if you can suspend these activities during the upgrade process.

Upgrading an Amazon RDS for Oracle DB instance with logical replication

The upgrade process using logical replication involves the following high-level steps:

  1. Promote a replica to a standalone, if available, or restore a standalone DB instance from a snapshot. This post demonstrates by promoting a read 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.
  2. Upgrade the new standalone DB instance to a newer version.
  3. Provision an AWS DMS instance and configure endpoints to the source and target DB instances.
  4. Create and start AWS DMS tasks to replicate ongoing changes starting from the restored system change number (SCN).
  5. Monitor and validate the ongoing replication.
  6. Test and cut over your applications.

The following diagram illustrates the upgrade process.

architecture diagram

Prerequisites

For this example, you should have the following prerequisites:

  • An AWS account with administrator IAM privileges.
  • Familiarity with the following AWS services:
  • Familiarity with SQL*Plus.
  • The sample CloudFormation template provisions an RDS for Oracle instance and read replica under the Bring Your Own License (BYOL) model. In the BYOL model, you can use your existing Oracle Database licenses to run Oracle deployments on Amazon RDS. You must have the appropriate Oracle Database license (with Software Update License and Support) for the DB instance class and Oracle Database edition you wish to run. You must also follow Oracle’s policies for licensing Oracle Database software in the cloud computing environment. For more information, see Oracle licensing options.

Set up the environment

To get started, set up your environment using the sample CloudFormation template.

  1. Download the CloudFormation template yaml from the GitHub repository.
  2. On the AWS CloudFormation console, create a stack with the CloudFormation template.
  3. For Stack name, enter a name.
  4. For DBUser, enter a username.
  5. For DBPassword, enter a password.
  6. Choose Next.
  7. On the Configure stack options page, choose Next.
  8. On the Review page, select I acknowledge that AWS CloudFormation might create IAM resources.
  9. Choose Create Stack.

The CloudFormation stack can take tens of minutes to finish creating resources. You can review the progress in the AWS CloudFormation console on the Events tab of the stack. When complete, the stack status displays as CREATE_COMPLETE. The stack deploys the following resources:

  • VPC
  • Internet gateway
  • Two private subnets
  • Two public subnets
  • Amazon Elastic Compute Cloud (Amazon EC2) instance bastion host with AWS CLI, SQL*Plus, and jq installed
  • RDS for Oracle DB instance and read replica using engine version 19.0.0.0.ru-2020-07.rur-2020-07.r1

You can modify the CloudFormation template if you need to integrate with existing resources in your environment.

Connect to the DB instance

We use AWS Systems Manager Session Manager to connect to the EC2 bastion host. For your convenience, the CloudFormation stack installs SQL*Plus on the EC2 bastion host, which you use to connect to the DB instance.

  1. On the Session Manager console, choose Start Session.
  2. For Target instances, select the instance that was created by your CloudFormation stack.
  3. Choose Start session.
  4. On the Session Manager terminal, switch to the ec2-user user and change the directory to the workshop directory using the following commands: 
    sudo su ec2-user
cd /workshop/

    For your convenience, the CloudFormation stack sets several environment variables on the bastion host as part of the creation process. You can find these values on the CloudFormation stack details page on the Outputs tab.

  5. Connect to your primary DB instance. Use the username and password you entered when you created the CloudFormation stack. 
    sqlplus <<username>>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$SRC_HOST)(PORT=$SRC_PORT))(CONNECT_DATA=(SID=$DB_NAME)))

Enable supplemental logging and write sample data

In AWS DMS, you can perform change data capture (CDC) for Oracle as a source using either Oracle LogMiner or AWS DMS Binary Reader. This post demonstrates how to configure Oracle for AWS DMS to use Oracle LogMiner for CDC. LogMiner and Binary Reader have different features and advantages. To understand more about the differences between the two methods, see Using an Oracle database as a source for AWS DMS.

  1. To capture ongoing changes, enable supplemental logging and increase the log retention period to 24 hours on your Oracle source database. Make sure that your storage has enough space for the archived redo logs during the specified retention period. 
    exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','PRIMARY KEY');
exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',24);

    The preceding commands add PRIMARY KEY supplemental logging on all replicated tables. If your tables don’t have a primary key, you may need to add supplemental logging to your tables on all columns or on a unique index’s columns. For more information, see Setting up supplemental logging.

  2. Create a table and write sample data to your Oracle source database: 
    CREATE TABLE purchase_orders (
	po_number NUMBER PRIMARY KEY,
	po_status VARCHAR2(50) NOT NULL,
	vendor_id NUMBER NOT NULL,    
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
);
 
INSERT INTO purchase_orders VALUES (200, 'new', 123, CURRENT_TIMESTAMP);
 
INSERT INTO purchase_orders VALUES (201, 'new', 321, CURRENT_TIMESTAMP);
  3. Exit your SQL*Plus session: 
    exit;

Verify the read replica (optional)

To verify that your sample data is written to the read replica, connect to the read replica DB instance.

  1. Connect to your read replica DB instance. Use the username and password you entered when you created the CloudFormation stack. 
    sqlplus <<username>>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$TGT_HOST)(PORT=$TGT_PORT))(CONNECT_DATA=(SID=$DB_NAME)))
  2. Query the table and sample data from your read replica: 
    SELECT * FROM purchase_orders;

    The query should return the sample data that was written to your primary DB instance.

  3. Exit your SQL*Plus session: 
    exit;

Query the system change number

This post demonstrates how to use AWS DMS to replicate ongoing changes from the source DB instance to the upgraded DB instance using the system change number (SCN). The SCN identifies the redo records for each committed transaction. To avoid potential data loss, suspend updates on the database before taking a snapshot or promoting the read replica so that it’s consistent to the SCN you query. For more information, see How to work with native CDC support in AWS DMS.

  1. Connect to the source DB instance. Use the username and password you entered when you created the CloudFormation stack. 
    sqlplus <<username>>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$SRC_HOST)(PORT=$SRC_PORT))(CONNECT_DATA=(SID=$DB_NAME)))
  2. Query the SCN: 
    SELECT CURRENT_SCN FROM V$DATABASE;

    The output of the query is in the form of a number.

  3. Record this number to use later for ongoing replication.
  4. Exit your SQL*Plus session: 
    exit;

Promote and upgrade the read replica

To upgrade the read replica, you need to first promote it to a standalone DB instance.

  1. Run the following command to promote the read replica to a standalone instance: 
    aws rds promote-read-replica --db-instance-identifier $TGT_RDS_ID --region $AWS_REGION
  2. Verify the status of the new standalone DB instance: 
    aws rds describe-db-instances --db-instance-identifier $TGT_RDS_ID --region $AWS_REGION --output table

    The promotion process can take several minutes. When the promotion process is complete, the DBInstanceStatus under the DBInstances section shows as available.

    Before upgrading the DB instance (the next step), we highly recommend you consult Oracle documentation for information about behavior changes and deprecated and desupported features.

  3. Upgrade the DB instance to a newer engine version: 
    aws rds modify-db-instance \
    --db-instance-identifier $TGT_RDS_ID \
    --engine-version 19.0.0.0.ru-2020-10.rur-2020-10.r1 \
    --apply-immediately \
    --region $AWS_REGION
  4. Verify the status of the upgraded DB instance: 
    aws rds describe-db-instances --db-instance-identifier $TGT_RDS_ID --region $AWS_REGION --output table

    The upgrade process can take tens of minutes. When the upgrade process is complete, the DBInstanceStatus under the DBInstances section shows as available.

Write more sample data

Connect and write additional sample data to the source DB instance. We use AWS DMS to replicate ongoing changes to keep the source and target DBs in sync.

  1. Connect to the source DB instance. Use the username and password you entered when you created the CloudFormation stack. 
    sqlplus <<username>>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$SRC_HOST)(PORT=$SRC_PORT))(CONNECT_DATA=(SID=$DB_NAME)))
  2. Write more sample data to your source database: 
    UPDATE purchase_orders SET po_status='completed' WHERE po_number=200;

INSERT INTO purchase_orders VALUES (202, 'new', 111, CURRENT_TIMESTAMP);

INSERT INTO purchase_orders VALUES (203, 'new', 222, CURRENT_TIMESTAMP);
  3. Exit your SQL*Plus session: 
    exit;

Create the IAM roles to use with AWS DMS

AWS Identity and Access Management (IAM) enables you to manage access to AWS services and resources securely. You must add two IAM roles to your AWS account before you can use the features of AWS DMS: dms-vpc-role and dms-cloudwatch-logs-role. If you previously used the AWS DMS console for your database migration, these roles were added to your AWS account automatically. For instructions about creating the required roles, see Creating the IAM roles to use with the AWS CLI and AWS DMS API.

Deploy an AWS DMS replication instance and endpoints

The CloudFormation template for deploying the AWS DMS replication instance is available in the GitHub repository.

  1. Clone the GitHub repository: 
    git clone https://github.com/aws-samples/amazon-rds-upgrade-blog.git
cd amazon-rds-upgrade-blog/templates
  2. Create the CloudFormation stack. Use the username and password you entered earlier when you deployed the template.yaml CloudFormation template. 
    # If your password has special characters, you may need to make modifications to prevent string interpolation
export DBUsername='<<username>>'
export DBPassword='<<password>>'

aws cloudformation create-stack --stack-name RdsUpgradeBlog \
    --region $AWS_REGION \
    --template-body file://dmsreplication.yaml \
    --parameters ParameterKey=RepAllocatedStorage,ParameterValue=100 ParameterKey=RepMultiAZ,ParameterValue=false ParameterKey=RepSecurityGroup,ParameterValue=$REP_SG ParameterKey=ReplInstanceType,ParameterValue=dms.r4.2xlarge ParameterKey=SrcDBUsername,ParameterValue=$DBUsername ParameterKey=SrcDBPassword,ParameterValue=$DBPassword ParameterKey=SrcDatabaseConnection,ParameterValue=$SRC_HOST:$SRC_PORT/$DB_NAME ParameterKey=SrcEngineType,ParameterValue=oracle ParameterKey=Subnets,ParameterValue="$SUBNET_A \, $SUBNET_B" ParameterKey=TgtDBUsername,ParameterValue=${DBUsername} ParameterKey=TgtDBPassword,ParameterValue=${DBPassword} ParameterKey=TgtDatabaseConnection,ParameterValue=$TGT_HOST:$TGT_PORT/$DB_NAME ParameterKey=TgtEngineType,ParameterValue=oracle
  3. Verify the CloudFormation stack creation: 
    aws cloudformation describe-stacks --stack-name RdsUpgradeBlog --region $AWS_REGION --output table

    The CloudFormation stack creates an AWS DMS replication instance and endpoints to your source and upgraded DB instances. It can take several minutes to finish creating resources. When complete, the StackStatus shows as CREATE_COMPLETE.

  4. Test the connection between the replication instance and the source and target endpoints: 
    # Set replication instance arn
DMSREP_INSTANCE_ARN=$(aws cloudformation describe-stacks --stack-name RdsUpgradeBlog --region $AWS_REGION | jq -r '.Stacks[].Outputs[] | select(.OutputKey=="ReplicationInstanceArn") | .OutputValue')
# Set source endpoint arn
DMS_SRC_ENDPOINT=$(aws cloudformation describe-stacks --stack-name RdsUpgradeBlog --region $AWS_REGION | jq -r '.Stacks[].Outputs[] | select(.OutputKey=="SrcEndpointArn") | .OutputValue')
# Set target endpoint arn
DMS_TGT_ENDPOINT=$(aws cloudformation describe-stacks --stack-name RdsUpgradeBlog --region $AWS_REGION | jq -r '.Stacks[].Outputs[] | select(.OutputKey=="TgtEndpointArn") | .OutputValue')

# Test source DB connection
aws dms test-connection --replication-instance-arn ${DMSREP_INSTANCE_ARN} --endpoint-arn ${DMS_SRC_ENDPOINT} --region $AWS_REGION
# Test target DB connection
aws dms test-connection --replication-instance-arn ${DMSREP_INSTANCE_ARN} --endpoint-arn ${DMS_TGT_ENDPOINT} --region $AWS_REGION
  5. Verify the connection statuses: 
    aws dms describe-connections --filter Name=endpoint-arn,Values=${DMS_SRC_ENDPOINT} Name=replication-instance-arn,Values=${DMSREP_INSTANCE_ARN} --region $AWS_REGION --output table

aws dms describe-connections --filter Name=endpoint-arn,Values=${DMS_TGT_ENDPOINT} Name=replication-instance-arn,Values=${DMSREP_INSTANCE_ARN} --region $AWS_REGION --output table

    Testing the connection between the replication instance and endpoints can take a few minutes. When the tests are complete, the Status for both endpoints show as successful.

  6. Populate the schemas for the source and target endpoints: 
    aws dms refresh-schemas --endpoint-arn $DMS_SRC_ENDPOINT --replication-instance-arn $DMSREP_INSTANCE_ARN --region $AWS_REGION --output table

aws dms refresh-schemas --endpoint-arn $DMS_TGT_ENDPOINT --replication-instance-arn $DMSREP_INSTANCE_ARN --region $AWS_REGION --output table
  7. Verify the schema statuses: 
    aws dms describe-refresh-schemas-status --endpoint-arn $DMS_SRC_ENDPOINT --region $AWS_REGION --output table

aws dms describe-refresh-schemas-status --endpoint-arn $DMS_TGT_ENDPOINT --region $AWS_REGION --output table

    Populating the schemas can take a few minutes. When the process is complete, the Status for both endpoints shows as successful.

Create and start replication tasks

AWS DMS supports full load migration as well as ongoing replication, also called change data capture (CDC). This post demonstrates how to use a CDC task to replicate changes in the source DB instance to the upgraded DB instance.

The table mappings and task settings parameter values are passed in using the table-mappings.json and task-settings.json, respectively, and specify the tables and schema to select. In this example, we use the user’s default schema.

  1. Modify these files to use the schema of your DB user by using the following commands: 
    sed -i "s/<<USERNAME>>/${DBUsername}/" table-mappings.json
sed -i "s/<<USERNAME>>/${DBUsername}/" task-settings.json
  2. Create a CDC replication task. Use the SCN value you recorded earlier from your source DB instance. 
    # Set SCN number
export SCN_NUMBER=<<SCN value>>
export task_identifier=dms-sample-task-cdc

# Create task
aws dms create-replication-task --replication-task-identifier ${task_identifier} \
    --source-endpoint-arn ${DMS_SRC_ENDPOINT} \
    --target-endpoint-arn ${DMS_TGT_ENDPOINT} \
    --replication-instance-arn ${DMSREP_INSTANCE_ARN} \
    --migration-type cdc --cdc-start-position ${SCN_NUMBER} \
    --table-mappings 'file://table-mappings.json' \
    --replication-task-settings 'file://task-settings.json' \
    --region ${AWS_REGION} 

# Set task arn
DMS_TASK_ARN=$(aws dms describe-replication-tasks --region ${AWS_REGION} | jq -r '.ReplicationTasks[]|select(.ReplicationTaskIdentifier=="dms-sample-task-cdc")|.ReplicationTaskArn')
  3. Verify the replication task status: 
    aws dms describe-replication-tasks --filters Name=replication-instance-arn,Values=${DMSREP_INSTANCE_ARN} --query "ReplicationTasks[:].{ReplicationTaskIdentifier:ReplicationTaskIdentifier,ReplicationTaskArn:ReplicationTaskArn,ReplicationTaskStatus:Status,ReplicationTFullLoadPercent:ReplicationTaskStats.FullLoadProgressPercent}" --region ${AWS_REGION} --output table

    It can take a few minutes to create the replication task. When the process is complete, the ReplicationTaskStatus shows as ready.

  4. Start the replication task to read ongoing changes from the online or archive redo logs based on the SCN: 
    aws dms start-replication-task --replication-task-arn ${DMS_TASK_ARN} --start-replication-task-type start-replication --region ${AWS_REGION}
  5. Track the progress of the replication task: 
    aws dms describe-replication-tasks --filters Name=replication-instance-arn,Values=${DMSREP_INSTANCE_ARN} --query "ReplicationTasks[:].{ReplicationTaskIdentifier:ReplicationTaskIdentifier,ReplicationTaskArn:ReplicationTaskArn,ReplicationTaskStatus:Status,ReplicationTFullLoadPercent:ReplicationTaskStats.FullLoadProgressPercent}" --region ${AWS_REGION} --output table

    While the replication is ongoing, the ReplicationTaskStatus shows as running. AWS DMS continuously replicates changes from the source DB instance to the upgraded DB instance.

  6. To verify changes are replicated to the upgraded DB instance, connect to the upgraded DB instance. Use the username and password you entered earlier when you deployed the template.yaml CloudFormation template. 
    sqlplus <<username>>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$TGT_HOST)(PORT=$TGT_PORT))(CONNECT_DATA=(SID=$DB_NAME)))
  7. Query the table and sample data from your read replica: 
    SELECT * FROM purchase_orders;

    The query should return the updates that were written to your source DB instance.

  8. Exit your SQL*Plus session: 
    exit;

Monitor and troubleshoot the replication

You can monitor the status of the replication task using Amazon CloudWatch. As a best practice, you should monitor the CDCLatencySource and CDCLatencyTarget metrics. These metrics represent the latency between the replication instance and the source and target DB instances, respectively. High CDCLatencySource indicates that the process of capturing changes from the source is delayed. High CDCLatencyTarget indicates that the process for applying changes to the target is delayed. For more information, see Monitoring AWS DMS tasks.

To ensure data integrity, validate that your data was migrated accurately from the source to the target. AWS DMS provides support for data validation. When data validation is enabled, AWS DMS compares incremental changes for a CDC-enabled task. You can view data validation information using the console, AWS CLI, or AWS DMS API.

While replicating complex and transactional databases, you might encounter errors or high replication latency. For best practices for using AWS DMS and troubleshooting techniques, refer to the following posts:

Test your application and cut over

When the combined values of CDCLatencySource and CDCLatencyMetrics are zero, there is no replication latency between the source and target databases. When your replication task is caught up, you can test your application by connecting to the upgraded DB instance. Upgrading a DB engine version can be a complex process and involve a variety of factors to consider. Therefore, it’s important to review the best practices for upgrading RDS for Oracle DB instances.

After testing is complete, you can point your applications to the upgraded DB instance. Make sure you have defined success criteria for the cut over along with a rollback plan. For strategies for rolling back from a migration, see Rolling back from a migration with AWS DMS.

Clean up

To avoid incurring future charges, delete the resources you created in this post. This includes stopping and deleting the replication tasks as well as the CloudFormation stacks.

Conclusion

This post shared step-by-step instructions for upgrading an Amazon RDS for Oracle DB engine version with minimal downtime by using AWS DMS. This post also provided code templates that you can use to upgrade your Amazon RDS databases. As a best practice, you should review Amazon RDS and AWS DMS documentation along with Oracle database engine release notes for the latest information.

About the authors

Loc Trinh is a Senior Database Solutions Architect with Amazon Web Services.

 

 

 

 

Anita Singh is a Senior Database Solutions Architect with Amazon Web Services.

 

 

 

 

Lyson Ludvic is a Senior Database Solutions Architect with Amazon Web Services.