Some CDC Rows From Postgres to Oracle Fail With Wrong Date Format

Yes, the ‘Wrong Date Format’ error in heterogeneous migration is a result of incompatible or unsupported Date format at the Target database (Oracle).

The workaround here can be using the Transformational rule parameter "rule-action": "change-data-type" to transform the source datatype into the desired/supported datatype at the target during migration.

Example:

{
    "rule-type": "transformation",
    "rule-id": "1",
    "rule-name": "RuleName 1",
    "rule-action": "change-data-type",
    "rule-target": "column",
    "object-locator": {
        "schema-name": "dbo",
        "table-name": "dms",
        "column-name": "SALE_AMOUNT"
    },
    "data-type": {
        "type": "int8"
    }
}

Fortunately, you do not have to include transformational rule for every affected column if you are aware of the schema, tables and datatype that requires this transformation.

You can use the ‘object-locator’ parameter to identify the columns by specifying their datatype that requires the transformation.

Example:

"object-locator": { 
    "schema-name": "dbo", 
    "table-name": "dms", 
    "column-name": "%", 
    "data-type": "int2"
},
"data-type": { 
    "type": "int8" }

Here, all the columns with data-type int2 at the source table dbo.dms would be transformed to int8 datatype. Likewise, you can make use of ‘%’ operator for “table-name” parameter if you have to apply this rule for multiple tables.

Source: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Transformations.html#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Transformations.Examples