Multi-Stage Migration Implementation Plan

A plan for moving data from a self-managed Oracle database into PostgreSQL on AWS where the final table needs columns from a second PostgreSQL database as well. DMS cannot join across databases, so the join happens in PostgreSQL — through a foreign data wrapper — and a second DMS task loads the resulting view into the target table.

The names below (staging_table, final_table, SOURCE_TABLE) are placeholders from one engagement; substitute real ones.

Architecture

┌───────────────┐         ┌─────────────────────────────────────────────────────┐
│               │         │                      AWS                             │
│  On-Premises  │         │                                                      │
│     Oracle    │─────┐   │  ┌──────────────┐        ┌──────────────┐           │
│   Database    │     │   │  │  PostgreSQL  │        │  PostgreSQL  │           │
│ (Source Table)│     │   │  │  Database 1  │        │  Database 2  │           │
└───────────────┘     │   │  │              │        │              │           │
                      │   │  │ ┌──────────┐ │        │ ┌──────────┐ │           │
                      └───┼──►│  Staging  │◄├────────┼─┤ Existing │ │           │
                          │  │ │  Table   │ │        │ │  Table   │ │           │
                 DMS Task 1  │ └────┬─────┘ │        │ └──────────┘ │           │
                          │  │      │       │        │              │           │
                          │  │      │       │        └──────────────┘           │
                          │  │      │       │            ▲                      │
                          │  │      │       │            │                      │
                          │  │      ▼       │            │                      │
                          │  │ ┌──────────┐ │            │                      │
                          │  │ │  Cross-  │ │            │                      │
                          │  │ │ Database │─┼────────────┘                      │
                          │  │ │   View   │ │       FDW Connection              │
                          │  │ └────┬─────┘ │                                   │
                          │  │      │       │                                   │
                          │  │      │       │                                   │
                          │  │      ▼       │                                   │
                          │  │ ┌──────────┐ │                                   │
                          │  │ │  Target  │ │                                   │
                          │  │ │  Table   │◄┼───────────────────────────────────┘
                          │  │ └──────────┘ │        DMS Task 2
                          │  │              │
                          │  └──────────────┘
                          │
                          └────────────────────────────────────────────┘
                                           AWS DMS

Step 1: Oracle to PostgreSQL staging

1.1 Prepare the Oracle source

DMS needs a dedicated account with read access to the tables being migrated and to the data dictionary and redo-log views it uses for CDC.

CREATE USER dms_user IDENTIFIED BY "&password";

GRANT CREATE SESSION TO dms_user;
GRANT SELECT ANY TRANSACTION TO dms_user;

GRANT SELECT ON V_$ARCHIVED_LOG TO dms_user;
GRANT SELECT ON V_$LOG TO dms_user;
GRANT SELECT ON V_$LOGFILE TO dms_user;
GRANT SELECT ON V_$DATABASE TO dms_user;
GRANT SELECT ON V_$THREAD TO dms_user;
GRANT SELECT ON V_$PARAMETER TO dms_user;
GRANT SELECT ON V_$NLS_PARAMETERS TO dms_user;
GRANT SELECT ON V_$TIMEZONE_NAMES TO dms_user;
GRANT SELECT ON V_$TRANSACTION TO dms_user;
GRANT SELECT ON ALL_INDEXES TO dms_user;
GRANT SELECT ON ALL_OBJECTS TO dms_user;
GRANT SELECT ON ALL_TABLES TO dms_user;
GRANT SELECT ON ALL_USERS TO dms_user;
GRANT SELECT ON ALL_CATALOG TO dms_user;
GRANT SELECT ON ALL_CONSTRAINTS TO dms_user;
GRANT SELECT ON ALL_CONS_COLUMNS TO dms_user;
GRANT SELECT ON ALL_TAB_COLS TO dms_user;
GRANT SELECT ON ALL_IND_COLUMNS TO dms_user;
GRANT SELECT ON ALL_ENCRYPTED_COLUMNS TO dms_user;
GRANT SELECT ON ALL_LOG_GROUPS TO dms_user;
GRANT SELECT ON ALL_TAB_PARTITIONS TO dms_user;
GRANT SELECT ON SYS.DBA_REGISTRY TO dms_user;
GRANT SELECT ON SYS.OBJ$ TO dms_user;
GRANT SELECT ON DBA_TABLESPACES TO dms_user;
GRANT SELECT ON DBA_OBJECTS TO dms_user;
GRANT SELECT ON SYS.ENC$ TO dms_user;

