{"id":5,"date":"2017-08-10T06:25:04","date_gmt":"2017-08-10T06:25:04","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=5"},"modified":"2017-08-13T22:38:47","modified_gmt":"2017-08-13T22:38:47","slug":"oracle-to-aurora-using-data-migration-services-dms","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2017\/08\/10\/oracle-to-aurora-using-data-migration-services-dms\/","title":{"rendered":"Oracle to Aurora using Data Migration Services (DMS)"},"content":{"rendered":"

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.<\/p>\n

create table customer_aws as select * from customer where custno =< 10000;\r\nalter table customer_aws add constraint customer_aws_pk primary key (userid) enable validate;\r\n<\/code><\/pre>\n
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.<\/p>\n
CREATE OR REPLACE TRIGGER SCOTT.CUSTOMERUPDATE\r\nBEFORE UPDATE\r\nON SCOTT.customer\r\nREFERENCING NEW AS NEW OLD AS OLD\r\nFOR EACH ROW\r\nBEGIN \r\n\r\n--delete the record, ensure we remove the record with the primary key.\r\ndelete from scott.customer_aws where userid = :old.USERID;\r\n\r\n-- If we have deleted a row, then perform the update.\r\nif sql%rowcount = 1 then\r\n\r\n  Insert into SCOTT.CUSTOMER_AWS\r\n       (HISTORYID, PROPERTYID, TABLENAME, IDENTITYKEY, USERID, \r\n        USEROVERRIDE, PROPERTYKEY, PROPERTYVALUE, LASTUPDATEDBY, TXNTYPE, \r\n        CREATEDATETIME, UPDATEDDATETIME)\r\n    Values\r\n        (:new.HISTORYID, :new.PROPERTYID, :new.TABLENAME, :new.IDENTITYKEY, :new.USERID, \r\n        :new.USEROVERRIDE, :new.PROPERTYKEY, :new.PROPERTYVALUE, :new.LASTUPDATEDBY, :new.TXNTYPE, \r\n        :new.CREATEDATETIME, :new.UPDATEDDATETIME);\r\nend if;\r\nEND;\r\n\/\r\n<\/code><\/pre>\n
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.<\/p>\n
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.<\/p>\n
Turn on supplemental logging for the database<\/p>\n
alter database add supplemental log data;\r\n<\/code><\/pre>\n
Then, for each table, you must turn on further supplemental logging for each table. Information can be found here<\/a><\/p>\n
alter table scott.CUSTOMER_AWS add supplemental log data (PRIMARY KEY) columns;\r\n<\/code><\/pre>\n
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.<\/p>\n
\"\"<\/p>\n
Then select “Get Started”<\/p>\n
\"\"<\/p>\n
Click next<\/p>\n
\"\"<\/p>\n
If you are on a private network, then make sure “Publicly accessible” is not checked.<\/p>\n
\"\"<\/p>\n
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”<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[12,11],"class_list":["post-5","post","type-post","status-publish","format-standard","hentry","category-oracle-aws","tag-aws","tag-dms"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/5","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=5"}],"version-history":[{"count":14,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/5\/revisions"}],"predecessor-version":[{"id":33,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/5\/revisions\/33"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=5"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=5"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=5"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}