Migrate Oracle data to PolarDB for PostgreSQL - Data Transmission Service

Data Transmission Service (DTS) supports migrating data from a self-managed Oracle database to a PolarDB for PostgreSQL cluster. You can choose schema migration, full data migration, or incremental data migration — or combine all three to keep the destination in sync with the source during migration.

Before you begin

Before you configure a migration task, complete the following preparation steps.

Step 1: Assess compatibility (recommended)

Use Advanced Database & Application Migration (ADAM) to evaluate your source Oracle database for compatibility with PolarDB for PostgreSQL. ADAM identifies incompatible schemas, data types, and SQL syntax before migration, reducing the risk of task failures.

For setup instructions and DTS capability limits specific to Oracle, see Prepare an Oracle database and DTS overview.

Step 2: Prepare the source Oracle database

Enable ARCHIVELOG mode. The source Oracle database must run in ARCHIVELOG mode with accessible archive log files and an appropriate retention period configured. See Managing archived redo log files.
Enable supplemental logging. Set SUPPLEMENTAL_LOG_DATA_PK and SUPPLEMENTAL_LOG_DATA_UI to Yes. See Supplemental logging.

Step 3: Prepare the destination PolarDB for PostgreSQL cluster

Create the PolarDB for PostgreSQL cluster. The cluster's available storage must exceed the total data size of the source Oracle database. See Create a PolarDB for PostgreSQL cluster.

Note
For supported database engine versions, see Overview of data migration scenarios.
Create the destination database that will receive the migrated data. See Database management.

Limitations

Foreign key behavior during migration

During schema migration, DTS migrates foreign keys from the source to the destination.
During full data migration and incremental data migration, DTS temporarily disables constraint checks and cascade operations on foreign keys at the session level. If you perform cascade or delete operations on the source database during migration, data inconsistency may occur.

Source database limitations

Connectivity

Scenario	Limitation
General	The server hosting the source database must have sufficient outbound bandwidth. Insufficient bandwidth reduces migration speed.
Oracle RAC over Express Connect	Specify a virtual IP address (VIP) for the source database. Using a Single Client Access Name (SCAN) IP address is not supported. Node failover is disabled after you specify the VIP.
Oracle RAC over Express Connect, VPN Gateway, Smart Access Gateway, Database Gateway, or Cloud Enterprise Network (CEN)	Use a single VIP instead of a SCAN IP address. Node failover is not supported after you specify the VIP.

Data compatibility

If a VARCHAR2 field in the source Oracle database contains an empty string (which Oracle evaluates as null) and the corresponding destination field has a NOT NULL constraint, the migration task fails.
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.
For Oracle 12c and later, table names cannot exceed 30 bytes.
If you select tables as migration objects and need to rename tables or columns in the destination, a single migration task supports up to 1,000 tables. Migrating more than 1,000 tables causes a request error. Run multiple tasks in batches, or migrate the entire database instead.

Incremental migration requirements

Migration mode	Required log retention period
Incremental data migration only	More than 24 hours
Full data migration + incremental data migration	At least 7 days. After full data migration completes, you can reduce this to more than 24 hours.

If DTS cannot obtain redo logs or archive logs due to insufficient retention, the migration task fails. In extreme cases, data inconsistency or loss may occur. DTS Service Level Agreement (SLA) does not cover failures caused by insufficient log retention.

Operations to avoid on the source database

Do not perform DDL operations that change database or table schemas during schema migration or full data migration. The task fails if DDL changes are detected.
If you run only full data migration (without incremental), do not write data to the source database during migration. Writes during full migration can cause data inconsistency.
Do not update LONGTEXT fields during migration. The task fails if LONGTEXT fields are updated.

Other limitations

The character sets of the source and destination databases must be compatible. Incompatible character sets can cause data inconsistency or task failure.
The source and destination databases must use the same time zone.
Use the DTS schema migration feature to migrate schemas. Manually managing schema migration may cause task failures due to incompatible data types.
The terminator \0 cannot be written to the destination PolarDB for PostgreSQL cluster. DTS silently drops this terminator if it appears in migrated data, which may cause data inconsistency.
Run migration tasks during off-peak hours. Full data migration consumes read and write resources on both databases.
After full data migration completes, the destination cluster's used tablespace will be larger than the source database due to fragmentation caused by concurrent INSERT operations.
DTS retries failed migration tasks for up to 7 days. Before switching workloads to the destination database, stop or release any failed tasks — or run REVOKE to remove write permissions from DTS accounts on the destination database. Otherwise, a resumed task may overwrite data in the destination.
During migration, only DTS should write data to the destination database. External writes may cause data inconsistency.

