Migrate PolarDB for PostgreSQL to self-managed Oracle

Prerequisites

• Tables to be migrated from the PolarDB for PostgreSQL cluster must have a primary key or a non-null unique index.
• The version number of the self-managed Oracle database is 9i, 10g, 11g, 12c, 18c, or 19c.
• The schemas of objects such as tables are created in the self-managed Oracle database.
• Ensure the self-managed Oracle database has more storage space than the PolarDB for PostgreSQL database.

Limits

• In this scenario, DTS supports only full data migration and incremental data migration. DTS does not support schema migration.
• During full data migration, DTS uses read and write resources of the source and destination databases. This may increase the loads of the database servers. Before you migrate data, evaluate the impact of data migration on the performance of the source and destination databases. We recommend that you migrate data during off-peak hours.

• 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.

• A data migration task can migrate data from only a single database. To migrate data from multiple databases, you must create a data migration task for each database.
• If you select a schema as the object to be migrated and create a table in the schema or execute the RENAME statement to rename a table in the schema, you must execute the ALTER TABLE schema.table REPLICA IDENTITY FULL; statement before you write data to the table.
  Note Replace the schema and table variables in the preceding statement with your schema name and table name.
• To ensure the accuracy of the displayed latency for incremental data migration, DTS creates a heartbeat table named dts_postgres_heartbeat in the source database. The table structure and sample data are as follows:

  => select * from dts_postgres_heartbeat;
   slot_name   | revice_time | revice_lsn | flushed_lsn |         update_time           | dts_service_time
  -------------+-------------+------------+-------------+-------------------------------+-----------------
   vxxx_xxx_ai | 161xxx194   | 0/4Dxxx F0 |             | 2021-04-02 06:58:38.219416+00 |        16173xxx
  (1 row)

• If the source database has long-running transactions and the task includes incremental data migration, this can cause Write-Ahead Logging (WAL) files to accumulate, potentially exhausting the disk space on the source database.

Billing

SQL operations that can be synchronized during incremental data migration

INSERT, UPDATE, and DELETE

Permissions required for database accounts

Procedure

1. Log on to the 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.

2. In the left-side navigation pane, click Data Migration.

3. At the top of the Migration Tasks page, select the region of the destination cluster.

4. In the upper-right corner of the page, click Create Data Migration Task.

5. Configure the source and destination databases.

   Section	Parameter	Description
   N/A	Task Name	The task name that DTS automatically generates. We recommend that you specify a descriptive name that makes it easy to identify the task. You do not need to specify a unique task name.
   Source Database	Instance Type	Select PolarDB.
   	Instance Region	Select the region of the source PolarDB for PostgreSQL cluster.
   	PolarDB Instance ID	Select the ID of the source PolarDB for PostgreSQL cluster.
   	Database Account	Enter the database account for the PolarDB for PostgreSQL cluster. For information about the required permissions, see Database account permissions.
   	Database Password	Enter the password for the database account. Note After configuring the source database, click next to Database PasswordTest Connectivity to verify the connection information. If the connection is successful, a Passed message appears. If the connection fails, a FailedFailed message appears. In this case, click Diagnose next to the failure message and modify the configuration as prompted.
   Destination Database	Instance Type	Select the type based on where the destination database is deployed. This topic uses User-Created Database with Public IP Address as an example. Note If your self-managed database is of a different type, you must perform additional preparation steps. For more information, see Overview of preparations.
   	Instance Region	You do not need to specify this parameter.
   	Database Type	Select Oracle.
   	Hostname or IP Address	The IP address that is used to access the self-managed Oracle database. In this example, the public IP address is used.
   	Port Number	Enter the service port of the database. This example uses 1521.
   	Instance Type	Non-RAC Instance: If you select this option, you must also specify the SID. RAC or PDB Instance: If you select this option, you must also specify the Service Name. This example uses Non-RAC Instance.
   	SID	The system ID (SID) of the destination database.
   	Database Account	Enter the database account for the self-managed Oracle database. For information about the required permissions, see Database account permissions.
   	Database Password	Enter the password for the database account. Note After configuring the destination database, click next to Database PasswordTest Connectivity to verify the connection information. If the connection is successful, a Passed message appears. If the connection fails, a FailedFailed message appears. In this case, click Diagnose next to the failure message and modify the configuration as prompted.

6. In the lower-right corner of the page, click Set Whitelist and Next.
   If the source or destination database is an Alibaba Cloud database instance, such as an RDS MySQL or ApsaraDB for MongoDB instance, DTS automatically adds the IP addresses of the DTS servers in the corresponding region to the allowlist of the database instance. If the source or destination database is self-managed on an ECS instance, DTS automatically adds the IP addresses of the DTS servers in the corresponding region to the security group rules of the ECS instance. You must also ensure that the self-managed database does not restrict access from the ECS instance. If the database is deployed in a cluster across multiple ECS instances, you must manually add the IP addresses of the DTS servers for the region to the security group rules of each ECS instance. If your database is in a data center or hosted by another cloud provider, you must manually add the IP addresses of the DTS servers to your database's allowlist. For a list of DTS server IP addresses, see IP addresses of DTS servers.

   Warning

   Adding the public CIDR blocks of DTS servers, whether automatically or manually, may introduce security risks. By using this product, you acknowledge and accept these potential risks. You are responsible for implementing basic security measures, including but not limited to using strong passwords, restricting open ports, using authentication for internal API calls, regularly reviewing and restricting unnecessary network segments, or connecting through private networks such as Express Connect, VPN Gateway, or Smart Access Gateway.

7. Select the migration types, the migration policy, and the objects to migrate.
   Select Full data migration and Incremental data migration as the migration types. In the Migration Objects section, select the database objects that you want to migrate (for example, dtstestdata) and move them to the Selected Objects list.

   Configuration	Description
   Rename objects	To rename a migrated object in the destination instance, use the object name mapping feature. For more information, see Object name mapping.
   Retry time for connection failures	If DTS fails to connect to the source or destination database, it retries for 720 minutes (12 hours) by default. You can specify a custom retry duration. If the connection is re-established within this duration, the task resumes. Otherwise, it fails. Note You are charged for the DTS instance during the retry period. Set a custom retry duration based on your business needs or release the DTS instance as soon as possible after the source and destination database instances are released.

8. After you complete the configuration, click Precheck and Start in the lower-right corner of the page.

   Note

   • Before the migration task starts, DTS runs a precheck. 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 as prompted and run the precheck again.

     • If you do not need to fix the warning items, you can select Ignore and then click Ignore Warnings and Rerun Precheck to run the precheck again.

9. After the task passes the precheck, click Next.

10. In the Confirm Settings dialog box that appears, select a Instance Class and select the Data Transmission Service (pay-as-you-go) Service Terms checkbox.

11. Click Buy and Start to begin the migration task.
    • Full data migration

      We recommend that you do not manually stop the task during full data migration. Otherwise, the data migrated to the destination database may be incomplete. You can wait until the data migration task automatically stops.

    • Full and incremental data migration

      The migration task does not stop automatically. You must stop it manually.

      Important Stop the task during off-peak hours or when you are ready to switch your business workloads to the destination database.
      1. Wait for the task progress to show Incremental Data Migration and the status to show Undelayed. Then, stop writing data to the source database for a few minutes. During this time, the Incremental Data Migration status may show a latency.
      2. After the Incremental Data Migration status changes back to Undelayed, manually stop the migration task.