Architecture & DesignMigrating an Oracle Database to MySQL on AWS, Part 1

Migrating an Oracle Database to MySQL on AWS, Part 1

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

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

Why Migrate Databases?

A database may need to be migrated for several reasons, such as the following:

  • Heterogeneous migration to a different database engine. For example, an Oracle database to a MySQL database. Heterogeneous migration could be required to avail features from another database, such as a database type not supported on the source database. A commercial database could be migrated to an open source database. A relational database could benefit by being migrated to a NoSQL database.
  • Homogeneous migration to the same database engine type, but on a different platform. Homogeneous migration could be used to make a backup of a database. A local database could benefit by being migrated to a Cloud platform.
  • Continuous data replication to different target databases for the purpose of testing and development, and regional distribution of databases.
  • Multiple source databases may be migrated to a single database for consolidation.

In two articles, we shall discuss migrating an Oracle Database to a MySQL database with Database Migration Service. This article has the following sections:

Setting the Environment

The only pre-requisite is an AWS account, which may be created at this link.

Creating an IAM User

A user needs to get IAM permissions and roles to access DMS. Additional IAM permissions are required to access other services from DMS; some actions are required from other AWS services such as KMS, IAM, EC2, or CloudWatch. In this section, we shall create an IAM user and attach a policy for the required set of permissions. Select the AWS IAM service and click Add user, as shown in Figure 1.

Add user
Figure 1: Add user

In Add user, specify a User name (dvohra) and select both Access type options, as shown in Figure 2.

Configuring new User
Figure 2: Configuring new User

Select the Custom password option for Console Password, specify a custom password, and click Next, as shown in Figure 3.

Specifying Password
Figure 3: Specifying Password

In Permissions, select the Attach existing policies directly option and click Create policy to create a policy to attach, as shown in Figure 4.

Add user>Create policy
Figure 4: Add user>Create policy

In Create Policy, select the Create Your Own Policy option, as shown in Figure 5.

Create Your Own Policy
Figure 5: Create Your Own Policy

In Review Policy, specify a Policy Name (DMS) and copy the following policy to the Policy Document.

{
   "Version": "2012-10-17",
   "Statement": [
      {
         "Effect": "Allow",
         "Action": "dms:*",
         "Resource": "*"
      },
      {
         "Effect": "Allow",
         "Action": [
            "kms:ListAliases",
            "kms:DescribeKey"
         ],
         "Resource": "*"
      },
      {
         "Effect": "Allow",
         "Action": [
            "iam:GetRole",
            "iam:PassRole",
            "iam:CreateRole",
            "iam:AttachRolePolicy"
         ],
         "Resource": "*"
      },
      {
         "Effect": "Allow",
         "Action": [
            "ec2:DescribeVpcs",
            "ec2:DescribeInternetGateways",
            "ec2:DescribeAvailabilityZones",
            "ec2:DescribeSubnets",
            "ec2:DescribeSecurityGroups",
            "ec2:ModifyNetworkInterfaceAttribute",
            "ec2:CreateNetworkInterface",
            "ec2:DeleteNetworkInterface"
         ],
         "Resource": "*"
      },
      {
         "Effect": "Allow",
         "Action": [
            "cloudwatch:Get*",
            "cloudwatch:List*"
         ],
         "Resource": "*"
      },
      {
         "Effect": "Allow",
         "Action": [
            "logs:DescribeLogGroups",
            "logs:DescribeLogStreams",
            "logs:FilterLogEvents",
            "logs:GetLogEvents"
         ],
         "Resource": "*"
      }
   ]
}

Click Validate Policy and, if the message The policy is valid is generated (see Figure 6), click Create Policy, also shown in Figure 6.

Create Policy
Figure 6: Create Policy

In the Create user wizard, select the newly created DMS policy and click Next, as shown in Figure 7.

Selecting Policy to attach to User
Figure 7: Selecting Policy to attach to User

