Oracle Migration Automated – How to Automate Generation of Selection and Transformation Rules for AWS Data Migration Service

Akm Raziul Islam is a database architect at Amazon Web Services.

You can use AWS Data Migration Service (AWS DMS) to copy data from multiple schemas in an Oracle source to a single Amazon RDS for Oracle target. You can also migrate data to different schemas on a target. But to do this, you need to use schema transformation rules on the AWS DMS table mappings.

If you are migrating multiple Oracle schemas, it can be challenging to manually create and track multiple selection and transformation rules. This post describes how you can use simple dynamic SQL in Oracle to generate custom JSON selection and transformation rules for all your user schemas.

Creating selection and transformation JSON rules
For this example, an Oracle target endpoint uses the RDSMASTER user, and the source endpoint uses DMS_USER, so you must create a transformation rule as follows. The rule ensures that the DMS_USER schema is targeted and the RDSMASTER schema is not created.

{
"rule-type": "selection",   
"rule-id": "1",         
"rule-name": "1",      
"object-locator": {     
 "schema-name": "DMS_USER",   
"table-name": "%"      
  },         
 "rule-action": "include"
},
  {
   "rule-type": "transformation",
   "rule-id": "2",
   "rule-name": "2",
   "rule-action": "rename",
   "rule-target": "schema",
   "object-locator": {
   "schema-name": "DMS_USER"
},
"value": "DMS_USER"
}

You can do the same thing in the AWS DMS console:

If the transformation rules are not created for the same schema, then by default, DMS_USER from the source schema and all other selected schemas for migration are copied under the target endpoint user RDSMASTER. In AWS DMS, you can create those selection and transformation rules using the console/CLI and JSON. But it can be challenging to create and track multiple selection and transformation rules for multiple Oracle schema migrations.

For example, if you have to migrate roughly 300+ Oracle schemas from each database, there would be about 300+ schemas (300 selection + 300 transformation rules). It’s pretty challenging to input every single schema for each JSON selection and transformation rule. A better approach is to use simple dynamic SQL in Oracle that generates JSON selection and transformation rules for all the user schemas.

Generating JSON rules for multiple schemas
Here is the dynamic SQL that you can run on the Oracle source to generate the JSON selection and transformation rules:

drop sequence my_number1;
drop sequence my_number2;
create sequence my_number1 start with 1 increment by 2;
create sequence my_number2 start with 2 increment by 2 ;
select '
        {
            "rule-type": "selection",
            "rule-id": "'||my_number1.nextval||'",
            "rule-name": "'||my_number1.currval||'",
            "object-locator": {
                "schema-name": "'||username||'",
                "table-name": "%"
            },
            "rule-action": "include"
        },
        {
            "rule-type": "transformation",
            "rule-id": "'||my_number2.nextval||'",
            "rule-name": "'||my_number2.currval||'",
            "rule-action": "rename",
            "rule-target": "schema",
            "object-locator": {
                "schema-name": "'||username||'"
            },
            "value": "'||username||'"
        },' FROM DBA_USERS where default_tablespace = 'USERS' and ORACLE_MAINTAINED ='N' ;

A few things to note about this code: I ran the code using SQL Developer connected to an Oracle 12c instance. Oracle 12c comes with an ORACLE_MAINTAINED column that isn’t available in other versions. This column makes sure that you consider only non-SYSTEM Schemas. If the source Oracle version is earlier than 12x, simply skip the following part of the code:

and ORACLE_MAINTAINED=’N’;

And replace it with something like this:

FROM DBA_USERS where default_tablespace='USERS' and USERNAME NOT in ('DIP','AUDSYS','GSMUSER','XS$NULL','GSMCATUSER');

You can add more schemas in the USERNAME field that you don’t want to be included in the JSON list.

To generate the transformation rules:

1. Run the code from SQL Developer (press F5 or choose Run Script).
2. Scroll down to the end of the line of the generated code. It should contain a comma (,) after the curly brace “}”. Go ahead and delete the comma and leave the curly brace.
3. Copy the rest of the JSON code (starts from the curly brace before the first “rule-type” to the last curly brace in the output window), and paste it under the following code:

   {
       "rules": [
    
   ----- code goes here
   ]
   }
   

4. Copy the code using any text editor (e.g., TextWrangler, Notepad++), and paste it in the AWS DMS task Table mappings on the JSON tab (making sure that Enable JSON editing is selected).
   After you copy the JSON code, if you choose the Guided tab, you can see that all the schemas are auto-populated for both selection and transformation rules.

Summary
You can use simple dynamic SQL in Oracle to generate AWS DMS custom selection and transformation rules for filtering and table mappings on the target. For more information about AWS DMS selection and transformation table mapping, see the AWS DMS documentation.