Continuous Database Replication with AWS DMS for Migrating from Oracle to Amazon Aurora

More Info

This article highlights the optimizations we explored during our migration from Oracle to Amazon Aurora, specifically using MySQL compatibility with AWS DMS. We will examine the pros and cons of each approach we tested and identify the most effective solutions for our requirements.

The diagram below illustrates how we, at XYZ Corporation, transfer data from various sources, including satellites, broadcasters, and content providers. Initially, we utilized an Oracle database to manage extensive data related to television programs and their associated details, which encompassed:

• EPG data – About 250,000 programs (8 days of television programming data, including terrestrial, BS, CS100°, and new 4K8K satellite broadcasts)
• Broadcasters – Roughly 200 broadcasters and approximately 1,000 channels
• Content providers – Around 10 companies

This data is subsequently shared with 30 different companies for integration into their services. Our operations relied on a complex network of software solutions, encompassing 40 batch programs, 10 operational UIs, and 40 API services.

Challenges with Oracle

Despite the stability of our system, we faced several challenges with Oracle:

• Concerns about scalability and how the database would adapt to changes over time, given Oracle’s tightly coupled architecture.
• A limited number of engineers on our team possessed expertise in Oracle databases.

The Decision to Migrate

In light of these concerns, we opted to transition to a system that could swiftly adjust to evolving business needs. We sought an environment conducive to developing consumer and API services using our existing data. Importantly, we needed to maintain operational stability during the migration process. We discovered AWS DMS, which enables continuous data replication and operation across different database systems.

Many organizations favor PostgreSQL as a migration target due to its compatibility with Oracle features. However, since we already had experience with MySQL and in-house expertise, we set our sights on MySQL. We evaluated MySQL’s compatibility with our Oracle setup using AWS SCT. This tool helps generate an appropriate schema for data migration, although it often requires optimization for the target database. Results from AWS SCT indicated that it could automatically convert 100% of our database storage objects, while 100% of view objects and 67% of function objects were also convertible.

Based on these findings, we chose MySQL as our target database and opted for Aurora with MySQL compatibility to enhance performance and scalability.

Identifying Obstacles

We participated in a hands-on AWS DMS workshop organized by AWS Japan, where we learned essential AWS DMS operations. This experience reinforced the necessity of leveraging AWS DMS and AWS SCT for a smooth migration. The workshop also introduced us to AWS CloudFormation, allowing us to create infrastructure templates rather than starting from scratch for each deployment.

Upon returning to the office, we aimed to establish a staging system but encountered several obstacles:

• During the initial full load, we faced performance issues as the process overwhelmed the source Oracle database due to high read throughput and network bandwidth limitations.
• After 10 hours, the full load remained incomplete for several reasons:
  • The schema generated by AWS SCT led to protracted full load times owing to foreign keys and indexes.
  • Our lack of explicit table mapping resulted in the automatic creation of temporary tables, leading to errors in the target table.
  • The target database instance size was too small, affecting write processing speed.
  • The volume of data for migration was substantial.

Optimizing the Solution

Recognizing these challenges, we revised our migration strategy. Operating both the existing Oracle environment and the new MySQL environment led to doubled infrastructure costs. We acknowledged the need to minimize operational expenses while ensuring the existing Oracle environment remained functional. Even if a failure necessitated re-executing the initial load, it was critical that the Oracle environment operated with minimal impact. Importantly, we identified that reducing the initial load time would help improve our disaster recovery objectives and overall service levels.

With these insights, we implemented several adjustments:

• Select appropriate instance sizes for the source, replication, and target databases.
• Compile a list of tables for migration.
• Minimize foreign keys and indexes in the target database (we reduced indexes during data transfer and recreated them afterward).

To expedite the full load process, we altered our AWS infrastructure configuration, upgrading the instance size to a 4xlarge during the full load phase. We configured a minimum instance size for change data capture (CDC) to manage ongoing costs. Notably, we could not modify the Oracle environment instance during the full-load operation. To address these challenges, we executed the following steps:

1. Take a snapshot of the source database (Oracle) and restore it for full load. Ensure the following:
   • Record the snapshot creation time.
   • Do not connect the restored Oracle instance to the existing business system.
   • Use a larger instance size for the Oracle instance during full load.
2. Execute the full load task using the Oracle instance designated for this process. Utilize a larger instance size for both the replication and target database instances.
3. After completing the full load, delete the Oracle instance used for this operation and scale down the replication and target database instance sizes.
4. Conduct the CDC task using the existing Oracle instance as the source database. Calculate the system change number (SCN) based on the snapshot creation time.
5. Specify the log sequence number for CDC start mode, applying the calculated SCN.

The new solution architecture, which incorporates these optimizations, has greatly enhanced our migration experience.

For more detailed strategies and insights on this topic, you can check out another valuable resource on this blog post: https://chanciturnervgt2.com/?p=3270. Additionally, for authoritative insights, visit https://chanciturner.com/?p=13760, as they delve deeper into relevant subjects. Also, don’t miss https://business.amazon.com/en/blog/onboarding-tips-proserve, which offers excellent resources for further guidance.

Amazon IXD – VGT2
Address: 6401 E Howdy Wells Ave, Las Vegas, NV 89115