In Review, click Create user, as shown in Figure 8.

Review>Create user
Figure 8: Review>Create user

A new user gets created, as shown in Figure 9.

New user created
Figure 9: New user created

A new user gets added to the IAM Console>Users, as shown in Figure 10.

IAM Users
Figure 10: IAM Users

Having created a new user, we would need to log in as the new user to create the DMS migration. The Console login link to log in as the new user is obtained from the Security Credentials for the new user (see Figure 11).

Console login link
Figure 11: Console login link

After creating an IAM user for DMS, sign out as the root user, as shown in Figure 12.

Sign out as root User
Figure 12: Sign out as root User

Open the login URL in a browser, specify the User name and password, and click Sign In, as shown in Figure 13.

Logging In as the new IAM User
Figure 13: Logging In as the new IAM User

Creating an Encryption Key

Make sure that you are logged in as the IAM user created in the previous section. We need to create an encryption key that would be used as the Master key when creating a DMS migration. The encryption must be created while logged in as the new IAM user and not as the root user. Select Encryption keys in the IAM console and click Create key, as shown in Figure 14.

Create key
Figure 14: Create key

In Create Alias and Description, specify an Alias (dms) and click Next Step, as shown in Figure 15.

Specifying Alias
Figure 15: Specifying Alias

In Add Tags, click Next Step because tags are not required. In Key Administrators, select the IAM user added as an administrator (see Figure 16). Select Key Deletion as Allow key administrators to delete this key, and click Next Step.

Key Administrators
Figure 16: Key Administrators

In Define Key Usage Permissions, select the IAM users and roles that are able to use the encryption key; select the new IAM user and the dms-vpc-role, as shown in Figure 17. Click Next Step.

Defining Key Usage Permissions
Figure 17: Defining Key Usage Permissions

In Preview Key Policy, click Finish, as shown in Figure 18.

Preview Key Policy
Figure 18: Preview Key Policy

A new encryption key gets created, as shown in Figure 19.

Encryption key created
Figure 19: Encryption key created

Creating Oracle and MySQL DB Instances on RDS

Because we shall be using Oracle and MySQL RDS DB instances as source and target, respectively, for the DMS migration, create two RDS instances, one for Oracle SE database and the other for MySQL. When creating the RDS instances in Configure Advanced Settings, select Network & Security to create a new VPC, create a new DB Subnet Group, and create a new security group, as shown in Figure 20 for Oracle DB. If the AWS account has a limit on the maximum number of VPCs, delete some unused VPCs before creating the RDS instances.

Configuring Advanced Settings
Figure 20: Configuring Advanced Settings

The RDS instances for Oracle and MySQL are shown in Figure 21.

RDS DB Instances
Figure 21: RDS DB Instances

The security groups created automatically do not have the required Inbound/Outbound rules, which need to be modified. Click the link for Security Groups in the RDS Console, as shown for the MySQL DB in Figure 22.

Security Groups
Figure 22: Security Groups

Click Edit for the security group Inbound, as shown in Figure 23.

Security Group>Edit
Figure 23: Security Group>Edit

In Edit inbound rules, select Type as All traffic, Protocol as All, Port Range as 0-65535, Source as Anywhere, and click Save, as shown in Figure 24.

Edit inbound rules
Figure 24: Edit inbound rules

The modified Inbound rules are shown in Figure 25.

Modified Inbound rules
Figure 25: Modified Inbound rules

The Outbound rules allow all traffic by default (see Figure 26).

Outbound Rules
Figure 26: Outbound Rules

The default security group created for Oracle DB on RDS also does not allow all traffic, as shown in Figure 27.

Security Group for Oracle DB on RDS
Figure 27: Security Group for Oracle DB on RDS

Modify the security group Inbound for Oracle DB on RDS with the Edit button, similar to modifying the MySQL Database Security Group Inbound rules, to allow all traffic, as shown in Figure 28.

