Migrate data from self-managed Oracle to ApsaraDB RDS for MySQL - Data Transmission Service

Data Transmission Service (DTS) migrates data from a self-managed Oracle database to an ApsaraDB RDS for MySQL instance with minimal downtime. DTS supports schema migration, full data migration, and incremental data migration — select all three to keep your application running while data is being transferred.

Prerequisites

Before you begin, make sure that you have:

A source Oracle database and a destination ApsaraDB RDS for MySQL instance. To create an RDS for MySQL instance, see Create an ApsaraDB RDS for MySQL instance
The source Oracle database running in ARCHIVELOG mode, with archive log files accessible and a suitable retention period set. See Managing Archived Redo Log Files
Supplemental logging enabled on the source Oracle database, with SUPPLEMENTAL_LOG_DATA_PK and SUPPLEMENTAL_LOG_DATA_UI set to Yes. See Supplemental Logging
For supported source and destination database versions, see Overview of data migration scenarios

Billing

Migration type	Instance configuration fee	Internet traffic fee
Schema migration and full data migration	Free	Charged only when data is migrated from Alibaba Cloud over the Internet. See Billing overview.
Incremental data migration	Charged. See Billing overview.	—

Migration types

Migration type	Description
Schema migration	Migrates schemas of selected objects to the destination database. Nested tables are not supported. Clustered tables and index-organized tables (IOTs) are converted to common tables. Function-based indexes, domain indexes, bitmap indexes, and reverse indexes are not migrated. Views, synonyms, stored procedures, functions, packages, and user-defined types are not migrated.
Full data migration	Migrates all existing data from the source Oracle database to the destination.
Incremental data migration	After full data migration completes, DTS reads redo log files and continuously applies incremental changes to the destination. This keeps your application running without interruption during the migration window.

Important

Because Oracle and MySQL use different data type systems, review the Data type mappings between heterogeneous databases before you start to understand how your data will be converted.

Choose a migration type combination

Goal	Migration types to select
Migrate data with the application offline (full migration only)	Schema migration + Full data migration
Migrate data with minimal downtime	Schema migration + Full data migration + Incremental data migration

For minimal-downtime migrations, after full data migration completes:

Wait for incremental migration latency to drop near zero.
Stop writes to the source Oracle database.
Let the final changes replicate to the destination.
Re-enable foreign keys, triggers, or other constraints on the destination if needed.
Switch your application to the destination RDS for MySQL instance.

SQL operations for incremental migration

Operation type	SQL statements
DML	INSERT, UPDATE, DELETE
DDL	CREATE TABLE (tables with nested functions are excluded); ALTER TABLE with ADD COLUMN, DROP COLUMN, RENAME COLUMN, and ADD INDEX; DROP TABLE; RENAME TABLE; TRUNCATE TABLE; CREATE INDEX (only operations within the current database account)

Limitations

Foreign key behavior

During schema migration, DTS migrates foreign keys from the source to the destination. During full data migration and incremental data migration, DTS temporarily disables foreign key constraint checks and cascade operations at the session level. If you run cascade or delete operations on the source while migration is in progress, data inconsistency may occur.

Source database constraints

Bandwidth: The server hosting the source database must have enough outbound bandwidth. Insufficient bandwidth reduces migration speed.
Oracle RAC over Express Connect: Specify a virtual IP address (VIP) rather than a Single Client Access Name (SCAN) IP address. After you set the VIP, node failover is not supported.
Oracle RAC over Express Connect, VPN Gateway, Smart Access Gateway, Database Gateway, or Cloud Enterprise Network (CEN): Use a single VIP instead of a SCAN IP. Node failover is not supported after the VIP is set.
VARCHAR2 empty strings: In Oracle, an empty VARCHAR2 string evaluates as NULL. If the corresponding column in the destination has a NOT NULL constraint, the migration task fails.
Primary key or unique constraints: Tables to migrate must have PRIMARY KEY or UNIQUE constraints with all fields unique. Without this, duplicate records may appear in the destination.
Oracle 12c or later: Table names cannot exceed 30 bytes.
Table count limit when renaming objects: If you select tables as the objects to migrate and plan to rename tables or columns, a single task supports up to 1,000 tables. For more than 1,000 tables, split the tables across multiple tasks or migrate the entire database instead.

Log retention for incremental migration

Migration scope	Minimum log retention
Incremental migration only	More than 24 hours
Full migration + Incremental migration	At least 7 days. After full migration completes, reduce to more than 24 hours.

