Migrate data from on-premises Oracle to RDS for MySQL

Last Updated: Jun 23, 2017

DTS can migrate data from on-premises Oracle databases to RDS MySQL instances for schema migration and existing data migration.

This section introduces how to configure a data migration task on the DTS Console to migrate data from an on-premises Oracle database to an RDS for MySQL instance database.

Migration types

Migration from Oracle to RDS for MySQL only supports schema migration and existing data migration. The restrictions on the two migration types are as follows:

  • Schema migration

    • DTS migrates the structure definition of the migration object to the target instance.

    • The supported object only includes table. Other objects, such as view, synonym, trigger, procedure, function, package, and user define type, are not supported.

  • Existing data migration

    • DTS migrates all existing data of the migrated object from the source to the target.

    • Data changes on the source on-premises Oracle database during migration are not guaranteed to be migrated to the target MySQL. Therefore, to ensure data consistency, we recommend that you do not write data into the source Oracle database during migration.

Required access privileges

An Oracle account and an RDS instance account with specific access privileges are required.

Before configuring the migration task, confirm the accounts are granted with the following permissions:

Migration type Schema migration Existing data migration
On-premises Oracle database Schema Owner Schema Owner
Target RDS MySQL instance Read and write permissions of the database to be migrated Read and write permissions of the database to be migrated

Migration process

DTS conducts data migration from Oracle to RDS for MySQL in the following steps:

  1. Schema migration: Migrate table object.

  2. Existing data migration: Migrate all existing data.

  3. Schema migration: Migrate foreign keys.

After the existing data migration is complete, the migration progress updates to Schema migration 100%, Existing data migration 100%, while the migration status remains migrating. This means that the migration task is going through step 3, the migration of foreign keys. Do not end the task manually during this situation, otherwise it may cause migration data inconsistency.

Data type mapping relationship

The data types defined in MySQL are different from Oracle. Generally, DTS maps the data types of source to target data types during schema migration.

Refer to the following table for the mapping relationship between Oracle and MySQL. Some special data types in Oracle do not have counterparts in MySQL. Therefore, DTS does not support these conversions.

Oracle data type MySQL data type Supported in 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 local time zone datetime[(fractional_seconds_precision)] Yes
timestamp[(fractional_seconds_precision)]with local time zone 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 mongth -/- No
interval day(day_precision) to second[(fractional_seconds_precision)] -/- No

Note:

  • For char(n) type, when the definition length n exceeds 255, DTS automatically converts the type to varchar(n).

  • MySQL does not support the following data types in Oracle: bfile, interval year to month, and interval day to second. If the table to be migrated contains these data types, we recommend substituting them with corresponding data types in MySQL. This allows for a smoother schema migration.

  • The timestamp with [local] time zone in Oracle contains the time zone, while timestamp in MySQL does not. When migrating this type of data, DTS converts the data into the UTC time zone format, and then stores them into MySQL.

Procedure

The following sections explain how to configure a DTS migration task to migrate data from an on-premises Oracle database to an RDS for MySQL database.

Create a database on the RDS instance

During data migration, if the database to be migrated is not defined in the target RDS instance, DTS automatically creates it. In some cases where the database name does not comply with the RDS Definition Standard, you need to create the target RDS database on the RDS Console before configuring the migration task.

The RDS Definition Standard requires the RDS database name to be up to 64 characters in length and:

  • Can have lowercase letters, digits, underscores, and hyphens
  • Must start with a letter
  • Must end with a letter or digit

Create a migration account

When configuring a migration task, you need to provide the migration accounts of the source Oracle database and the target RDS instance. Specific access privileges are required for the accounts, as described in the section Required access privileges.

If you have not created a migration account for your Oracle database, refer to Oracle Grant Syntax Instructions.

For creating and authorizing a RDS for MySQL migration account, refer to the RDS User Manual Creating an account.

Configure a migration task

Perform the following steps to configure a migration task:

  1. Log on to the Alibaba Cloud DTS Console, and click Create Migration Task in the top right to start task configuration.

  2. Configure the following connection information of the on-premises Oracle database and the target RDS instance. Where:

    • Task name

      By default, DTS automatically generates a name for the migration task. The name can be edited, and is not required to be unique.

    • Source instance

      • Instance type: Select On premises databases.
      • Instance Region: Select the closest region to the source instance.
      • Database engine: Select Oracle.
      • Host name or IP address: Enter the accessing IP address to the source Oracle database. The address must be in public access mode.
      • Port: Enter the listener port of the Oracle database.
      • SID: Enter the SID of the Oracle instance.
      • Account: Enter the access account of the Oracle database.
      • Password: Enter the password of the Oracle access account.
    • Target instance

      • Instance type: Select RDS instance.
      • RDS instance ID: Select the instance ID of the target RDS instance to be migrated. The RDS instances can be in classic network or VPC network.
      • Account: Enter the access account to the RDS instance.
      • Password: Enter the password of the RDS access account.

      After the connection information is complete, click Authorize whitelist and enter into next step. In this stage, DTS adds the IP address of DTS server to the whitelist of the target RDS instance to avoid migration failure.

  3. Configure the migration object and migration type

    • Migration type

      The migration type includes schema migration and existing data migration. By default, Schema migration and Existing data migration are checked.

    • Migration object

      Select the object to be migrated: database, table, or column.

      By default, after the object is migrated to the RDS instance, the name remains the same with that in Oracle. If the object bears different names in source and target, use the object name mapping function.

    After the migration object and migration type are configured, click Pre-check and start.

  4. Pre-check

    A pre-check is performed before the migration task is formally started.

    If the pre-check fails, click Failure to the see the reason and solution. Fix the problem accordingly and perform pre-check again until it is successful.

  5. Start the migration task

    After pre-check is successful, start the migration task. The migration status and progress can be viewed in the task list.

Thank you! We've received your feedback.