Migrate data from Oracle to PolarDB-X using DTS - Data Transmission Service

Use Data Transmission Service (DTS) to migrate data from a self-managed Oracle database to PolarDB-X. DTS supports full and incremental data migration, enabling smooth migration without application downtime.

Prerequisites

The source self-managed Oracle database is version 9i, 10g, 11g, 12c, 18c, or 19c.
The source Oracle database has supplemental logging enabled, including supplemental_log_data_pk and supplemental_log_data_ui.
The source Oracle database runs in ARCHIVELOG mode, and archived logs are accessible and retained for a sufficient period.
The service port of the source Oracle database is accessible over the internet.
The PolarDB-X database must be backed by an ApsaraDB RDS for MySQL instance. Databases backed by PolarDB for MySQL are not supported.
The ApsaraDB RDS for MySQL instance backing your PolarDB-X database must have more storage than the source Oracle database.

Limitations

DTS does not support schema migration from Oracle to PolarDB-X.

Note
Schema migration refers to migrating database object definitions (such as table structures) from source to destination.
Full data migration increases load on both source and destination databases, which may degrade performance or cause unavailability—especially with low-spec instances, slow SQL queries, missing primary keys, or deadlocks. Evaluate database performance first and migrate during off-peak hours (CPU utilization below 30%).
If a source table lacks a primary key or unique constraint, duplicate data may occur in the destination.
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.
DTS automatically resumes failed migration tasks. Stop or release the task before switching your business to the destination instance to prevent the resumed task from overwriting destination data.

Billing

Migration type	Task configuration fee	Internet traffic fee
Schema migration and full data migration	Free of charge.	DTS charges an Internet traffic fee when the Access Method of the destination database is set to Public IP Address. Billing overview.
Incremental data migration	Charged. Billing overview.

Migration types

Full data migration

DTS migrates all existing data from selected objects in the source Oracle database to the destination PolarDB-X database.

Note
Do not write new data to the source Oracle database during full data migration to ensure consistency.
Incremental data migration

After full data migration completes, DTS polls and captures redo logs from the source Oracle database to synchronize incremental changes to the destination PolarDB-X database. This minimizes application downtime during the migration to PolarDB-X.

Note
Incremental data migration supports INSERT, DELETE, and UPDATE operations. DDL operations are not synchronized.

Before you begin

In your destination PolarDB-X instance, create databases and tables that match the source Oracle table schemas (Create a database and Create a table).

Note
The data types in Oracle and PolarDB-X do not map one-to-one. Define the corresponding data types in PolarDB-X. Data type mapping between heterogeneous databases.

Log on to the source Oracle database and create a data collection account with the required permissions.

Database	Full data migration	Incremental data migration
Self-managed Oracle database	Permissions of the schema owner	DBA permissions
PolarDB-X	Write permissions on the destination database	Write permissions on the destination database

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

Important

If you want to migrate data from an Oracle database but the database administrator (DBA) permission cannot be granted to the database account, you can enable archive logging and supplemental logging, and grant fine-grained permissions to the account.

Enable archived logs 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;`

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

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.
In the navigation pane on the left, click Data Migration.
At the top of the Migration Tasks page, select the region where the destination instance is located.
In the upper-right corner of the page, click Create Data Migration Task.

Configure the source and destination databases.

Category	Parameter	Description
N/A	Task Name	DTS generates a task name automatically. Specify a descriptive name for easy identification.
Source Database	Instance Type	Select the source database deployment type. This example uses User-Created Database with Public IP Address. Note If your source database uses a different instance type, complete the steps in Overview of preparations.
	Instance Region	If you select User-Created Database with Public IP Address, you do not need to set the Instance Region parameter. Note If a whitelist is configured for the source Oracle database, click Get DTS IP Segment next to the Instance Region parameter to get the DTS server IP addresses and add them to the source database whitelist.
	Database Type	Select Oracle.
	Hostname or IP Address	Enter the endpoint of the source Oracle database. In this example, enter a public IP address.
	Port	Enter the service port of the source Oracle database. Default: 1521.
	Instance Type	Non-RAC Instance: If you select this option, you must specify the SID. RAC Instance: If you select this option, you must specify the Service Name.
	Database Account	Enter the database account of the source Oracle database. Required permissions are listed in Before you begin.
	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 DRDS Instance.
	Instance Region	Select the region where the destination PolarDB-X instance resides.
	PolarDB-X Instance ID	Select the ID of the destination PolarDB-X instance.
	Database Account	Enter the database account of the destination PolarDB-X instance. Required permissions are listed in Before you begin.
	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.

After you configure the settings, click Set Whitelist and Next.

If the source or destination database is an Alibaba Cloud database instance such as ApsaraDB RDS for MySQL or ApsaraDB for MongoDB, DTS automatically adds the IP addresses of DTS servers in the corresponding region to the whitelist of the instance. If the source or destination database is a self-managed database on an ECS instance, DTS automatically adds the IP addresses of DTS servers in the corresponding region to the security rules of the ECS instance. You must also ensure that the self-managed database allows access from the ECS instance. If the database is deployed in a cluster on multiple ECS instances, you must manually add the IP addresses of DTS servers in the corresponding region to the security rules of each ECS instance. If the source or destination database is a self-managed database in an on-premises data center or a database from another cloud provider, you must manually add the IP addresses of DTS servers for the corresponding region to your firewall or security settings to allow access. For the IP address ranges of DTS servers, see IP address ranges of DTS servers.

Warning
Adding public IP addresses of DTS servers may introduce security risks. By using this product, you acknowledge and accept these risks. Implement basic security measures such as strong passwords, restricted ports, API authentication, regular network segment reviews, or private connections through Express Connect, VPN Gateway, or Smart Access Gateway.

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.

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.
After the task passes the precheck, click Next.
On the Confirm Order page, select a Instance Class and select the Data Transmission Service (Pay-As-You-Go) Terms of Service checkbox.
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.
Switch your business to the PolarDB-X instance.

Next steps

After migration completes, delete the migration database accounts from both the source Oracle database and the PolarDB-X instance to reduce security exposure.