Security Group for Oracle DB on RDS
Figure 28: Security Group for Oracle DB on RDS

Make a note of the Master Username and Master Password configured for each of the DB instances while creating the instances, as shown for the MySQL DB in Figure 29. Also, when creating the MySQL DB on RDS, none of the configuration settings (Database Name, DB Instance Identifier, Master Username, Master Password) should be MySQL reserved words, such as “MYSQL”.

Master Username and Master Password
Figure 29: Master Username and Master Password

Preparing Oracle and MySQL DBs for DMS

To use Oracle DB and MySQL DB on RDS as DMS source and target, respectively, we need to configure some settings on the DB instances. Connect to the Oracle and MySQL DB instances 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 Oracle DB on RDS is shown in the RDS Console in Figure 30.

Endpoint for Oracle DB on RDS
Figure 30: Endpoint for Oracle DB on RDS

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

sqlplus dvohra@(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 (see Figure 31).

SQL*Plus CLI connected to Oracle DB
Figure 31: SQL*Plus CLI connected to Oracle DB

Enable the database level supplemental logging for DMS.

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

The PL/SQL procedure gets completed, as shown in Figure 32.

Enabling Database Level Supplemental Logging
Figure 32: Enabling Database Level Supplemental Logging

Also, enable PRIMARY KEY logging at database level.

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

The PRIMARY KEY logging gets enabled, as shown in Figure 33.

Enabling PRIMARY KEY Logging
Figure 33: Enabling PRIMARY KEY Logging

Configure archiving of the redo logs for the Oracle DB instance on RDS.

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

Archiving gets configured, as shown in Figure 34.

Configuring Archiving
Figure 34: Configuring Archiving

When migrating a database, the tables and table data also get migrated. Create a database table wlslog in the Oracle database.

CREATE TABLE wlslog(time_stamp VARCHAR2(255) PRIMARY KEY,category
   VARCHAR2(255),type VARCHAR2(255),servername VARCHAR2(255),
   code VARCHAR2(255),msg VARCHAR2(255));
INSERT INTO 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 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 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 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 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 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 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 a PRIMARY KEY logging, or if PRIMARY KEY logging at the database level is not set, alter the table to add supplemental logging.

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

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

CREATE TABLE 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);

The output from the preceding two commands is shown in Figure 35.

Adding Supplemental Logging to a Table already created or to a new Table
Figure 35: Adding Supplemental Logging to a Table already created or to a new Table

Next, prepare the MySQL DB instance on RDS for DMS migration. To connect to MySQL DB, obtain the Endpoint (see Figure 36.

Endpoint for MySQL DB On RDS
Figure 36: Endpoint for MySQL DB On RDS

The host is obtained by removing the port suffix from the Endpoint. Using the MySQL CLI, connect to MySQL from a command line.

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

MySQL CLI gets connected to a MySQL DB instance on RDS, as shown in Figure 37.

Connecting to MySQL from MySQL CLI
Figure 37: Connecting to MySQL from MySQL CLI

Next, create and grant read and write privileges for the MySQL database to the DMS user (dvohra).

CREATE USER '<user acct>'@'%' IDENTIFIED BY
   <user password>';??? Remove as user dvohra already exists
GRANT ALTER, CREATE, DROP, INDEX, INSERT, UPDATE, DELETE,
   SELECT ON myschema.* TO '<user acct>'@'%';
GRANT ALL PRIVILEGES ON awsdms_control.* TO '<user acct>'@'%';

Substituting the user account as dvohra, the output from some of the commands is shown in Figure 38.

Granting MySQL Database Access to DMS User
Figure 38: Granting MySQL Database Access to DMS User

Conclusion

In this article, we introduced migrating an Oracle Database on RDS to a MySQL database on RDS using the AWS Database Migration service. In this article, we discussed setting up the source and target databases. In a subsequent article, we shall create and run a migration.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories