How to Migrate an Oracle Database to MySQL Using AWS Database Migration Service

AWS Database Migration Service (DMS) is a service to migrate databases on AWS. The target database has to be one of the supported databases on AWS such as one of the AWS Relational Database Service (RDS) hosted databases, Amazon DynamoDB, or Amazon Redshift. The source database could be a database on AWS, a local database, or a database on some other cloud platform such as Oracle Database Cloud service. The source database incurs zero downtime during the migration process. 

Why Migrate Databases?

You may need to migrate a database for one or more of several reasons such as the following:

• Homogeneous migration—Migration to the same database engine type but on a different platform. Homogeneous migration could be used to make a backup of a database. 
• Heterogeneous migration—Migration to a different database engine, for example, Oracle Database to MySQL database. Heterogeneous migration could be required to avail of features from another database such as a database data type not supported on the source database. A commercial database could be migrated to an open source database and vice versa. A relational database could benefit by migrating to a NoSQL database. 
• Data replication—Continuous data replication to different target databases for the purpose of testing and development, and regional distribution of databases.
• Database Consolidation—Multiple source databases may be migrated to a single database for consolidation. 
• Platform migration—Data on a local machine may be migrated to a cloud database service. The benefits of a cloud-based Database-as-a-Service (DBaaS) include a fully managed service not requiring a DBA, high availability with failover, scalability, and integration with other cloud services for automated monitoring and automated logging.

In this article, we will migrate Oracle Database to MySQL database. The migration could be necessary if you prefer the open-source MySQL database to the commercial Oracle Database. MySQL and Oracle databases are completely different and you may want to migrate to MySQL for one or more of several reasons such as data types support, operating system support, and scale of deployment. 

The only prerequisite is an AWS account, which may be created here.

Step 1: Creating Oracle and MySQL DB Instances on RDS

As we shall be using Oracle and MySQL RDS DB instances as the source and target respectively for the DMS migration, create two RDS instances, one for Oracle Database and the other for MySQL. Before creating the RDS instances, create a new VPC (Virtual Private Cloud), create a new DB Subnet Group, and optionally create a new security group. The default VPC Security group may be used. If the AWS account has a limit on the maximum number of VPCs, delete some unused VPCs before creating the RDS instances. When creating the RDS instances, set the Public access option to Yes as we would need to access these instances directly to run some preparation commands before a DMS migration is performed, and also verify the migrated data after the migration has been completed. As RDS does not create a database by default, we need to select the Additional configurations and specify an initial database name for each of the RDS instances. For the MySQL RDS instance database name mysqldb is used, and for the Oracle RDS instance database name orcl is used. Certain reserved words such as “mysql” for a MySQL RDS instance cannot be used for any of the configuration settings (Database Name, DB Instance Identifier, Master Username, Master Password). The source and target RDS instances for Oracle and MySQL are shown in Figure 1.

Figure 1. RDS DB Instances

If security groups used do not have the required Inbound/Outbound rules, they may need to be modified. Click on the link for Security Groups in the RDS Console for each database instance. Verify that the security group used has settings for Type as All traffic, Protocol as All, Port Range as All, and Source as Anywhere (0.0.0.0/0). If not, these settings click on the Edit inbound rules button and modify the settings, and click on Save rules. Similarly, the Outbound rules should allow all traffic. Make a note of the Master Username and Master Password configured for each of the DB instances. 

Step 2: Preparing Oracle and MySQL DBs for DMS

To be able to use Oracle DB on RDS as a DMS source we need to configure some settings on the DB instance. Connect to the Oracle instance in a command line shell to configure the settings. To connect to the RDS DB instances in a command line shell we need to obtain the Endpoint for the RDS instances. The Endpoint for the DB instances on RDS may be obtained from the RDS Console.

Install Oracle Instant Client. Connect to the Oracle DB instance with the following sqlplus command in which the HOST, SID, and PORT could be different for different users; the HOST value is obtained by removing the port suffix from the Endpoint.

sqlplus admin@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.crbmlbxmp8qi.us-east-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))

The SQL*Plus command-line interface gets connected to the Oracle DB on RDS. Enable the database level supplemental logging for DMS.

exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');

The PL/SQL procedure gets completed successfully.

Also enable PRIMARY KEY logging at the database level with the command:

exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','PRIMARY KEY');

As the source database is Oracle, replicating ongoing changes requires supplemental logging to be turned on. Ensure your archive logs are retained on the server for a sufficient amount of time, (24 hours is usually enough.) To set your archivelog retention on RDS databases you can use the following command:

exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',24);

When migrating a database, the tables and table data also get migrated. Create a new schema specifically for a database migration. Grant CREATE SESSION and CREATE TABLE privileges to the new user.

CREATE USER dms_user IDENTIFIED BY dms_user_pw
DEFAULT TABLESPACE users
QUOTA unlimited ON users;

GRANT CREATE SESSION, CREATE TABLE TO dms_user;

