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

Prerequisites

• The self-managed Oracle database is version 9i, 10g, 11g, 12c, 18c, or 19c.

• Supplemental logging is enabled for the self-managed Oracle database, with both supplemental_log_data_pk and supplemental_log_data_ui enabled. Supplemental Logging.

• The self-managed Oracle database is running in ARCHIVELOG mode. A suitable retention period is set for archived logs, and the logs are accessible. ARCHIVELOG.

• The ApsaraDB RDS for MySQL instance must have more storage space than the self-managed Oracle database.

Usage notes

• A full data migration consumes read and write resources on both the source and destination databases, increasing their server load. This added load can degrade performance or cause service unavailability, especially if your databases have poor performance, low specifications, or are already busy (for example, with many slow SQL queries, tables without primary keys, or deadlocks in the destination database). Before you start the migration, evaluate the performance of both databases and perform the migration during off-peak hours, for example, when CPU utilization is below 30%.

• If a source table to be migrated lacks a primary key or unique constraint, duplicate data may occur in the destination database.

• If the self-managed Oracle database is deployed in a Real Application Cluster (RAC) architecture and is connected to DTS over an Alibaba Cloud virtual private cloud (VPC), you must connect the Single Client Access Name (SCAN) IP address of the Oracle RAC and the virtual IP address (VIP) of each node to the VPC and configure routes. The settings ensure that your DTS task can run as expected. For more information, see Connect a data center to DTS through VPN Gateway.

  Important

  When you configure the source Oracle database in the DTS console, you can specify the SCAN IP address of the Oracle RAC as the database endpoint or IP address.

• Table names in an ApsaraDB RDS for MySQL instance are case-insensitive. ApsaraDB RDS for MySQL converts uppercase table names to lowercase.

  If the source Oracle database contains tables with names that differ only in case, the schema migration may fail with an "Object already exists" error. Use the Object name mapping feature to rename conflicting objects.

• DTS automatically creates databases in the destination ApsaraDB RDS for MySQL instance. If a source database name does not comply with ApsaraDB RDS naming conventions, create a compliant database in the destination ApsaraDB RDS for MySQL instance before configuring the task. Create a database.

Billing

Migration types

• Schema migration

  DTS supports schema migration for tables and indexes but not for views, synonyms, triggers, stored procedures, stored functions, packages, or user-defined data types. Additional limitations:

  • Tables: Nested tables are not supported. Clustered tables and index-organized tables are converted into regular tables in the destination database.

  • Indexes: Function-based indexes, domain indexes, bitmap indexes, and reverse indexes are not supported.

• Full data migration

  DTS migrates all existing data from the selected objects to the destination ApsaraDB RDS for MySQL instance.

• Incremental data migration

  After full data migration, DTS captures redo logs from the self-managed Oracle database and synchronizes incremental changes to the destination ApsaraDB RDS for MySQL instance. This enables a smooth cutover with minimal downtime.

SQL operations for incremental migration

• INSERT, DELETE, UPDATE

• CREATE TABLE

  Note

  The table definition cannot contain functions.

• ALTER TABLE, ADD COLUMN, DROP COLUMN, RENAME COLUMN, ADD INDEX

• DROP TABLE

• RENAME TABLE, TRUNCATE TABLE, CREATE INDEX

Data type mapping

Oracle-to-MySQL type conversions are listed in Data type mapping for heterogeneous databases.

Required permissions

Create a dedicated migration account in the source Oracle database and grant the required permissions.

To create a database account and grant permissions:

• For a self-managed Oracle database, see CREATE USER and GRANT.

• For an ApsaraDB RDS for MySQL instance, see Create an account and Modify account permissions.

Enable logging and grant fine-grained permissions to an Oracle database account

