Synchronize PolarDB-X 2.0 data to a self-managed Oracle database - Data Transmission Service

Use Data Transmission Service (DTS) to keep a self-managed Oracle database in sync with a PolarDB-X 2.0 instance. DTS handles schema synchronization, full data synchronization, and incremental data synchronization for this path, so your Oracle database stays current with ongoing changes in PolarDB-X 2.0.

Prerequisites

Before you begin, make sure that:

The self-managed Oracle database is created and has enough available storage to hold all data from the source PolarDB-X 2.0 instance.
A schema exists in the Oracle database. A schema is created automatically when you create a user in Oracle.
The database accounts for both source and destination have the required permissions. See Permissions required for database accounts.

Limitations

During schema synchronization, DTS synchronizes foreign keys from the source database to the destination database.

Source database limitations

Limitation	Details	Workaround
Primary key or unique constraint required	Tables must have a PRIMARY KEY or UNIQUE constraint, with all fields unique. Without this, the destination database may contain duplicate records.	Add a PRIMARY KEY or UNIQUE constraint to the tables before creating the task.
Table count limit when renaming objects	If you select tables as objects to synchronize and need to rename tables or columns in the destination, a single task supports up to 5,000 tables. Exceeding this limit causes a request error.	Split the work across multiple tasks, or switch from table-level to database-level synchronization.
Tables with uppercase letters in names	Only schema synchronization is supported for these tables. Full data synchronization and incremental data synchronization are not available.	Rename the tables in the source to use lowercase letters before creating the task, or limit the task scope to schema synchronization only.
`binlog_row_image` parameter	Binary logging is enabled by default on PolarDB-X 2.0. The `binlog_row_image` parameter must be set to `full`. If this is not set correctly, the precheck fails and the task cannot start.	Set `binlog_row_image` to `full` before creating the task. See Parameter settings.

Other limitations

Run synchronization tasks during off-peak hours. Initial full data synchronization uses read and write resources on both databases and may increase server load.
After initial full data synchronization completes, the destination tablespace is larger than the source due to fragmentation from concurrent INSERT operations.
During synchronization, write data to the destination database only through DTS. Writes from other sources can cause data inconsistency.
At most one column's attributes can be changed. Basic data types cannot be changed.
DTS creates a database named dts_health_check and a table named ha_health_check in the source instance, and updates this table regularly to advance the binary log position.

Billing

Synchronization type	Fee
Schema synchronization and full data synchronization	Free
Incremental data synchronization	Charged. See Billing overview.

SQL operations that can be synchronized

DTS supports the following DML operations for this synchronization path: INSERT, UPDATE, and DELETE.

Operation type	SQL operation statement
DML	INSERT, UPDATE, DELETE

Permissions required for database accounts

Grant the following permissions before configuring the task.

Database	Required permissions	References
Source PolarDB-X 2.0 instance	SELECT on the objects to be synchronized (to read source data), REPLICATION SLAVE and REPLICATION CLIENT (to read binary logs for incremental sync)	Manage database accounts and Permissions required for an account to synchronize data
Destination self-managed Oracle database	Owner permissions on the schema (to create and write to tables in the target schema)	CREATE USER and GRANT

Create a data synchronization task

Step 1: Go to the Data Synchronization Tasks page

Log on to the DMS console.
In the top navigation bar, click DTS.
In the left-side navigation pane, choose DTS (DTS) > Data Synchronization.

The actual navigation may differ depending on your DMS mode and layout. See Simple mode and Customize the layout and style of the DMS console. You can also go directly to the Data Synchronization Tasks page in the new DTS console.

Step 2: Select a region

From the drop-down list to the right of Data Synchronization Tasks, select the region where you want to create the task.Data Synchronization Tasks page of the new DTS console

In the new DTS console, select the region in the upper-left corner of the page.

Step 3: Configure source and destination databases

Click Create Task. In the Create Task wizard, configure the following parameters.

Task name

Parameter	Description
Task Name	DTS auto-assigns a name. Specify a descriptive name to identify the task more easily. The name does not need to be unique.

Source database

