dcsimg
 

Migrating an Oracle Database to MySQL on AWS, Part 2

Thursday Oct 25th 2018 by Deepak Vohra

Perform the migration begun in a previous article, available here on Developer.com.

AWS Database Migration Service (DMS) is a service to migrate databases on AWS in which the source database could be:

  • A database on AWS (EC2,RDS)
  • A local database
  • A database on some other cloud platform such as Oracle Cloud Platform

The target database could be:

  • A database on AWS (EC2, RDS, Redshift, DynamoDB)
  • A local database
  • A database on some other Cloud platform, such as Oracle Cloud Platform

At least one of the two databases, source and target, must be on AWS. As an example, DMS may not be used to migrate a local database to the Google Cloud platform. In two articles,we are discussing migrating an Oracle Database to a MySQL database with Database Migration Service. In an earlier article, "Migrating an Oracle Database to MySQL on AWS, Part 1," we discussed setting up source and target databases. In this continuation article, we shall perform the migration. This article has the following sections:

Creating a Migration

In this section, we shall create a DMS migration to migrate the Oracle DB instance on RDS to the MySQL DB instance on RDS. Log in as the IAM user (dvohra or other) created earlier. Select the DMS service in AWS console, as shown in Figure 1.

DMS Service in AWS Console
Figure 1: DMS Service in AWS Console

Click Create migration, as shown in Figure 2.

Create Migration
Figure 2: Create Migration

The AWS Database Migration Service wizard gets started, as shown in Figure 3. Click Next in the Welcome page.

AWS Database Migration Service wizard
Figure 3: AWS Database Migration Service wizard

Next, the Create replication instance page is displayed, as shown in Figure 4.

Create replication instance
Figure 4: Create replication instance

Specify a replication instance name, or keep the default setting, as shown in Figure 5. Select an Instance class, or keep the default setting. Select a VPC because no default VPC is configured. Select Multi-AZ as No to use a single Availability Zone. Select the Publicly accessible option.

Configuring Replication Instance
Figure 5: Configuring Replication Instance

In Advanced settings, the default settings for Allocated Storage, Replicated Subnet Group, and Availability zone are shown in Figure 6.

Advanced Settings
Figure 6: Advanced Settings

Select the KMS master key as the encryption key (dms) created earlier, as shown in Figure 7. Click Next.

Selecting KMS master key
Figure 7: Selecting KMS master key

Next, configure the Database endpoints to connect to source and target database endpoints, as shown in Figure 8. The database endpoints may be specified while the replication instance is being created. The source and target endpoints are configured in separate columns labeled Source database connection details and target database connection details.

Database Endpoints
Figure 8: Database Endpoints

Select the Source engine from the drop-down list as oracle, as shown in Figure 9.

Source Engine
Figure 9: Source Engine

Select the Target engine from the drop-down list as mysql, as shown in Figure 10.

Selecting Target Engine
Figure 10: Selecting Target Engine

For the Server names, specify the Endpoints for the DB instances, as listed on the RDS Console by removing the :port suffix. The Server name for the Oracle DB is orcl.crbmlbxmp8qi.us-east-1.rds.amazonaws.com and the Server name for the MySQL DB is mysqldb.crbmlbxmp8qi.us-east-1.rds.amazonaws.com. The port is specified separately in the Port field and is 1521 for Oracle database and 3306 for MySQL database. Select SSL mode as none for both Source and Target databases. Specify the User name and Password as the Master User Name and Master Password configured when creating the RDS instances. The Source and Target connection details are shown in Figure 11.

Source and Target Connection Details
Figure 11: Source and Target Connection Details

Scroll down for the connection Advanced settings. For Oracle Database, specify the SID, as shown in Figure 12. For the MySQL database, set the Extra connection attributes to targetDbType=SPECIFIC_DATABASE. The KMS master key must be the encryption key (dms) created as the IAM user (dvohra) for DMS.

Connection Advanced Settings
Figure 12: Connection Advanced Settings

Click Run test for each of the databases to test the connection. The result should be Connection tested successfully, as shown in Figure 13.

Run test
Figure 13: Run test

Next, a migration task needs to be configured, before which the replication instance should have been created, as shown in Figure 14.

Replication Instance Created
Figure 14: Replication Instance Created

After configuring the database endpoints, click Next, as shown in Figure 15.

Database Endpoints Configured
Figure 15: Database Endpoints Configured

In Task, at least one selection rule must be configured or a task does not get created, as indicated by the error message when Create task is clicked (see Figure 16).

At least one selection rule is required
Figure 16: At least one selection rule is required

Click the add selection rule link shown in Figure 16. In the selection rule detail, configure a Where selection rule. Select Schema as DVOHRA (schema would be different for different users) and specify Table name is like as %. In Action, objects may included or excluded by selecting Include or Exclude. Exclusions are processed after inclusions. Select Action as Include. Source filters to limit the number and type of records transferred from source to target may be added. Click Add selection rule, as shown in Figure 17. Subsequently, click Create task.

Adding a Selection Rule
Figure 17: Adding a Selection Rule

A new task gets added, as shown in Figure 18. Initially, the task status is "Creating."

