Migrate an on-premises Oracle database to Oracle on Amazon EC2 using AWS DMS

There are many ways to migrate on premises Oracle database to Elastic Compute Cloud (EC2).
In this post I will show how to use AWS Database Migration Service to migrate on-premises Oracle Database 12.1.0 running in Windows to AWS EC2 running in Linux.

I installed locally Oracle 12c software in Windows environment and created manually a new Oracle database 12.1.0 (SID SRCDB) then installed the Sample Schemas/Users that populate data in the new schemas: IX , SH , OE , HR , BI , PM.

I installed Oracle 12c software in Linux enviroment (CentOS) on AWS EC2 and created manually a new Oracle Database 12.1.0 (SID TRGDB).

1. Enable the port 1521 in the router to allow access to the database SRCDB on premises server (IP: 192.168.2.199) from the replication instance (52.86.237.7).

2. Create a new security group that will be used to grant access to EC2 Instance and Database TRGDB.

Click on EC2 > Security Groups > Create security Group

1. Security group name: sg_migr_oracle_oracle_dms
2. Enter a description
3. Select the VPC virtual private cloud: vpc-09734b495f9a644b5 that includes the EC2 instance of the database TRGDB.

3. Create a replication instance to use AWS Database Migration Service (this step is mandatory)

Click on the Services > Database Migration Service

b. Click on the button Create replication instance

c. Enter the name and description of the replication instance and link to the VPC.

1. Name: replic-instance-ora-premise-to-ora-ec2
2. VPC: Select the virtual private cloud: vpc-09734b495f9a644b5

d. Select the Availability zone and security group.

1. Availability zpne: us-east-1b

2. Security group: select sg_migr_oracle_oracle_dms was created in the step 2

3. click on the button Create

e. Wait a few minutes until the replication instance is available.

4. Add two inbound rules to have access to the EC2 Instance and database TRGDB

Click on EC2 > Security Groups > sg-092b5c714bb3ef2d6 – sg_migr_oracle_oracle_dms

1. We add a new Custom TCP rule to allow to the replication instance (IP 171.9.0.28) connect to Oracle database using the port 1521.
2. We add a rule SSH to allow from a public IP to the EC2 instance.

5. Assign the Security Group sg_migr_oracle_oracle_dms to the instance EC2.

Click on Services > Instances > Right Click > Networking > Change Security Group

1. Click on the check box of security group name sg_migr_oracle_oracle_dms.

2. Click on the button Assign Security Groups.

6. Create a Source Endpoint for the on-premises Database (SRCDB)

a. Click on Services > Database Migration Service > Endpoints

1. Select Source endpoint
2. Enter Endpoint identifier: ep-oracle-premises
3. Source engine: oracle
4. Server name: public IP of the on premises server
5. Port : 1521 for Source Oracle DB (SRCBD)
6. User: system
7. Password :password_of_system
8. SID: SRCDB

b. Go to Test endpoint connection section.

1.Select virtual private cloud: vpc-09734b495f9a644b5 – vpc_parrot.

2. Select the Replication Instance : replic-instance-ora-premise-to-ora-ec2

3. Click on the button Run test to verify if the connection was sucessfull.

4. Click on the button Create endpoint

7. Create a Target Endpoint for the the Database TRGDB located in the EC2 Instance.

a. Click on Services > Database Migration Service > Endpoints

1. Select Target endpoint
2. Enter Endpoint identifier: ep-oracle-c2
3. Source engine: oracle
4. Server name: Local IP 171.0.24.109 where is locate EC2 Database TRGDB
5. Port : 1521 for Source Oracle DB (TRGBD)
6. User: system
7. Password :password_of_system
8. SID: TRGDB

b. Go to Test endpoint connection section.

1.Select virtual private cloud: vpc-09734b495f9a644b5 – vpc_parrot.

2. Select the Replication Instance : replic-instance-ora-premise-to-ora-ec2

3. Click on the button Run test to verify if the connection was sucessfull.

4. Click on the button Create endpoint

8. Create the users BI, HR, IX, OE, SH and grant their privileges in the EC2 target database TRGDB.

a. User BI

-- USER SQL
CREATE USER BI IDENTIFIED BY oracle
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMPTS1";
-- QUOTAS
ALTER USER BI QUOTA UNLIMITED ON USERS;
-- ROLES
GRANT "RESOURCE" TO BI ;
ALTER USER BI DEFAULT ROLE "RESOURCE";
-- SYSTEM PRIVILEGES
GRANT CREATE SEQUENCE TO BI ;
GRANT CREATE TABLE TO BI ;
GRANT CREATE CLUSTER TO BI ;
GRANT CREATE SYNONYM TO BI ;
GRANT CREATE VIEW TO BI ;
GRANT CREATE SESSION TO BI ;
GRANT UNLIMITED TABLESPACE TO BI ;
GRANT CREATE DATABASE LINK TO BI ;
GRANT ALTER SESSION TO BI ;

b. User HR

-- USER SQL
CREATE USER HR IDENTIFIED BY oracle 
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMPTS1";
-- QUOTAS
ALTER USER HR QUOTA UNLIMITED ON USERS;
-- ROLES
GRANT "RESOURCE" TO HR ;
ALTER USER HR DEFAULT ROLE "RESOURCE";
-- SYSTEM PRIVILEGES
GRANT CREATE SEQUENCE TO HR ;
GRANT CREATE SYNONYM TO HR ;
GRANT CREATE VIEW TO HR ;
GRANT CREATE SESSION TO HR ;
GRANT UNLIMITED TABLESPACE TO HR ;
GRANT CREATE DATABASE LINK TO HR ;
GRANT ALTER SESSION TO HR ;

c. User IX

