ORACLE AND AWS RDS FOR ORACLE TIP 3: HOW TO CREATE A TASK AT DMS AND START YOUR MIGRATION

This post is part of the first post “ORACLE AND AWS RDS FOR ORACLE TIP: HOW TO CONFIGURE YOUR ORACLE ON-PREM TO BE MIGRATED TO AWS RDS ORACLE? A GUIDE TO USE AMAZON DATA MIGRATION SERVICE (DMS)” where I briefly explained how to configure your Oracle database On-Prem to be migrated to an AWS RDS Oracle and second post “ORACLE AND AWS RDS FOR ORACLE TIP: HOW TO CONFIGURE YOUR ORACLE AWS RDS TO RECEIVE DATA FROM AN ORACLE ON-PREM? A GUIDE TO USE AMAZON DATA MIGRATION SERVICE (DMS)” where I explained how to configure your AWS RDS Oracle after to receive the data from the DMS.

The first thing to be able to create a task at DMS services is to have a DMS replication instance. This instance will receive the data from the source Oracle database (source endpoint) and it will transfer to the target AWS RDS Oracle (target endpoint). Then to create the DMS replication instance you just have to go through the AWS console following these steps “Services” -> “AWS DMS” -> “Replication instance” -> button “Create replication instance” and then you can start the configuration of your replication instance as below:

Regarding all the screens showed above, when it comes to payment I highlighted attention to the class, engine version , allocated storage and if you are willing to use the Amazon High-Availability called  Multi-AZ deployment. The other information regarding network configuration may be provider by your Networking or Cloud team.

After created you will see your replication instance as below (in this case I have created 2 DMS instances used in different migrations):

After you create the replication instance you do need to create the 2 endpoints. Why 2? One will be the source endpoint, basically your Oracle On-Prem and the other one will be the target endpoint where it is your AWS RDS Oracle instance. Among the setup of the endpoints you must choose the replication instance that you want to be use to transfer the data between the two endpoints. Then to create the 2 endpoints you have to go through these steps “Services” -> “AWS DMS” -> “Resource management” -> Endpoints-> button “Create endpoint”.

So let’s start the creation of the source endpoint which in our case is our Oracle database On-Prem:

While creating the Endpoints the DMS is very intuitive and has already an option to choose between source and target. Besides this you also must provide some ordinary information as server name (sometimes IP), port, and the username and password that you have created to connect in your source instance. If you check in the first post “ORACLE AND AWS RDS FOR ORACLE TIP: HOW TO CONFIGURE YOUR ORACLE ON-PREM TO BE MIGRATED TO AWS RDS ORACLE? A GUIDE TO USE AMAZON DATA MIGRATION SERVICE (DMS)“, the user that I chose as example is called MIGUSERSOURCE. Besides these, other information are important in the screen above as for instance the “Endpoint-specific settings” value when I have put the value useLogminerReader=Y;useBfile=Y;archivedLogDestId=1;, which means that I have enabled the use of Oracle Log Miner, Bfile and configured the archive log destination ID as 1 once I do have just one archive area. However, in case if you have multiplex archive area you have to configure for the correct value.

OK, now that we have a glimpse about how the source endpoint may be, we are ready to start the configuration of the target endpoint what it will be responsible to receive the data from the DMS and apply in the AWS RDS Oracle instance:

The information that should be given are alike of the source endpoint. However, as in this case our target is an RDS AWS Oracle instance we could select the option “Select RDS DB instance” what would bring to you all the existed RDS instances in your region. Beside this information the Endpoint-specific seetings are also different once for my specific case I have chosen useDirectPathFullLoad=Y;charLengthSemantics=CHAR but you can select that one who better attend the requirement of the data in your database. For more information about the setting in the endpoint check the Amazon’s documentation. Another interesting point regarding the two endpoint is the possibility to test the connection between both of them and the created DMS replication instance what can save you to waste time performing additional troubleshooting when starts your DMS task. After the creation you will see your Endpoints like this:

Ok, now that we have both the Source and Target Endpoints configured is time to create the DMS task.

Once you have the 2 Endpoints configured as informed above, to create the DMS task you have to follow these steps at the AWS console : AWS DMS -> Conversion & migration -> Database migration tasks -> Button “Create Task”.