-- Grant per table rather than SELECT ANY TABLE
GRANT SELECT ON SOURCE_SCHEMA.SOURCE_TABLE TO dms_user;

SELECT ANY TABLE appears in many DMS guides and is worth avoiding. It is an estate-wide read over every table in the database, including ones outside the migration scope, and it survives the migration unless somebody remembers to revoke it. Grant SELECT on the specific tables instead; the list is finite and it is the list the table mappings already contain. SELECT ANY TRANSACTION is genuinely required for CDC and has no per-object equivalent.

Enable supplemental logging so that changes carry enough context to be applied on the target:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE SOURCE_SCHEMA.SOURCE_TABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

1.2 Prepare the PostgreSQL target

CREATE SCHEMA staging;
CREATE SCHEMA target;

CREATE USER dms_user WITH PASSWORD :'dms_password';

GRANT USAGE ON SCHEMA staging TO dms_user;
GRANT USAGE ON SCHEMA target  TO dms_user;
GRANT ALL PRIVILEGES ON ALL TABLES    IN SCHEMA staging TO dms_user;
GRANT ALL PRIVILEGES ON ALL TABLES    IN SCHEMA target  TO dms_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA staging TO dms_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA target  TO dms_user;

CREATE EXTENSION postgres_fdw;

Pass the password as a psql variable (psql -v dms_password="$(...)") sourced from the secret store, so it is never a literal in the script.

1.3 Create the DMS endpoints, instance and task

Endpoints take their credentials from Secrets Manager rather than from the command line. A password given as --password is visible to any local ps while the command runs and lands in shell history afterwards.

aws dms create-endpoint \
  --endpoint-identifier oracle-source-endpoint \
  --endpoint-type source \
  --engine-name oracle \
  --server-name oracle-server.example.com \
  --port 1521 \
  --database-name YOUR_SID \
  --secrets-manager-secret-id "prod/oracle/dms_user" \
  --secrets-manager-access-role-arn "arn:aws:iam::account:role/dms-secrets-access" \
  --extra-connection-attributes "useLogminerReader=N;useBfile=Y"

aws dms create-endpoint \
  --endpoint-identifier postgresql-target-endpoint \
  --endpoint-type target \
  --engine-name postgres \
  --server-name postgres-db1.abcdef123456.region.rds.amazonaws.com \
  --port 5432 \
  --database-name postgres_db1 \
  --secrets-manager-secret-id "prod/postgres/dms_user" \
  --secrets-manager-access-role-arn "arn:aws:iam::account:role/dms-secrets-access"

useLogminerReader=N;useBfile=Y selects Binary Reader, which reads the redo logs directly rather than through LogMiner. AWS recommends LogMiner in general and Binary Reader where redo volume is high, where several tasks read the same source, or on Oracle RAC. The choice belongs to the engagement, but it should be made once and applied consistently across every task. Note also that the oraclePathPrefix / usePathPrefix / replacePathPrefix attributes that appear in many Binary Reader examples are specific to Amazon RDS for Oracle and its internal /rdsdbdata/ layout; on a self-managed host they point at directories that do not exist.

aws dms create-replication-instance \
  --replication-instance-identifier dms-replication-instance \
  --replication-instance-class dms.c5.large \
  --allocated-storage 50 \
  --vpc-security-group-ids sg-abcdef123456 \
  --replication-subnet-group-id default-vpc-subnet-group

Task settings — task-settings.json. DROP_AND_CREATE is correct here because DMS is creating the staging table; the second task later uses DO_NOTHING because the target table is created by hand.