New task
Figure 18: New task

When the task gets created, the Status becomes Ready, as shown in Figure 19.

Task Status as Ready
Figure 19: Task Status as Ready

Running the Migration

To run the migration, click Start/Resume, as shown in Figure 20.

Start/Resume
Figure 20: Start/Resume

The Status should become Starting (see Figure 21).

Task Starting
Figure 21: Task Starting

After Starting, the status becomes Running. When the migration has completed, the Status becomes Load complete, as shown in Figure 22.

Load Complete
Figure 22: Load Complete

The Table statistics tab should list the tables migrated, as shown in Figure 23.

Table Statistics
Figure 23: Table Statistics

Querying Migrated Database Schema

In the MySQL CLI, list the databases with the show databases command, as shown in Figure 24. The DVOHRA database (schema) gets listed.

Listing Databases
Figure 24: Listing Databases

Select the DVOHRA database with the use DVOHRA command. List the tables with the show tables command, as shown in Figure 25. The WEBLOGICLOG and WLSLOG tables that were created in Oracle database earlier have been migrated and get listed.

Listing Tables
Figure 25: Listing Tables

The table also gets migrated. The WEBLOGICLOG table does not have any data and none gets returned in a SQL query (see Figure 26).

SQL Query on WEBLOGLOG does not list any data
Figure 26: SQL Query on WEBLOGLOG does not list any data

TheWLSLOGtable migrated includes data, as shown by a query result in Figure 27.

Data in WLSLOG Table is migrated
Figure 27: Data in WLSLOG Table is migrated

Restarting a Migration

By default, the migration does not actively poll in the source database to migrate the changes, if any. If any modifications are made to the source database, such as data is added to a table or a new table is created, the migration has to be run again. As an example, add data to the weblogiclog table in the Oracle database.

INSERT INTO weblogiclog(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 weblogiclog(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 weblogiclog(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 weblogiclog(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 weblogiclog(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 weblogiclog(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 weblogiclog(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');

To migrate the data added to MySQL, click Start/Resume, as shown in Figure 28.

Restarting a Migration
Figure 28: Restarting a Migration

In the Start task dialog, two options are provided: Start and Restart, as shown in Figure 29. The Start option only loads new tables (full load) and tables that were incomplete (partial loads) in a previous run of the task.

Start Task
Figure 29: Start Task

Because the WEBLOGICLOG table has already been migrated (full load is complete) and new data needs to be migrated, the Restart option needs to be selected, as shown in Figure 30.

Restarting a Migration
Figure 30: Restarting a Migration

The task status again becomes Starting followed by Running (see Figure 31).

Task Status Running
Figure 31: Task Status Running

When the task has completed running, the Status becomes Load complete, as shown in Figure 32.

Load complete
Figure 32: Load complete

Query the WEBLOGICLOG data in MySQL CLI and the data migrated gets listed, as shown in Figure 33.

Data migrated in restarting task
Figure 33: Data migrated in restarting task

Modifying a Migration

A migration task may need to be modified, such as to add/remove a selection rule or modify one of the other task settings. A task may be modified when it is not running and status is Load complete. Click Modify to modify a task, as shown in Figure 34.

Modify
Figure 34: Modify

The Modify Task dialog gets started. Though different Migration types are listed, as shown in Figure 35, the migration type cannot be modified at this time. The default setting is "Migrate existing data."

Modify Task
Figure 35: Modify Task

The Task Settings shown in Figure 36 may be modified. Click Advanced Settings to modify advanced settings.

Task Settings
Figure 36: Task Settings

In Advanced Settings, the Control table settings may be modified, as shown in Figure 37.

Control Table Settings
Figure 37: Control Table Settings

The Tuning Settings may be modified, as shown in Figure 38.

Tuning Settings
Figure 38: Tuning Settings

To add a transformation rule, click add transformation rule, as shown in Figure 39.

Add Transformation Rule
Figure 39: Add Transformation Rule

In Transformation rules, select the Target for the transformation as Table, as shown in Figure 40. Select Schema name is as DVOHRA. Specify Table name is like as %. Select Action as Add prefix, as an example.

Adding Transformation Rules
Figure 40: Adding Transformation Rules

Specify the table prefix to add to each table migrated (see Figure 41). Click Add transformation rule.

Add transformation rule
Figure 41: Add transformation rule

Click Modify, as shown in Figure 42.

Modify
Figure 42: Modify

In Start task, select Restart and click Start task, as shown in Figure 43.

Start task
Figure 43: Start task

The task status becomes Modifying, as shown in Figure 44.

Modifying
Figure 44: Modifying

The task status may be refreshed with the refresh button. The Status becomes Running, as shown in Figure 45.

Task Running
Figure 45: Task Running

When the task has completed running, the status becomes Load complete (see Figure 46).

Load Complete
Figure 46: Load Complete

Subsequently, list the tables in MySQL. New tables with a prefix are listed, as shown in Figure 47.

Tables with prefix
Figure 47: Tables with prefix

Conclusion

In two articles, we discussed migrating an Oracle Database on RDS to a MySQL database on RDS using the AWS Database Migration service.

Home
Mobile Site | Full Site