Parameter	Description
Select an existing DMS database instance	Optional. Select an existing DMS instance to auto-populate the parameters below, or leave blank and configure the parameters manually.
Database Type	Select PolarDB-X 2.0.
Access Method	Select Alibaba Cloud Instance.
Instance Region	Select the region where the source PolarDB-X 2.0 instance resides.
Replicate Data Across Alibaba Cloud Accounts	Select No to synchronize within the same Alibaba Cloud account.
Instance ID	Select the source PolarDB-X 2.0 instance.
Database Account	Enter the database account. See Permissions required for database accounts.
Database Password	Enter the password for the database account.

Destination database

Parameter	Description
Select an existing DMS database instance	Optional. Select an existing DMS instance to auto-populate the parameters below, or leave blank and configure the parameters manually.
Database Type	Select Oracle.
Access Method	Select Self-managed Database on ECS. If you use a different access method, configure the network environment first. See Preparation overview.
Instance Region	Select the region where the Oracle database resides.
ECS Instance ID	Select the ECS instance that hosts the Oracle database.
Port Number	Enter the Oracle service port. Default: 1521.
Oracle Type	Select the Oracle architecture: Non-RAC Instance or RAC or PDB Instance. For a standalone Oracle installation, select Non-RAC Instance and enter the SID (for example, `ORCL`). For a Real Application Clusters (RAC) or Pluggable Database (PDB) setup, select RAC or PDB Instance and enter the Service Name (for example, `orclpdb1`).
Database Account	Enter the Oracle database account. See Permissions required for database accounts.
Database Password	Enter the password for the database account.

Step 4: Test connectivity

Click Test Connectivity and Proceed at the bottom of the page.

Add the CIDR blocks of DTS servers to the security settings of the self-managed database (such as firewall settings) to allow access from DTS servers. Then, click Test Connectivity and Proceed.

If the source or destination database is an Alibaba Cloud database instance (such as the source PolarDB-X 2.0 instance), DTS automatically adds the CIDR blocks of DTS servers to the whitelist of the instance. If the Oracle database is on ECS, DTS automatically adds its server CIDR blocks to the ECS security group rules. Make sure the ECS instance can reach the Oracle database. If the database is in an on-premises data center or with a third-party cloud provider, manually add the DTS server CIDR blocks to the database allowlist. See Add the CIDR blocks of DTS servers to the security settings of on-premises databases.

Warning

Adding DTS server CIDR blocks to your database allowlist or ECS security group rules may introduce security risks. Before proceeding, take appropriate precautions: use strong credentials, limit exposed ports, authenticate API calls, and regularly audit your allowlist and security group rules. For stronger isolation, connect DTS to your database through Express Connect, VPN Gateway, or Smart Access Gateway.

Step 5: Configure synchronization objects and settings

Configure the following parameters.

Synchronization types

Parameter	Description
Synchronization Types	By default, Incremental Data Synchronization is selected. You must also select Schema Synchronization and Full Data Synchronization. DTS synchronizes historical data first (full), then continues with ongoing changes (incremental).

Conflict handling

Parameter	Description
Processing Mode of Conflicting Tables	Choose how DTS handles tables in the destination that share names with tables in the source. Precheck and Report Errors stops the task if duplicate table names are detected — recommended for most cases. Ignore Errors and Proceed skips the precheck and continues, but may cause data inconsistency — use with caution. If you need to resolve naming conflicts without deleting destination tables, use object name mapping to rename destination tables before enabling this option. See Map object names.

Parameter

Description

Processing Mode of Conflicting Tables

Choose how DTS handles tables in the destination that share names with tables in the source. Precheck and Report Errors stops the task if duplicate table names are detected — recommended for most cases. Ignore Errors and Proceed skips the precheck and continues, but may cause data inconsistency — use with caution. If you need to resolve naming conflicts without deleting destination tables, use object name mapping to rename destination tables before enabling this option. See Map object names.

Object capitalization

Parameter	Description
Capitalization of Object Names in Destination Instance	Controls the capitalization of database, table, and column names in the destination. Defaults to DTS default policy. See Specify the capitalization of object names.

Select objects

In the Source Objects section, select the objects to synchronize and click the arrow icon to move them to Selected Objects.