1. Enable archive and supplemental logging.

   Type	Procedure
   Archive logging	Execute the following statements to enable archive logging: shutdown immediate; startup mount; alter database archivelog; alter database open; archive log list;
   Supplemental logging	Enable supplemental logging at the database or table level based on your business requirements: Note You can enable database-level supplemental logging to ensure the stability of Data Transmission Service (DTS) tasks. You can enable table-level supplemental logging to reduce the disk usage of the source Oracle database. Enable database-level supplemental logging Execute the following statement to enable minimal supplemental logging: alter database add supplemental log data; Execute the following statement to enable primary key and unique key supplemental logging at the database level: alter database add supplemental log data (primary key,unique index) columns; Enable table-level supplemental logging Execute the following statement to enable minimal supplemental logging: alter database add supplemental log data; Enable table-level supplemental logging by using one of the following methods: Enable primary key supplemental logging at the table level alter table table_name add supplemental log data (primary key) columns; Enable table-level supplemental logging for all columns alter table tb_name add supplemental log data (all) columns;
   Force logging	Execute the following statements to enable force logging: alter database force logging;

2. Grant fine-grained permissions to an Oracle database account.

   Oracle versions 9i to 11g

   # Create a database account named rdsdt_dtsacct and grant permissions to the account.
   create user rdsdt_dtsacct IDENTIFIED BY rdsdt_dtsacct;
   grant create session to rdsdt_dtsacct;
   grant connect to rdsdt_dtsacct;
   grant resource to rdsdt_dtsacct;
   grant execute on sys.dbms_logmnr to rdsdt_dtsacct;
   grant select on V_$LOGMNR_LOGS to rdsdt_dtsacct;
   grant select on  all_objects to rdsdt_dtsacct;
   grant select on  all_tab_cols to rdsdt_dtsacct;
   grant select on  dba_registry to rdsdt_dtsacct;
   grant select any table to rdsdt_dtsacct;
   grant select any transaction to rdsdt_dtsacct;
   -- v$log privileges
   grant select on v_$log to rdsdt_dtsacct;
   -- v$logfile privileges
   grant select on v_$logfile to rdsdt_dtsacct;
   -- v$archived_log privileges
   grant select on v_$archived_log to rdsdt_dtsacct;
   -- v$parameter privileges
   grant select on v_$parameter to rdsdt_dtsacct;
   -- v$database privileges
   grant select on v_$database to rdsdt_dtsacct;
   -- v$active_instances privileges
   grant select on v_$active_instances to rdsdt_dtsacct;
   -- v$instance privileges
   grant select on v_$instance to rdsdt_dtsacct;
   -- v$logmnr_contents privileges
   grant select on v_$logmnr_contents to rdsdt_dtsacct;
   -- system tables
   grant select on sys.USER$ to rdsdt_dtsacct;
   grant select on SYS.OBJ$ to rdsdt_dtsacct;
   grant select on SYS.COL$ to rdsdt_dtsacct;
   grant select on SYS.IND$ to rdsdt_dtsacct;
   grant select on SYS.ICOL$ to rdsdt_dtsacct;
   grant select on SYS.CDEF$ to rdsdt_dtsacct;
   grant select on SYS.CCOL$ to rdsdt_dtsacct;
   grant select on SYS.TABPART$ to rdsdt_dtsacct;
   grant select on SYS.TABSUBPART$ to rdsdt_dtsacct;
   grant select on SYS.TABCOMPART$ to rdsdt_dtsacct;
   grant select_catalog_role TO rdsdt_dtsacct;

   Oracle 12c to 19c (multitenant)

   # Switch to the pluggable database (PDB). Create a database account named rdsdt_dtsacct and grant permissions to the account.
   ALTER SESSION SET container = ORCLPDB1;
   create user rdsdt_dtsacct IDENTIFIED BY rdsdt_dtsacct;
   grant create  session to rdsdt_dtsacct;
   grant connect  to rdsdt_dtsacct;
   grant resource to rdsdt_dtsacct;
   grant execute on sys.dbms_logmnr to rdsdt_dtsacct;
   grant select on  all_objects to rdsdt_dtsacct;
   grant select on  all_tab_cols to rdsdt_dtsacct;
   grant select on  dba_registry to rdsdt_dtsacct;
   grant select any table to rdsdt_dtsacct;
   grant select any transaction to rdsdt_dtsacct;
   -- v$log privileges
   grant select on v_$log to rdsdt_dtsacct;
   -- v$logfile privileges
   grant select on v_$logfile to rdsdt_dtsacct;
   -- v$archived_log privileges
   grant select on v_$archived_log to rdsdt_dtsacct;
   -- v$parameter privileges
   grant select on v_$parameter to rdsdt_dtsacct;
   -- v$database privileges
   grant select on v_$database to rdsdt_dtsacct;
   -- v$active_instances privileges
   grant select on v_$active_instances to rdsdt_dtsacct;
   -- v$instance privileges
   grant select on v_$instance to rdsdt_dtsacct;
   -- v$logmnr_contents privileges
   grant select on v_$logmnr_contents to rdsdt_dtsacct;
   grant select on sys.USER$ to rdsdt_dtsacct;
   grant select on SYS.OBJ$ to rdsdt_dtsacct;
   grant select on SYS.COL$ to rdsdt_dtsacct;
   grant select on SYS.IND$ to rdsdt_dtsacct;
   grant select on SYS.ICOL$ to rdsdt_dtsacct;
   grant select on SYS.CDEF$ to rdsdt_dtsacct;
   grant select on SYS.CCOL$ to rdsdt_dtsacct;
   grant select on SYS.TABPART$ to rdsdt_dtsacct;
   grant select on SYS.TABSUBPART$ to rdsdt_dtsacct;
   grant select on SYS.TABCOMPART$ to rdsdt_dtsacct;
   -- V$PDBS privileges
   grant select on V_$PDBS to rdsdt_dtsacct;
   grant select on v$database to rdsdt_dtsacct;
   grant select on dba_objects to rdsdt_dtsacct;
   grant select on DBA_TAB_COMMENTS to rdsdt_dtsacct;
   grant select on dba_tab_cols to rdsdt_dtsacct;
   grant select_catalog_role TO rdsdt_dtsacct;
   
   # Switch to the CDB$ROOT, which is the root container of the container database (CDB). Create a database account and grant permissions to the account.
   ALTER SESSION SET container = CDB$ROOT;
   # Create a database account named rdsdt_dtsacct and grant permissions to the account. You must modify the default parameters of the Oracle database. 
   alter session set "_ORACLE_SCRIPT"=true;
   create user rdsdt_dtsacct IDENTIFIED BY rdsdt_dtsacct;
   grant create session to rdsdt_dtsacct;
   grant connect to rdsdt_dtsacct;
   grant select on v_$logmnr_contents to rdsdt_dtsacct;
   grant LOGMINING TO rdsdt_dtsacct;
   grant EXECUTE_CATALOG_ROLE to rdsdt_dtsacct;
   grant execute on sys.dbms_logmnr to rdsdt_dtsacct;

   Oracle 12c to 19c (non-multitenant)

   # Create a database account named rdsdt_dtsacct and grant permissions to the account.
   create user rdsdt_dtsacct IDENTIFIED BY rdsdt_dtsacct;
   grant create  session to rdsdt_dtsacct;
   grant connect  to rdsdt_dtsacct;
   grant resource to rdsdt_dtsacct;
   grant select on V_$LOGMNR_LOGS to rdsdt_dtsacct;
   grant select on  all_objects to rdsdt_dtsacct;
   grant select on  all_tab_cols to rdsdt_dtsacct;
   grant select on  dba_registry to rdsdt_dtsacct;
   grant select any table to rdsdt_dtsacct;
   grant select any transaction to rdsdt_dtsacct;
   grant select on v$database to rdsdt_dtsacct;
   grant select on dba_objects to rdsdt_dtsacct;
   grant select on DBA_TAB_COMMENTS to rdsdt_dtsacct;
   grant select on dba_tab_cols to rdsdt_dtsacct;
   -- v$log privileges
   grant select on v_$log to rdsdt_dtsacct;
   -- v$logfile privileges
   grant select on v_$logfile to rdsdt_dtsacct;
   -- v$archived_log privileges
   grant select on v_$archived_log to rdsdt_dtsacct;
   -- v$parameter privileges
   grant select on v_$parameter to rdsdt_dtsacct;
   -- v$database privileges
   grant select on v_$database to rdsdt_dtsacct;
   -- v$active_instances privileges
   grant select on v_$active_instances to rdsdt_dtsacct;
   -- v$instance privileges
   grant select on v_$instance to rdsdt_dtsacct;
   -- v$logmnr_contents privileges
   grant select on v_$logmnr_contents to rdsdt_dtsacct;
   grant select on sys.USER$ to rdsdt_dtsacct;
   grant select on SYS.OBJ$ to rdsdt_dtsacct;
   grant select on SYS.COL$ to rdsdt_dtsacct;
   grant select on SYS.IND$ to rdsdt_dtsacct;
   grant select on SYS.ICOL$ to rdsdt_dtsacct;
   grant select on SYS.CDEF$ to rdsdt_dtsacct;
   grant select on SYS.CCOL$ to rdsdt_dtsacct;
   grant select on SYS.TABPART$ to rdsdt_dtsacct;
   grant select on SYS.TABSUBPART$ to rdsdt_dtsacct;
   grant select on SYS.TABCOMPART$ to rdsdt_dtsacct;
   grant LOGMINING TO rdsdt_dtsacct;
   grant EXECUTE_CATALOG_ROLE to rdsdt_dtsacct;
   grant execute on sys.dbms_logmnr to rdsdt_dtsacct;
   grant select_catalog_role TO rdsdt_dtsacct;

   Note

   For more information about the multitenant architecture of Oracle, see Oracle Multitenant.

