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

Blocking limitations

Fix the following issues before starting the migration. They will cause the task to fail or result in data loss.

Source database requirements:

• Tables must have primary keys or UNIQUE constraints with unique fields. Without these, duplicate records may appear in the destination database.

• If you use MySQL 8.0.23 or later and the source data includes invisible columns, those columns cannot be read and will be lost. To make invisible columns visible, run:

  ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE;

  Tables without explicit primary keys automatically generate invisible primary keys. Make these visible before migrating. See Invisible Columns and Generated Invisible Primary Keys.

• For incremental migration, the source database must meet the following binary log requirements:

  • binlog_format = row

  • binlog_row_image = full

  • For self-managed MySQL in a dual-primary cluster: log_slave_updates = ON

  • Binary log retention period:

    Source database type	Minimum	Recommended
    ApsaraDB RDS for MySQL	3 days	7 days
    Self-managed MySQL	7 days	7 days

    If DTS cannot obtain the binary logs, the task fails. In exceptional circumstances, data inconsistency or loss may occur.

  • Read-only ApsaraDB RDS for MySQL V5.6 instances cannot be used as the source for incremental migration.

• Do not perform DDL operations during schema migration or full migration. DDL operations during full migration may be blocked by metadata locks that DTS creates on the source database.

• If performing full migration only (without incremental migration), do not write to the source database during migration. Otherwise, the source and destination databases will become inconsistent. To maintain real-time data consistency, select Schema Migration, Full Data Migration, and Incremental Data Migration.

Destination database requirements:

• Disable foreign keys and triggers in the destination Oracle database before migration. Otherwise, the task may fail.

• Do not use pt-online-schema-change or similar tools for online DDL on migrated objects. Online DDL operations that use temporary tables, including multi-table merge scenarios, may cause data loss.

Feature limitations:

• If the EncDB feature is enabled on the source RDS for MySQL instance, full data migration cannot be performed. Transparent Data Encryption (TDE) is supported and does not block any migration type.

• Cascade update and delete operations in the source database may cause data inconsistency. Data generated by cascade operations or physical backup restores is not recorded in binary logs and will not be migrated. If the change data is not recorded and migrated to the destination database, you can migrate full data again on the premise that your business is not affected.

Operational notes

• Object name limit: When migrating at the table level with object name mapping enabled, a single task supports up to 1,000 tables. Split larger migrations into multiple tasks, or migrate at the database level instead.

• FLOAT/DOUBLE precision: DTS reads FLOAT and DOUBLE values using ROUND(COLUMN,PRECISION). Default precision is 38 for FLOAT and 308 for DOUBLE. Verify this meets your requirements before starting.

• Migration timing: Run full migration during off-peak hours. Full migration uses concurrent INSERT operations and consumes read and write resources on both databases, which may increase load and cause table fragmentation in the destination. As a result, the storage space used by the tables in the destination database is larger than that in the source instance.

• Task resumption: DTS automatically retries failed tasks for up to 7 days. Before switching your business to the destination instance, stop or release the DTS instance. Alternatively, run the REVOKE command to remove write permissions from the DTS account on the destination instance.

• Instance recovery: If a DTS instance fails, DTS support will attempt recovery within 8 hours. This may involve restarting the instance or adjusting DTS instance parameters (database parameters are not modified).

Step 1: Open the Data Migration page

Use one of the following consoles.

DTS console:

1. Log on to the DTS console.

2. In the left-side navigation pane, click Data Migration.

3. In the upper-left corner, select the region where the migration instance will reside.

DMS console:

Steps may vary depending on your DMS console mode and layout. For details, see Simple mode and Customize the layout and style of the DMS console.

1. Log on to the DMS console.

2. In the top navigation bar, go to Data + AI > DTS (DTS) > Data Migration.

3. From the drop-down list next to Data Migration Tasks, select the region where the migration instance will reside.

Step 2: Configure source and destination databases

1. Click Create Task.

2. Set Task Name to something descriptive. DTS generates a name automatically, but a meaningful name helps identify the task later.

3. Configure the source database (Source Database section):

   Parameter	Value
   Select a DMS database instance	Select an existing DMS instance, or leave blank to configure manually.
   Database Type	MySQL
   Access Method	Cloud Instance
   Instance Region	The region of the source RDS for MySQL instance
   Replicate Data Across Alibaba Cloud Accounts	No (for same-account migration)
   RDS Instance ID	The ID of the source RDS for MySQL instance
   Database Account	The account with the required permissions (see Database account permissions)
   Database Password	The password for the database account
   Encryption	Select Non-encrypted or SSL-encrypted. To use SSL, enable SSL encryption on the RDS for MySQL instance first. See Use a cloud certificate to enable SSL encryption.

