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
- Running the Migration
- Querying Migrated Database Schema
- Restarting a Migration
- Modifying a Migration
- Conclusion
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.
Figure 1: DMS Service in AWS Console
Click Create migration, as shown in Figure 2.
Figure 2: Create Migration
The AWS Database Migration Service wizard gets started, as shown in Figure 3. Click Next in the Welcome page.
Figure 3: AWS Database Migration Service wizard
Next, the Create replication instance page is displayed, as shown in Figure 4.
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.
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.
Figure 6: Advanced Settings
Select the KMS master key as the encryption key (dms) created earlier, as shown in Figure 7. Click Next.
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.
Figure 8: Database Endpoints
Select the Source engine from the drop-down list as oracle, as shown in Figure 9.
Figure 9: Source Engine
Select the Target engine from the drop-down list as mysql, as shown in Figure 10.
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.
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.
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.
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.
Figure 14: Replication Instance Created
After configuring the database endpoints, click Next, as shown in Figure 15.
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).
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.
Figure 17: Adding a Selection Rule
A new task gets added, as shown in Figure 18. Initially, the task status is “Creating.”
Figure 18: New task
When the task gets created, the Status becomes Ready, as shown in Figure 19.
Figure 19: Task Status as Ready
Running the Migration
To run the migration, click Start/Resume, as shown in Figure 20.
Figure 20: Start/Resume
The Status should become Starting (see Figure 21).
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.
Figure 22: Load Complete
The Table statistics tab should list the tables migrated, as shown in Figure 23.
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.
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.
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).
Figure 26: SQL Query on WEBLOGLOG does not list any data
TheWLSLOGtable migrated includes data, as shown by a query result in Figure 27.
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.
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.
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.
Figure 30: Restarting a Migration
The task status again becomes Starting followed by Running (see Figure 31).
Figure 31: Task Status Running
When the task has completed running, the Status becomes Load complete, as shown in Figure 32.
Figure 32: Load complete
Query the WEBLOGICLOG data in MySQL CLI and the data migrated gets listed, as shown in Figure 33.
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.
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.”
Figure 35: Modify Task
The Task Settings shown in Figure 36 may be modified. Click Advanced Settings to modify advanced settings.
Figure 36: Task Settings
In Advanced Settings, the Control table settings may be modified, as shown in Figure 37.
Figure 37: Control Table Settings
The Tuning Settings may be modified, as shown in Figure 38.
Figure 38: Tuning Settings
To add a transformation rule, click add transformation rule, as shown in Figure 39.
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.
Figure 40: Adding Transformation Rules
Specify the table prefix to add to each table migrated (see Figure 41). Click Add transformation rule.
Figure 41: Add transformation rule
Click Modify, as shown in Figure 42.
Figure 42: Modify
In Start task, select Restart and click Start task, as shown in Figure 43.
Figure 43: Start task
The task status becomes Modifying, as shown in Figure 44.
Figure 44: Modifying
The task status may be refreshed with the refresh button. The Status becomes Running, as shown in Figure 45.
Figure 45: Task Running
When the task has completed running, the status becomes Load complete (see Figure 46).
Figure 46: Load Complete
Subsequently, list the tables in MySQL. New tables with a prefix are listed, as shown in Figure 47.
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.
