Migrate an Oracle database to Amazon Aurora MySQL using AWS DMS and AWS SCT

R Type :RePlatform

source :Databases: Relational

target :Amazon Aurora MySQL

tags :oracle, database, aurora

categories :Independent Software Vendor, LifeCycle, Pattern Accreditation, Pattern Provenance, Pattern Type, Software Infrastructure

Summary

This pattern provides guidance for migrating an Oracle database to Amazon Aurora MySQL using AWS Data Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT). The pattern covers the migration of 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.

Assumptions and Prerequisites

Prerequisites

An active AWS account
SQL clients installed either on a local machine or on an Amazon EC2 instance
JDBC drivers for AWS SCT connectors installed either on a local machine or on an Amazon EC2 instance where AWS SCT is installed
Technical knowledge: Using an Oracle Database as a Source for AWS DMS
Technical knowledge: Using a MySQL-Compatible Database as a Target for AWS DMS

Limitations

Database size limit: 64 TB

Architecture

Source technology stack

An on-premises Oracle database
An Oracle database on an Amazon EC2 instance
An Amazon RDS for Oracle DB instance

This pattern supports Oracle Database versions 10g, 11g, and 12c for the Enterprise, Standard, Standard One, and Standard Two editions. For a complete, up-to-date list of source databases, see Sources for Data Migration in the AWS DMS documentation.

Target technology stack

Amazon Aurora MySQL DB instance (Aurora supports MySQL version 5.6)

Source and target architecture

For an Oracle source database running in the AWS Cloud:

For an on-premises Oracle source database:

Tools Used

AWS DMS - AWS Database Migration Services (AWS DMS) supports several types of source and target databases. For details, see AWS DMS Step-by-Step Walkthroughs.

AWS SCT- AWS Schema Conversion Tool (AWS SCT) automatically converts the source database schema and a majority of the custom code to a format that's compatible with the target database.

Epics

Plan the migration

Tasks

Title	Description	Skills	Predecessor
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

Prepare the target database

Tasks

Title	Description	Skills	Predecessor
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.		AppOwner
Create users in the target database.		DBA, AppOwner
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

Transfer objects

Tasks

Title	Description	Skills	Predecessor
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, AppOwner
Apply the role and user grants that were generated, and review exceptions.		DBA

Migrate the database

Tasks

Title	Description	Skills	Predecessor
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

Migrate the application

Tasks

Title	Description	Skills	Predecessor
Use AWS SCT to analyze and convert the SQL code inside the application code.		AppOwner
Create the new application servers on AWS.		AppOwner
Migrate the application code to the new servers.		AppOwner
Configure the application server for the target database and drivers.		AppOwner
Fix any source database engine-specific code in the application.		AppOwner
Optimize the application code for the target database engine.		AppOwner

Cut over

Tasks

Title	Description	Skills	Predecessor
Administer any new users, grants, and code changes in the target database.		DBA
Lock the application from any further changes.		AppOwner
Validate that all changes have been propagated to the target database.		DBA
Point the new application server to the target database.		AppOwner
Perform sanity checks.		AppOwner
Go live.		AppOwner

Close the project

Tasks

Title	Description	Skills	Predecessor
Shut down temporary AWS resources (AWS DMS replication instance, EC2 instance used for AWS SCT).		DBA, AppOwner
Update feedback on the AWS DMS process to internal teams.		DBA, AppOwner
Revise the AWS DMS process and improve the template.		DBA, AppOwner
Review and validate the project documents.		DBA, AppOwner
Gather metrics around time to migrate, % of manual vs. tool, cost savings, etc.		DBA, AppOwner
Close out the project and provide feedback.		DBA, AppOwner