Migrate an Oracle database to Aurora PostgreSQL using AWS DMS and AWS SCT - AWS Prescriptive Guidance

Migrate an Oracle database to Aurora PostgreSQL using AWS DMS and AWS SCT

Created by Mark Szalkiewicz (AWS)

R Type: Re-architect

Source: Databases: Relational

Target: Amazon Aurora PostgreSQL

Created by: AWS

Environment: PoC or pilot

Technologies: Databases; Migration

Workload: Oracle

AWS services: Amazon Aurora

Summary

This pattern describes how to migrate an Oracle database to Amazon Aurora PostgreSQL-Compatible Edition by using AWS Data Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT). 

The pattern covers source Oracle databases that are on premises, Oracle databases that are installed on Amazon Elastic Compute Cloud (Amazon EC2) instances, and Amazon Relational Database Service (Amazon RDS) for Oracle databases. The pattern converts these databases to Aurora PostgreSQL-Compatible.

Prerequisites and limitations

Prerequisites

  • An active AWS account

  • An Oracle database in an on-premises data center or in the AWS Cloud .

  • SQL clients installed either on a local machine or on an EC2 instance.

  • Java Database Connectivity (JDBC) drivers for AWS SCT connectors, installed on either a local machine or an EC2 instance where AWS SCT is installed.

  • If the source database supports a commercial off-the-shelf (COTS) application or is vendor-specific, you might not be able to convert it to another database engine. Before using this pattern, confirm that the application supports Aurora PostgreSQL-Compatible .  

Limitations

  • Database size limit: 64 TB 

Product versions

Architecture

Source technology stack

One of the following:

  • An on-premises Oracle database

  • An Oracle database on an EC2 instance  

  • An Amazon RDS for Oracle DB instance

Target technology stack

  • Aurora PostgreSQL-Compatible 

Target architecture

Data migration architecture

  • From an Oracle database running in the AWS Cloud

  • From an Oracle database running in an on-premises data center

Tools

  • AWS DMS - AWS Data Migration Service (AWS DMS) helps you migrate your data to and from widely used commercial and open-source databases, including Oracle, MySQL, and PostgreSQL. The service supports homogeneous migrations such as Oracle to Oracle, and heterogeneous migrations such as Oracle to PostgreSQL or MySQL to Oracle.  

  • AWS SCT - AWS Schema Conversion Tool (AWS SCT) makes heterogeneous database migrations easy by automatically converting the source database schema and a majority of the custom code to a format compatible with the target database.

Epics

TaskDescriptionSkills required
Validate the source and target database version and engine.

DBA
Create an outbound security group to the source and target databases.

SysAdmin
Create and configure an EC2 instance for AWS SCT, if required.

DBA
Download the latest version of AWS SCT and associated drivers.

DBA
Add and validate the prerequisite users and grants in the source database.

DBA
Create an AWS SCT project for the workload and connect to the source database.

DBA
Generate an assessment report and evaluate feasibility.

DBA
TaskDescriptionSkills required
Create a target Amazon RDS DB instance, using Amazon Aurora as the database engine.

DBA
Extract the list of users, roles, and grants from the source.

DBA
Map the existing database users to the new database users.

App owner
Create users in the target database.

DBA, App owner
Apply roles from the previous step to the target database.

DBA
Review the database for options, parameters, network files, and database links from the source and then evaluate applicability to the target.

DBA
Apply any relevant settings to the target.

DBA
TaskDescriptionSkills required
Configure AWS SCT connectivity to the target database.

DBA
Convert the schema using AWS SCT.

AWS SCT automatically converts the source database schema and most of the custom code to a format that is compatible with the target database. Any code that the tool cannot convert automatically is clearly marked so that you can convert it yourself.

DBA
Review the generated SQL report and save any errors and warnings.

DBA
Apply automated schema changes to the target or save them as a .sql file.

DBA
Validate that AWS SCT created the objects on the target.

DBA
Manually rewrite, reject, or redesign any items that failed to convert automatically.

DBA, App owner
Apply the generated role and user grants and review any exceptions.

DBA
TaskDescriptionSkills required
Determine the migration method.

DBA
Create a replication instance from the AWS DMS console.

DBA
Create the source and target endpoints.

DBA
Create a replication task.

DBA
Start the replication task and monitor the logs.

DBA
TaskDescriptionSkills required
Use AWS SCT to analyze and convert the SQL items within the application code.

When you convert your database schema from one engine to another, you also need to update the SQL code in your applications to interact with the new database engine instead of the old one. You can view, analyze, edit, and save the converted SQL code.

App owner
Create the new application servers on AWS.

App owner
Migrate the application code to the new servers.

App owner
Configure the application server for the target database and drivers.

App owner
Fix any code specific to the source database engine in the application.

App owner
Optimize the application code for the target engine.

App owner
TaskDescriptionSkills required
Perform the cutover.

DBA
Lock the application from any further changes.

App owner
Validate that all changes were propagated to the target database.

DBA
Point the new application server to the target database.

App owner
Perform a final comprehensive system check.

App owner
Go live.

App owner
TaskDescriptionSkills required
Shut down the temporary AWS resources (AWS DMS replication instance and EC2 instance used for AWS SCT).

DBA, App owner
Update feedback on the AWS DMS process for internal teams.

DBA, App owner
Revise the AWS DMS process and improve the template if necessary.

DBA, App owner
Review and validate the project documents.

DBA, App owner
Gather metrics around time to migrate, percent of manual versus tool cost savings, and so on.

DBA, App owner
Close the project and provide any feedback.

DBA, App owner

References

Tutorials and videos