You can select columns, tables, or databases as synchronization objects. If you select tables or columns, DTS does not synchronize other objects such as views, triggers, or stored procedures.

After adding objects to Selected Objects, right-click the destination database entry and select Edit Schema. In the Schema Name field, enter the name of the Oracle schema that will receive the synchronized data.

Use uppercase letters for the schema name.

To filter rows, right-click a table in Selected Objects and specify SQL WHERE conditions. See Use SQL conditions to filter data.

If you use object name mapping to rename an object, other objects that depend on it may fail to synchronize.

Step 6: Configure advanced settings

Click Next: Advanced Settings and configure the following parameters.

Parameter	Description
Select the dedicated cluster used to schedule the task	Leave at the default to use a shared cluster. To run the task on a dedicated cluster with specific resource guarantees, purchase a dedicated cluster first. See What is a DTS dedicated cluster.
Set Alerts	Select Yes to receive notifications when the task fails or synchronization latency exceeds a threshold. Configure the alert threshold and contacts. See Configure monitoring and alerting.
Retry Time for Failed Connections	The time window in which DTS retries failed connections after the task starts. Range: 10–1440 minutes. Default: 720. Set this to more than 30 minutes. If the connection is not restored within this window, the task fails. If multiple tasks share the same source or destination, the shortest retry window applies. During retries, the DTS instance continues to incur charges.
The wait time before a retry when other issues occur in the source and destination databases	The retry window for failed DDL or DML operations. Range: 1–1440 minutes. Default: 10. Set this to more than 10 minutes. This value must be less than the value for Retry Time for Failed Connections.
Enable Throttling for Full Data Migration	Limit the queries per second (QPS), records per second (RPS), and bytes per second (BPS) for full data synchronization to reduce load on the destination database. Available only when Full Data Synchronization is selected.
Enable Throttling for Incremental Data Synchronization	Limit the RPS and BPS for incremental synchronization to reduce load on the destination database.
Environment Tag	Optional. Assign an environment tag (for example, production or staging) to identify the DTS instance.
Configure ETL	Select Yes to transform data during synchronization using extract, transform, and load (ETL) statements. See Configure ETL in a data migration or data synchronization task.
Whether to delete SQL operations on heartbeat tables of forward and reverse tasks	Select Yes to prevent DTS from writing heartbeat table operations to the source database. This avoids affecting features such as physical backup and cloning, but may show a latency offset in the DTS instance. Select No to write heartbeat operations, which keeps latency metrics accurate but may affect source database features.

Step 7: Run the precheck

Click Next: Save Task Settings and Precheck.

To preview the API parameters used to configure this task, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.

DTS runs a precheck before the task can start. After the precheck completes:

If all items pass, proceed to the next step.
If an item fails, click View Details next to the failed item, fix the issue, and click Precheck Again.
If an alert is triggered:
- If the alert cannot be ignored, click View Details, fix the issue, and rerun the precheck.
- If the alert can be ignored, click Confirm Alert Details, click Ignore in the View Details dialog box, and click OK. Then click Precheck Again. Ignoring alerts may result in data inconsistency.

Wait until the success rate reaches 100%, then click Next: Purchase Instance.

Step 8: Purchase an instance and start the task

On the Purchase Instance page, configure the following parameters.

Parameter	Description
Billing Method	Subscription: Pay upfront for a fixed duration. More cost-effective for long-term use. Pay-as-you-go: Charged hourly. Suitable for short-term use. Release the instance when no longer needed to stop charges.
Resource Group	The resource group for the instance. Default: default resource group. See What is Resource Management?.
Instance Class	Select a synchronization specification based on your throughput requirements. Synchronization speed varies by specification. See Specifications of data synchronization instances.
Subscription Duration	Available for the Subscription billing method. Options: 1–9 months, or 1, 2, 3, or 5 years.

Read and accept the Data Transmission Service (Pay-as-you-go) Service Terms, then click Buy and Start.

The task appears in the task list. Monitor the synchronization progress from there.

What's next

To monitor synchronization latency and set alerts, see Configure monitoring and alerting when you create a DTS task.
To rename objects in the destination, see Map object names.
To filter specific rows during synchronization, see Use SQL conditions to filter data.