Create a database table called wlslog in the Oracle Database.

CREATE TABLE dms_user.wlslog(time_stamp VARCHAR2(255) PRIMARY KEY,category VARCHAR2(255),type VARCHAR2(255),servername VARCHAR2(255), code VARCHAR2(255),msg VARCHAR2(255));
INSERT INTO dms_user.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 dms_user.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 dms_user.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 dms_user.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 dms_user.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 dms_user.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 dms_user.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');

To add supplemental logging for a table without PRIMARY KEY logging or if PRIMARY KEY logging at the database level is not set, alter the table to add supplemental logging.

alter table dms_user.wlslog add supplemental log data (ALL) columns;

Supplemental logging may also be enabled in the CREATE TABLE statement itself 

CREATE TABLE dms_user.wlslog(time_stamp VARCHAR2(255) PRIMARY KEY,category VARCHAR2(255),type VARCHAR2(255),servername VARCHAR2(255), code VARCHAR2(255),msg VARCHAR2(255),supplemental log data(ALL) columns);

Step 3: Creating a Replication Instance

In this section, we will create a replication instance to migrate the Oracle DB instance on RDS to the MySQL DB instance on RDS. Select the DMS service in the AWS console. Click on Create replication instance as shown in Figure 2.

Figure 2. Create replication instance

The Create replication instance wizard gets started. Specify a replication instance name (ora-mysql-repl) in the Name field. Select an Instance class; dms.t3.medium used. Select the AWS DMS version (3.4.7 used) in the Engine version drop-down. Select Allocated storage; 50 GiB used. Select an Amazon Virtual Private Cloud (VPC) where your replication instance should run. Select Multi-AZ drop-down as Single-AZ to use a single Availability Zone. Select the Publicly accessible option.

In Advanced security and network configuration, select a Replication subnet group. Keep Availability zone as the default value of No preference. Select the VPC security group(s) as default. Select the KMS key as the Default (aws/dms). Click on Create. A replication instance gets created as shown in Figure 3.

Figure 3. Replication instance 

Step 4: Creating Endpoints

Next, configure the Endpoints, which are the source and target database endpoints. Select Endpoints in the margin. Click on Create endpoint. The source and target endpoints are created separately. In the DMS -> Endpoints -> Create endpoint wizard, select Endpoint type as Source endpoint. Click in the Select RDS DB instance checkbox. Select the orcl RDS instance from the drop-down as the orcl instance created earlier. In the Endpoint configuration header, specify an Endpoint identifier; orcl. The Source engine should get selected as Oracle because an Oracle Database RDS engine is selected. In Access to endpoint database, select the Provide access information manually option. The Server name and Port should get added automatically. Specify Password for the ADMIN user; the user name must be uppercase because Oracle Database is case-sensitive. Keep the Secure Socket Layer (SSL) mode as none. The SID/Service name should get filled automatically with a value (ORCL) that is configured in the Oracle RDS instance. In the Test endpoint connection (optional) header, click on Run test. The replication instance ora-mysql-repl is used for the endpoint connection. A Status of successful indicates that a connection gets established. Click on Create endpoint. An endpoint for the source database gets created as shown in Figure 4.

Figure 4. Source Endpoint

To create a target endpoint, click on Create endpoint again. The Create endpoint wizard is again launched. This time, select Endpoint type as Target endpoint. Click in the Select RDS DB instance checkbox and select the mysqldb instance. In the Endpoint configuration header, the Endpoint identifier should get added automatically. The Target engine gets set to MySQL. For Access to endpoint database, select Provide access information manually. The Server name and Port should get added automatically from the MySQL RDS instance. Specify the Password for the admin user. The user name must be specified in lowercase because MySQL uses lowercase. Keep the Secure Socket Layer (SSL) mode as none. In the Test endpoint connection (optional) header, click on Run test. The replication instance ora-mysql-repl is used for the endpoint connection. A Status of successful indicates that a connection gets established. Click on Create endpoint. A target endpoint gets created as shown in Figure 5.

Figure 5. Target Endpoint

Step 5: Creating a Task

Next, we need to create a migration task, but before that, we need to have created a replication instance as discussed earlier. Select Database migration tasks in the margin. In the DMS -> Database migration tasks table, click on Create task. In the Create database migration task wizard, specify a Task identifier (orcl-to-mysql) in the Task configuration header. In the Replication instance drop-down, select the replication instance created earlier. In the Source database endpoint, select the orcl endpoint created for the Oracle Database RDS instance. For the Target database endpoint, select the mysqldb endpoint created earlier. For Migration type, select Migrate existing data and replicate ongoing changes. 