Regarding all the information above where you must provide the name of your task in the field “Task identifier” and the replication instance , source and target endpoints that you want to use, you have to choose the type of your migration. Hence you have 3 types of migration “Migrate existing data”, “Migrating existing data and replicate ongoing changes” and “Replicate data changes only”. All the information regarding this configuration is showed in the picture below where I personally prefer to setup my tasks as “Migrating existing data and replicate ongoing changes” where the task will perform the FULL LOAD + CDC (CHANGE DATA CAPTURE) and I can occasionally choose to stop the task automatically when the FULL LOAD is finished and create the indexes in the target to speed up the CDC.

Then the next step is setup the “Task settings” where the first option is setup up the “CDC stop mode” where you can also enable the creation of a recovery table on the Target database in case if any recovery is needed in this step.

As my mentioned my preferences before, when you choose the task to perform FULL LOAD + CDC you have the option to decide when the CDC are going to stop:

As I usually create the tables manually in the target database using DDL extracted from the source, I choose the option “Truncate” where the DMS just removes the data from the table. All the information regarding the options you can see below:

The following step in my understanding of this configuration is basically this: “Dont stop” if your database does not have a constraint, “Stop before applying cached changes” if your database has secondary indexes and “Stop after applying cached changes” if your database has constraint :

The next step is if you want to include LOBS or not. Depending of the type of data that you are using is better to use the option “Limited LOB mode” with the value of the biggest LOB column in your database because for instance if you have XMLTYPE data type DMS has a limitation and it won’t work with “Full LOB mode” and your task will fail with the following error “ORA-24318: call not allowed for scalar data types”. So below you can see the options that you can choose and their description according Amazon’s console:

So to finish the “Task Settings” field, I strong advise you enable the “Enable validation” option, where the DMS will check if the data were migrated completed and also the “CloudWatch logs” that it will help you to perform any troubleshooting in case if your task failed. Also I would like to remember that even though that you enable the “CloudWatch logs” is important you have the following roles in your account:

For Cloudwatch access to see DMS logs :

• You policy must have these option:
  “logs:DescribeLogGroups”,
  “logs:DescribeLogStreams”,
  “logs:FilterLogEvents”,
  “logs:GetLogEvents” – DMS IAM permissions https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Security.IAMPermissions.html

• To use the features of AWS DMS, three IAM roles are required in AWS account.
  -dms-vpc-role with policy- AmazonDMSVPCManagementRole
  -dms-cloudwatch-logs-role with policy- AmazonDMSCloudWatchLogsRole and
  -dms-access-for-endpoint with policy- AmazonDMSRedshiftS3Role (not mandatory in this case as we are not using Redshift).

Below you can see the full option that are required to complete the “Task settings” option

Then you have to configure the “Table mappings” which is a very important configuration. Once it is in this section that you decide the user that you want to be migrated . You can either choose the “GUIDED UI” and inform the values of your use or choose the option “JSON editor” and inform the value of your Selection and Transformation rule by code where the Selection rule is responsible to gather the information of your use in your Source database and the Transformation rule is responsible to change the user in your Target database. If you do not setup the Transformation rule the DMS will create all the migrated data into the user owner of the RDS instance. So below you have example of both case:

JSON EDITOR: Example of migration the user BRUNORSTECH to other user with the same name in the Target database:

{
"rules": [
{
"rule-type": "transformation",
"rule-id": "1",
"rule-name": "1",
"rule-target": "schema",
"object-locator": {
"schema-name": "BRUNORSTECH",
"table-name": "%"
},
"rule-action": "rename",
"value": "BRUNORSTECH",
"old-value": null
},
{
"rule-type": "selection",
"rule-id": "2",
"rule-name": "2",
"object-locator": {
"schema-name": "BRUNORSTECH",
"table-name": "%"
},
"rule-action": "include",
"filters": []
}
]
}

Guided UI: Example of migration the user BRUNORSTECH to other user with the same name in the Target database:

Additionally you can create a control table in the “Advanced task settings”:

Then you are ready to configure the “Full load tuning settings” and then create your task:

At this moment you can start your task right away after the creation or manually afterwards. The next post will be about the DMS limitation and other additional troubleshooting in the migration phase.

References:

Working with an AWS DMS Replication Instance (https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReplicationInstance.html)
Working with AWS DMS Endpoints (https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Endpoints.html)
Working with AWS DMS Tasks (https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.html)
Creating a Task (https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.Creating.html)
Using Table Mapping to Specify Task Settings (https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.html)