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

Prerequisites

Before you begin, make sure that:

• The source Oracle database and the destination PolarDB for PostgreSQL (Compatible with Oracle) cluster are both created. To create a PolarDB for PostgreSQL (Compatible with Oracle) cluster, see Create a cluster. For supported database versions, see Overview of data migration scenarios.

• The source Oracle database is running in ARCHIVELOG mode, archived log files are accessible, and a retention period is configured. See Managing archived redo log files.

• Supplemental logging is enabled on the source Oracle database, with SUPPLEMENTAL_LOG_DATA_PK and SUPPLEMENTAL_LOG_DATA_UI set to Yes. See Supplemental logging.

• You are familiar with DTS capabilities and limits for Oracle migrations. Use Advanced Database & Application Migration (ADAM) to evaluate the source database before migration. See Prepare an Oracle database and Overview.

Billing

Migration types

SQL operations supported for incremental migration

Data type mappings

See Data type mappings between heterogeneous databases.

Limitations

During schema migration, DTS migrates foreign keys to the destination database. During full data migration and incremental data migration, DTS temporarily disables constraint checks and cascade operations on foreign keys at the session level. Cascade update and delete operations performed on the source database during migration may cause data inconsistency.

Set up the Oracle database account

Log on to the source Oracle database, create an account for DTS, and grant the required permissions.

Skip this step if you have already created an account with the permissions listed below.

For instructions on creating accounts and granting permissions, see:

• Self-managed Oracle database: Prepare a database account, CREATE USER, and GRANT

• PolarDB for PostgreSQL (Compatible with Oracle) cluster: Create database accounts

Create a migration task

1. Go to the Data Migration Tasks page.

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

   2. In the top navigation bar, move the pointer over DTS.

   3. Choose DTS (DTS) > Data Migration.

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

2. From the drop-down list on the right side of Data Migration Tasks, select the region where your migration instance resides.

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

3. Click Create Task. In the Create Data Migration Task wizard, configure the source and destination databases.

   Warning

   After configuring the source and destination databases, read the Limits displayed at the top of the page before proceeding. Skipping this step may cause the task to fail or result in data inconsistency.

   Section	Parameter	Description
   N/A	Task Name	A name for the task. DTS generates a name automatically. Specify an informative name to identify the task. The name does not need to be unique.
   Source Database	Database Type	Select Oracle.
   	Access Method	The method used to connect to the source database. This example uses Public IP Address. For other access methods, set up the required environment first. See Preparation overview.
   	Instance Region	The region where the source Oracle database resides.
   	Hostname or IP address	The endpoint of the source Oracle database.
   	Port Number	The service port of the source Oracle database. Default: 1521. The port must be accessible over the Internet for this example.
   	Oracle Type	The architecture of the source Oracle database. Select Non-RAC Instance to configure the SID parameter, or RAC or PDB Instance to configure the Service Name parameter. This example uses Non-RAC Instance.
   	Database Account	The account of the source Oracle database. See Set up the Oracle database account for required permissions.
   	Database Password	The password for the database account.
   Destination Database	Database Type	Select PolarDB (Compatible with Oracle).
   	Access Method	Select Public IP Address.
   	Instance Region	The region where the destination cluster is deployed.
   	Data Sources	Select Single Data Source to configure the primary node endpoint and port. Select Multiple Data Sources to specify IP addresses and port numbers for multiple nodes in the IP:Port of Data Sources field. With Multiple Data Sources, DTS can automatically switch to the new primary node during a primary/secondary switchover, which is suitable for disaster recovery scenarios. This example uses Single Data Source.
   	Domain Name or IP Address	The endpoint of the primary node in the destination cluster. Run ping on your computer to get the IP address.
   	Port Number	The service port of the destination database. Default: 1521.
   	IP:Port of Data Sources	The IP addresses and port numbers of multiple nodes, including the primary node. Separate multiple entries with commas (,).
   	Database Name	The name of the database in the destination cluster.
   	Database Account	The account for the destination cluster. See Set up the Oracle database account for required permissions.
   	Database Password	The password for the database account.

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

   Warning

   Adding DTS server CIDR blocks to a database whitelist or ECS security group may introduce security risks. Before proceeding, take preventive measures such as: strengthening account credentials, restricting exposed ports, validating API calls, and regularly auditing whitelist rules. Consider using Express Connect, VPN Gateway, or Smart Access Gateway to connect the database to DTS instead of using public IP addresses.

