Database

Migration Service
Migrating Oracle Database from On-Premises/EC2 to RDS
 Table of Contents
Introduction 3

Environment Details 3

Approach 3

Starting DMS service 6

Monitoring DMS service 13

Procedures, Functions, Packages and other object migration 14

Post migration activity and checks 15

Conclusion 16
 DMS-EC2 Oracle database to RDS Oracle database | Page 3
Introduction
Managing Oracle Databases in On-Premises/EC2 instances require more resources and cost. Hence moving it to RDS instance will ease our job,
optimizing the IT budget and also providing us with various features like Multi-AZ, Scalability, Automatic Backups…etc.
In our case we are migrating 120 GB Oracle Database from EC2 instance to RDS using the DMS service which migrates the data very faster and
effortlessly .The task can also be monitored and optimized through the DMS service. The same approach is applicable while migrating the Oracle
Database from On-Premises to RDS.

Environment Details
Below are the current EC2 and opted RDS environment details involved in this migration.

EC2 Instance Type M4.large RDS Instance Type T2.medium

Database Size 120 GB Allocated Size 200 GB
Database Version 11.2.0.4 Database Version 11.2.0.4
DB Instance Name TEST RDS Instance Name PRODTEST
DOCUMENTS, PROD, HOW,OWS,
Schemas to migrate
BACKUP

Approach
The below approach is followed to migrate the Database:

Prerequisites to
start DMS service

Starting DMS
service
Post migration activity
and checks

Procedures ,Functions,
Packages and other object
migration

Prerequisites to start DMS service:

a. Source database with Instance and Listener services need to be up and running.
b. Target RDS database should be up and running.
c. Target RDS database should have sufficient storage for data migration.
d. Source database should be in “archive log” mode.
e. Create the necessary tablespaces on the target database.
f. Create schemas on the target database which needs to be migrated.

Below screen shots confirms the prerequisites are met in our migration.
 DMS-EC2 Oracle database to RDS Oracle database | Page 4
a. Source database
The source database instance “TEST” and the “LISTENER” are up and running fine.

b. Target database
The target RDS instance is up and ready to be used

c. Storage space in target database.

The target database is equipped with enough storage space.

d. Source database in archive log mode.

The archive log mode is enabled in the source database.
 DMS-EC2 Oracle database to RDS Oracle database | Page 5

e. Tablespace creation in the target database.

Tablespaces “DOCUMENTS”, “PROD”, “HOW”, “OWS” and “BACKUP” are created.

f. Schema creation and grant privileges in the target database.

Schemas “DOCUMENTS”, “PROD”, “HOW”, “OWS” and “BACKUP” are created and the required privileges are granted
as per the application/user needs.
Schemas are created
 DMS-EC2 Oracle database to RDS Oracle database | Page 6
Privileges are granted

Note: Same privileges been granted to all the schemas.

Starting DMS service.

Step 1:
Login to the AWS console and Click on “DMS” option under Database Services.
 DMS-EC2 Oracle database to RDS Oracle database | Page 7
Step 2:
Click on “Create Migration” button

Step 3:
Since this is an Oracle to Oracle database migration, downloading the schema conversion tool is optional. However it is applicable if the
migration is heterogeneous. Click on “Next” button.

Step 4:
Under the “Create replication Instance” window, provide a meaningful replication instance name, description of the migration, Instance
class and VPC where the RDS instance must reside. Select Multi-AZ to YES for high availability and redundant replication streaming.
Check the publicly accessible box to access the replication instance through internet.
 DMS-EC2 Oracle database to RDS Oracle database | Page 8
Step 5:
Under advanced section enter allocated storage for DMS instance, Replication subnet group based on the VPC selected in the previous
step, VPC security group(s) to allow inbound/outbound traffic, KMS master key as default if there is no existing key for database volume
encryption. Click on “Next” button.

Step 6:
Under “connect source and target database endpoints” window, enter source and target database details to create endpoints. Provide a
meaningful endpoint identifier name, Source and Target engines (Database engines), Server name (Database server IP /end point), Port
(Database listening port), SSL mode (To encrypt the connection) which is none for oracle. Username in source identifier should have
data dictionary views access privileges and the Username in target identifier should be the schema name which needs to be migrated.

In our case the username in the source identifier is “SYSTEM” which by default has all the privileges and the target username is
“DOCUMENTS” which needs to be migrated.
 DMS-EC2 Oracle database to RDS Oracle database | Page 9
Step 7:
Click “Run test” to check the connectivity between replication instance and source, target databases. You should receive a message
stating “Connection tested successfully” and click on “Next” button.

Step 8:
Under the “Create task” window provide a meaningful task name and task description. Select Migration type as “Migrate existing data”
and select the check box “Start task on create” to start the activity automatically post task creation.

Step 9:
Under Task settings select the “Target table preparation mode” as “Do nothing” since it is a fresh migration. Select Full LOB mode in “Include LOB
columns in replication” if the size of the LOB is unknown and to migrate complete LOBs regardless of size. The option “Limited LOB mode” can be
selected if the LOB chunk size is known. The option “Don’t include LOB columns” cab be selected to exclude LOB columns. Check “Enable logging”
box to capture the errors and warnings in the logs. Select advanced settings to configure “Control Tables” and “Tuning” settings to optimize the
data migration.
 DMS-EC2 Oracle database to RDS Oracle database | Page 10
Under advanced settings

Under advanced settings