Procedure

1. Log on to the Data Transmission Service (DTS) console.

   Note

   If you are automatically redirected to the Data Management (DMS) console, you can click the icon in the lower-right corner and then click to return to the classic DTS console.

2. In the navigation pane on the left, click Data Migration.

3. At the top of the Migration Tasks page, select the region where the destination instance is located.

4. In the upper-right corner of the page, click Create Data Migration Task.

5. Configure the source and destination databases.

   Category	Setting	Description
   None	Task Name	DTS automatically generates a task name. You can specify a descriptive name. Uniqueness is not required.
   Source Database	Instance Type	Select an option based on the deployment of the source database. This example uses self-managed database with a public IP address. Note For other instance types, complete the required preparations first. Preparations.
   	Instance Region	When the instance type is set to Self-managed database with a public IP address, the Instance Region does not need to be set. Note If your self-managed Oracle database uses an IP address whitelist, click Get DTS IP Segments after selecting an Instance Region to obtain DTS server IP addresses, and add them to the whitelist.
   	Database Type	Select Oracle.
   	Hostname or IP Address	Enter the endpoint of the self-managed Oracle database. This example uses a public endpoint.
   	Port	Enter the service port (default: 1521). The port must be internet-accessible.
   	Instance Type	Non-RAC Instance: If you select this option, also enter the SID. RAC or PDB Instance: If you select this option, also enter the Service Name.
   	Database Account	Enter the database account of the self-managed Oracle database. Required permissions: Preparations.
   	Database Password	Enter the password for the database account. Note After you enter the source database information, you can click Test Connectivity next to Database Password to verify that the information is correct. If the information is correct, the message Passed is displayed. If the message Failed is displayed, click Diagnose next to the Failed message and adjust the source database information based on the prompts.
   Destination Database	Instance Type	Select RDS Instance.
   	Instance Region	Select the region where the destination RDS instance is located.
   	RDS Instance ID	Select the ID of the destination RDS instance.
   	Database Account	Enter the database account of the destination RDS instance. Required permissions: Preparations.
   	Database Password	Enter the password for the database account. Note After you enter the destination database information, you can click Test Connectivity after Database Password to verify that the entered information is correct. If the information is correct, a Passed message is displayed. If a Failed message is displayed, click Diagnose after Failed and adjust the destination database information based on the prompts.