Insufficient log retention can cause DTS to lose redo logs or archive logs, which may fail the task or cause data loss. Log retention directly affects the Service Level Agreement (SLA) guarantees of DTS.

Operations to avoid during migration

During schema migration and full data migration: Do not run DDL operations that change database or table schemas. DDL changes during this phase will fail the migration task.
During full migration only: Do not write data to the source database. Use Schema Migration + Full Data Migration + Incremental Data Migration together to maintain data consistency.
At any time during migration: Do not update LONGTEXT fields. Doing so will fail the migration task.

General constraints

Run migrations during off-peak hours. DTS consumes read and write resources from both the source and destination, which increases server load.
After full migration, the destination tablespace will be larger than the source because concurrent INSERT operations create table fragmentation.
DTS retries failed tasks for up to 7 days. Before you cut over to the destination, stop or release any failed tasks, or use the REVOKE statement to remove write permissions from the DTS accounts on the destination. Otherwise, a resumed task may overwrite data you have already written to the destination after the cutover.

RDS for MySQL-specific behavior

Case sensitivity: Table names in ApsaraDB RDS for MySQL are case-insensitive. DTS converts all uppercase letters in Oracle table names to lowercase. If your Oracle database has table names that differ only in capitalization, they will be identified as duplicates, and schema migration will return "The object already exists." Use the object name mapping feature to resolve naming conflicts before starting the task.
Automatic database creation: DTS creates the destination database in the RDS for MySQL instance automatically. If the source database name is invalid for MySQL, create the database manually first. See Create a database for an ApsaraDB RDS for MySQL instance.

Set up accounts and permissions

Required permissions

Database	Schema migration	Full data migration	Incremental data migration
Self-managed Oracle	Schema owner permissions	Schema owner permissions	Fine-grained permissions
ApsaraDB RDS for MySQL	Write permissions on the destination database	Write permissions on the destination database	Write permissions on the destination database

Create and grant permissions

Oracle database:

Use the CREATE USER and GRANT statements to create an account and assign the required permissions.

For incremental migration, you also need to enable archiving and supplemental logging to obtain incremental changes. For the full list of required permissions and configuration steps, see Configure an Oracle database.

ApsaraDB RDS for MySQL instance:

See Create an account and Modify the permissions of an account to create an account with write permissions on the destination database.

Create a migration task

Log on to the Data Management (DMS) console. In the top navigation bar, click DTS. In the left-side navigation pane, choose DTS (DTS) > Data Migration.
You can also go directly to the Data Migration Tasks page of the new DTS console. For console navigation options, see Simple mode and Customize the layout and style of the DMS console.
From the drop-down list next to Data Migration Tasks, select the region where the migration instance resides.
In the new DTS console, select the region in the upper-left corner.

Click Create Task. Configure the source and destination databases.

Warning

After you configure the source and destination, read the limits displayed at the top of the page before proceeding. Skipping this step may cause task failures or data inconsistency.

Source database (Oracle)

Parameter	Description
Task Name	DTS assigns a name automatically. Specify a descriptive name to identify the task. A unique name is not required.
Database Type	Select Oracle.
Access Method	Select Public IP Address for this example. Other access methods require additional environment setup — see Preparation overview.
Instance Region	The region where the source Oracle database resides.
Hostname or IP address	The endpoint of the source Oracle database.
Port Number	The service port. Default: 1521. The port must be accessible over the Internet for this example.
Oracle Type	Select Non-RAC Instance (configure SID) or RAC or PDB Instance (configure Service Name). This example uses Non-RAC Instance.
Database Account	The Oracle account with the required permissions. See Set up accounts and permissions.
Database Password	The password for the account.

Destination database (ApsaraDB RDS for MySQL)

Parameter	Description
Database Type	Select MySQL.
Access Method	Select Alibaba Cloud Instance.
Instance Region	The region where the destination RDS for MySQL instance resides.
RDS Instance ID	The ID of the destination RDS for MySQL instance.
Database Account	The account with write permissions on the destination database.
Database Password	The password for the account.
Encryption	Select Non-encrypted or SSL-encrypted. For SSL encryption, enable SSL on the RDS for MySQL instance first — see Configure the SSL encryption feature.