Billing

Migration type	Task configuration fee	Internet traffic fee
Schema migration	Free	Free (charged only when migrating from Alibaba Cloud over the Internet — see Billing overview)
Full data migration	Free	Free (charged only when migrating from Alibaba Cloud over the Internet — see Billing overview)
Incremental data migration	Charged — see Billing overview	—

SQL operations supported for incremental migration

Operation type	SQL statements
DML	INSERT, UPDATE, DELETE
DDL	CREATE TABLE (excludes statements with partitioning clauses, subpartitioning clauses, or functions; CREATE TABLE AS SELECT is not supported); ALTER TABLE (ADD COLUMN, ADD INDEX, DROP COLUMN, DROP INDEX, MODIFY COLUMN, RENAME COLUMN); DROP TABLE; RENAME TABLE; TRUNCATE TABLE; CREATE INDEX

Required database account permissions

Database	Schema migration	Full data migration	Incremental data migration
Self-managed Oracle database	Schema owner permissions	Schema owner permissions	Fine-grained permissions
PolarDB for PostgreSQL cluster	Schema owner permissions Note For the PolarDB for PostgreSQL cluster, use the database owner account created during database setup.

For instructions on creating accounts and granting permissions:

Self-managed Oracle database: See Prepare an Oracle database, CREATE USER, and GRANT in the Oracle documentation.
PolarDB for PostgreSQL cluster: See Create a database account and Database management.

Important

To migrate incremental data from an Oracle database, you must enable archive logging and supplemental logging on the Oracle database. See the Configure an Oracle database section of "Prepare an Oracle database."

Create a migration task

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.
Note
- DMS console steps may vary based on the mode and layout. See Simple mode and Customize the layout and style of the DMS console for details.
- You can also go directly to the Data Migration Tasks page of the new DTS console.
From the drop-down list next to Data Migration Tasks, select the region where the migration instance resides.

Note
In the new DTS console, select the region in the upper-left corner instead.

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

Warning

After configuring source and destination databases, read the limits shown in the upper part of the page before proceeding. Skipping this step may cause task failure or data inconsistency.

Source database parameters

Parameter	Description
Task Name	A descriptive name for the task. DTS auto-assigns a name, but a meaningful name makes the task easier to identify. Task names do not need to be unique.
Select an existing DMS database instance (optional)	Select an existing DMS instance to auto-populate database parameters, or skip this and configure parameters manually.
Database Type	Select Oracle.
Access Method	Select based on where the source database is deployed. This example uses Self-managed Database on ECS. For other access methods, see Preparation overview.
Instance Region	The region where the source Oracle database resides.
ECS Instance ID	The ID of the Elastic Compute Service (ECS) instance hosting the source Oracle database.
Port Number	The service port of the source Oracle database. Default: 1521.
Oracle Type	The architecture of the source Oracle database: Non-RAC Instance (configure the SID parameter) or RAC or PDB Instance (configure the Service Name parameter). This example uses RAC or PDB Instance with the Service Name parameter.
Database Account	The account for the source Oracle database. See Required database account permissions.
Database Password	The password for the database account.

Destination database parameters

Parameter	Description
Select an existing DMS database instance (optional)	Select an existing DMS instance to auto-populate database parameters, or skip this and configure parameters manually.
Database Type	Select PolarDB for PostgreSQL.
Access Method	Select Alibaba Cloud Instance.
Instance Region	The region where the destination PolarDB for PostgreSQL cluster resides.
Instance ID	The ID of the destination PolarDB for PostgreSQL cluster.
Database Name	The name of the destination database that will receive migrated objects.
Database Account	The database account for the destination cluster. See Required database account permissions.
Database Password	The password for the database account.

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

Warning
Adding DTS server CIDR blocks to your database whitelist or ECS instance security group rules introduces security risks. Before proceeding, take preventive measures, including enforcing strong credentials, limiting exposed ports, authenticating API calls, and reviewing whitelist rules regularly. Alternatively, connect the database to DTS using Express Connect, VPN Gateway, or Smart Access Gateway to avoid public internet exposure.

Configure the objects to migrate and advanced settings. Capitalization of object names in destination instance By default, DTS default policy is applied. Select another option to align object name capitalization with the source or destination database. See Specify the capitalization of object names in the destination instance. Selecting objects In the Source Objects section, select one or more objects (columns, tables, or databases) and click the Right arrow icon icon to move them to the Selected Objects section. In the Selected Objects section:

To rename a single object, right-click it. See Map the name of a single object.
To rename multiple objects at once, click Batch Edit in the upper-right corner. See Map multiple object names at a time.
To filter rows by condition, right-click a table and specify WHERE conditions. See Set filter conditions.

Note

If you do not select Incremental Data Migration, do not write data to the source database during migration — this is required to maintain data consistency.

Note

Renaming an object with the object name mapping feature may prevent other objects that depend on it from being migrated.

Migration type

Goal	Migration types to select
Full migration only (no service continuity needed)	Schema Migration and Full Data Migration
Full migration with service continuity	Schema Migration, Full Data Migration, and Incremental Data Migration

Processing mode of conflicting tables

Mode	Behavior
Precheck and Report Errors	Checks for tables with identical names in source and destination databases. The precheck passes only if no name conflicts exist. If conflicts are found, the task cannot start. To resolve conflicts, use the object name mapping feature to rename migrated objects. See Map object names.
Ignore Errors and Proceed	Skips the name conflict check. Use with caution: if source and destination have the same schema, records with matching primary keys are not migrated; if schemas differ, only specific columns may be migrated or the task may fail.

Click Next: Advanced Settings to configure advanced settings. Data verification settings To enable data verification after migration, see Configure data verification.

Advanced settings

Parameter	Description
Select the dedicated cluster used to schedule the task	DTS schedules tasks to a shared cluster by default. To use a dedicated cluster, purchase one first. See What is a DTS dedicated cluster?
Retry Time for Failed Connections	How long DTS retries a connection after a failure. Valid values: 10–1,440 minutes. Default: 720 minutes. We recommend that you set the parameter to a value greater than 30 minutes. If DTS reconnects within the retry window, the migration task resumes; otherwise, it fails. Note When multiple tasks share the same source or destination, the most recently set retry time applies to all. DTS instance charges continue during retries — release the instance promptly after source and destination are decommissioned.
The wait time before a retry when other issues occur in the source and destination databases	How long DTS retries DDL or DML operations that fail after the task starts. Valid values: 1–1,440 minutes. Default: 10 minutes. We recommend that you set the parameter to a value greater than 10 minutes. This value must be less than the Retry Time for Failed Connections value.
Enable Throttling for Full Data Migration	Limits read/write load on source and destination databases during full data migration. When enabled, configure Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s). Available only when Full Data Migration is selected.
Enable Throttling for Incremental Data Migration	Limits load during incremental data migration. When enabled, configure RPS of Incremental Data Migration and Data migration speed for incremental migration (MB/s). Available only when Incremental Data Migration is selected.
Environment Tag	An optional tag to identify the DTS instance by environment type.
Actual Write Code	The encoding format for writing data to the destination database. Select based on your requirements.
Configure ETL	Whether to enable extract, transform, and load (ETL). Select Yes to enter data processing statements in the code editor. See Configure ETL in a data migration or data synchronization task. Select No to skip ETL configuration. For an overview, see What is ETL?
Monitoring and Alerting	Whether to configure alerts for the migration task. Select Yes to set an alert threshold and alert contacts. Contacts receive notifications if the task fails or migration latency exceeds the threshold. See Configure monitoring and alerting for a new DTS task.

Click Next: Save Task Settings and Precheck.
Note
- To preview the API parameters used to configure this instance, hover over Next: Save Task Settings and Precheck and click Preview API Call.
- DTS runs a precheck before starting the migration task. The task starts only after the precheck passes.
- If the precheck fails for an item, click View Details next to the failed item, fix the issue, then run the precheck again.
- If a precheck item generates a warning: fix the issue if it cannot be ignored; or click Confirm Alert Details > Ignore > OK > Precheck Again if it can be safely ignored. Ignoring a warning may cause data inconsistency or expose your system to risks.
Wait until the success rate reaches 100%, then click Next: Purchase Instance.

On the Purchase Instance page, configure the instance class.

Parameter	Description
Resource Group	The resource group for the migration instance. Default: default resource group. See What is Resource Management?
Instance Class	The instance class determines migration speed. Select based on your requirements. See Specifications of data migration instances.

Read and accept the Data Transmission Service (Pay-as-you-go) Service Terms by selecting the check box.
Click Buy and Start. Monitor the migration progress on the Task Management page.