How to Migrate Your Oracle Database to Amazon Aurora

Ed Murray is a manager at Amazon Web Services.

This blog post gives you a quick overview of how you can use the AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS) to facilitate and simplify migrating your commercial database to Amazon Aurora. In this case, we focus on migrating from Oracle to the MySQL-compatible Amazon Aurora.

Changing database engines can be daunting. However, the value proposition of a highly scalable, cost-effective fully managed service such as Amazon Aurora can make the challenge worth it—especially when there are tools to simplify the process. When migrating your database from one engine to another, there are two major things to consider: the conversion of the schema and code objects, and the migration and conversion of the data itself. Fortunately, AWS has tools to facilitate both the conversion and migration of your database.

The AWS Schema Conversion Tool helps simplify heterogeneous database migrations by automatically converting your source database schema and a majority of the custom code to a format compatible with your new target database. The custom code that the tool converts includes views, stored procedures, and functions. Any code that the tool cannot convert automatically is clearly marked so that you can convert it yourself. The AWS Database Migration Service helps you migrate your data easily and securely with minimal downtime.

Great! So where do we begin?

Working with AWS SCT
Usually, the first step in every migration is an assessment of feasibility and effort. You can use AWS SCT to generate a high-level overview of the effort required to convert your Oracle database to Aurora. The SCT runs on a number of operating systems. For the purposes of this blog, we’ll run the tool on Windows. To download a copy of the SCT, see the Installing and Updating the AWS Schema Conversion Tool documentation. To find overall documentation for SCT, start with What Is the AWS Schema Conversion Tool?

Although this blog post won’t cover the installation and configuration of the SCT, it’s important to point out that you need to install drivers for Oracle and MySQL to connect the SCT to your source and target databases. After connecting to your Oracle source database, you can right-click on any given schema and generate an assessment report. The assessment report tells you at a very high level how much of the schema can be converted automatically from Oracle to Aurora and the work that is left after conversion. You can find an example report following.

In addition to the assessment report, the SCT also tells you how exactly each object was converted. If an object can’t be converted, the SCT tells you why and give you hints on how you can remedy the situation.

In the likely event that 100% of your schema doesn’t convert from Oracle to Aurora, you can remedy the situation in a few ways:

• Modify objects on your source Oracle database so the SCT can better convert them to Aurora.
• Convert the schema as-is and modify the scripts generated by the SCT before applying them to your Aurora database.
• Ignore the inconvertible objects and replace or ignore them on the target. For example, suppose you have a function that calls the sys.dbms_random package in Oracle. This package doesn’t exist in Aurora. To remedy this, you can do the following:
  • Push the generation of the random value into the application code and pass it into the function as a parameter. You can choose to make this modification on your source database before the conversion, or on your target after the conversion.
  • Modify the code generated by the SCT to use the RAND() function, which is available in MySQL, and apply the new code to your Aurora database.

As another example, suppose you are using sequences in Oracle to populate some of your unique identifiers. Aurora doesn’t support sequences, so to remedy this you can do the following:

• Use the auto-increment feature of Aurora to populate your unique identifiers automatically. If you go this route, you’ll likely want to create a script to modify your target tables after you create the schema in your Aurora database.
• Create an alternative method for generating unique identifiers (using a function or something similar), and replace your references to the sequences with your new function. You can do this on your Oracle source before the conversion or in your Aurora database after the conversion.
• You might need to use both techniques.

In general, a good approach to using the SCT as part of your migration should include the following:

• Generate the SCT assessment report and use it to develop a plan to close any gaps in the conversion. If you have multiple systems that are candidates for migration, use the SCT assessment report to help determine which system you should tackle first.
• Review the action items and determine an appropriate remedy for each item that fails in the conversion.
• You will likely want to iterate on this process, using it in conjunction with the AWS Database Migration Service to load data into your new schema while you test your application against your new Aurora database.

Which brings us to AWS DMS!

