How do I use AWS DMS mapping rules to migrate an Oracle schema to PostgreSQL in lowercase?

Short description

Oracle stores metadata in its data dictionary in uppercase, and PostgreSQL stores it in lowercase. With either database, it's possible to override these default formats by encasing object names in quotes when you create them. However, this isn't a best practice, because doing so can cause unexpected behavior. Because the AWS Database Migration Service (AWS DMS) can't autocorrect metadata storage formatting, AWS DMS creates the objects in quotes. As a workaround, you can override AWS DMS quotes with mapping rules. For more information, see Use the AWS Schema Conversion Tool (AWS SCT) to convert the Oracle schema to PostgreSQL.

Resolution

This example migrates the Oracle SCOTT schema to PostgreSQL. If you have an on-premises Oracle database, you can create this schema by running the following command:

$ORACLE_HOME/rdbms/admin/utlsampl.sql

If you have an Amazon Relational Database Service (Amazon RDS) DB instance that's running Oracle, search online for a similar script.

This script creates four tables under the SCOTT schema: EMP, DEPT, BONUS, and SALGRADE. Collectively, they contain a small number of rows.

Verify that no tables in the Oracle database were created with lowercase letters in the table or column names. If the application is accustomed to mixed-case table and column names, it's a best practice to allow AWS DMS to replicate the tables exactly as it finds them. For this reason, don't use these mapping rules.

oracle> SELECT table_name from dba_tables where owner='SCOTT' and table_name <> upper(table_name);

oracle> SELECT table_name, column_name from dba_tab_columns where owner='SCOTT' and column_name <> upper(column_name);

Create endpoints and a replication instance:

1.    Use the AWS DMS console or the AWS Command Line Interface (AWS CLI) to create the replication instance and then create the source and target endpoints.

2.    Create a task. Be sure to choose Enable logging.

3.    In the Table mappings view, select the JSON tab, and then choose Enable JSON editing. Then, use code similar to the following example to build the transformation rules for schema, tables, and column case handling. For more information, see Using table mapping to specify task settings.

{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "select-scott",
      "object-locator": {
        "schema-name": "SCOTT",
        "table-name": "%"
      },
      "rule-action": "include"
    },
    {
      "rule-type": "transformation",
      "rule-id": "2",
      "rule-name": "convert-schemas-to-lower",
      "rule-action": "convert-lowercase",
      "rule-target": "schema",
      "object-locator": {
        "schema-name": "%"
      }
    },
    {
      "rule-type": "transformation",
      "rule-id": "3",
      "rule-name": "convert-tables-to-lower",
      "rule-action": "convert-lowercase",
      "rule-target": "table",
      "object-locator": {
        "schema-name": "%",
        "table-name": "%"
      }
    },
    {
      "rule-type": "transformation",
      "rule-id": "4",
      "rule-name": "convert-columns-to-lowercase",
      "rule-action": "convert-lowercase",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "%",
        "table-name": "%",
        "column-name": "%"
      }
    }
  ]
}

4.    Choose Create task.

Note: You can also use a rename schema rule instead of converting the schema to lowercase.

After the task is created, verify that all the objects are created in lowercase in PostgreSQL:

postgres> select table_name from information_schema.tables where table_schema='scott';
 table_name
------------
 bonus
 dept
 salgrade
 emp
(4 rows)


postgres> select table_name, column_name from information_schema.columns  where table_schema='scott' order by table_name;
 table_name | column_name
------------+-------------
 bonus      | ename
 bonus      | job
 bonus      | sal
 bonus      | comm
 dept       | deptno
 dept       | dname
 dept       | loc
 emp        | deptno
 emp        | hiredate
 emp        | sal
 emp        | comm
 emp        | empno
 emp        | ename
 emp        | job
 emp        | mgr
 salgrade   | losal
 salgrade   | hisal
 salgrade   | grade
(18 rows)

Related information

AWS Database Migration Service step-by-step walkthroughs

Migrate an on-premises Oracle database to Amazon RDS for PostgreSQL using an Oracle bystander and AWS DMS