Migrate data from Amazon RDS for Oracle to ApsaraDB RDS for MySQL - User Guide| Alibaba Cloud Documentation Center

Prerequisites

To ensure that DTS can access Amazon RDS for Oracle through the public network, you need to set Public Availability to Yes in Amazon RDS for Oracle.
The version of Amazon RDS for Oracle must be V10g, V11g, or V12c.
The version of ApsaraDB RDS for MySQL must be V5.6 or V5.7.
The storage space of your ApsaraDB RDS for MySQL instance is at least twice the size of the data to be migrated from Amazon RDS for Oracle.

Note The binlog files generated during the migration occupy some space. The files are automatically cleared after the migration is completed.

Precautions

If the source instance does not have a primary key or uniqueness constraint, and the fields in the table are not unique, there may be duplicate data in the destination database.
Table names under the ApsaraDB RDS for MySQL instance are case-insensitive. If you create a table whose name contains uppercase letters, ApsaraDB RDS for MySQL converts the uppercase letters to lowercase letters before the table is created.
If the source database has tables that have the same name in different cases, the objects to be migrated may have the same name. This causes the "The object already exists" error to be displayed during schema migration. In this case, use the object name mapping feature provided by DTS to rename the objects with the same name when configuring the objects to be migrated. For more information, see Object name mapping.
If the database to be migrated does not exist in the ApsaraDB RDS for MySQL instance, DTS will automatically create a new database. However, in the following two cases, you need to Create a database in the destination ApsaraDB RDS for MySQL instance.
- The database name does not comply with the naming conventions of databases under ApsaraDB RDS for MySQL instances. For more information, see Create a database.
- The name of the database to be migrated in the source Amazon RDS for Oracle instance is different from that in the destination ApsaraDB RDS for MySQL instance.

Billing information


Migration type	Configuration fee	Public network traffic fee
Full data migration	Not billed	Not billed
Incremental data migration	Billed. For more information, see Data Transmission Service Pricing.	Not billed

Migration type description

Schema migration
DTS supports schema migration of tables, indexes, constraints, and sequences. Other objects such as views, synonyms, triggers, stored procedures, stored functions, packages, and custom object types are not supported.
Full data migration
DTS migrates all the existing data of objects from the Amazon RDS for Oracle database to the database in the ApsaraDB RDS for MySQL instance.
Incremental data migration
In addition to migrating the existing data, DTS also captures the redo logs generated by the Amazon RDS for Oracle database. The incremental data of the Amazon RDS for Oracle database is synchronized to the ApsaraDB RDS for MySQL instance. Incremental data migration allows you to migrate Amazon RDS for Oracle databases without any service interruptions.

SQL operations that can be synchronized during incremental data migration

INSERT, DELETE, and UPDATE
CREATE TABLE

Note The CREATE TABLE operations for creating partition tables or tables that contain functions cannot be synchronized.
ALTER TABLE operations, only including ADD COLUMN, DROP COLUMN, RENAME COLUMN, and ADD INDEX
DROP TABLE
RENAME TABLE, TRUNCATE TABLE, and CREATE INDEX

Migration permission requirements


Source database	Schema migration	Full data migration	Incremental data migration
Amazon RDS for Oracle database	Owner permissions on the schema to be migrated	Owner permissions on the schema to be migrated	Master user permissions
ApsaraDB RDS for MySQL instance	Read and write permissions on the destination database	Read and write permissions on the destination database	Read and write permissions on the destination database

How to create a database account and grant permissions to the account:

For operations in an Amazon RDS for Oracle database, see CREATE USER and GRANT.
For operations in an ApsaraDB RDS for MySQL instance, see Create an account and Modify account permissions.

Data types and mappings

There are some differences between MySQL and Oracle data types. Therefore, DTS maps the data types of Oracle and MySQL according to their definitions during schema migration. The following table describes the mappings of the data types.