5. Configure the objects to migrate and advanced settings.

   Parameter	Description
   Migration Types	Select Schema Migration and Full Data Migration for a one-time migration. To migrate without interrupting your services, also select Incremental Data Migration. Note If you do not select Incremental Data Migration, avoid writing to the source database during migration to maintain data consistency.
   Add a hidden primary key for a table without a primary key/a table without a unique key	Set to Yes if any tables being migrated lack a primary key or unique key. DTS uses the Oracle ROWID column as the hidden primary key in the destination, preventing duplicate data. This parameter is available only when Schema Migration, Full Data Migration, and Incremental Data Migration are all selected.
   Processing Mode of Conflicting Tables	Precheck and Report Errors: DTS checks whether the destination database contains tables with the same names as the source. If duplicates exist, the precheck fails and the task cannot start. Use the object name mapping feature to rename conflicting tables. See Map object names. Ignore Errors and Proceed: Skips the precheck for duplicate table names. If source and destination have identical schemas and a record shares a primary key with an existing destination record: during full migration, DTS skips the record; during incremental migration, DTS overwrites the existing record. If schemas differ, only specific columns are migrated or the task fails. Use with caution.
   Source Objects	Select one or more objects. Click the icon to move them to Selected Objects.
   Selected Objects	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. See Map multiple object names at a time. Note Renaming an object may cause other objects that depend on it to fail migration. To filter rows, right-click a table and specify WHERE conditions. See Specify filter conditions. To select specific SQL operations for incremental migration, right-click an object and select the operations.

6. Click Next: Advanced Settings and configure the following settings. Data Verification Settings See Configure a data verification task.

   Advanced Settings

   Parameter	Description
   Dedicated Cluster for Task Scheduling	By default, DTS schedules tasks to a shared cluster. To use a dedicated cluster, purchase one with the required specifications. See What is a DTS dedicated cluster?
   Retry Time for Failed Connections	How long DTS retries after a connection failure. Valid values: 10–1440 minutes. Default: 720 minutes. Set this to more than 30 minutes. If DTS reconnects within this period, it resumes the task; otherwise, the task fails. Note If multiple tasks share the same source or destination database, the most recently configured retry time takes precedence. DTS charges for the instance during retries.
   Retry Time for Other Issues	How long DTS retries after DDL or DML operation failures. Valid values: 1–1440 minutes. Default: 10 minutes. Set this to more than 10 minutes. Important This value must be less than the Retry Time for Failed Connections value.
   Enable Throttling for Full Data Migration	Limits read/write resource usage during full migration to reduce server load. 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 resource usage during incremental migration. 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.
   Actual Write Code	The encoding format for writing data to the destination database.
   Configure ETL	Enables the extract, transform, and load (ETL) feature. 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. See What is ETL?
   Monitoring and Alerting	Configures alerts for task failures or latency exceeding a threshold. Select Yes to configure alert thresholds and notification settings. See Configure monitoring and alerting when you create a DTS task.

7. Click Next: Save Task Settings and Precheck. To view the API parameters for this task configuration, move the pointer over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.

   DTS runs a precheck before starting the task. The task can only start after passing the precheck. If the precheck fails, click View Details next to each failed item, troubleshoot the issue, then click Precheck Again. If an alert item can be ignored, click Confirm Alert Details > Ignore > OK > Precheck Again. Ignoring alert items may cause data inconsistency.

8. Wait until Success Rate reaches 100%, then click Next: Purchase Instance.

9. On the Purchase Instance page, configure the instance class.

   Section	Parameter	Description
   New Instance Class	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 a class based on your requirements. See Instance classes of data migration instances.

10. Read and select the Data Transmission Service (Pay-as-you-go) Service Terms check box.

11. Click Buy and Start. In the confirmation dialog, click OK. View migration progress on the Data Migration page.

What's next

Delete the hidden primary key (if applicable)

If you set Add a hidden primary key for a table without a primary key/a table without a unique key to Yes, delete the hidden primary key after switching your workloads to the destination cluster and before terminating the DTS instance. On the Basic Information tab of the Full Data Migration or Incremental Write module, click Delete Invisible Primary Key in the Actions column.

Reset sequence initial values

After switching workloads to the destination, newly written sequences do not continue from the maximum value in the source database. Before switching, query the maximum sequence values from the source database, then set those values as the initial values in the destination.

To query maximum sequence values from the source Oracle database, run:

DECLARE
  CURSOR cur_data IS
    SELECT SEQUENCE_NAME, LAST_NUMBER FROM user_sequences;
  v_column1_value varchar(1000);
  v_column2_value number(20);
BEGIN
  dbms_output.enable(NULL);
  OPEN cur_data;
  LOOP
    FETCH cur_data INTO v_column1_value, v_column2_value;
    EXIT WHEN cur_data%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('select setval(''' || lower(v_column1_value) || ''', ' || v_column2_value || ');');
  END LOOP;
  CLOSE cur_data;
END;
/

The output contains setval statements for all sequences in the source database. Run only the statements for the sequences relevant to your workloads in the destination database.