Migrate an Oracle database to Amazon Aurora MySQL using AWS DMS and AWS SCT
Created by Mark Szalkiewicz (AWS)
R Type: Re-architect | Source: Databases: Relational | Target: Amazon Aurora MySQL |
Created by: AWS | Environment: PoC or pilot | Technologies: Databases; Migration |
Workload: Oracle | AWS services: Amazon Aurora |
Summary
This pattern provides guidance for migrating an Oracle database to Amazon Aurora MySQL-Compatible Edition using AWS Data Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT).
This pattern covers three types of source databases: on-premises Oracle databases, Oracle databases on Amazon Elastic Compute Cloud (Amazon EC2) instances, and Amazon Relational Database Service (Amazon RDS) for Oracle DB instances. The target is an Aurora MySQL-Compatible database.
Prerequisites and limitations
Prerequisites
An active AWS account
SQL clients installed either on a local machine or on an EC2 instance
Java Database Connectivity (JDBC) drivers for AWS SCT connectors, installed either on a local machine or on an EC2 instance where AWS SCT is installed
Familiarity with Using an Oracle Database as a Source for AWS DMS
Familiarity with Using a MySQL-Compatible Database as a Target for AWS DMS
Limitations
Adhere to the storage size limit for an Aurora database cluster. For more information, see Amazon Aurora size limits.
Product versions
All Oracle database editions for versions 11g (versions 11.2.0.3.v1 and later) and up to 12.2, and 18c. For the latest list of supported versions, see Using an Oracle Database as a Source for AWS DMS and Using a MySQL-Compatible Database as a Target for AWS DMS. We recommend that you use the latest version of AWS DMS for the most comprehensive version and feature support. For information about Oracle database versions supported by AWS SCT, see the AWS SCT documentation.
MySQL versions 5.5, 5.6, and 5.7
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 MySQL-Compatible DB instance
Data migration architecture
For an Oracle source database running in the AWS Cloud:

For an on-premises Oracle source database:

Tools
AWS DMS - AWS Database Migration Services (AWS DMS) helps you migrate relational databases, data warehouses, NoSQL databases, and other types of data stores. You can use AWS DMS to migrate your data into the AWS Cloud, between on-premises instances (through an AWS Cloud setup), or between combinations of cloud and on-premises setups.
AWS SCT - AWS Schema Conversion Tool (AWS SCT) is used to convert your database schema from one database engine to another.
Epics
Task | Description | Skills 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 AWS SCT and drivers. | DBA | |
Add and validate 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 |
Task | Description | Skills required |
---|---|---|
Create an Amazon RDS target database. | DBA | |
Extract the lists for users, roles, and grants from the source. | DBA | |
Map existing database users to new database users. | App owner | |
Create users in the target database. | DBA, App owner | |
Apply roles from the preceding step to the target Amazon RDS database. | DBA | |
Review the source database options, parameters, network files, and database links, and evaluate their applicability to the target database. | DBA | |
Apply any relevant settings to the target database. | DBA |
Task | Description | Skills required |
---|---|---|
Configure AWS SCT connectivity to the target database. | DBA | |
Convert the schema in AWS SCT. | DBA | |
Review the generated SQL code, and save the error and warning objects. | DBA | |
Apply the automated schema changes to the target, or save them as a .sql file. | DBA | |
Validate AWS SCT object creation in the target database. | DBA | |
Manually rewrite, reject, or redesign any items that failed to convert automatically. | DBA, App owner | |
Apply the role and user grants that were generated, and review exceptions. | DBA |
Task | Description | Skills required |
---|---|---|
Determine the migration method. | DBA | |
Create a replication instance in the AWS DMS console. | DBA | |
Create source and target endpoints. | DBA | |
Create a replication task. | DBA | |
Run the replication task and monitor the logs. | DBA |
Task | Description | Skills required |
---|---|---|
Use AWS SCT to analyze and convert the SQL code inside the application 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 source database engine-specific code in the application. | App owner | |
Optimize the application code for the target database engine. | App owner |
Task | Description | Skills required |
---|---|---|
Administer any new users, grants, and code changes in the target database. | DBA | |
Lock the application from any further changes. | App owner | |
Validate that all changes have been propagated to the target database. | DBA | |
Point the new application server to the target database. | App owner | |
Perform sanity checks. | App owner | |
Go live. | App owner |
Task | Description | Skills required |
---|---|---|
Shut down temporary AWS resources (AWS DMS replication instance, EC2 instance used for AWS SCT). | DBA, App owner | |
Update feedback on the AWS DMS process to internal teams. | DBA, App owner | |
Revise the AWS DMS process and improve the template. | DBA, App owner | |
Review and validate the project documents. | DBA, App owner | |
Gather metrics around time to migrate, % of manual vs. tool, cost savings, etc. | DBA, App owner | |
Close out the project and provide feedback. | DBA, App owner |
Related resources
References
Tutorials and videos