Oracle data type	MySQL data type	Supported by DTS
varchar2(n [char/byte])	varchar(n)	Yes
nvarchar2[(n)]	national varchar[(n)]	Yes
char[(n [byte/char])]	char[(n)]	Yes
nchar[(n)]	national char[(n)]	Yes
number[(p[,s])]	decimal[(p[,s])]	Yes
float(p)]	double	Yes
long	longtext	Yes
date	datetime	Yes
binary_float	decimal(65,8)	Yes
binary_double	double	Yes
timestamp[(fractional_seconds_precision)]	datetime[(fractional_seconds_precision)]	Yes
timestamp[(fractional_seconds_precision)]with localtimezone	datetime[(fractional_seconds_precision)]	Yes
timestamp[(fractional_seconds_precision)]with localtimezone	datetime[(fractional_seconds_precision)]	Yes
clob	longtext	Yes
nclob	longtext	Yes
blob	longblob	Yes
raw	varbinary(2000)	Yes
long raw	longblob	Yes
bfile	-	No
interval year(year_precision) to month	-	No
interval day(day_precision)tosecond[(fractional_seconds_precision)]	-	No

Note

A char column with a length greater than 255 Bytes is converted to the varchar(n) type.
MySQL does not support Oracle data types such as bfile, interval year to month, and interval day to second. Therefore, these data types are not converted during schema migration.
The schema migration fails if the table to be migrated contains these three data types. When you select a migration object, you must exclude columns with these three data types from the object to be migrated.
The timestamp data type of MySQL does not contain the time zone information. However, Oracle provides timestamp with time zone and timestamp with local time zone data types that allow you to store datetime with the time zone information. Therefore, DTS converts the values of these data types from the current time zone to UTC for storage in the ApsaraDB RDS for MySQL instance.
Some tables may fail to be migrated because MySQL limits the row size of tables. In this case, you must exclude these tables from the object to be migrated, or adjust the fields in these tables to meet the requirements of MySQL.

Premigration preparation

Log on to the Amazon RDS Management Console.
Go to the Basic Information page of the source Amazon RDS for Oracle instance.
In the Security group rules section, click the name of the security group corresponding to the existing inbound rule.
On the Security Groups page, click the Inbound tab in the Security Group section. On the Inbound tab, click Edit to add IP address ranges of the DTS server in the corresponding region to the inbound rule. For more information about the IP address ranges, see DTS IP address ranges.
Note
- You only need to add the DTS IP address ranges corresponding to the region where the destination database is located. In this case, the source database is located in Singapore and the destination database is located in Hangzhou. You only need to add the DTS IP address ranges corresponding to China (Hangzhou).
- You can add the required IP address ranges to the inbound rule at one time.
Adjust log configuration of Amazon RDS for Oracle. Skip this step if you do not need to perform incremental data migration.
1. Use the Master User account and the SQL*Plus tool provided by Oracle to connect to the Amazon RDS for Oracle database.
2. Run the archive log list; command to confirm that the Amazon RDS for Oracle instance is in archiving mode.
  
  Note If the instance is not archived, enable the archiving mode. For more information, see Managing Archived Redo Logs.
3. Enable the force logging mode.
```
exec rdsadmin.rdsadmin_util.force_logging(p_enable => true);
```
4. Enable primary key supplemental logging.
```
begin rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD',p_type => 'PRIMARY KEY');end;/
```
5. Enable unique key supplemental logging.
```
begin rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD',p_type => 'UNIQUE');end;/
```
6. Set the retention period of archived logs.
  
  Note We recommend that you set the retention period of archived logs to at least 24 hours.
```
begin rdsadmin.rdsadmin_util.set_configuration(name => 'archivelog retention hours', value => '24');end;/
```
7. Submit changes.
```
commit;
```

Procedure

Log on to the Data Transmission Service console.
In the left-side navigation pane, click Data Migration.
In the upper-right corner, click Create Migration Task.

Configure the parameters of Source and Destination Databases. Configure Source and Destination Databases