In a task, at least one selection rule must be configured or a task does not get created and an error message is generated. In the Table mappings header, click on the Selection rules drop-down. Click on the Add new selection rule button. In the selection rule detail, configure a Where selection rule. Select Enter Schema in the Schema drop-down. Specify Source name as DMS_USER, and Source table name as WLSLOG. The schema/table/view/column names must be uppercase because Oracle Database is case-sensitive. The % character is a wildcard for schema/table name, which implies all schemas/tables. In the Action field, objects may be included or excluded by selecting Include or Exclude. Exclusions are processed after inclusions. Select Action as Include. Source filters limit the number and type of records transferred from source to target that may be added. The selection rule configuration is shown in Figure 6.

Figure 6. Selection rule

You can use transformation rules to change or transform schema, table, or column names of some or all of the selected objects. Click on the Add transformation rule button to add one. As an example, transform the table name. In the Rule target drop-down, select table. In Source name, select Enter a schema. Specify the schema name as DMS_USER. Specify the Source table name as WLSLOG. In the Action drop-down, select from one of the available actions, such as add/remove/replace prefix/suffix among others. Select the Action as Make lowercase because MySQL stores schema/table names in lowercase. The transformation rule for table name is shown in Figure 7. 

Figure 7. Transformation rule to make table name lowercase

Similarly, add another transformation rule to make the schema name lowercase as shown in Figure 8.

Figure 8. Transformation rule to make schema name lowercase

In Task settings -> Advanced task settings -> Control table settings, set Create control table in target using schema to mysqldb. The Premigration assessment may optionally be performed by selecting the Enable premigration assessment run checkbox. In Migration task startup configuration, select Manually later. The other option is Automatically on create, which is available only if the premigration assessment is not enabled. Subsequently, click on Create task. 

A new task gets added. Initially, the task status is “Creating.” When the task gets created the Status becomes Ready, as shown in Figure 9.

Figure 9. Task Status as Ready

Step 6: Running the Task

To run the migration task, select the task in the table with the checkbox, and click on Restart/Resume in the Actions drop-down. The Status should become Starting. After Starting, the status becomes Running and when the migration has completed, the Status becomes Load complete, as shown in Figure 10. The Table statistics tab should list the tables migrated.

Figure 10. Load complete

Step 7: Querying Migrated Database Schema

Use the MySQL client interface to list the databases and tables migrated. First, connect to the MySQL RDS instance. To connect to MySQL DB, obtain the Endpoint from its Configuration Details. Using the MySQL client, connect to MySQL from a command line. Provide the password when prompted:

mysql -h mysqldb.crbmlbxmp8qi.us-east-1.rds.amazonaws.com -P 3306 -u admin  –p

List the schemas with the SHOW SCHEMAS command. The dms_user schema gets listed.

~$ mysql -h mysqldb.crbmlbxmp8qi.us-east-1.rds.amazonaws.com -P 3306 -u admin -p 
Enter password: 
MySQL [(none)]> SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| dms_user           |
| information_schema |
| mysql              |
| mysqldb            |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.078 sec)

Select the dms_user database with the use dms_user command. List the tables with the show tables command. The wlslog table that was created in Oracle Database earlier has been migrated and gets listed. 

MySQL [(none)]> use dms_user;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [dms_user]> SHOW TABLES;
+--------------------+
| Tables_in_dms_user |
+--------------------+
| wlslog             |
+--------------------+
1 row in set (0.078 sec)

The wlslog table migrated includes data as shown by a query result.

MySQL [dms_user]> SELECT * FROM wlslog;
+---------------------------+----------+----------------+-------------+------------+----------------------------------+
| TIME_STAMP                | CATEGORY | TYPE           | SERVERNAME  | CODE       | MSG                              |
+---------------------------+----------+----------------+-------------+------------+----------------------------------+
| Apr-8-2014-7:06:16-PM-PDT | Notice   | WebLogicServer | AdminServer | BEA-000365 | Server state changed to STANDBY  |
| Apr-8-2014-7:06:17-PM-PDT | Notice   | WebLogicServer | AdminServer | BEA-000365 | Server state changed to STARTING |
| Apr-8-2014-7:06:18-PM-PDT | Notice   | WebLogicServer | AdminServer | BEA-000365 | Server state changed to ADMIN    |
| Apr-8-2014-7:06:19-PM-PDT | Notice   | WebLogicServer | AdminServer | BEA-000365 | Server state changed to RESUMING |
| Apr-8-2014-7:06:20-PM-PDT | Notice   | WebLogicServer | AdminServer | BEA-000361 | Started WebLogic AdminServer     |
| Apr-8-2014-7:06:21-PM-PDT | Notice   | WebLogicServer | AdminServer | BEA-000365 | Server state changed to RUNNING  |
| Apr-8-2014-7:06:22-PM-PDT | Notice   | WebLogicServer | AdminServer | BEA-000360 | Server started in RUNNING mode   |
+---------------------------+----------+----------------+-------------+------------+----------------------------------+
7 rows in set (0.078 sec)

MySQL [dms_user]> 

By default, a migration does not actively poll the source database to migrate any changes. 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 task has to be run again. A migration task may need to be modified, such as add/remove a selection rule or modify one of the other task settings. A task may be modified when it is not running. Click on Modify to modify a task.