Migrate Amazon RDS for Oracle to Amazon RDS for PostgreSQL with AWS SCT and AWS DMS using AWS CLI and AWS CloudFormation - AWS Prescriptive Guidance

Migrate Amazon RDS for Oracle to Amazon RDS for PostgreSQL with AWS SCT and AWS DMS using AWS CLI and AWS CloudFormation

Created by Pinesh Singal (AWS)

Environment: PoC or pilot

Source: Amazon RDS for Oracle

Target: Amazon RDS for PostgreSQL

R Type: Re-architect

Workload: Oracle; Open-source

Technologies: Migration; Databases

AWS services: AWS DMS; Amazon RDS; AWS SCT

Summary

This pattern provides an approach with minimal downtime for migrating a multi-terabyte Amazon Relational Database Service (Amazon RDS) for Oracle DB instance to an Amazon RDS for PostgreSQL DB instance, without signing in to the Amazon Web Services (AWS) Management Console.

This approach helps your organization avoid manual configurations and individual migrations using the AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS) consoles. Instead, you set up a one-time configuration for multiple databases and perform the migrations using AWS SCT and AWS DMS on the AWS Command Line Interface (AWS CLI).

The pattern uses AWS SCT to convert database schema objects from Amazon RDS for Oracle to Amazon RDS for PostgreSQL and then uses AWS DMS to migrate the data. Using Python scripts in AWS CLI, you create AWS SCT objects and AWS DMS tasks with an AWS CloudFormation template.

Prerequisites and limitations

Prerequisites 

  • An active AWS account.

  • An existing Amazon RDS for Oracle DB instance.

  • An existing Amazon RDS for PostgreSQL DB instance. 

  • An understanding of the following AWS DMS migration task types: full-load, cdc, full-load-and-cdc.  For more information about this, see Creating a task in the AWS DMS documentation. 

  • AWS SCT, installed and configured with Java Database Connectivity (JDBC) drivers for Oracle and PostgreSQL database engines. For more information about this, see Installing AWS SCT and Installing the required database drivers in the AWS SCT documentation. 

  • The AWSSchemaConversionToolBatch.jar file from the installed AWS SCT folder, copied to your working directory.

  • The cli-sct-dms-cft.zip file (attached), downloaded and extracted in your working directory.

Limitations 

  • The minimum requirements for your source Amazon RDS for Oracle DB instance are: 

    • Oracle versions 12c (v12.1.0.2, v12.2.0.1), 18c (v18.0.0.0) and 19c (v19.0.0.0) for the Enterprise, Standard, Standard One, and Standard Two editions.

    • Although Amazon RDS supports Oracle 18c (v18.0.0.0), this version is on a deprecation path because Oracle no longer provide patches for 18c after the end-of-support date. For more information about this, see Oracle on Amazon RDS in the Amazon RDS documentation.

    • Amazon RDS for Oracle 11g is no longer supported.

  • The minimum requirements for your target Amazon RDS for PostgreSQL DB instance are: 

    • PostgreSQL versions 9 (versions 9.5 and 9.6), 10.x, 11.x, 12.x, and 13.x

Product versions

  • Amazon RDS for Oracle DB instance version 12.1.0.2 and later

  • Amazon RDS for PostgreSQL DB instance version 11.5 and later

  • AWS CLI version 2 

  • AWS SCT most recent version

Architecture

Source technology stack  

  • Amazon RDS for Oracle

Target technology stack  

  • Amazon RDS for PostgreSQL

 

Source and Target architecture 

The following diagram shows the migration of an Amazon RDS for Oracle DB instance to an Amazon RDS for PostgreSQL DB instance using AWS DMS and Python scripts.

The diagram shows the following migration workflow using AWS CLI:

  1. The Python script uses AWS SCT to connect to the source and target DB instances.

  2. The user starts AWS SCT with the Python script, converts the Oracle code to PostgreSQL code, and runs it on the target DB instance.

  3. The Python script creates AWS DMS replication tasks for the source and target DB instances.

  4. The user deploys Python scripts to start the AWS DMS tasks and then stops the tasks after the data migration is complete.

