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 shows how to migrate a multi-terabyte Amazon Relational Database Service (Amazon RDS) for Oracle DB instance to an Amazon RDS for PostgreSQL DB instance by using the AWS Command Line Interface (AWS CLI). The approach provides minimal downtime and doesn’t require signing in to the AWS Management Console.

This pattern helps avoid manual configurations and individual migrations by using the AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS) consoles. The solution sets up a one-time configuration for multiple databases and performs the migrations by using AWS SCT and AWS DMS on the 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 Amazon EC2 instance or local machine with Windows or Linux OS for running scripts.

• An understanding of the following AWS DMS migration task types: full-load, cdc, full-load-and-cdc.  For more information, 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, 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.

• The most recent AWS DMS replication instance engine version. For more information, see How do I create an AWS DMS replication instance in the AWS Support documentation and AWS DMS 3.4.4 release notes in the AWS DMS documentation. 

• AWS CLI version 2, installed and configured with your access key ID, secret access key, and default AWS Region name for the Amazon Elastic Compute Cloud (Amazon EC2) instance or operating system (OS) where the scripts are run. For more information, see Installing, updating, and uninstalling the AWS CLI version 2 and Configuring the AWS CLI in the AWS CLI documentation. 

• Familiarity with AWS CloudFormation templates. For more information, see AWS CloudFormation concepts in the AWS CloudFormation documentation. 

• Python version 3, installed and configured on the Amazon EC2 instance or OS where the scripts are run. For more information, see the Python documentation. 

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, 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 

• The latest version of AWS SCT

• The latest version of Python 3

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:

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 Command Line Interface (AWS CLI) is an open-source tool that helps you interact with AWS services through commands in your command-line shell.

• AWS CloudFormation helps you set up AWS resources, provision them quickly and consistently, and manage them throughout their lifecycle across AWS accounts and Regions. 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 Database Migration Service (AWS DMS) helps you migrate data stores into the AWS Cloud or between combinations of cloud and on-premises setups. 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 Schema Conversion Tool (AWS SCT) supports heterogeneous database migrations by automatically converting the source database schema and a majority of the custom code to a format that’s compatible with the target database. 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

Configure AWS SCT and create database objects in AWS CLI

Task	Description	Skills required
Configure AWS SCT to run from the 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.	Manually modify the SQL files generated by AWS SCT, if required. Run the SQL files and create objects in your Amazon RDS for PostgreSQL DB instance.	DBA

Configure and create AWS DMS tasks by using the AWS CLI and AWS CloudFormation

Task	Description	Skills 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, 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, 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, see Creating source and target endpoints in the AWS DMS documentation.	DevOps engineer
Configure the AWS DMS replication details to run from the 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:123456789012:endpoint:EH7AINRUDZ5GOYIY6HVMXECMCQ arn:aws:dms:us-east-1:123456789012:endpoint:HHJVUV57N7O3CQF4PJZKGIOYY5 arn:aws:dms:us-east-1:123456789012:rep:LL57N77AQQAHHJF4PJFHNEDZ5G	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.	In the AWS console, check that your AWS DMS tasks are in Ready status in the Status section.	DBA

Start and stop the AWS DMS tasks by using the AWS CLI

Task	Description	Skills 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.	After the full-load migration is complete, the task is continuously kept running for continuous data change (CDC). 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. 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, 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 failedstatus, depending on the validation status. For more information, see the troubleshooting table in the Additional information section.	DBA

Troubleshooting

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, 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, 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, see Extra connection attributes when using Oracle as a source for AWS DMS, OracleSettings, and Troubleshooting in the AWS DMS documentation.

Related resources

• Installing AWS SCT

• Introduction to AWS DMS (video)

• Using the AWS CLI in AWS CloudFormation

• Using the AWS SCT user interface

• Using an Oracle database as a source for AWS DMS

• Using Oracle as a source for AWS SCT

• Using a PostgreSQL database as a target for AWS DMS 

• Sources for data migration in AWS DMS 

• Targets for data migration in AWS DMS

•  cloudformation (AWS CLI documentation)

• cloudformation create-stack (AWS CLI documentation) 

• dms (AWS CLI documentation) 

Attachments

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