4. Configure the destination database (Destination Database section):

   Note

   If the destination is a self-managed database, complete the required preparations before proceeding. See Overview of preparations.

   Parameter	Value
   Select a DMS database instance	Select an existing DMS instance, or leave blank to configure manually.
   Database Type	Oracle
   Access Method	Self-managed Database On ECS
   Instance Region	The region of the ECS instance hosting the Oracle database
   ECS Instance ID	The ID of the ECS instance
   Port	The Oracle service port (default: 1521)
   Oracle Type	RAC Or PDB Instance (requires Service Name) or Non-RAC Instance (requires SID)
   Database Account	The Oracle account with owner permission on the destination schema
   Database Password	The password for the database account

5. Click Test Connectivity and Proceed. In the CIDR Blocks of DTS Servers dialog box, click Test Connectivity.

   Make sure the DTS server CIDR blocks are added to the security settings of both databases. See Add DTS server IP addresses to a whitelist.

Step 3: Select objects to migrate

1. On the Configure Objects page, set Migration Types. See Choose a migration type for guidance.

2. Set Processing Mode of Conflicting Tables:

   • Precheck And Report Error: The precheck fails if a table with the same name exists in the destination. Use this to catch naming conflicts early. If a conflict exists, rename the destination table or use object name mapping. See Map table and column names.

   • Ignore Error And Continue: Skips the conflict check. > Warning: Ignoring conflicts may cause data inconsistency. During full migration, DTS keeps the existing destination record. During incremental migration, the source record overwrites the destination record. If table schemas differ, migration may fail partially or entirely.

3. (Optional) Configure Capitalization of Object Names in Destination Instance to control how migrated table, database, and column names are capitalized. The default is DTS Default Policy.

4. In Source Objects, select the objects to migrate and click the right-arrow icon to move them to Selected Objects.

   You can select columns, tables, or schemas. If you select tables or columns, DTS does not migrate views, triggers, or stored procedures.

5. Map the source schema to the destination Oracle schema: By default, the schema name matches the Database Account used for the Oracle connection. If you want to migrate to a different schema, update this field. For more about object name mapping, see Map table and column names.

   • Right-click a database in Selected Objects and open the Edit Schema page.

   • Set Schema Name to the target schema in the Oracle database.

   If object name mapping is used, other objects that depend on the mapped objects may fail to migrate. To filter rows, right-click a table in Selected Objects and set a WHERE clause. See Set filter conditions.

6. Click Next: Advanced Settings.

Step 4: Configure advanced settings

Step 5: Configure data verification (optional)

Click Next Step: Data Verification to set up a data verification task. See Configure a data verification task.

Step 6: Run the precheck

Click Next: Save Task Settings and Precheck.

To view the API parameters for this task configuration, hover over the button and click Preview OpenAPI parameters before proceeding.

DTS runs a precheck before the task starts. If any items fail:

1. Click View Details next to the failed item.

2. Fix the issue based on the check result.

3. Run the precheck again.

If an item shows an alert (not a failure), you can choose to ignore it:

1. Click Confirm Alert Details.

2. In the View Details dialog box, click Ignore, then OK.

3. Click Precheck Again.

Step 7: Purchase the instance and start the task

1. Wait until Success Rate reaches 100%, then click Next: Purchase Instance.

2. On the Purchase Instance page, configure:

   Parameter	Description
   Resource Group	The resource group for the migration instance. Default: default resource group. See What is Resource Management?
   Instance Class	The migration speed depends on the instance class. See Instance classes of data migration instances.

3. Read and accept Data Transmission Service (Pay-as-you-go) Service Terms.

4. Click Buy and Start, then OK.

Migration latency for self-managed MySQL sources

DTS calculates migration latency based on the timestamp of the latest migrated record and the current timestamp on the source. If no DML operations run on the source for an extended period, the displayed latency may be inaccurate. To refresh the latency, run any DML operation on the source database.

If you migrate an entire database, create a heartbeat table that receives an update every second. This keeps the latency reading accurate.

DTS also executes CREATE DATABASE IF NOT EXISTS \test\`` on the source periodically to advance the binary log file position. This applies to both self-managed MySQL sources and ApsaraDB RDS for MySQL sources.

Primary/secondary switchover during migration

If a primary/secondary switchover occurs on the source self-managed MySQL database while the task is running, the task fails. Avoid switchovers during active migration.

Before switching your business to the destination

Stop or release the DTS instance before redirecting application traffic to the destination Oracle database. If you leave the DTS instance running, it may resume after a failure and overwrite data in the destination with data from the source. Alternatively, run REVOKE to remove the DTS account's write access to the destination instance.