6. Click Authorize Whitelist and Go to Next Step.

   If the source or destination is an Alibaba Cloud database instance, such as ApsaraDB for MySQL or ApsaraDB for MongoDB, DTS automatically adds the IP addresses of its servers in the corresponding region to the instance's whitelist. If the source or destination is a self-managed database on an ECS instance, DTS automatically adds its IP addresses to the security rules of the ECS instance. You must also ensure that the self-managed database does not restrict access from the ECS instance. If the database is a cluster deployed across multiple ECS instances, you must manually add the DTS IP addresses to the security rules for each ECS instance. If the source or destination is a database in an on-premises data center or on another cloud, you must manually add the DTS IP addresses for the corresponding region to allow access from DTS servers. For a list of DTS server IP addresses, see DTS server IP addresses.

   Warning

   Adding public IP addresses of DTS servers, whether automatically or manually, may introduce security risks. By using this product, you acknowledge and accept these potential risks. You are responsible for implementing basic security measures, such as using strong passwords, restricting open ports, using authentication for internal APIs, regularly reviewing and limiting unnecessary network segments, or connecting by using private connections such as Express Connect, VPN Gateway, or Smart Access Gateway.

7. Select the migration types and the objects to migrate.

   Setting	Description
   Migration Type	For a full migration only, select both Schema Migration and Full Data Migration. For a migration with minimal downtime, select Schema Migration, Full Data Migration, and Incremental Data Migration. Note If you do not select Incremental Data Migration, do not write new data to the source database during the migration to ensure data consistency.
   Objects to Migrate	In the Objects to Migrate box, select the objects that you want to migrate and click the icon to move them to the Selected Objects box. Note You can select objects to migrate at the database, table, and column levels. Migrated object names remain unchanged by default. To rename objects in the destination RDS instance, use the Object name mapping feature.
   Mapped Name Change	To rename migrated objects in the destination instance, use Object name mapping.
   Retry Duration for Unreachable Source/Destination	Default: 720 minutes. If DTS reconnects to the source or destination database within this duration, the task resumes automatically. Otherwise, the task fails. Note DTS charges fees during retries. Adjust the retry duration as needed, or release the DTS instance after the source and destination instances are released.