-- USER SQL
CREATE USER IX IDENTIFIED BY oracle 
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMPTS1";
-- QUOTAS
ALTER USER IX QUOTA UNLIMITED ON USERS;
-- ROLES
GRANT "AQ_ADMINISTRATOR_ROLE" TO IX ;
GRANT "RESOURCE" TO IX ;
GRANT "AQ_USER_ROLE" TO IX ;
GRANT "SELECT_CATALOG_ROLE" TO IX ;
GRANT "CONNECT" TO IX ;
ALTER USER IX DEFAULT ROLE "AQ_ADMINISTRATOR_ROLE","RESOURCE","AQ_USER_ROLE","SELECT_CATALOG_ROLE","CONNECT";
-- SYSTEM PRIVILEGES
GRANT CREATE TRIGGER TO IX ;
GRANT CREATE SEQUENCE TO IX ;
GRANT CREATE TABLE TO IX ;
GRANT CREATE OPERATOR TO IX ;
GRANT CREATE CLUSTER TO IX ;
GRANT CREATE RULE TO IX ;
GRANT CREATE PROCEDURE TO IX ;
GRANT CREATE SYNONYM TO IX ;
GRANT CREATE VIEW TO IX ;
GRANT CREATE RULE SET TO IX ;
GRANT CREATE TYPE TO IX ;
GRANT CREATE SESSION TO IX ;
GRANT UNLIMITED TABLESPACE TO IX ;
GRANT CREATE INDEXTYPE TO IX ;
GRANT SELECT ANY DICTIONARY TO IX ;
GRANT CREATE DATABASE LINK TO IX ;
GRANT ALTER SESSION TO IX ;

d. User OE

-- USER SQL
CREATE USER OE IDENTIFIED BY null 
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMPTS1";
-- QUOTAS
ALTER USER OE QUOTA UNLIMITED ON USERS;
-- ROLES
GRANT "RESOURCE" TO OE ;
GRANT "XDBADMIN" TO OE ;
ALTER USER OE DEFAULT ROLE "RESOURCE","XDBADMIN";
-- SYSTEM PRIVILEGES
GRANT CREATE SYNONYM TO OE ;
GRANT CREATE VIEW TO OE ;
GRANT CREATE SESSION TO OE ;
GRANT QUERY REWRITE TO OE ;
GRANT UNLIMITED TABLESPACE TO OE ;
GRANT CREATE MATERIALIZED VIEW TO OE ;
GRANT CREATE DATABASE LINK TO OE ;
-- USER SQL
CREATE USER PM IDENTIFIED BY oracle
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMPTS1";
-- QUOTAS
ALTER USER PM QUOTA UNLIMITED ON USERS;
-- ROLES
GRANT "RESOURCE" TO PM ;
GRANT "CONNECT" TO PM ;
ALTER USER PM DEFAULT ROLE "RESOURCE","CONNECT";
-- SYSTEM PRIVILEGES
GRANT UNLIMITED TABLESPACE TO PM ;

e. User SH

-- USER SQL
CREATE USER SH IDENTIFIED BY oracle
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMPTS1";
-- QUOTAS
ALTER USER SH QUOTA UNLIMITED ON USERS;
-- ROLES
GRANT "RESOURCE" TO SH ;
GRANT "SELECT_CATALOG_ROLE" TO SH ;
ALTER USER SH DEFAULT ROLE "RESOURCE","SELECT_CATALOG_ROLE";
-- SYSTEM PRIVILEGES
GRANT CREATE SEQUENCE TO SH ;
GRANT CREATE TABLE TO SH ;
GRANT CREATE CLUSTER TO SH ;
GRANT CREATE DIMENSION TO SH ;
GRANT CREATE SYNONYM TO SH ;
GRANT CREATE VIEW TO SH ;
GRANT CREATE SESSION TO SH ;
GRANT QUERY REWRITE TO SH ;
GRANT UNLIMITED TABLESPACE TO SH ;
GRANT CREATE MATERIALIZED VIEW TO SH ;
GRANT CREATE DATABASE LINK TO SH ;
GRANT ALTER SESSION TO SH ;

9. Create database migration task.

DMS > Database migration tasks > Create database migration task.

a. Enter Task Configuration.

1. Enter Task identifier for the migration task : MIG-PREM-ORA-TO-EC2-ORA

2. Select the replication instance replic-instance-ora-premise-to-ora-ec2 (created in the step 3).

3. Select Source database endpoint: ep-oracle-premises

4. Select Source database endpoint: ep-oracle-ec2

5. Migrate type: Select Migrate existing data because for this tutorial we are going to make an initial load.

b. Enter Task Setting.

1.Target table preparation mode: Select drop tables on target

2. Include LOB columns in replication: Select Limited LOB mode

3. Enable validation

c. Enter Table Mapping

1.Select Enter a schema

2. Schema name: BI

3.Action : Select Include

4. Click on the icon duplicate this rule and repeat the steps 1-3 to includes the others schemas IX, SH, OE, HR and PM.

d. Enter Transformation rules.

1.Select Enter a schema

2. Schema name: BI

3. Action: Rename to

4. Enter BI

5. Click on the icon duplicate this transformation rule and repeat the steps 1-4 to includes the others schemas IX, SH, OE, HR and PM.

e. Enter Advanced task settings.

1.Enter the schema will contain the control table of the TRGDB

2. Enable: Apply exceptions / Replication Status / Suspended Tables/ Replication history.

3.Click on Create task button.

f. The migration task MIG-PREM-ORA-TO-EC2-ORA migrated the schemas BI, IX, SH, OE, HR and PM in 2 minutes but finished with errors.

10. Check the tables statistics to analyze which table had errors.

DMS > Database migration tasks > mig-prem-ora-to-ec2-ora

The table SH.SALES_TRANSACTION_EXT could not be migrated with a error.