How to migrate Oracle to AWS Aurora SQL using AWS DMS ?

Migrating a database using AWS Database Migration Service (DMS) involves 2 major steps:

Step-1 : Schema Conversion

To convert a database schema using AWS Schema Conversion Tool (AWS SCT), follow these brief steps:

1) Download and Install AWS SCT: Install AWS SCT on your local machine. Here I am using EC2 instance.

2) Connect to Source and Target Databases: Open AWS SCT and connect to your source database (e.g., Oracle). Then, connect to your target database (e.g., Amazon Aurora,).

3) Analyze and Convert the Schema: AWS SCT will analyze the source schema and provide an assessment of what can be automatically converted. Here is the migration assessment report.

4) Review and Apply Changes: Review the conversion results. For any parts of the schema that need manual changes, AWS SCT provides recommendations. Apply the converted schema to the target database.

AWS SCT analyzes the schema and creates a database migration assessment report for the conversion to Aurora MySQL. Items with a blue mark next to them cannot be directly translated from the source to the target. Items in green will be translated over from source to target. In this case, this includes the stored procedures. For each conversion issue, you can complete one of the following actions:

• Modify the objects on the source database so that AWS SCT can convert the objects to the target Aurora MySQL database.
• Instead of modifying the source schema, modify scripts that AWS SCT generates before applying the scripts on the target Aurora MySQL database.

However, for the sake of time, we skip modifying all the objects that could not be automatically converted. Instead, as an example, you will manually modify one of the stored procedures from within SCT to make it compatible with the target database.

Step-2 Data Migration

We are using AWS Database Migration Service to migrate data from the source database to the target Amazon Aurora (MySQL). Additionally, you will use AWS DMS to continually replicate database changes from the source database to the target database.

AWS DMS doesn't migrate your secondary indexes, sequences, default values, stored procedures, triggers, synonyms, views, and other schema objects that aren't specifically related to data migration. To migrate these objects to your Aurora MySQL target, we used the AWS Schema Conversion Tool in the previous section.

1. Create DMS Source and Target Endpoints

2. Create a new migration task and select the migration type as Full Load + CDC which migrates all data and syncs changes in real time during migration.

3. Specify the source tables or entire database to migrate. Set any transformation or filtering rules if needed.

4. Start the migration task and monitor the progress.

CDC (Change Data Capture)

Now you are going to simulate a transaction to the source database by updating the sport_type table. The Database Migration Service will automatically detect and replicate these changes to the target database.

1) On the EC2 instance you RDP-ed into, use Oracle SQL Developer connect to the source Oracle RDS. Here is the initial values of the sport_type table

2) Open a New Query window and execute the following statement to insert 5 new sports into the sport_type table:

3) The new records for that you added for basketball, cricket, hockey, soccer, volleyball to the sports_type table in the source database have been replicated to your dms_sample_ database. You can further investigate the number of inserts, deletes, updates, and DDLs by viewing the Table statistics of your Database migration tasks in AWS console.