Oracle to Aurora using Data Migration Services (DMS)

We are looking to migrate some customer data, however we don’t need all the data, but only a subset. The idea was to create a copy of the existing tables using a copy with the relevant rows, then create a series of triggers to keep them up-to-date with the main tables. It goes something like this.

create table customer_aws as select * from customer where custno =< 10000;
alter table customer_aws add constraint customer_aws_pk primary key (userid) enable validate;

Once the table has been created, we can place a trigger to update the newly created table when there is a change to the parent.

CREATE OR REPLACE TRIGGER SCOTT.CUSTOMERUPDATE
BEFORE UPDATE
ON SCOTT.customer
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN 

--delete the record, ensure we remove the record with the primary key.
delete from scott.customer_aws where userid = :old.USERID;

-- If we have deleted a row, then perform the update.
if sql%rowcount = 1 then

  Insert into SCOTT.CUSTOMER_AWS
       (HISTORYID, PROPERTYID, TABLENAME, IDENTITYKEY, USERID, 
        USEROVERRIDE, PROPERTYKEY, PROPERTYVALUE, LASTUPDATEDBY, TXNTYPE, 
        CREATEDATETIME, UPDATEDDATETIME)
    Values
        (:new.HISTORYID, :new.PROPERTYID, :new.TABLENAME, :new.IDENTITYKEY, :new.USERID, 
        :new.USEROVERRIDE, :new.PROPERTYKEY, :new.PROPERTYVALUE, :new.LASTUPDATEDBY, :new.TXNTYPE, 
        :new.CREATEDATETIME, :new.UPDATEDDATETIME);
end if;
END;
/

Now that we have created the new table and trigger, test this out and make sure changes to the parent are replicated to the table copy.

If we want to use the “Ongoing Replication” AWS Data Migration Service (DMS) from Amazon, we need to set a few things up on the Oracle database.

Turn on supplemental logging for the database

alter database add supplemental log data;

Then, for each table, you must turn on further supplemental logging for each table. Information can be found here

alter table scott.CUSTOMER_AWS add supplemental log data (PRIMARY KEY) columns;

Login to your aws.amzon.com account and select RDS to setup your target database. Once your RDS is complete, we can select the DMS component.

Then select “Get Started”

Click next

If you are on a private network, then make sure “Publicly accessible” is not checked.

Please be aware, that you must select an IP Address on your internal network for the server name of the Oracle database, it does now know how to resolve a host name. Once your endpoints are created and the replication database has finished building, then you can perform “Run test”