8. After you complete these settings, click Pre-check and Start in the lower-right corner of the page.

   Note

   • DTS performs a precheck before the task starts. The task can start only after it passes the precheck.

   • If the precheck fails, click the icon next to the failed item to view details.

     • Fix the issues based on the details, and then run the precheck again.

     • For warnings that do not require a fix, you can select Ignore or Ignore and Rerun Precheck to run the precheck again.

9. After the task passes the precheck, click Next.

10. On the Confirm Order page, select a Instance Class and select the Data Transmission Service (Pay-As-You-Go) Terms of Service checkbox.

11. Click Buy and Start. The migration task starts.

    • Full data migration

      Do not manually stop the task. Wait for it to complete automatically to avoid data loss.

    • Incremental data migration

      The task does not stop automatically. Stop it manually.

      Note

      Stop the task during off-peak hours or when you are ready to switch to the destination instance.

      1. Wait until the task status changes to Incremental Data Migration and shows Undelayed. Then, stop writing data to the source database for a few minutes. The status of Incremental Data Migration may show a latency.

      2. Wait until the status of Incremental Data Migration changes back to Undelayed. Then, stop the migration task manually. The progress of schema migration and full data migration both show 100%. To pause the migration task, select the task and click Pause in the batch operations bar at the bottom.

12. Switch your business to the RDS instance.

What to do next

After migration, delete the migration account from both the self-managed Oracle database and the ApsaraDB RDS for MySQL instance to reduce security risk.

More information

DTS supports reverse data synchronization when migrating from a self-managed Oracle database to an ApsaraDB RDS for MySQL instance. This lets you synchronize changes from the ApsaraDB RDS for MySQL instance back to Oracle.