Working with AWS DMS
AWS DMS can be used to load data from your Oracle source database into your new Aurora target database. The great thing about DMS is that in addition to loading your bulk data, it captures and applies ongoing transactions. It brings and keeps your Oracle source and Aurora target databases in sync until you’re ready to cut over. This approach can significantly reduce the outage time required to complete your migration. Any DMS migration includes the following elements: a source endpoint, Oracle; a target endpoint, Aurora; a replication server; and a task.

When migrating from Oracle to Aurora, you’ll want to configure your task to migrate existing data and replicate ongoing changes. Doing this directs DMS to capture transactions while it migrates the bulk data. Once the bulk data has been loaded, DMS begins applying captured transactions bringing the Oracle and Aurora databases in sync. When you are ready to cut over to Aurora, you simply stop the application, let DMS apply the few remaining transactions, and start the application pointing to your new Aurora database.

There are a few things to consider when using DMS to migrate from Oracle to Aurora:

Supplemental logging. For DMS to capture changes from your Oracle source, you need to enable supplemental logging. Detailed instructions can be found in the DMS documentation.

The three phases of DMS. DMS goes through three phases when migrating data and replicating ongoing changes:

• Bulk load: During the bulk load phase of a migration, DMS loads your tables individually n tables at a time; by default, n = 8. You can configure this number by using the DMS Management Console or the AWS CLI.
• Application of cached transactions: During the bulk load phase, DMS captures changes to your source database. Once the bulk load is completed for a table, DMS applies any cached changes to that table as soon as possible, as if they were part of the bulk load.
• Transactional apply: Once the bulk load is complete for all tables, DMS will begin applying captured changes as transactions, rather than as single table updates.

Secondary indexes. In some situations, you might want to remove secondary indexes during the bulk load phase of DMS for performance reasons. If you do choose to remove some or all of your secondary indexes during the bulk phase, you probably should pause the migration and add them back during the transactional apply phase. You can safely pause your migration after the full load is complete for all tables.

Foreign keys, triggers, and so on. Because bulk load is done table by table, foreign keys in your Aurora target might be violated during the bulk load and cached transaction phases of your migration. You can disable foreign key checks by adding the following as an extra connection attribute of your target Aurora endpoint definition: initstmt=SET FOREIGN_KEY_CHECKS=0. In general, you should develop a strategy for dealing with anything that might be disrupted or negatively affected by bulk loading of data. For example, to avoid issues, you may want to postpone the installation of triggers to the cut-over phase of the migration.

Data types. When migrating to a new database engine, it’s critical to understand which data types are supported and how the source data types are transformed into the new target data types. For this example, you should check Oracle source datatypes and Aurora target datatypes in the DMS documentation.

Performance: The overall performance of your migration can vary depending on the amount, type, and distribution of data in your Oracle source.  The Database Migration Services Best Practices whitepaper has some good recommendations for optimizing the performance of your migration.

To recap the process:

1. Use the SCT assessment report to get an overview of the task at hand. If you have multiple candidates for migration to Aurora, this report can help you decide which one to tackle first.
2. Practice generating your target schema and loading it using DMS to flush out any migration steps that might be required before and after processing.
3. Test your application on your target system to ensure it will work as expected in your new environment. Try to test your application in a configuration that resembles your production configuration, including load, network configuration, and so on.
4. Practice the actual migration including generating your schema, loading your data, applying postprocessing steps, bringing the target system in sync with the source, and any cut-over steps you might require.
5. Keep in mind that neither the SCT nor DMS require you to migrate your entire system in one fell swoop. You can use these tools to effectively migrate and rearchitect your system piecemeal if you want to.

Before you begin your actual migration, we recommend you thoroughly read the documentation for both the SCT and DMS. We also recommend you read the step-by-step walkthrough and the Database Migration Services Best Practices whitepaper.

If you’d like to use our sample database to get a feel for using the tools, you can find it in the AWS GitHub repository.

Although this blog post is not intended to outline all possible steps or considerations that might be required for your particular situation, it should give you a good idea how you can use the SCT and DMS to relieve you of the shackles of your proprietary Oracle database. Good luck and happy migrating!