Architecture & DesignHow To Migrate an Oracle Database from AWS EC2 to AWS RDS

How To Migrate an Oracle Database from AWS EC2 to AWS RDS

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

A Database Migration Service may be used to migrate a database not only between two RDS DB instances but the source/target may also be a non-RDS database. The only requirement is that at least one of the database endpoints of a DMS migration be an AWS-based database. The AWS-based database could a RDS DB instance, Redshift, MongoDB, or a database hosted on EC2. In four articles, we shall discuss migrating an Oracle database from EC2 to RDS. This article has the following sections:

Setting the Environment

First, create an AWS account. Next, create a KMS master key (dms).

Creating an Oracle Database Instance on AWS RDS

Create an RDS DB instance for Oracle database. Select Oracle as the engine in the RDS dashboard, as shown in Figure 1.

Selecting Oracle Database as RDS Engine
Figure 1: Selecting Oracle Database as RDS Engine

For Production?, select Dev/Test, as shown in Figure 2, and click Next Step.

Selecting Use Case as Dev/Test
Figure 2: Selecting Use Case as Dev/Test

In Specify DB Details, select the checkbox Only show options that are eligible for RDS Free Tier. The default settings for the free tier are shown in Figure 3.

Specify DB Details
Figure 3: Specify DB Details

Specify DB Instance Identifier (ORCL), and specify a Master User name and Master Password, as shown in Figure 4. Click Next Step.

Specifying DB Instance Identifier
Figure 4: Specifying DB Instance Identifier

In Configure Advanced Settings, select the option to Create new VPC, as shown in Figure 5. Also, select the option to Create new DB Subnet group, and Set Publicly Accessible to Yes. Set Availability Zone to No Preference. Select the option to Create new Security Group. In the Database options header, specify a Database Name (ORCL), Database Port (1521), and keep the default settings for DB Parameter Group and Option Group.

Configuring Advanced Settings
Figure 5: Configuring Advanced Settings

Keep the other options as default including those for Backup, Monitoring, and Maintenance (see Figure 6). Click Launch DB Instance.

Configuring other Advanced Settings and launching DB Instance
Figure 6: Configuring other Advanced Settings and launching DB Instance

The message Your DB Instance is being created gets displayed, as shown in Figure 7. Click View Your DB Instances.

DB Instance is being created
Figure 7: DB Instance is being created

A new DB instance gets created, as shown in Figure 8. The Endpoint is in the format Hostname:Port and we shall use the Hostname when we configure the target database endpoint in a later section. When an Oracle database is used as the target in a DMS migration, any target schema used in a migration has to be created prior to running the migration. We shall be using the schema DVOHRA created in Oracle database on RDS.

RDS DB Instance ORCL
Figure 8: RDS DB Instance ORCL

For a DMS replication instance to be able to connect to the RDS DB instance, modify the Security Group Inbound rules to allow all traffic. Click the link in Security Groups in Figure 8. The Security group console gets displayed, as shown in Figure 9. Select the Inbound tab and click Edit.

Security group>Inbound>Edit
Figure 9: Security group>Inbound>Edit

In Edit Inbound rules, set Type to All traffic, Protocol to All, Port Range to 0-65535, Source to Anywhere, and click Save, as shown in Figure 10.

Edit Inbound rules
Figure 10: Edit Inbound rules

The Inbound rules get modified to allow all traffic (see Figure 11).

Inbound Rules set to allow all traffic
Figure 11: Inbound Rules set to allow all traffic

The Outbound rules are not required to be modified because they are already set to allow all traffic, as shown in Figure 12.

Outbound Rules
Figure 12: Outbound Rules

Connecting to an Oracle Database Instance on RDS

To use Oracle database as the target, we need to grant the DMS user certain privileges in the Oracle database. Connect to the Oracle database to grant the privileges using the SQL*Plus command line interface.

C:appdeepakproduct12.1.0dbhome_1BIN>sqlplus
   dvohra@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
   (HOST=orcl.crbmlbxmp8qi.us-east-1.rds.amazonaws.com)
   (PORT=1521))(CONNECT_DATA=(SID=ORCL)))

SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 17 14:11:18 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit
   Production

SQL>

Next, grant privileges to the DMS user, which would be different for different users.

SQL> GRANT SELECT ANY TRANSACTION to DVOHRA;
Grant succeeded.

SQL> GRANT CREATE SESSION,CREATE ANY TABLE to DVOHRA;
Grant succeeded.

SQL> GRANT CREATE ANY INDEX to DVOHRA;
Grant succeeded.

SQL> GRANT DROP ANY TABLE to DVOHRA;

Grant succeeded.

SQL> GRANT SELECT ANY TABLE to DVOHRA;

Grant succeeded.

SQL> GRANT INSERT ANY TABLE to DVOHRA;

Grant succeeded.

SQL> GRANT UPDATE ANY TABLE to DVOHRA;

Grant succeeded.

SQL> GRANT CREATE ANY VIEW to DVOHRA;

Grant succeeded.

SQL> GRANT DROP ANY VIEW to DVOHRA;

Grant succeeded.

SQL> GRANT CREATE ANY PROCEDURE to DVOHRA;

Grant succeeded.

SQL> GRANT ALTER ANY PROCEDURE to DVOHRA;

Grant succeeded.

SQL> GRANT DROP ANY PROCEDURE to DVOHRA;

Grant succeeded.

SQL> GRANT CREATE ANY SEQUENCE to DVOHRA;

Grant succeeded.

SQL> GRANT ALTER ANY SEQUENCE to DVOHRA;

Grant succeeded.

