Aurora Data Migration: Oracle Database to Aurora MySQL using DMS

Every year more businesses lean towards the cloud. This has led to a decline in local data centers as more businesses leverage cloud databases. The main reason behind this is that it saves a lot of trouble.

The expense of building physical data centers and acquiring hardware and software is greatly reduced. Moreover, the budget for IT staff to manage the data centers can now be used for other tasks that need attention.

What is Amazon Aurora?

Above all, you must understand what Amazon Aurora is. The simplest definition is – Amazon Aurora is a fully-managed relational database engine. It offers compatibility with MySQL as well as PostgreSQL.

Both, MySQL and PostgreSQL bring the speed and reliability of high-end databases. In addition, they offer the simplicity and affordability of open-source databases. What does this mean?

It means that all the tools, code, and applications you have been using with MySQL and PostgreSQL databases will work with Aurora. Therefore, Aurora data migration will not require you to learn a lot of new things.

What Makes Amazon Aurora Better?

You already know that Aurora offers full compatibility with MySQL and PostgreSQL. If you just work with some workloads, Aurora is capable of delivering:

Five times the throughput of MySQL,
Three times the throughput of PostgreSQL.

Best of all, almost no changes are required to most of the existing applications. Furthermore, Aurora also features a high-performance storage subsystem.

The MySQL and PostgreSQL compatible database engines of Aurora are tailored to leverage that fast-distributed storage.

How Does Amazon Aurora Compare to Amazon RDS?

Following is the list of reasons how Aurora relates to Amazon RDS standard MySQL and PostgreSQL engines:

Ability to choose Aurora as the database option at the time of new database servers setup through Amazon RDS.
Aurora leverages the familiar Amazon RDS features for administration and management.
Aurora offers the same management console interface, AWS CLI commands, and API operations as Amazon RDS. This allows you to handle the daily database tasks as you would on RDS.
Management operations normally consist of entire database server clusters that synchronize through replication. To clarify, individual database instances are not used.
Automatic replication, clustering, and storage allocation simplify the process of setting up, operating and scaling your largest MySQL and PostgreSQL deployments. These processes are also made more affordable.
You can move data between Amazon RDS (MySQL and PostgreSQL) and Aurora by using snapshots or one-way replications.
Push-button migration tools can be used to convert Amazon RDS applications to Aurora.

AWS Data Migration Service (DMS) Outline: Oracle to Aurora MySQL

Migrating your data from Oracle to Aurora MySQL with AWS DMS is relatively easy. Whether you have prior experience or not, the following summary should help you get started.

Prepare Oracle Source Database

Using AWS DMS requires a bit of preparation. Here are some recommended steps you should take:

Create a separate AWS DMS account for the sole purpose of data migration. Set the account to a minimal set of privileges. That is to say, enable only those that are required for the migration process.
Enable supplemental logging to use DMS. You can do so by running the following command:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

The DMS requires this for each table that is being migrated. You can run the following command to automatically add this supplemental logging:

addSupplementalLogging=Y

Amazon DMS replication server requires access to the source database. Ensure that firewall rules are giving the DMS replication server ingress.

Launch and Prepare Aurora MySQL Target Database

There are certain things to consider at the time of launching the Aurora MySQL instance:

It is recommended that you locate the Aurora MySQL instance and replication instance in the same VPC and availability zone (if possible).
In addition, create a separate account with minimal privileges for migrating data. The following are privileges required on all databases for a successful migration.

[ALTER, CREATE, DROP INDEX, INSERT, UPDATE, DELETE, SELECT]

Finally, the AWS DMS requires complete access to “awsdms_control” database. You can enable it by using the following command:

ALL PRIVILEGES ON awsdms_control.* TO 'dms_user'

The mentioned database consists of information required by AWS DMS specific to the migration in process.

Initiate a Replication Instance

The Amazon DMS service uses a replication instance to connect to your target and source databases. Here are a few things you should consider as you launch the replication instance:

Locate the replication instance in the same VPC and Availability Zone as the target database.
If either the source or target database is outside of the VPC, you need to ensure the replication server is accessible publicly.
AWS DMS consumes a fair share of CPU and memory. You can scale by anticipating how many tasks you will be running on a single replication server. Also, if the migration involves large volumes of tables, try using a larger instance.

Normally, the default storage is sufficient for most migrations. Depending on the size of your database, you may or may not need to scale up.

Create a Source Endpoint

You will need to create a source endpoint so your oracle source database can be accessed by AWS DMS. Basically, the source endpoint defines all required information for the DMS to connect to the source from the replication server.

Here are some source endpoint requirements.

The source endpoint must be accessible from the replication server. You might need to modify firewall rules for whitelisting the replication server. To locate your replication server IP address, check in the AWS DMS management console.
Oracle requires supplemental logging enabled for AWS DMS to capture the changes. If you want AWS DMS to take care of this, add the following code to extra connection attributes for the source endpoint;

addSupplementalLogging=Y

Create Target Endpoint

Similar to the source endpoint, AWS DMS needs a target endpoint to access the Aurora MySQL target database. Again, this defines all required information for DMS to establish a connection with the target database.

The target endpoint must be accessible from the replication server. In addition, you may or may not need to modify security groups to make the target endpoint accessible.
In case you have pre-created the database on the target, it is recommended that you disable foreign key checks during full load. Use the following command in extra connection attributes to achieve this:

initstmt=SET FOREIGN_KEY_CHECKS=0

Create and Run Migration Task

Finally, the last step involves creating and running a migration task. It serves the purpose of informing AWS DMS where and how to migrate the data. Prior to creating a migration task, consider setting the parameters for migration as follows:

Endpoints and replication server – select the endpoints and replication server you have created in the previous steps.
Migration type – Most cases work well with the “migrate existing data and replication ongoing changes” option. This allows DMS to apply outstanding changes and keep source and target databases in sync until stopped.
Target table preparation mode – Choose “drop tables on target” if you are having AWS DMS create the tables. In case of another method, choose “truncate”.
LOB parameters – For test runs on DMS, choose “include LOB columns in replication, Limited LOB mode” and set the max LOB size to 16.
Enable logging – This will help you debug migration issues. It’s always a best practice to enable logging.
Table mapping – It is recommended you convert schema, table, and column names to lowercase when migrating from Oracle to Aurora MySQL. You can achieve this by creating custom table mapping.

Takeaway – Aurora Data Migration (Oracle to Aurora MySQL)

AWS Aurora MySQL offers remarkable built-in security, serverless compute, continuous backups, automated multi-region application, 15 read replicas, and seamless integration with other AWS services. These reasons are enough to consider migrating data from Oracle to your AWS Aurora MySQL database. The step-by-step guide mentioned above provides an outline for you to give the Aurora data migration process a try.

However, we still recommend that you use Amazon’s official documentation on data migration to ensure a seamless migration to AWS Aurora MySQL.