Configuring Amazon Data Migration Service for Oracle Replication

Whenever I have a new task, one of the first things I do is scour the internet for documentation and blogs, usually, I find a treasure trove of information, but sometimes, the google gods just don't seem to smile on me. Regardless of what I find, I make sure I document the steps I take from beginning to end - mostly by using TeamViewer to record my working session and then reviewing that to create my documents. When it comes to migrating using Amazon's Data Migration Service (DMS), there aren't that many documents out there - or maybe I'm not as awesome of a Google Ninja as I thought I was.  Since DMS is still a fairly new service - it's a little over a year old- I didn't expect much in the way of blogs and docs and decided it would be beneficial to share my document with the world - I'm sure at least 1 person would benefit from it.

If you look through my old posts, you'll notice that I've blogged a lot about migrating an on-premise Oracle database to AWS. I've talked about a heterogenous migration from Oracle to MySQL using AWS Schema Conversion Tool (SCT) and Data Migration Service (DMS) as well as a homogenous migration of Oracle to RDS Oracle using datapump. Knowing that you could use RMAN if the target is EC2 or take a datapump export, you may be wondering, why use DMS? Well, what happens when you have a VLDB and your Network Bandwidth is not the best or what happens if you have LOB segments that need to be migrated and DataPump fails, or you just want a more hands-free approach? That's where DMS comes in to save the day! 

Amazon's Data Migration Service (DMS) is a wonderful change data capture service that uses a lot of the same database features that Oracle GoldenGate uses as a minimal downtime solution for migrating your database tables with the potential for continuous data replication to allow for high-availability on your Source Oracle database. However, since DMS only migrates schema tables - there is additional work required to migrate the other schema objects such as procedures, triggers, etc.

DMS supports all Oracle database editions for versions 10.2 and later, 11g, and up to 12.1 on an on-premises or EC2 instance, and all Oracle database editions for versions 11g (versions 11.2.0.3.v1 and later) and up to 12.1 for an Amazon RDS DB instance. 

I've detailed the steps to create a DMS task and Replication Instance using the AWS Console in a past blog post that you can read here. This post will mostly focus on the pre- and post- configuration steps required on the source and target databases to ensure a successful data migration using DMS for Oracle Databases.

To use DMS for data replication using Oracle as the Source database - you would have to

1. Create an Oracle User Account for the DMS tasks with specific permissions,
2. Ensure that Archivelog mode is enabled on the Oracle Instance, and
3. Set up supplemental logging - database level for a Full Load, Table level for continuous replication mode.

On the Target database, you would need to

1. Ensure there is enough storage
2. Create the necessary tablespace(s)
3. Create the necessary schema(s) and roles with identical privileges/permissions as the source database.
4. Turn off database backups and Multi-AZ
5. Export the schema DDL with no data using SQL Developer or any other tools that can export the schema with no data from the source database.
6. Disable triggers and foreign keys 

The steps for completing the Data Migration Task are as follows:

1. Launch and Configure your RDS Oracle Target with the appropriate instance size and a little bit more storage than your current Source Database.
2. Create a user in IAM with the necessary policies(permissions) attached 
   • AmazonDMSVPCManagementRole
   • If you will leverage CloudWatch for logging the task, the user will need the following policy as well:
     • AmazonDMSCloudWatchLogsRole
3. Create a KMS key and add the user created in the last step as a Key Administrator
4. Create a database user on the Target with the necessary permissions.
5. Create your Source Endpoint
   • If your source Oracle database is a RAC database or uses ASM, you will have to enter the asm user password along with the database user you created. For instance, the password field would have <ORACLE_DMS_USER_PASSWORD>,<ASM_USER_PASSWORD> (Password01,ASMRocks02)
   • You may also need to use some Extra connection attributes such as
     • If you would rather have the DMS user access the redo logs as a binary file instead of using the LogMiner Utility, you would add useLogminerReader=N;useBfile=Y as Extra connection Attributes.
     • If the Oracle source database is using Oracle ASM (Automatic Storage Management), the extra connection parameter needs to include the asm username and asm server address. So, you would add  asm_user=asmsnmp;asm_server=192.14.120.14/+ASM as an Extra connection Attribute.
     • If you want DMS to automatically set up supplemental logging for the Oracle database rather than run the command separately as part of your pre-configuration tasks, you would add addSupplementalLogging=Y as an Extra connection attribute.
     • If you want DMS to extract data from views, you would add exposeViews=true
   • All these extra connection attributes are separated by semi-colons(;), for instance:
     • useLogminerReader=N;useBfile=Y;asm_user=asmsnmp;asm_server=192.14.120.14/+ASM;exposeViews=true;addSupplementalLogging=Y
6. Create your Target Endpoint
   • The Target Endpoint username/password should be the schema that will own the replicated tables. The tables will be replicated to whatever schema you enter as the user - you can either create the endpoint with the schema that owns the objects or be sure to specify transformation rules to map the source tables to the target tables.
7. Create a Replication Instance.
   • Be sure to pick an instance size that has enough processing power to migrate your data efficiently - this is similar to choosing the appropriate instance type for an EC2 or RDS instance.
8. Create a Data Migration task via the terminal using AWSCLI, scripting, or the AWS Console
   • Migrate existing data - for a one-time full load
   • Migrate existing data and replicate ongoing changes - for a full load that also captures changes to the source data during the migration task and applies those changes after the full load is done.  Requires table level supplemental logging - all columns, primary key, and unique key
   • Replicate data changes only - read the archived logs and group all changes to be applied to the target. Requires table level supplemental logging - all columns, primary key, and unique key
9. Start the task.
10. Once the full load portion of the tasks is complete, enable triggers and foreign keys, compile the schema (procedures, packages, functions and triggers).
11. Turn on backups and Multi-AZ on the Target.

To export the schema without data, you can use SQL Developer

TOOLS > DATABASE EXPORT

UNCHECK EXPORT DATA

Be sure to make a note of the file path > Next

Specify the schema objects you would like to migrate > Next

Choose specific objects(optional) > Next

Review and Click Finish.

Once the export is done, connect to the Target database and run the DDL export script that was created.

To disable/enable the triggers and foreign keys

I created procedures to dynamically disable the triggers and foreign key constraints. You can execute the procedure and input the parameter ENABLE or DISABLE to have the procedure complete the task on all user triggers and Foreign Keys.

To Recompile the Schema

Once the DMS task has been completed, you will want to ensure all schema objects are compiled and valid, you can either compile each object separately, or just compile the schema using the following command:

EXEC SYS.UTL_RECOMP.RECOMP_SERIAL('SCHEMA_NAME');

Some things to Note:

• If you don't enable supplemental logging on your source before starting a Full Load (Migrate existing data) DMS task, it will run and state the task completed without any errors. This will leave you scratching your head as you will not have any data transferred and the logs won't really mention what happened. the only time you'll see an error is when you try to run a Full Load with Ongoing Replication (Migrate existing data and replicate ongoing changes) task.
• Using AWSCLI to create the replication task gives you more control over the settings, allowing you to modify virtually all the settings. Whereas, the Console only allows you to modify a few settings.

With that said and done, you should now have a fully migrated/replicated Oracle database in the cloud without any downtime on your source database! Feel free to reach out with questions and comments as you use DMS for your migration tasks.