{
  "TargetMetadata": {
    "TargetSchema": "staging",
    "SupportLobs": true,
    "FullLobMode": false,
    "LobChunkSize": 64,
    "LimitedSizeLobMode": true,
    "LobMaxSize": 32
  },
  "FullLoadSettings": {
    "TargetTablePrepMode": "DROP_AND_CREATE",
    "CreatePkAfterFullLoad": true,
    "StopTaskCachedChangesApplied": false,
    "StopTaskCachedChangesNotApplied": false,
    "MaxFullLoadSubTasks": 8,
    "TransactionConsistencyTimeout": 600,
    "CommitRate": 10000
  },
  "Logging": {
    "EnableLogging": true,
    "LogComponents": [
      { "Id": "SOURCE_UNLOAD", "Severity": "LOGGER_SEVERITY_DEFAULT" },
      { "Id": "TARGET_LOAD",   "Severity": "LOGGER_SEVERITY_DEFAULT" },
      { "Id": "TASK_MANAGER",  "Severity": "LOGGER_SEVERITY_DEFAULT" }
    ]
  },
  "OracleSettings": {
    "ReadTableSpaceName": false,
    "EnableHomogenousTablespace": false,
    "StandbyDelayTime": 0,
    "ArchivedLogsOnly": false,
    "ArchivedLogDestId": 0,
    "UseDirectPathFullLoad": true,
    "UseParallelReadThreads": true,
    "NumberOfThreads": 4,
    "ParallelASMReadThreads": 2,
    "ReadAheadBlocks": 10000,
    "EnableHomogenousPartitionOps": false
  }
}

Table mappings — table-mappings.json, selecting the source table and renaming it into the staging schema:

{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "1",
      "object-locator": { "schema-name": "SOURCE_SCHEMA", "table-name": "SOURCE_TABLE" },
      "rule-action": "include"
    },
    {
      "rule-type": "transformation",
      "rule-id": "2",
      "rule-name": "2",
      "rule-action": "rename",
      "rule-target": "schema",
      "object-locator": { "schema-name": "SOURCE_SCHEMA" },
      "value": "staging"
    },
    {
      "rule-type": "transformation",
      "rule-id": "3",
      "rule-name": "3",
      "rule-action": "rename",
      "rule-target": "table",
      "object-locator": { "schema-name": "SOURCE_SCHEMA", "table-name": "SOURCE_TABLE" },
      "value": "staging_table"
    }
  ]
}

Table and column names in transformation rules are case-sensitive, and must be upper case for an Oracle source.

aws dms create-replication-task \
  --replication-task-identifier oracle-to-postgres-task \
  --source-endpoint-arn arn:aws:dms:region:account:endpoint:oracle-source-endpoint \
  --target-endpoint-arn arn:aws:dms:region:account:endpoint:postgresql-target-endpoint \
  --replication-instance-arn arn:aws:dms:region:account:rep:dms-replication-instance \
  --migration-type full-load-and-cdc \
  --table-mappings file://table-mappings.json \
  --replication-task-settings file://task-settings.json

aws dms start-replication-task \
  --replication-task-arn arn:aws:dms:region:account:task:oracle-to-postgres-task \
  --start-replication-task-type start-replication

Step 2: The cross-database join

2.1 Foreign data wrapper into the second database

CREATE SERVER postgres_db2_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'postgres-db2.abcdef123456.region.rds.amazonaws.com',
         port '5432',
         dbname 'postgres_db2');

CREATE USER MAPPING FOR dms_user
SERVER postgres_db2_server
OPTIONS (user 'fdw_user', password :'fdw_password');

CREATE FOREIGN TABLE staging.foreign_table (
    id         integer NOT NULL,
    name       varchar(255),
    value      numeric,
    created_at timestamp
)
SERVER postgres_db2_server
OPTIONS (schema_name 'public', table_name 'existing_table');

GRANT SELECT ON staging.foreign_table TO dms_user;

Two things about foreign tables that trip people up.

No primary key. CREATE FOREIGN TABLE accepts only NOT NULL, CHECK, DEFAULT and GENERATED constraints; a PRIMARY KEY (id) clause is rejected outright. Even the constraints it does accept are not enforced by PostgreSQL — they are declarations about what the remote server enforces, used for planning. The key on existing_table is enforced in database 2, which is where it belongs.

