dcsimg
November 20, 2018
Hot Topics:

Migrating an Oracle Database from AWS EC2 to AWS RDS, Part 2

  • May 11, 2018
  • By Deepak Vohra
  • Send Email »
  • More Articles »

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

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.

Create migration
Figure 1: Create migration

In the Welcome page for Database Migration Service, click Next, as shown in Figure 2.

Welcome>Next
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.

Create replication instance
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.

Advanced Settings
Figure 4: Advanced Settings

Select the default Maintenance settings and click Next, as shown in Figure 5.

Create replication instance>Next
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.

Replication instance is being Created
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).

Replication instance created
Figure 7: Replication instance created

A new replication instance gets added in DMS>Replication Instances, as shown in Figure 8.

DMS>Replication Instances
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.

Public DNS Name
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.

RDS DB Instance Endpoint
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.

Source and Target Connection Details
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.

Source and Target Database Connection Details
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.

Advanced Settings
Figure 13: Advanced Settings

Click Run test to test the source database connection, as shown in Figure 14.

Run test
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.

Testing endpoint connections
Figure 15: Testing endpoint connections

If a connection gets established, the message Connection tested successfully gets displayed (see Figure 16).

Connection tested successfully
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.

Testing and Saving Endpoint Connection
Figure 17: Testing and Saving Endpoint Connection

The source and target database endpoint connections are shown in DMS>Endpoints in Figure 18.

Source and Target Database Endpoints
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.






Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap

Thanks for your registration, follow us on our social networks to keep up-to-date