This topic describes how to migrate data from Amazon RDS for Oracle to ApsaraDB RDS for MySQL by using Data Transmission Service (DTS). DTS supports schema migration, full data migration, and incremental data migration. You can combine these methods to migrate databases without any service interruptions.

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:

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

  1. Log on to the Amazon RDS Management Console.
  2. Go to the Basic Information page of the source Amazon RDS for Oracle instance.
  3. In the Security group rules section, click the name of the security group corresponding to the existing inbound rule.Security group rules
  4. 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.
    Edit AWS inbound rules
  5. 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

  1. Log on to the Data Transmission Service console.
  2. In the left-side navigation pane, click Data Migration.
  3. In the upper-right corner, click Create Migration Task.
  4. 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.
    Connection Address
    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.
  5. 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.
  6. 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.
  7. 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 Note icon corresponding to the failed items to view their details. Perform the precheck again after you have rectified the failed items.
  8. After the precheck succeeds, click Next.
  9. On the Confirm Settings page, set Channel Specification and select Data Transmission Service (Pay-As-You-Go) Service Terms.
  10. 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.