If your source database has an IP address whitelist, add the DTS server CIDR blocks to the whitelist. Then click Test Connectivity and Proceed.
Warning
Adding DTS server CIDR blocks to a database whitelist or ECS security group creates potential security exposure. Before proceeding, take protective measures: use strong credentials, restrict exposed ports, authenticate API calls, and audit whitelist rules regularly. For network isolation, connect through Express Connect, VPN Gateway, or Smart Access Gateway.

Configure the objects to migrate and select the migration types. For filtering data by row condition, see Set filter conditions. For renaming objects, see Map object names.

Parameter	Options
Migration Types	Select Schema Migration and Full Data Migration for a full migration. Select all three (Schema Migration, Full Data Migration, and Incremental Data Migration) for a minimal-downtime migration. If you do not select incremental migration, do not write to the source database while migration is running.
Processing Mode of Conflicting Tables	Precheck and Report Errors (default): the precheck fails if identical table names exist in both the source and destination. Use this to catch naming conflicts early. Ignore Errors and Proceed: skips the check. Use this only if you understand the risk — DTS will skip records with matching primary keys, and schema differences may cause partial migration or task failure.
Source Objects	Select objects from the Source Objects list and click the arrow icon to add them to Selected Objects.
Selected Objects	Right-click an object to rename it or set a WHERE filter condition. Click Batch Edit to rename multiple objects at once. Right-click a table to select which SQL operations to include in incremental migration. Note: renaming an object with the object name mapping feature may cause dependent objects to fail migration.

Click Next: Advanced Settings and configure the following parameters as needed.

Parameter	Description
Select the dedicated cluster used to schedule the task	DTS uses a shared cluster by default. To use a dedicated cluster, purchase one first. See What is a DTS dedicated cluster.
Retry Time for Failed Connections	How long DTS retries a lost connection before failing the task. Valid values: 10–1,440 minutes. Default: 720. Set this to at least 30. If multiple tasks share the same source or destination, the most recently set retry time applies to all. During retries, the DTS instance is still billed.
The wait time before a retry when other issues occur in the source and destination databases	How long DTS retries failed DDL or DML operations. Valid values: 1–1,440 minutes. Default: 10. Set this to at least 10. This value must be lower than the Retry Time for Failed Connections value.
Enable Throttling for Full Data Migration	Limits the read/write load on the source and destination during full migration. Configure queries per second (QPS) to the source, records per second (RPS) of full data migration, and data migration speed (MB/s). Available only when full data migration is selected.
Enable Throttling for Incremental Data Migration	Limits the load during incremental migration. Configure RPS of incremental data migration and data migration speed (MB/s). Available only when incremental data migration is selected.
Environment Tag	An optional label to identify the DTS instance.
Actual Write Code	The encoding format for data written to the destination database. Select based on your data requirements.
Configure ETL	Enable to apply extract, transform, and load (ETL) transformations during migration. Enter data processing statements in the code editor. See Configure ETL in a data migration or data synchronization task.
Monitoring and Alerting	Configure alerts so that designated contacts are notified when the task fails or migration latency exceeds the threshold. See Configure monitoring and alerting for a new DTS task.

Click Next: Save Task Settings and Precheck. DTS runs a precheck before the task starts. If any item fails, click View Details to see the cause, fix the issue, and run the precheck again. For alert items that can be safely ignored: click Confirm Alert Details, then Ignore, then OK, and then Precheck Again. Ignoring alert items may result in data inconsistency.
To review the API parameters being used, hover over Next: Save Task Settings and Precheck and click Preview API Call.
Wait for the success rate to reach 100%, then click Next: Purchase Instance.
Read the Data Transmission Service (Pay-as-you-go) Service Terms and select the check box to agree.
Click Buy and Start. Track the migration progress on the Task Management page.

Troubleshooting

Two issues account for most Oracle-to-MySQL migration failures:

Supplemental logging not fully configured

If the task fails during incremental migration with log-related errors, verify that supplemental logging is enabled at the database level and that SUPPLEMENTAL_LOG_DATA_PK and SUPPLEMENTAL_LOG_DATA_UI are both set to Yes. For configuration steps, see Configure an Oracle database.

Table name case conflicts

ApsaraDB RDS for MySQL is case-insensitive for table names. If your Oracle database has tables whose names differ only in capitalization (for example, Orders and ORDERS), DTS will treat them as duplicates and schema migration will fail with "The object already exists." Use the object name mapping feature to rename conflicting tables before running the task.

Data Transmission Service:Migrate data from a self-managed Oracle database to an ApsaraDB RDS for MySQL instance