Step 10:
Under Table mappings select the “Mapping method” as default to migrate the table with the same name to target database. To remap with
a different table name click on “Custom” and edit the JSON script for relevant mapping. Select the migration schema name from “Schema
to migrate” drop down list. Click on “Create task” button.
Table mappings option

Step 11:
The Task has been created successfully and the status is changed from “Creating” to “Ready”.
Task is in creating status

Task is in ready status

 DMS-EC2 Oracle database to RDS Oracle database | Page 11
Step 12:
If the task is not started automatically, select the appropriate task and click on “Start/Resume” button to initiate the migration activity.
Once the activity is initiated the status will change from “Ready” to “Starting” and then eventually to “Running” state. The progress bar
shows the percentage of the task completion.
Task is in starting status

Task is in running status

Task is in Load complete status

The Progress bar is changed to 100% and the status shows as “Load Complete”. This indicates that one schema migration has completed
from source to target Database successfully.

Step 13:
Create a new endpoint, new task and repeat the same steps to migrate all schemas to the target database.
New “ProdtestProdmig” endpoint is created.
 DMS-EC2 Oracle database to RDS Oracle database | Page 12

Prodtestprodmig endpoint is in active status

New Task “prodmigration” is created.

 DMS-EC2 Oracle database to RDS Oracle database | Page 13
Prodmigration task is in “Running” state

The same way backupmigration, owsmigration, howmigration tasks are created and successfully migrated all the schemas
from source to target database.

Monitoring DMS service Task

The progress of the task and resources used can be monitored through DMS console.
a. Task status, complete %, Elapsed time and table loaded can be monitored.

b. Table Statistics tab shows the table migration details as shown below.
 DMS-EC2 Oracle database to RDS Oracle database | Page 14
c. Task monitoring tab shows the detailed cloud watch metrics.

d. Migration logs shows the details of the migration, error and warnings with time stamp under the logs tab.

Procedures, Functions, Packages and other object migration:

All schemas tables are migrated successfully through the DMS service. The below method is followed to migrate the other object types
like procedures, functions, packages, package body’s, views and indexes.
Step 1:
Exporting objects from source database except tables which is already been migrated.

Step 2:
Creating database link from source to target database.

Step 3:
Testing the connection from source to target Database.
 DMS-EC2 Oracle database to RDS Oracle database | Page 15
Step 4:
Dump file is transferred from source to target database through DBMS_FILE_TRANSFER.

Step 5:
Importing the Dump file from the source to the target database.

Post migration activity and checks

Performed invalid object compilation and object comparison between source and target databases.
Below command is used to compile the invalid objects in the target RDS database.
exec SYS.UTL_RECOMP.RECOMP_SERIAL('PROD');

Source database object count:

Target database object count:

The same way the objects can be compared for all the schemas.If there are any missing objects it can be created manually.
 DMS-EC2 Oracle database to RDS Oracle database | Page 16
Note:
Below are the required source database user privileges to migrate the schemas through DMS. We have used SYSTEM user to migrate
all schema’s hence granting the below privileges are not required.

SELECT ANY TRANSACTION CREATE ANY VIEW

SELECT on V$NLS_PARAMETERS DROP ANY VIEW
SELECT on V$TIMEZONE_NAMES CREATE ANY PROCEDURE
SELECT on ALL_INDEXES ALTER ANY PROCEDURE
SELECT on ALL_OBJECTS DROP ANY PROCEDURE
SELECT on DBA_OBJECTS CREATE ANY SEQUENCE
SELECT on ALL_TABLES ALTER ANY SEQUENCE
SELECT on ALL_USERS DROP ANY SEQUENCE
SELECT on ALL_CATALOG SELECT on DBA_USERS
SELECT on ALL_CONSTRAINTS SELECT on DBA_TAB_PRIVS
SELECT on ALL_CONS_COLUMNS SELECT on DBA_OBJECTS
SELECT on ALL_TAB_COLS SELECT on DBA_SYNONYMS
SELECT on ALL_IND_COLUMNS SELECT on DBA_SEQUENCES
DROP ANY TABLE SELECT on DBA_TYPES
SELECT ANY TABLE SELECT on DBA_INDEXES
INSERT ANY TABLE SELECT on DBA_TABLES
UPDATE ANY TABLE SELECT on DBA_TRIGGERS

Conclusion:
Database migration has been successfully completed from EC2 oracle
database to RDS oracle database using DMS service.

About Author:
Prabhakaran Ponnusamy a Cloud Architect at 8k Miles, an Oracle
expertise along with multiple database technologies hands-on.
Passionate in learning, experimenting and exploring new technologies
and making business much more than usual.

San Ramon, CA (HQ) Chicago, IL Dallas, TX

12647 Alcosta Boulevard, 1827 Walden Office Square 17740 Preston Road
Suite 450,San Ramon, CA 94583, USA Suite #460 Suite #200
www.8kmiles.com Schaumburg, IL 60173 Dallas, TX -75252
sales@8kmiles.com Phone : 708-289-5111 Phone : 214-272-2404
Direct : 925-270-4800
Toll-Free : 855-856-4537

Chennai, India Ontario, Canada Sharjah, UAE

Srinivasa Towers 4 Robert Speck Parkway, Q1-05-109/C
New No.5, Old No. 11, Suite 1500, SAIF Zone
Cenotaph Road, Mississauga, Ontario L4Z 1S1 PO Box 121213
Alwarpet,Chennai – 600 018 Phone : 416-366-7762 Sharjah-UAE
Phone : +91-44-6602-8000
Fax : +91-44-4300-9049