SQL> GRANT DROP ANY SEQUENCE to DVOHRA;

Grant succeeded.

SQL>

Creating an EC2 Instance

To create an Oracle database instance on EC2, select an AMI from the AWS Marketplace. Several different AMIs for Oracle database are available. Select Oracle Database 11g Standard Edition, as shown in Figure 13.

Selecting AMI for Oracle Database on EC2
Figure 13: Selecting AMI for Oracle Database on EC2

The Oracle Database AMI based EC2 instance is shown in Figure 14. Obtain the IPv4 Public IP address of the EC2 instance from the EC2 management console.

EC2 Instance for Oracle Database
Figure 14: EC2 Instance for Oracle Database

The Inbound rules for the Security Group in which the EC2 instance is created should allow traffic, as shown in Figure 15. At the very least, connectivity to the Replication instance IP Address should be provided.

Security Group Inbound Rules to Allow All Traffic
Figure 15: Security Group Inbound Rules to Allow All Traffic

Similarly, the Outbound rules should allow all traffic (see Figure 16).

Outbound Rules
Figure 16: Outbound Rules

Creating and Starting an Oracle Database Instance on EC2

Connect to the EC2 instance from a local machine using the Public IP address of the EC2 instance and the Key Pair used to create the EC2 instance. The permissions for the key pair must be set to 400.

[root@localhost ~]# chmod 400 rhel.pem
[root@localhost ~]# ssh -i "rhel.pem" ec2-user@34.234.193.179

The Oracle Database software gets installed, as indicated by the message in Figure 17. The message also indicates that, to use the software, a database needs to be created. A user prompt is displayed “Would you like to create a database now [y|n].”

Connecting to EC2 Instance and installing Oracle Database Software
Figure 17: Connecting to EC2 Instance and installing Oracle Database Software

Specify y to create a database, as shown in Figure 18. A User prompt to provide a name for the Oracle Database is displayed, as shown in Figure 18. The name specified is used as the Oracle SID.

User Prompt to specify an Oracle SID
Figure 18: User Prompt to specify an Oracle SID

Specify SID as ORCL and click Enter. A user prompt to specify a password for the SYS user gets displayed, as shown in Figure 19.

User Prompt to provide a Password for SYS User
Figure 19: User Prompt to provide a Password for SYS User

Re-specify the SYS password at the Confirm SYS Password prompt, as shown in Figure 20. Another User prompt to specify a Password for the SYSTEM user gets displayed.

User Prompt to specify Password for SYSTEM User
Figure 20: User Prompt to specify Password for SYSTEM User

Similarly, specify a password for each of the DBSNMP, SYSMAN, and ADMIN users. The database starts to get created (see Figure 21).

Creating Oracle Database
Figure 21: Creating Oracle Database

The database gets installed on EC2. The complete output is listed:

The Oracle Database Software (ORACLE_HOME) has been installed at
   /u01/app/oracle/product/11.2.0/db1.
Before you can use the Oracle Software, you will need to create a
   database.

Would you like to create a database now [y|n]:  y
Please enter the name for your Oracle Database.
This name will be used as your ORACLE SID (System Identifier):
   ORCL

Please specify the passwords for the database administrative
   accounts.
All passwords must be a minimum of 6 characters in length and must
   contain a combination of letters and numbers.


SYS (Database Administrative Account) Password:
Confirm SYS password:

SYSTEM (Database Administrative Account) Password:
Confirm SYSTEM password:

DBSNMP (Enterprise Manager Administrative Account) Password:
Confirm DBSNMP password:

SYSMAN (Enterprise Manager Administrative Account) Password:
Confirm SYSMAN password:

ADMIN Password:
Confirm ADMIN password:

Please wait while your database is created, it may take up to 15
   minutes.

Copying database files
1% complete
3% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
57% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/ORCL/
   ORCL.log" for further details.
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://ip-172-30-4-238:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
 ...  Stopped.

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 17 19:45:25 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> Connected.
SQL> Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area 4676636672 bytes
Fixed Size                  2261048 bytes
Variable Size             939528136 bytes
Database Buffers         3724541952 bytes
Redo Buffers               10305536 bytes
Database mounted.
SQL>
Database altered.

SQL>
Database altered.

SQL> Disconnected from Oracle Database 11g Release 11.2.0.4.0 -
   64bit Production Oracle Enterprise Manager 11g Database Control
   Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://ip-172-30-4-238:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ....
   started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db1
   /ip-172-30-4-238_ORCL/sysman/log

Adding ORACLE_SID=ORCL to /home/oracle/.bash_profile

The database was created successfully.

The database and config was created successfully.


To use the database web console, navigate to https://34.234.193
   .179:1158/em and login with the username SYSMAN and the password
   you created earlier for the SYSMAN account.
Note that you must have properly configured your security groups to
   allow the IP you are browsing from to connect to port 1158 on
   the database instance.
To connect to the database from the command line, type 'sudo su -
   oracle' to change to the oracle user.
To start working with the database instance type
   'sqlplus / as sysdba'

Thank You for choosing Oracle Database on EC2!

Connecting to an Oracle Database Instance on EC2

Set Oracle user as oracle with the following command:

ec2-user@ip-172-30-4-238 ~> sudo su - oracle

Start a SQL*Plus CLI to connect to the database from the command line.

oracle@ip-172-30-4-238 ~> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 17 19:47:30 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL>

Conclusion

In this first of four articles on migrating an Oracle Database instance from EC2 to RDS, we got started by creating an Oracle Database instance on EC2 and another on RDS. In the second article, we shall create an IAM user, create a database table to migrate, and create a replication instance, including replication endpoints.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories