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
- Creating an IAM User
- Creating an Encryption Key
- Creating Oracle and MySQL DB Instances on RDS
- Preparing Oracle and MySQL DBs for DMS
- Conclusion
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.
Figure 1: Add user
In Add user, specify a User name (dvohra) and select both Access type options, as shown in Figure 2.
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.
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.
Figure 4: Add user>Create policy
In Create Policy, select the Create Your Own Policy option, as shown in Figure 5.
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.
Figure 6: Create Policy
In the Create user wizard, select the newly created DMS policy and click Next, as shown in Figure 7.
Figure 7: Selecting Policy to attach to User
In Review, click Create user, as shown in Figure 8.
Figure 8: Review>Create user
A new user gets created, as shown in Figure 9.
Figure 9: New user created
A new user gets added to the IAM Console>Users, as shown in Figure 10.
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).
Figure 11: Console login link
After creating an IAM user for DMS, sign out as the root user, as shown in Figure 12.
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.
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.
Figure 14: Create key
In Create Alias and Description, specify an Alias (dms) and click Next Step, as shown in Figure 15.
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.
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.
Figure 17: Defining Key Usage Permissions
In Preview Key Policy, click Finish, as shown in Figure 18.
Figure 18: Preview Key Policy
A new encryption key gets created, as shown in Figure 19.
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.
Figure 20: Configuring Advanced Settings
The RDS instances for Oracle and MySQL are shown in Figure 21.
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.
Figure 22: Security Groups
Click Edit for the security group Inbound, as shown in Figure 23.
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.
Figure 24: Edit inbound rules
The modified Inbound rules are shown in Figure 25.
Figure 25: Modified Inbound rules
The Outbound rules allow all traffic by default (see Figure 26).
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.
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.
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”.
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.
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).
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.
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.
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.
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.
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.
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.
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.
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.
