Migrate data from on-premises Oracle to RDS for MySQL

Last Updated: Dec 07, 2017

DTS can migrate data from on-premises Oracle databases to RDS for MySQL databases for schema migration and existing data migration. This document introduces how to configure a data migration task in the DTS console to migrate data from an on-premises Oracle database to an RDS for MySQL database.

Background

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.
    • Only table is supported as the object. 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 may not be migrated to the target MySQL. We recommend that you do not write data into the source Oracle database during migration to ensure data consistency.

Migration process

DTS performs data migration from Oracle to RDS for MySQL through the following steps:

  1. Schema migration: migrates table object.
  2. Existing data migration: migrates all existing data.
  3. Schema migration: migrates foreign keys.

After the existing data migration is complete, the status of migration progress becomes Schema migration 100%, Existing data migration 100%, but the migration status remains migrating. This means that the migration task is migrating foreign keys (step 3). Do not end the task manually under this situation, otherwise it may cause migration data inconsistency.

Data type mapping relationship

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

The following table describes the mapping relationship between Oracle and MySQL. Some special data types in Oracle that do not have counterparts in MySQL are not supported in DTS.

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), if 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 that you replace them with corresponding data types in MySQL. This allows for a smoother schema migration.
  • timestamp with [local] time zone in Oracle contains the time zone, but timestamp in MySQL does not. When migrating this type of data, DTS converts the data into the UTC time zone format before storing them into MySQL.

Prerequisite

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 the following permissions.

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

Procedure

The following sections describe 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 must create the target RDS database in the RDS console before configuring the migration task.

According to the RDS Definition Standard, the RDS database name consists of up to 64 characters and contains lowercase letters, numbers, underscores, and hyphens. It must begin with a letter and end with a letter or number.

Create a migration account

When configuring a migration task, you must 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 Required access privileges.

Configure a migration task

Follow these steps to configure a migration task.

  1. Log on to the Alibaba Cloud DTS console.

  2. Click Data migration from the left-side navigation pane.

  3. Click Create migration task.

  4. Configure the following connection information of the on-premises Oracle database and the target RDS instance, and then click Authorize whitelist and enter into next step.

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

    • Source database

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

      • Instance type: specifies RDS instance.
      • Instance region: specifies the region of the target RDS instance.
      • RDS instance ID: specifies the instance ID of the target RDS instance. The RDS instance can reside in a classic network or VPC network.
      • Database account: specifies the access account to the RDS instance.
      • Database password: specifies the password of the RDS access account.

    In this stage, DTS adds the IP address of the DTS server to the whitelist of the target RDS instance.

  5. Configure the migration object and migration type, and then click Pre-check and start.

    • Migration type: includes schema migration and existing data migration. By default, Schema migration and Existing data migration are checked.

    • Migration object: specifies the object to be migrated (database, table, or column).

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

  6. A pre-check is performed before the migration task is formally started. If the pre-check fails, click Failure to show the reason and solution. Fix the problem accordingly and perform pre-check again until it is successful.

  7. After the 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.