Data Transmission Service:Migrate data from a PolarDB for PostgreSQL (Compatible with Oracle) cluster to a self-managed Oracle database

Source database limitations

• The source database server must have sufficient outbound bandwidth. Insufficient bandwidth reduces migration speed.

• Tables to be migrated must have PRIMARY KEY or UNIQUE constraints, and all fields must be unique. Otherwise, the destination database may contain duplicate records.

• If you select tables as migration objects and need to rename tables or columns in the destination database, a single task supports up to 1,000 tables. For more than 1,000 tables, configure multiple tasks or migrate the entire database.

• For incremental data migration, WAL must be enabled and WAL logs must be retained for the following durations: If logs are not retained for the required duration, DTS may fail to obtain WAL logs and the task may fail. In exceptional circumstances, data loss or inconsistency may occur. After full data migration completes, you can reduce the retention period to more than 24 hours.

  • Incremental data migration only: more than 24 hours

  • Full data migration and incremental data migration: at least seven days

• During full data migration, do not perform DDL operations that change the schemas of databases or tables. The migration task will fail.

• If you perform only full data migration, do not write data to the source database during migration. To ensure data consistency, select both Full Data Migration and Incremental Data Migration.

• If you plan to perform a primary/secondary switchover on the source cluster, enable the Logical Replication Slot Failover feature first to prevent logical subscriptions from being interrupted. For more information, see Logical replication slot failover.

• If long-running transactions exist in the source database during incremental migration, WAL logs generated before those transactions commit may accumulate and cause insufficient disk space.

Other limitations

• Schema migration is not supported. Before configuring a migration task, create the required databases and tables in the destination instance.

• A single migration task migrates data from one database only. To migrate multiple databases, create a separate task for each database.

• During incremental migration, if you select a schema as the migration object and then create or rename a table in that schema, run the following statement before writing data to the table:

  ALTER TABLE schema.table REPLICA IDENTITY FULL;

  Replace schema and table with the actual schema name and table name.

• To track migration latency, DTS creates a table named dts_postgres_heartbeat in the source database. The following figure shows the table structure.

• During incremental migration, DTS creates a replication slot prefixed with dts_sync_ in the source database. DTS can obtain incremental logs from the last 15 minutes using this slot.

  • After the DTS instance is released, the replication slot is automatically deleted.

  • If you change the source database password or remove DTS from the IP address whitelist, the replication slot is not automatically deleted. Manually delete it from the source database to prevent slot accumulation.

  • If the migration task is released or fails, DTS automatically clears the replication slot. If a primary/secondary switchover is performed on the source cluster, log on to the secondary instance to clear the replication slot.

• Full data migration uses concurrent INSERT operations, which causes fragmentation in destination tables. After migration completes, the tablespace of the destination database is larger than that of the source database.

• DTS uses the ROUND(COLUMN,PRECISION) function to retrieve values from FLOAT and DOUBLE columns. The default precision is 38 digits for FLOAT and 308 digits for DOUBLE. Confirm that these precision settings meet your requirements before starting the migration.

• DTS attempts to resume failed tasks for up to seven days. Before switching workloads to the destination database, stop or release any failed tasks, or run a REVOKE statement to remove write permissions from the DTS accounts on the destination database. Otherwise, the resumed task may overwrite data in the destination database.

Special cases

If the self-managed Oracle database is deployed in a Real Application Cluster (RAC) architecture and connected to DTS over a virtual private cloud (VPC), connect the Single Client Access Name (SCAN) IP address and the virtual IP address (VIP) of each node to the VPC and configure routes. When configuring the Oracle database in the DTS console, specify the SCAN IP address as the database endpoint or IP address. For more information, see Connect an on-premises database to Alibaba Cloud and Connect a data center to DTS by using VPN Gateway.

Step 1: Go to the Data Migration Tasks page

1. Log on to the Data Management (DMS) console.

2. In the top navigation bar, click DTS.

3. In the left-side navigation pane, choose DTS (DTS) > Data Migration.

Step 2: Select the region

From the drop-down list next to Data Migration Tasks, select the region where the data migration instance resides.

Step 3: Configure source and destination databases

Click Create Task. In the Create Task wizard, configure the source and destination databases using the following parameters.

Source database

Destination database

Step 4: Test connectivity and add DTS IP addresses to the whitelist

If your self-managed database has an IP address whitelist configured, add the CIDR blocks of DTS servers to the whitelist. Then click Test Connectivity and Proceed.

Step 5: Configure migration objects and settings

Step 6: Configure advanced settings

Click Next: Advanced Settings and configure the following parameters.

Step 7: Save settings and run the precheck

Click Next: Save Task Settings and Precheck.

DTS runs a precheck before the task can start. If the precheck fails, click View Details next to each failed item, resolve the issues, and click Precheck Again.

If a precheck alert appears:

• If the alert cannot be ignored, click View Details, resolve the issue, and rerun the precheck.

• If the alert can be ignored, click Confirm Alert Details. In the View Details dialog, click Ignore, then click OK, and click Precheck Again. Ignoring an alert may cause data inconsistency.

Step 8: Purchase the instance

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

On the Purchase Instance page, configure the following parameters.

Step 9: Start the task

Select the Data Transmission Service (Pay-as-you-go) Service Terms check box, then click Buy and Start. Monitor progress in the task list.