The mapping password is stored in the catalog. It is held in pg_user_mapping and is readable by superusers and by the role the mapping belongs to. Use a dedicated remote account with read-only access to just the tables the join needs, and rotate it on the same schedule as everything else.

See Joining tables across separate PostgreSQL databases for the FDW setup in more detail, including the AWS networking requirements.

2.2 The view

CREATE OR REPLACE VIEW staging.combined_view AS
SELECT
    s.id,
    s.column1,
    s.column2,
    f.name,
    f.value,
    s.created_at AS source_created_at,
    f.created_at AS related_created_at
FROM staging.staging_table s
JOIN staging.foreign_table f ON s.id = f.id;

GRANT SELECT ON staging.combined_view TO dms_user;

Every query against this view pulls from the remote database. For anything more than a modest row count, a materialized view refreshed on a schedule will perform very differently from a plain view — measure before committing to either.

Step 3: Load the view into the target table

3.1 Create the target table

CREATE TABLE target.final_table (
    id                  integer PRIMARY KEY,
    column1             varchar(255),
    column2             varchar(255),
    name                varchar(255),
    value               numeric,
    source_created_at   timestamp,
    related_created_at  timestamp,
    migration_timestamp timestamp DEFAULT CURRENT_TIMESTAMP
);

GRANT ALL PRIVILEGES ON target.final_table TO dms_user;

3.2 Second DMS task

DMS can migrate a view only in a full-load-only task — a CDC task, or a full-load task that continues into CDC, replicates tables only. So this task is --migration-type full-load, and it is a one-off rather than a continuous pipeline.

view-mappings.json:

{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "1",
      "object-locator": { "schema-name": "staging", "table-name": "combined_view" },
      "rule-action": "include"
    },
    {
      "rule-type": "transformation",
      "rule-id": "2",
      "rule-name": "2",
      "rule-action": "rename",
      "rule-target": "schema",
      "object-locator": { "schema-name": "staging" },
      "value": "target"
    },
    {
      "rule-type": "transformation",
      "rule-id": "3",
      "rule-name": "3",
      "rule-action": "rename",
      "rule-target": "table",
      "object-locator": { "schema-name": "staging", "table-name": "combined_view" },
      "value": "final_table"
    }
  ]
}

view-task-settings.json — DO_NOTHING, because target.final_table was created deliberately above and DMS must not recreate it:

{
  "TargetMetadata": {
    "TargetSchema": "target",
    "SupportLobs": true,
    "FullLobMode": false,
    "LobChunkSize": 64,
    "LimitedSizeLobMode": true,
    "LobMaxSize": 32
  },
  "FullLoadSettings": {
    "TargetTablePrepMode": "DO_NOTHING",
    "CreatePkAfterFullLoad": false,
    "StopTaskCachedChangesApplied": false,
    "StopTaskCachedChangesNotApplied": false,
    "MaxFullLoadSubTasks": 8,
    "TransactionConsistencyTimeout": 600,
    "CommitRate": 10000
  },
  "Logging": { "EnableLogging": true }
}

aws dms create-replication-task \
  --replication-task-identifier view-to-target-task \
  --source-endpoint-arn arn:aws:dms:region:account:endpoint:postgresql-target-endpoint \
  --target-endpoint-arn arn:aws:dms:region:account:endpoint:postgresql-target-endpoint \
  --replication-instance-arn arn:aws:dms:region:account:rep:dms-replication-instance \
  --migration-type full-load \
  --table-mappings file://view-mappings.json \
  --replication-task-settings file://view-task-settings.json

aws dms start-replication-task \
  --replication-task-arn arn:aws:dms:region:account:task:view-to-target-task \
  --start-replication-task-type start-replication

Because the target table is left as-is, re-running this task appends rather than replaces. With a primary key on final_table the repeats fail rather than duplicating, which is the reason the key is there — truncate the table deliberately before a re-run.

3.3 Simpler alternative

Source and target here are the same database, so DMS is doing work that SQL can do directly. For a one-off load, this is less machinery:

