Migrate from Amazon RDS for Oracle to Amazon RDS for MySQL - AWS Prescriptive Guidance

Migrate from Amazon RDS for Oracle to Amazon RDS for MySQL

Created by Jitender Kumar (AWS)

R Type: Re-architect

Source: Databases: Relational

Target: Amazon RDS for MySQL

Created by: AWS

Environment: PoC or pilot

Technologies: Databases; Migration

Workload: Oracle

AWS services: Amazon RDS

Summary

This pattern provides guidance for migrating an Amazon Relational Database Service (Amazon RDS) for Oracle DB instance to an Amazon RDS for MySQL DB instance by using AWS Database Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT). 

The pattern covers best practices for handling the migration of stored procedures and code changes to support the application layer.

Additionally, this pattern uses AWS Workload Qualification Framework (AWS WQF) to estimate efforts required to carry out this migration. AWS WQF classifies this migration as workload category 1.

Prerequisites and limitations

Prerequisites

  • An active AWS account.

  • An Amazon RDS for Oracle source database.

  • An Amazon RDS for MySQL target database. Source and target databases should be in the same virtual private cloud (VPC), or you must have the required access permissions if you're using multiple VPCs.

  • Security groups that allow connectivity between the source and target databases, AWS SCT, the application server, and AWS DMS.

  • A user account with the required privilege to run AWS SCT on the source database.

  • Supplemental logging enabled, to run AWS DMS on the source database.

Limitations

  • The source and target Amazon RDS database size limit is 64 TB.

  • Oracle is case-insensitive for database objects, but MySQL is not. AWS SCT can handle this issue while creating an object; however, some manual work is required to support full case insensitivity.

  • This migration doesn't use MySQL extensions to enable Oracle-native functions. AWS SCT handles most of the conversion, but some work is required to change code manually.

  • Java Database Connectivity (JDBC) driver changes are required in the application.

Product versions

  • Amazon RDS for Oracle 11.2.0.4.v20

  • Amazon RDS for MySQL 8.0.15

  • AWS DMS version 3.3.0

  • AWS SCT version 1.0.628

Architecture

Source technology stack

Target technology stack

Migration architecture

Tools

  • AWS Schema Conversion Tool – AWS SCT makes heterogeneous database migrations predictable by automatically converting the source database schema and a majority of the database code objects, including views, stored procedures, and functions, to a format compatible with the target database.

  • AWS Data Migration Service – AWS DMS can migrate your data to and from most widely used commercial and open-source databases. AWS DMS supports homogeneous migrations such as Oracle to Oracle, as well as heterogeneous migrations between different database platforms, such as Oracle or Microsoft SQL Server to Amazon Aurora.

  • AWS Workload Qualification Framework – AWS WQF uses AWS SCT to collect information to model existing Oracle and Microsoft SQL Server database workloads and provides instructions to convert them to an AWS database.

Epics

TaskDescriptionSkills required
Validate the source and target database versions and engines.

DBA
Use AWS WQF to estimate efforts required for this migration.

DBA, Sysadmin
Identify hardware requirements for the target server instance.

DBA, SysAdmin
Identify storage requirements (storage type and capacity).

DBA, SysAdmin
Choose the proper instance type (capacity, storage features, network features).

DBA, SysAdmin
Identify network-access security requirements for the source and target databases.

DBA, SysAdmin
Choose an application migration strategy.

Consider whether you want full downtime or partial downtime for cutover activities.

DBA, SysAdmin, App owner
TaskDescriptionSkills required
Create a VPC and subnets.

SysAdmin
Create security groups and network access control lists (ACLs).

SysAdmin
Configure and start the Amazon RDS for Oracle instance.

DBA, SysAdmin
Configure and start the Amazon RDS for MySQL instance.

DBA, SysAdmin
Prepare a test case for validation of code conversion.

This will help in unit-testing for the converted code.

DBA, Developer
Configure the AWS DMS instance.

Configure source and target endpoints in AWS DMS.

TaskDescriptionSkills required
Generate the target database script using AWS SCT.

Check the accuracy of the code that was converted by AWS SCT. Some manual work will be required.

DBA, Developer
In AWS SCT, choose the "Case Insensitive" setting.

In AWS SCT, choose Project Settings, Target Case Sensitivity, Case Insensitive.

DBA, Developer
In AWS SCT, choose not to use the Oracle native function.

In Project Settings, check the functions TO_CHAR/TO_NUMBER/TO_DATE.

DBA, Developer
Make changes for "sql%notfound" code.

You might have to convert the code manually.

Query on tables and objects in stored procedures (use lowercase queries).

DBA, Developer
Create the primary script after all changes are made, and then deploy the primary script on the target database.DBA, Developer
Unit-test stored procedures and application calls using sample data.

Clean up data that was created during unit testing.

DBA, Developer
Drop foreign key constraints on the target database.

This step is required to load initial data. If you don't want to drop the foreign key constraints, you must create a migration task for data specific to the primary and secondary tables.

DBA, Developer
Drop primary keys and unique keys on the target database.

This step results in better performance for the initial load.

DBA, Developer
Enable supplemental logging on the source database.

DBA
Create a migration task for the initial load in AWS DMS, and then run it.

Choose the option to migrate existing data.

DBA
Add the primary keys and foreign keys to the target database.

Constraints need to be added after the initial load.

DBA, Developer
Create a migration task for ongoing replication.

Ongoing replication keeps the target database synchronized with the source database.

DBA
TaskDescriptionSkills required
Replace Oracle native functions with MySQL native functions.

App owner
Make sure that only lowercase names are used for database objects in SQL queries.

DBA, SysAdmin, App owner
TaskDescriptionSkills required
Shut down the application server.

App owner
Validate that the source and target databases are in sync.

DBA, App owner
Stop the Amazon RDS for Oracle DB instance.

DBA
Stop the migration task.

This will stop automatically after you complete the previous step.

DBA
Change the JDBC connection from Oracle to MySQL.

App owner, DBA
Start the application.

DBA, SysAdmin, App owner
TaskDescriptionSkills required
Review and validate the project documents.

DBA, SysAdmin
Gather metrics about time to migrate, percentage of manual versus tool tasks, cost savings, etc.

DBA, SysAdmin
Stop and delete AWS DMS instances.

DBA
Remove the source and target endpoints.

DBA
Remove migration tasks.

DBA
Take a snapshot of the Amazon RDS for Oracle DB instance.

DBA
Delete the Amazon RDS for Oracle DB instance.

DBA
Shut down and delete any other temporary AWS resources you used.

DBA, SysAdmin
Close the project and provide any feedback.

DBA