Migrate On-Premise Oracle to MySQL in AWS

This post walks through migrating an on-premise Oracle database to a MySQL database in RDS using AWS Schema Conversion Tool (SCT) to manage the conversions of the schema objects and Data Migration Service (DMS) replication instance to move the data. This high level description, assumes the MySQL RDS instance has been configured as well as the other AWS resources such as VPC, VPG (VPN to your internal network) and any other AWS resources you will be leveraging in your environment.

The following steps will be taken to complete the migration task:

Create a user access account using Identity and Access Management (IAM) for the DMS task.

Select Programmatic access - you will need the access keys to configure the profile within the AWS SCT Project.

Create a group with the AmazonDMSVPCManagementRole policy for the DMS user, it is require by the replication instance

Note: If you plan on logging the replication tasks using CloudWatch, the AmazonDMSCloudWatchLogsRole policy is required.

Finish creating the user and download the access keys CSV file.

Create an encrypted key using AWS Key Management Service (KMS)

You can create a Encryption key from the IAM console.

Click on Create Key and fill in the required details

Note: Be sure to select the same Region that your replication instance and other resources are located for the KMS key.

Select the user account you created for the DMS task.

Finish creating the key and make sure it's enabled.

CONFIGURE the Oracle database - Source

Create a user for the DMS replication Instance and a second user for SCT with the appropriate permissions.

Configure the RDS Instance - Target

Create a DMS user and a SCT user with appropriate permissions.

Configure DMS for the Migration

Create a Subnet Group for the Replication Instance

From the AWS Console > DMS > Subnet Groups > Create subnet group

Note: The subnet group must have one subnet in at least two Availability Zones(AZ)

Launch a DMS replication instance

Configure the Oracle and MySQL Endpoints

Create a migration task

Create a project using AWS Schema Conversion Tool (SCT)

Note: Be sure to download the appropriate database drivers for the SCT as well - you will need one for Oracle and one for MySQL.

Create a project and configure the connections to the Oracle source database and the MySQL target database.

Select the schema(s) you would like to convert.

The assessment will show you the objects that will require user intervention/action in order to complete the schema export conversion.

There are several options for a target database conversion Engine, select the appropriate database engine.

Use the AWS_SCT user account created on the target MySQL database to connect to the AWS SCT.

Click on Schema you want to migrate > At the top of the AWS Schema Conversion Tool Console, click on View > Select Assessment Report View (You can toggle between the Summary and Action Items tabs for holistic vs schema object specific issues)

This will show you the assessment of the schema - any potential issues such as migrating triggers, sequences, global temporary tables, etc

Run and review the Database Migration Report, and save the report for later review.

Make any necessary changes to the SQL for exporting the schema objects. This report will list all the findings, potential errors, etc with the schema conversion from Oracle to MySQL. It is an invaluable tool in ensuring all schema objects translate accurately.

After addressing the issues, click on the Action tab at the top of the SCT console and select Apply to Database to create the schemas in the RDS target database.

Start and monitor the migration task.

At the top of the SCT console, click on View > Select Data Migration View.

You should see the replication instance and a dashboard that has a start button, you can click on start from the SCT console, or start the migration task in the AWS Web Console online.

DMS logs all errors in the task log, so it is important to monitor that log for errors and warnings - you can perform all the monitoring via the DMS console online or leverage CloudWatch to monitor the task and alert you.

Once the migration task is complete, verify your schema and its objects were migrated successfully.

Verify that all tables were migrated by looking at the table status on the DMS console and complete data validation check by using a 3rd party tool such as MySQL Workbench to query the target database.

DMS migrations using AWS SCT can be invaluable and efficient resource when migrating to a different database engine. If migrating from an on-premise database to the same database engine in AWS, you can also  leverage the default tools for that database engine such as SQL Developer, pgAdmin, or MySQL Workbench.

Before completing a migration task, I would advise reviewing pertinent AWS User Guides as well as familiarizing yourself with DMS Best Practices from the AWS User Guide here.

Images from cloudxchange.io