Category	Parameter	Description
Task Name	-	DTS automatically generates a name for each task. Task names are not required to be unique. You can modify task names as needed. We recommend that you specify meaningful names to help identify the tasks.
Source Database	Instance Type	Select User-Created Database with Public IP Address.
	Instance Region	When the instance type is set to User-Created Database with Public IP Address, you do not need to set Instance Region. Note Click Get IP Address ranges of DTS corresponding to Instance Region to obtain IP address ranges of the DTS server and add the obtained IP address ranges to the inbound rule of Amazon RDS for Oracle. For more information, see Premigration preparation.
	Database Type	Select Oracle.
	Hostname or IP Address	Enter the URL of the Amazon RDS for Oracle database. Note You can query the database connection information on the Basic Information page of Amazon RDS for Oracle.
	Port Number	Enter the port of the Amazon RDS for Oracle database. The default value is 1521.
	Instance Type	Non-RAC Instance: After this option is selected, you also need to enter SID. RAC Instance: After this option is selected, you also need to enter Service Name. In this case, select Non-RAC Instance and enter SID.
	Database Account	Enter the account used to connect to the Amazon RDS for Oracle database. For permission requirements, see Migration permission requirements.
	Database Password	Enter the password for the Amazon RDS for Oracle database account. Note After the source database information is specified, click Test Connectivity corresponding to the Database Password to verify whether the specified information is correct. If the source database information is correct, the Test Passed message is displayed. If the Test Failed message is displayed, click Diagnose in the Test Failed message. Adjust the entered source database information as prompted.
Destination Database	Instance Type	Select RDS Instance.
	Instance Region	Select the region of the destination instance.
	RDS Instance ID	Select the ID of the destination instance.
	Database Account	Enter the account used to connect to the database under the destination instance. For permission requirements, see Migration permission requirements.
	Database Password	Enter the password for the account of the database under the destination instance. Note After the destination database information is specified, click Test Connectivity corresponding to Database Password to verify whether the specified information is correct. If the destination database information is correct, the Test Passed message is displayed. If the Test Failed message is displayed, click Diagnose in the Test Failed message. Adjust the specified destination database information as prompted.

After the configuration is complete, click Set Whitelist and Next in the lower-right corner.

Note The IP address of the DTS server is automatically added to the whitelist of the destination RDS instance. This ensures that the DTS server can connect to the destination instance. After the migration is complete, you can delete the IP address of the DTS server from the whitelist if you no longer need it. For more information, see Configure a whitelist.

Configure migration objects and types. Configure Migration Types and Objects


Parameter	Description
Migration Type	If you only need to migrate the existing data, select Schema Migration and Full Data Migration. Note To ensure data consistency, do not write new data to the Amazon RDS for Oracle database during full data migration. If you need to migrate the data without stopping your business, select Schema Migration, Full Data Migration, and Incremental Data Migration.
Available	Select the database to be migrated from the Available section, and click the icon to move the database to the Selected section. Note The object to be migrated can be a database, a table, or a column. After an object is migrated to the destination RDS instance, the name of the object remains the same as that in the Amazon RDS for Oracle database. If the object to be migrated has a different name in the destination instance, you can use the object name mapping feature provided by DTS. For more information, see Object name mapping.

In the lower-right corner, click Precheck.
Note
- A precheck is performed before a migration task starts. The migration task starts only after the precheck succeeds.
- If the precheck fails, click the icon corresponding to the failed items to view their details. Perform the precheck again after you have rectified the failed items.
After the precheck succeeds, click Next.
On the Confirm Settings page, set Channel Specification and select Data Transmission Service (Pay-As-You-Go) Service Terms.
Click Buy and Start to start the migration.
- Full data migration
  Do not manually stop a migration task, because the system may fail to migrate the full data of the database. Wait until the migration task stops automatically.
- An incremental data migration task does not automatically end. You need to manually end the migration task.
  
  Note Select an appropriate time point to manually end the migration task. For example, you can end the migration task during off-peak hours for business or before you migrate your business to the RDS instance.
  1. When the status of the migration task is The migration task is not delayed, stop writing data to the source database for several minutes. The latency may be displayed.
  2. When the status of the migration task becomes The migration task is not delayed again, stop the migration task manually.
  3. Migrate business to the RDS instance.

Subsequent operations

The database account used for data migration has read and write permissions. To ensure database security, delete the accounts of both source and destination databases after the migration is complete.