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
- Creating an Oracle Database Instance on AWS RDS
- Connecting to an Oracle Database Instance on RDS
- Creating an EC2 Instance
- Creating and Starting an Oracle Database Instance on EC2
- Connecting to an Oracle Database Instance on EC2
Create an RDS DB instance for Oracle database. Select Oracle as the engine in the RDS dashboard, as shown in Figure 1.
Figure 1: Selecting Oracle Database as RDS Engine
For Production?, select Dev/Test, as shown in Figure 2, and click Next Step.
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.
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.
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.
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.
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.
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.
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.
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.
Figure 10: Edit Inbound rules
The Inbound rules get modified to allow all traffic (see Figure 11).
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.
Figure 12: Outbound Rules
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:\app\deepak\product\12.1.0\dbhome_1\BIN>sqlplus dvohra@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=orcl.crbmlbxmp8qi.us-east-1.rds.amazonaws.com) (PORT=1521))(CONNECT_DATA=(SID=ORCL))) SQL*Plus: Release 126.96.36.199.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 188.8.131.52.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>
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.
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.
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.
Figure 15: Security Group Inbound Rules to Allow All Traffic
Similarly, the Outbound rules should allow all traffic (see Figure 16).
Figure 16: Outbound Rules
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" email@example.com
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]."
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.
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.
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.
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).
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 184.108.40.206.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 220.127.116.11.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 18.104.22.168.0 - 64bit Production Oracle Enterprise Manager 11g Database Control Release 22.214.171.124.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!
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 126.96.36.199.0 Production on Sun Sep 17 19:47:30 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Release 188.8.131.52.0 - 64bit Production SQL>
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.