Automation and scale

You can automate this migration by adding additional parameters and security-related changes for multiple functionalities in a single program to your Python script. 

Tools

  • AWS CLI – AWS Command Line Interface (AWS CLI) is an open-source tool that enables you to interact with AWS services using commands in your command-line shell.

  • AWS CloudFormation – AWS CloudFormation is a service that helps you model and set up your AWS resources so that you can spend less time managing those resources and more time focusing on your applications that run in AWS. This pattern converts the .csv input file to a .json input file using a Python script. The .json file is used in AWS CLI commands to create an AWS CloudFormation stack that creates multiple AWS DMS replication tasks with Amazon Resource Names (ARNs), migration types, task settings, and table mappings.

  • AWS DMS – AWS Database Migration Service (AWS DMS) helps you migrate databases to AWS quickly and securely. This pattern uses AWS DMS to create, start, and stop tasks with a Python script run over the command-line and create the AWS CloudFormation template.

  • AWS SCT – AWS Schema Conversion Tool (AWS SCT) converts your existing database schema from one database engine to another. This patterns requires the AWSSchemaConversionToolBatch.jar file from the installed AWS SCT directory.

Code

The cli-sct-dms-cft.zip file (attached) contains the complete source code for this pattern.

Epics

TaskDescriptionSkills required
Configure AWS SCT to run from AWS CLI.

1. Configure the source and target environment configuration details in the database_migration.txt file by using the following format:

#source_vendor,source_hostname,source_dbname,source_user,source_pwd,source_schema,source_port,source_sid,target_vendor,target_hostname,target_user,target_pwd,target_dbname,target_port ORACLE,myoracledb.cokmvis0v46q.us-east-1.rds.amazonaws.com,ORCL,orcl,orcl1234,orcl,1521,ORCL,POSTGRESQL,mypgdbinstance.cokmvis0v46q.us-east-1.rds.amazonaws.com,pguser,pgpassword,pgdb,5432

2. Modify the AWS SCT configuration parameters according to your requirements in the following files: project_settings.xml, Oracle_PG_Test_Batch.xml, and ORACLE-orcl-to-POSTGRESQL.xml.

DBA
Run the run_aws_sct.py Python script.

Run the run_aws_sct.py Python script by using the following command:

$ python run_aws_sct.py database_migration.txt

The Python script converts the database objects from Oracle to PostgreSQL and creates SQL files in PostgreSQL format. The script also creates the Database migration assessment report .pdf file that provides you with detailed recommendations and conversion statistics for database objects.

DBA
Create objects in Amazon RDS for PostgreSQL.
  1. Manually modify the SQL files generated by AWS SCT, if required.

  2. Run the SQL files and create objects in your Amazon RDS for PostgreSQL DB instance.

DBA
TaskDescriptionSkills required
Create an AWS DMS replication instance.

Sign in to the AWS Management Console, open the AWS DMS console, and create a replication instance that is configured according to your requirements.

For more information about this, see Creating a replication instance in the AWS DMS documentation and How do I create an AWS DMS replication instance in the AWS Support documentation.

DBA
Create the source endpoint.

On the AWS DMS console, choose Endpoints and then create a source endpoint for the Oracle database according to your requirements. 

Note: The extra connection attribute must be numberDataTypeScale with a -2 value.

For more information about this, see Creating source and target endpoints in the AWS DMS documentation.

 

DBA
Create the target endpoint.

On the AWS DMS console, choose Endpoints and then create a target endpoint for the PostgreSQL database according to your requirements.  

For more information about this, see Creating source and target endpoints in the AWS DMS documentation.

DevOps engineer
Configure the AWS DMS replication details to run from AWS CLI.

Configure the AWS DMS source and target endpoints and replication details in the dms-arn-list.txt file with the source endpoint ARN, target endpoint ARN, and the replication instance ARN by using following format:

#sourceARN,targetARN,repARN arn:aws:dms:us-east-1:424639141742:endpoint:EH7AQQAHHJVUV5EPBCZKGIOYIY,arn:aws:dms:us-east-1:424639141742:endpoint:INRUQY55NJU3CQF4PJFHNE6HVM,arn:aws:dms:us-east-1:424639141742:rep:DZ5GLL57N7O37VJF4RNVKTXECM
DBA
Run the dms-create-task.py Python script to create the AWS DMS tasks.

1. Run the dms-create-task.py Python script by using the following command:

$ python dms-create-task.py database_migration.txt dms-arn-list.txt <cft-stack-name> <migration-type>

  • database_migration.txt is the database migration text file

  • dms-arn-list.txt is the ARN list for AWS DMS

  • <cft-stack-name> is the user-defined AWS CloudFormation stack name

  • <migration-type> is the migration type (full-load, cdc, or full-load-and-cdc)

2. Depending on your migration type, you can use the following commands to create three types of AWS DMS tasks:

  • $ python dms-create-task.py database_migration.txt dms-arn-list.txt dms-cli-cft-stack full-load

  • $ python dms-create-task.py database_migration.txt dms-arn-list.txt dms-cli-cft-stack cdc

  • $ python dms-create-task.py database_migration.txt dms-arn-list.txt dms-cli-cft-stack full-load-and-cdc

3. The AWS CloudFormation stack and AWS DMS tasks are created 

 

DBA
Check that AWS DMS tasks are ready.

On the AWS console, check that your AWS DMS tasks are in Ready status in the Status section.

DBA
TaskDescriptionSkills required
Start the AWS DMS tasks.

Run the dms-start-task.py Python script by using the following command:

$ python dms-start-task.py start ‘<cdc-start-datetime>’

Note: The start date and time must be in the 'DD-MON-YYYY' or 'YYYY-MM-DDTHH:MI:SS' timestamp data type formats (for example, ‘01-Dec-2019’ or ‘2018-03-08T12:12:12’)

You can review the AWS DMS task status in the Table statistics tab of your migration tasks on the Tasks page of the AWS DMS console.

DBA
Validate the data.
  1. After the full-load migration is complete, the task is continuously kept running for continuous data change (CDC).

  2. When CDC is complete or no more changes need to be migrated, review and validate the migration task results and data in your Oracle and PostgreSQL databases.

  3. You can validate your data by checking status and count columns (Validation state, Validation pending, Validation failed, Validation suspended, and Validation details) in the Table statistics tab of your database migration task on the Tasks page of the AWS DMS console. 

For more information about this, see AWS DMS data validation in the AWS DMS documentation.

DBA
Stop the AWS DMS tasks.

Run the Python script by using the following command:

$ python dms-start-task.py stop

Note: AWS DMS tasks might stop with a failed status, depending on the validation status. For more information about this, see the troubleshooting table in the Additional information section.

DBA

Related resources

Additional information

Troubleshooting

The following table provides guidance about troubleshooting this pattern.

Issue

Solution

AWS SCT source and target test connections fail

 

Configure the JDBC driver versions and VPC security group inbound rules to accept the incoming traffic.

 

Source or target endpoint test run fails

Check if the endpoint settings and replication instance is in Available status. Check if the endpoint connection status is Successful

For more information about this, see How can I troubleshoot AWS DMS endpoint connectivity failures in the AWS Support documentation.

 

Full-load run fails

Check if the source and target databases have matching data types and sizes. 

For more information about this, see Troubleshooting migration tasks in AWS DMS in the AWS DMS documentation.

 

Validation run errors

Check if the table has a primary key because non-primary key tables are not validated.

If the table has a primary key and errors, check that the extra connection attribute in the source endpoint has numberDataTypeScale=-2.

For more information about this, see Extra connection attributes when using Oracle as a source for AWS DMS, OracleSettings, and Troubleshooting in the AWS DMS documentation. 

 

Attachments

To access additional content that is associated with this document, unzip the following file: attachment.zip