INSERT INTO target.final_table (
    id, column1, column2, name, value, source_created_at, related_created_at
)
SELECT id, column1, column2, name, value, source_created_at, related_created_at
FROM staging.combined_view
ON CONFLICT (id) DO UPDATE SET
    column1            = EXCLUDED.column1,
    column2            = EXCLUDED.column2,
    name               = EXCLUDED.name,
    value              = EXCLUDED.value,
    source_created_at  = EXCLUDED.source_created_at,
    related_created_at = EXCLUDED.related_created_at;

Step 4: Ongoing synchronisation

If the target table has to track Oracle continuously, the staging table is already doing so through task 1’s CDC; what remains is propagating each change through the join.

CREATE OR REPLACE FUNCTION staging.update_final_table()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO target.final_table (
        id, column1, column2, name, value, source_created_at, related_created_at
    )
    SELECT s.id, s.column1, s.column2, f.name, f.value, s.created_at, f.created_at
    FROM staging.staging_table s
    JOIN staging.foreign_table f ON s.id = f.id
    WHERE s.id = NEW.id
    ON CONFLICT (id) DO UPDATE SET
        column1            = EXCLUDED.column1,
        column2            = EXCLUDED.column2,
        name               = EXCLUDED.name,
        value              = EXCLUDED.value,
        source_created_at  = EXCLUDED.source_created_at,
        related_created_at = EXCLUDED.related_created_at;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sync_to_final_table
AFTER INSERT OR UPDATE ON staging.staging_table
FOR EACH ROW
EXECUTE FUNCTION staging.update_final_table();

PostgreSQL has no UPSERT statement in any version — INSERT … ON CONFLICT is the construct, and a function containing UPSERT INTO fails to compile.

Understand the cost before adopting this. The trigger fires per row and each firing queries the remote database across the FDW connection, so a CDC batch of a thousand changes becomes a thousand round trips. Where the change rate is anything but low, a scheduled batch merge over the rows changed since the last run will be both faster and easier to reason about.

Step 5: Validation and monitoring

SELECT COUNT(*) FROM staging.staging_table;
SELECT COUNT(*) FROM staging.foreign_table;
SELECT COUNT(*) FROM staging.combined_view;
SELECT COUNT(*) FROM target.final_table;

SELECT
    (SELECT COUNT(*) FROM staging.combined_view) AS view_count,
    (SELECT COUNT(*) FROM target.final_table)    AS target_count,
    CASE WHEN (SELECT COUNT(*) FROM staging.combined_view)
            = (SELECT COUNT(*) FROM target.final_table)
         THEN 'MATCH' ELSE 'MISMATCH' END AS status;

-- Rows in the view that never reached the target
SELECT v.id
FROM staging.combined_view v
LEFT JOIN target.final_table t ON v.id = t.id
WHERE t.id IS NULL;

A count match is necessary and not sufficient. Enable DMS data validation on task 1 so that the Oracle-to-PostgreSQL type mapping is checked row by row rather than inferred from totals.

aws dms describe-replication-tasks \
  --filters Name=replication-task-arn,Values=arn:aws:dms:region:account:task:oracle-to-postgres-task

aws dms describe-table-statistics \
  --replication-task-arn arn:aws:dms:region:account:task:oracle-to-postgres-task

aws logs get-log-events \
  --log-group-name /aws/dms/replication-task/oracle-to-postgres-task \
  --log-stream-name dms-task-oracle-to-postgres-task \
  --limit 10

Troubleshooting

The foreign table will not read. SELECT * FROM staging.foreign_table LIMIT 10; is the first test. Failures are almost always network: the two RDS instances need security groups that permit traffic between them, and either a shared VPC or peering. Check the PostgreSQL error log in CloudWatch for the wrapper’s own message.

A DMS task fails. describe-replication-task-assessment-results reports the premigration assessment; describe-table-statistics shows which tables errored and how many rows each processed.

The view migration fails. Confirm the view is readable as the DMS user, then check the target table’s constraints — SELECT * FROM information_schema.constraint_column_usage WHERE table_schema = 'target' AND table_name = 'final_table'; — since a constraint that holds in the view’s source data may not hold once it is written into a table with a primary key.