Replicate Oracle data to Amazon RDS using AWS DMS services

Below are the steps to migrate to replicate Oracle tables to Amazon RDS Oracle database:

Setup Source database

1.       Mount the database

SQL> startup mount

ORACLE instance started.

Total System Global Area 1071333376 bytes

Fixed Size                  1341312 bytes

Variable Size             427821184 bytes

Database Buffers          629145600 bytes

Redo Buffers               13025280 bytes

Database mounted.

SQL>

2.       Enable Archive Log feature:

SQL> alter database archivelog;

Database altered.

SQL>

3.       Modify init.ora file to set below parameter:

                  log_archive_start             = true

4.       Open the database:

SQL> alter database open;

Database altered.

5.       Enable Supplemental log data:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

6.       Enable Table level identification key supplemental logging for specific tables:

SQL> alter table ONT.OE_ORDER_LINES_ALL add supplemental log data (unique) columns;

Table altered.

7.       Create a tablespace AWSDMS_DATA

Create a database user account AWSDMS:
create user AWSDMS identified by awsdms default tablespace awsdms_data quota unlimited on awsdms_data temporary tablespace temp1;

8.       Grant below privileges to AWSDMS user.

grant CREATE SESSION  to awsdms;

grant SELECT ANY TRANSACTION  to awsdms;

grant SELECT on V_$ARCHIVED_LOG  to awsdms;

grant SELECT on V_$LOG  to awsdms;

grant SELECT on V_$LOGFILE  to awsdms;

grant SELECT on V_$DATABASE  to awsdms;

grant SELECT on V_$THREAD  to awsdms;

grant SELECT on V_$PARAMETER  to awsdms;

grant SELECT on V_$NLS_PARAMETERS  to awsdms;

grant SELECT on V_$TIMEZONE_NAMES  to awsdms;

grant SELECT on V_$TRANSACTION  to awsdms;

grant SELECT on ALL_INDEXES  to awsdms;

grant SELECT on ALL_OBJECTS  to awsdms;

grant SELECT on ALL_TABLES  to awsdms;

grant SELECT on ALL_USERS  to awsdms;

grant SELECT on ALL_CATALOG  to awsdms;

grant SELECT on ALL_CONSTRAINTS  to awsdms;

grant SELECT on ALL_CONS_COLUMNS  to awsdms;

grant SELECT on ALL_TAB_COLS  to awsdms;

grant SELECT on ALL_IND_COLUMNS  to awsdms;

grant SELECT on ALL_LOG_GROUPS  to awsdms;

grant SELECT on SYS.DBA_REGISTRY  to awsdms;

grant SELECT on SYS.OBJ$  to awsdms;

grant SELECT on DBA_TABLESPACES  to awsdms;

grant SELECT on ALL_TAB_PARTITIONS  to awsdms;

grant SELECT on ALL_ENCRYPTED_COLUMNS  to awsdms;

grant SELECT on ont.oe_order_lines_all   to awsdms;

grant EXECUTE on DBMS_LOGMNR  to awsdms;

grant SELECT on V_$LOGMNR_LOGS to awsdms;

grant SELECT on V_$LOGMNR_CONTENTS to awsdms;

Setup Target RDS Database:

1.       Go to AWS RDS console and click on “Get Started Now”

2.       Select Oracle -> “Oracle SE Two”

3.       Select “Dev/Test” option on next page

4.       Specify DB details as below:

5.       Configure Advanced Settings:

6.       Click on Launch DB Instance button

7.       Create replication instance

Open browser with url https://console.aws.amazon.com/dms

Click on “Create Migration”

Click Next

Create Source and target database endpoints

Click Next

 

Create on “Create Task”

Now, you can test making a change in the table at source database and see it populated in target database.