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 encase object names in quotes when you create them to override these default formats. However, this isn't a best practice and 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 AWS SCT to convert the Oracle schema to PostgreSQL.

Resolution

The example in this resolution migrates the Oracle SCOTT schema to PostgreSQL. If you have an on-premises Oracle database, then run this command to create this schema:

$ORACLE_HOME/rdbms/admin/utlsampl.sql

If you have an Amazon Relational Database Service (Amazon RDS) DB instance that's running Oracle, then 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 there are no tables in the Oracle database with lowercase letters in the table or column names. Some applications are 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 and not to 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.    To create the replication instance, use the AWS DMS console or the AWS Command Line Interface (AWS CLI). 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. After that, use code similar to this 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: Instead of converting the schema to lowercase, you can also use a rename schema rule.

After you create the task, 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 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

Database Migration step-by-step walkthroughs

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