Database Migration Service becomes essential for several reasons, including migrating from a proprietary database to an open source database, migrating between proprietary or open source databases, and migrating to a managed service. We are exploring migrating an Oracle Database from EC2 to RDS and in the first of four articles, “Migrating an Oracle Database from AWS EC2 to AWS RDS, Part 1,” we created an Oracle Database instance on EC2 and another on RDS. In this second article, we shall create a database user, create a database table to migrate, and create a DMS replication instance, including replication endpoints.
This article has the following sections:
- Creating a Database User
- Creating a Database Table
- Creating an AWS DMS Replication Instance
- Creating Replication Endpoints
- Conclusion
Creating a Database User
Create an Oracle Database user to be used with DMS:
SQL> CREATE USER DVOHRA IDENTIFIED BY Calgary_10 DEFAULT TABLESPACE SYSTEM QUOTA 10M ON SYSTEM TEMPORARY TABLESPACE TEMP QUOTA 5M ON SYSTEM; 2 3 4 5 User created.
Grant the user privileges in Oracle database. Only a subset of privileges (http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html#CHAP_Source.Oracle.Privileges) are required, but grant all privileges.
SQL> grant all privileges to DVOHRA identified by password; Grant succeeded.
Oracle Database, when used as source database endpoint in Database Migration Service, must be in ARCHIVELOG mode. Verify that the database is in ARCHIVELOG mode.
SQL> select log_mode from v$database; LOG_MODE ------------ ARCHIVELOG
Creating a Database Table
We shall be discussing two types of migration tasks:
- Migrate Existing Data only
- Migrate Existing Data and Replicate Ongoing Changes
To migrate existing data, create a database table in the DVOHRA schema with the following DDL statement:
SQL> CREATE TABLE DVOHRA.wlslog(time_stamp VARCHAR2(255) PRIMARY KEY,category VARCHAR2(255),type VARCHAR2(255), servername VARCHAR2(255),code VARCHAR2(255),msg VARCHAR2(255)); Table created.
Add table data with the following DMS statements.
SQL> INSERT INTO DVOHRA.wlslog(time_stamp,category,type,
servername,code,msg) VALUES('Apr-8-2014-7:06:16-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000365',
'Server state changed to STANDBY');
INSERT INTO DVOHRA.wlslog(time_stamp,category,type,servername,
code,msg) VALUES('Apr-8-2014-7:06:17-PM-PDT','Notice',
'WebLogicServer','AdminServer','BEA-000365','Server state
changed to STARTING');
INSERT INTO DVOHRA.wlslog(time_stamp,category,type,servername,
code,msg) VALUES('Apr-8-2014-7:06:18-PM-PDT','Notice',
'WebLogicServer','AdminServer','BEA-000365','Server state
changed to ADMIN');
INSERT INTO DVOHRA.wlslog(time_stamp,category,type,servername,
code,msg) VALUES('Apr-8-2014-7:06:19-PM-PDT','Notice',
'WebLogicServer','AdminServer','BEA-000365','Server state
changed to RESUMING');
INSERT INTO DVOHRA.wlslog(time_stamp,category,type,servername,
code,msg) VALUES('Apr-8-2014-7:06:20-PM-PDT','Notice',
'WebLogicServer','AdminServer','BEA-000361','Started WebLogic
AdminServer');
INSERT INTO DVOHRA.wlslog(time_stamp,category,type,servername,
code,msg) VALUES('Apr-8-2014-7:06:21-PM-PDT','Notice',
'WebLogicServer','AdminServer','BEA-000365','Server state
changed to RUNNING');
INSERT INTO DVOHRA.wlslog(time_stamp,category,type,servername,
code,msg) VALUES('Apr-8-2014-7:06:22-PM-PDT','Notice',
'WebLogicServer','AdminServer','BEA-000360','Server started in
RUNNING mode');
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL> COMMIT;
Commit complete.
Creating an AWS DMS Replication Instance
We shall use the same replication instance for creating two different types of migration tasks. To create a replication instance, click Create migration in the DMS Dashboard, as shown in Figure 1.
Figure 1: Create migration
In the Welcome page for Database Migration Service, click Next, as shown in Figure 2.
Figure 2: Welcome>Next
In the Create replication instance, specify a name and description, as shown in Figure 3. Select an Instance class and replication engine version; the default settings for these are used. Select VPC as the VPC in which the RDS DB instance is created. Select the option No to not create a Multi-AZ replication instance. Select the checkbox Publicly accessible to make the replication instance publicly accessible.
Figure 3: Create replication instance
In Advanced, select the Allocated storage (default is 50 GB), the Replication Subnet Group, and Availability Zone (must be No Preference). Select the VPC Security Group(s) as the Security Group in which the RDS DB instance is created. Select the KMS master key as dms. The Advanced settings are shown in Figure 4.
Figure 4: Advanced Settings
Select the default Maintenance settings and click Next, as shown in Figure 5.
Figure 5: Create replication instance>Next
The Connect source and target database endpoints page gets displayed and the replication instance starts to get created, as indicated by the message in Figure 6.
Figure 6: Replication instance is being Created
When the replication instance has been created, the message Replication instance created successfully gets displayed (see Figure 7).
Figure 7: Replication instance created
A new replication instance gets added in DMS>Replication Instances, as shown in Figure 8.
Figure 8: DMS>Replication Instances
Creating Replication Endpoints
The Connect source and target database endpoints page has fields for Server name for source and target database endpoints, as shown earlier in Figure 6. The source database endpoint Server name is the Public DNS of the EC2 instance on which the Oracle Database is running. The Public DNS may be obtained from the EC2 management console, as shown in Figure 9.
Figure 9: Public DNS Name
The Server name for the target database endpoint is derived from the RDS DB instance Endpoint listed in the RDS Console in Figure 10. The Endpoint is in the format Hostname:Port.
Figure 10: RDS DB Instance Endpoint
Select Source engine and Target engine as oracle (see Figure 11). Specify the Server name for source and target database endpoints. Specify Port as 1521 for both source and target database endpoints.
Figure 11: Source and Target Connection Details
Specify SSL mode as none for both source and target database endpoints. Specify User name for source database as DVOHRA and target database as the user configured in the RDS DB instance. Specify the Password for the source database as the password specified when creating a database instance on EC2, and specify the Password for the target database as the password used in RDS DB instance configuration. Specify SID for the source database as the Oracle SID (ORCL) specified when creating Oracle Database on EC2. Specify the SID for the target database endpoint as the SID (ORCL) specified when creating the RDS DB instance, as shown in Figure 12.
Figure 12: Source and Target Database Connection Details
In the Advanced section, select the KMS master key as dms for both source and target database endpoints, as shown in Figure 13.
Figure 13: Advanced Settings
Click Run test to test the source database connection, as shown in Figure 14.
Figure 14: Run test
Similarly, click Run test for the target database connection.
The message Testing endpoint connection gets displayed for both source and target database connections, as shown in Figure 15.
Figure 15: Testing endpoint connections
If a connection gets established, the message Connection tested successfully gets displayed (see Figure 16).
Figure 16: Connection tested successfully
A database endpoint also may be added with Create endpoint in DMS>Endpoints. When using the Create endpoint option, the Test endpoint connection also has the provision to select the VPC and Replication Instance for the endpoint, as shown in Figure 17. Click Save to save the endpoint connection.
Figure 17: Testing and Saving Endpoint Connection
The source and target database endpoint connections are shown in DMS>Endpoints in Figure 18.
Figure 18: Source and Target Database Endpoints
Conclusion
In this second of four articles, we created a database user, created a database table to migrate, and created a replication instance including replication endpoints. In the third article, we shall explore migrating existing data from the source (EC2) to target (RDS) endpoints.
