Migrate self-managed Oracle to AnalyticDB for PostgreSQL

Billing

Notes

• During a full data migration, DTS consumes read and write resources from the source and destination databases, which can increase database load. Before starting the migration, evaluate its impact on database performance. We recommend performing the data migration during off-peak hours.

• If a data migration task fails, DTS automatically resumes the task. Before switching your workload to the destination database, stop or release the task. Otherwise, the task may automatically resume and overwrite data in the destination database.

• DTS supports only table-level migration. You cannot currently use append-optimized (AO) tables as destination tables.

• When using column mapping, if you do not migrate all columns or if the table schemas differ, data in columns that exist only in the source table is lost.

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

Migration types

Database account permissions

To learn how to create a database account and grant permissions, see the following topics:

• For a self-managed Oracle database, see CREATE USER and GRANT.

• For AnalyticDB for PostgreSQL, see Configure an account.

Oracle logs and fine-grained permissions

1. Enable archive logging and supplemental logging.

   Type	Procedure
   Archive logging	Execute the following statements to enable archive logging: shutdown immediate; startup mount; alter database archivelog; alter database open; archive log list;
   Supplemental logging	Enable supplemental logging at the database or table level based on your business requirements: Note You can enable database-level supplemental logging to ensure the stability of Data Transmission Service (DTS) tasks. You can enable table-level supplemental logging to reduce the disk usage of the source Oracle database. Enable database-level supplemental logging Execute the following statement to enable minimal supplemental logging: alter database add supplemental log data; Execute the following statement to enable primary key and unique key supplemental logging at the database level: alter database add supplemental log data (primary key,unique index) columns; Enable table-level supplemental logging Execute the following statement to enable minimal supplemental logging: alter database add supplemental log data; Enable table-level supplemental logging by using one of the following methods: Enable primary key supplemental logging at the table level alter table table_name add supplemental log data (primary key) columns; Enable table-level supplemental logging for all columns alter table tb_name add supplemental log data (all) columns;
   Force logging	Execute the following statements to enable force logging: alter database force logging;

2. Grant fine-grained permissions to the Oracle database account.

   Oracle 9i to 11g

   # Create a database account (for example, rdsdt_dtsacct) and grant permissions.
   create user rdsdt_dtsacct IDENTIFIED BY rdsdt_dtsacct;
   grant create session to rdsdt_dtsacct;
   grant connect to rdsdt_dtsacct;
   grant resource to rdsdt_dtsacct;
   grant execute on sys.dbms_logmnr to rdsdt_dtsacct;
   grant select on V_$LOGMNR_LOGS to rdsdt_dtsacct;
   grant select on all_objects to rdsdt_dtsacct;
   grant select on all_tab_cols to rdsdt_dtsacct;
   grant select on dba_registry to rdsdt_dtsacct;
   grant select any table to rdsdt_dtsacct;
   grant select any transaction to rdsdt_dtsacct;
   -- v$log privileges
   grant select on v_$log to rdsdt_dtsacct;
   -- v$logfile privileges
   grant select on v_$logfile to rdsdt_dtsacct;
   -- v$archived_log privileges
   grant select on v_$archived_log to rdsdt_dtsacct;
   -- v$parameter privileges
   grant select on v_$parameter to rdsdt_dtsacct;
   -- v$database privileges
   grant select on v_$database to rdsdt_dtsacct;
   -- v$active_instances privileges
   grant select on v_$active_instances to rdsdt_dtsacct;
   -- v$instance privileges
   grant select on v_$instance to rdsdt_dtsacct;
   -- v$logmnr_contents privileges
   grant select on v_$logmnr_contents to rdsdt_dtsacct;
   -- system tables
   grant select on sys.USER$ to rdsdt_dtsacct;
   grant select on SYS.OBJ$ to rdsdt_dtsacct;
   grant select on SYS.COL$ to rdsdt_dtsacct;
   grant select on SYS.IND$ to rdsdt_dtsacct;
   grant select on SYS.ICOL$ to rdsdt_dtsacct;
   grant select on SYS.CDEF$ to rdsdt_dtsacct;
   grant select on SYS.CCOL$ to rdsdt_dtsacct;
   grant select on SYS.TABPART$ to rdsdt_dtsacct;
   grant select on SYS.TABSUBPART$ to rdsdt_dtsacct;
   grant select on SYS.TABCOMPART$ to rdsdt_dtsacct;
   grant select_catalog_role TO rdsdt_dtsacct;

   Oracle 12c to 19c (multitenant)

   # Switch to the PDB, then create a database account (for example, rdsdt_dtsacct) and grant permissions.
   ALTER SESSION SET container = ORCLPDB1;
   create user rdsdt_dtsacct IDENTIFIED BY rdsdt_dtsacct;
   grant create session to rdsdt_dtsacct;
   grant connect to rdsdt_dtsacct;
   grant resource to rdsdt_dtsacct;
   grant execute on sys.dbms_logmnr to rdsdt_dtsacct;
   grant select on all_objects to rdsdt_dtsacct;
   grant select on all_tab_cols to rdsdt_dtsacct;
   grant select on dba_registry to rdsdt_dtsacct;
   grant select any table to rdsdt_dtsacct;
   grant select any transaction to rdsdt_dtsacct;
   -- v$log privileges
   grant select on v_$log to rdsdt_dtsacct;
   -- v$logfile privileges
   grant select on v_$logfile to rdsdt_dtsacct;
   -- v$archived_log privileges
   grant select on v_$archived_log to rdsdt_dtsacct;
   -- v$parameter privileges
   grant select on v_$parameter to rdsdt_dtsacct;
   -- v$database privileges
   grant select on v_$database to rdsdt_dtsacct;
   -- v$active_instances privileges
   grant select on v_$active_instances to rdsdt_dtsacct;
   -- v$instance privileges
   grant select on v_$instance to rdsdt_dtsacct;
   -- v$logmnr_contents privileges
   grant select on v_$logmnr_contents to rdsdt_dtsacct;
   grant select on sys.USER$ to rdsdt_dtsacct;
   grant select on SYS.OBJ$ to rdsdt_dtsacct;
   grant select on SYS.COL$ to rdsdt_dtsacct;
   grant select on SYS.IND$ to rdsdt_dtsacct;
   grant select on SYS.ICOL$ to rdsdt_dtsacct;
   grant select on SYS.CDEF$ to rdsdt_dtsacct;
   grant select on SYS.CCOL$ to rdsdt_dtsacct;
   grant select on SYS.TABPART$ to rdsdt_dtsacct;
   grant select on SYS.TABSUBPART$ to rdsdt_dtsacct;
   grant select on SYS.TABCOMPART$ to rdsdt_dtsacct;
   -- V$PDBS privileges
   grant select on V_$PDBS to rdsdt_dtsacct;
   grant select on dba_objects to rdsdt_dtsacct;
   grant select on DBA_TAB_COMMENTS to rdsdt_dtsacct;
   grant select on dba_tab_cols to rdsdt_dtsacct;
   grant select_catalog_role TO rdsdt_dtsacct;
   # Switch to CDB$ROOT to create a database account and grant permissions.
   ALTER SESSION SET container = CDB$ROOT;
   # Set the required session parameter, then create a database account (e.g., rdsdt_dtsacct) and grant permissions.
   alter session set "_ORACLE_SCRIPT"=true;
   create user rdsdt_dtsacct IDENTIFIED BY rdsdt_dtsacct;
   grant create session to rdsdt_dtsacct;
   grant connect to rdsdt_dtsacct;
   grant select on v_$logmnr_contents to rdsdt_dtsacct;
   grant LOGMINING TO rdsdt_dtsacct;
   grant EXECUTE_CATALOG_ROLE to rdsdt_dtsacct;
   grant execute on sys.dbms_logmnr to rdsdt_dtsacct;

   Oracle 12c to 19c (non-multitenant)

   # Create a database account (for example, rdsdt_dtsacct) and grant permissions.
   create user rdsdt_dtsacct IDENTIFIED BY rdsdt_dtsacct;
   grant create session to rdsdt_dtsacct;
   grant connect to rdsdt_dtsacct;
   grant resource to rdsdt_dtsacct;
   grant select on V_$LOGMNR_LOGS to rdsdt_dtsacct;
   grant select on all_objects to rdsdt_dtsacct;
   grant select on all_tab_cols to rdsdt_dtsacct;
   grant select on dba_registry to rdsdt_dtsacct;
   grant select any table to rdsdt_dtsacct;
   grant select any transaction to rdsdt_dtsacct;
   grant select on v$database to rdsdt_dtsacct;
   grant select on dba_objects to rdsdt_dtsacct;
   grant select on DBA_TAB_COMMENTS to rdsdt_dtsacct;
   grant select on dba_tab_cols to rdsdt_dtsacct;
   -- v$log privileges
   grant select on v_$log to rdsdt_dtsacct;
   -- v$logfile privileges
   grant select on v_$logfile to rdsdt_dtsacct;
   -- v$archived_log privileges
   grant select on v_$archived_log to rdsdt_dtsacct;
   -- v$parameter privileges
   grant select on v_$parameter to rdsdt_dtsacct;
   -- v$database privileges
   grant select on v_$database to rdsdt_dtsacct;
   -- v$active_instances privileges
   grant select on v_$active_instances to rdsdt_dtsacct;
   -- v$instance privileges
   grant select on v_$instance to rdsdt_dtsacct;
   -- v$logmnr_contents privileges
   grant select on v_$logmnr_contents to rdsdt_dtsacct;
   grant select on sys.USER$ to rdsdt_dtsacct;
   grant select on SYS.OBJ$ to rdsdt_dtsacct;
   grant select on SYS.COL$ to rdsdt_dtsacct;
   grant select on SYS.IND$ to rdsdt_dtsacct;
   grant select on SYS.ICOL$ to rdsdt_dtsacct;
   grant select on SYS.CDEF$ to rdsdt_dtsacct;
   grant select on SYS.CCOL$ to rdsdt_dtsacct;
   grant select on SYS.TABPART$ to rdsdt_dtsacct;
   grant select on SYS.TABSUBPART$ to rdsdt_dtsacct;
   grant select on SYS.TABCOMPART$ to rdsdt_dtsacct;
   grant LOGMINING TO rdsdt_dtsacct;
   grant EXECUTE_CATALOG_ROLE to rdsdt_dtsacct;
   grant execute on sys.dbms_logmnr to rdsdt_dtsacct;
   grant select_catalog_role TO rdsdt_dtsacct;

   Note

   For more information about the multitenant architecture of Oracle, see Oracle Multitenant.

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 where the destination instance is located.

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

5. Configure the connection settings for the source and destination databases.

   Category	Parameter	Description
   N/A	Task Name	DTS automatically generates a task name. For easier identification, we recommend that you specify a descriptive name. The name does not need to be unique.
   Source Database	Instance Type	Select an option based on where the source database is deployed. This topic uses a self-managed database on an ECS instance as an example. Note If your self-managed database is of another instance type, you must perform additional preparations. For more information, see Preparation overview.
   	Region	Select the region where the ECS instance that hosts the Oracle database is located.
   	ECS Instance ID	Select the ID of the ECS instance that hosts the self-managed Oracle database.
   	Database Type	Select Oracle.
   	Port	Enter the service port of the self-managed Oracle database. The default value is 1521.
   	Instance Type	Non-RAC Instance: If you select this option, you must also specify the SID. RAC Instance: If you select this option, you must also specify the Service Name. In this example, Non-RAC Instance is selected.
   	SID	Enter the SID of the self-managed Oracle database.
   	Database Account	Enter the database account of the self-managed Oracle database. For information about the required permissions, see Permissions required for database accounts.
   	Database Password	Enter the password for the database account. Note After you enter the source database information, you can click Test Connectivity next to Database Password to verify that the information is correct. If the information is correct, the message Passed is displayed. If the message Failed is displayed, click Diagnose next to the Failed message and adjust the source database information based on the prompts.
   Destination Database	Instance Type	Select AnalyticDB for PostgreSQL.
   	Region	Select the region where the destination AnalyticDB for PostgreSQL instance is located.
   	Instance ID	Select the ID of the destination AnalyticDB for PostgreSQL instance.
   	Database	Enter the name of the destination database to which you want to migrate data.
   	Database Account	Enter the database account of the destination AnalyticDB for PostgreSQL instance. For information about the required permissions, see Permissions required for database accounts.
   	Database Password	Enter the password for the database account.

6. After you complete the configuration, click Set Whitelist and Next in the lower-right corner of the page.

   If the source or destination is an Alibaba Cloud database instance, such as ApsaraDB for MySQL or ApsaraDB for MongoDB, DTS automatically adds the IP addresses of its servers in the corresponding region to the instance's whitelist. If the source or destination is a self-managed database on an ECS instance, DTS automatically adds its IP addresses to the security 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 a cluster deployed across multiple ECS instances, you must manually add the DTS IP addresses to the security rules for each ECS instance. If the source or destination is a database in an on-premises data center or on another cloud, you must manually add the DTS IP addresses for the corresponding region to allow access from DTS servers. For a list of DTS server IP addresses, see DTS server IP addresses.

   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, operation types, and migration objects.

   Parameter	Description
   Migration Types	If you only need to perform a full migration, select both Schema Migration and Full Data Migration. To perform a migration with no downtime, select Schema Migration, Full Data Migration, and Incremental Data Migration. Note If you do not select Incremental Data Migration, avoid writing new data to the source database during the migration to ensure data consistency.
   Operation Types	Based on your business requirements, select the operation types to synchronize during incremental data migration. By default, all operation types are selected.
   Objects to Migrate	In the Available box, click the objects that you want to migrate and then click the icon to move them to the Selected Objects box. Note You can select objects to migrate at the table level. By default, the names of migrated objects in the destination database are the same as in the source database. To change the name of an object in the destination database, use the object name mapping feature. For more information, see Object name mapping. If you use the object name mapping feature, other objects that depend on the renamed object may fail to be migrated.
   Object Name Mapping	To change the name of a migrated object in the destination instance, use the object name mapping feature. For more information, see Object name mapping. Important If you use column mapping for a partial table migration or if the source and destination table schemas are different, data in columns that exist in the source table but not in the destination table will be lost.
   Connection Failure Retry Duration	The default retry duration is 12 hours. You can also specify a custom duration. If DTS reconnects to the source and destination databases within the specified duration, the migration task automatically resumes. Otherwise, the task fails. Note DTS charges for the task run time during the connection retry period. We recommend that you customize the 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.
   Enclose Object Names in Quotation Marks	Specifies whether to add quotation marks to destination object names. If you select Yes, and one of the following conditions is met, DTS adds single or double quotation marks to the destination object during the schema migration and incremental data migration phases: The business environment of the source database is case-sensitive, and the object names use a mix of uppercase and lowercase letters. A source table name does not start with a letter and contains characters other than letters, digits, or special characters. Note The supported special characters are underscores (_), number signs (#), and dollar signs ($). The names of the schemas, tables, or columns to be migrated are keywords, reserved words, or invalid characters in the destination database.

8. Set the primary key and distribution key for the tables to be migrated to the AnalyticDB for PostgreSQL instance.

   On the configuration page, you must also set the Type for each table, such as a hash-distributed table. After the configuration is complete, the Definition Status column displays "Defined".

   Note

   • For more information about primary keys and distribution keys, see Table constraints and Table distribution keys.

   • If any tables lack a primary key, the Set Primary Keys and Distribution Keys of All Tables Without Primary Keys to ROWID option appears on this page. If you select this option, DTS adds a ROWID column to the destination tables to serve as both the primary key and distribution key.

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

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

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

12. Click Buy and Start to begin the migration.

    • Schema migration + Full data migration

      Allow the task to complete automatically. Stopping it manually may result in incomplete data.

    • Schema migration + Full data migration + Incremental data migration

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

      Important

      Choose an appropriate time to stop the task manually, such as during off-peak hours or when you are ready to switch your business to the destination cluster.

      1. Wait until the migration task enters the Incremental Data Migration phase and the status shows Undelayed. Then, stop writing data to the source database for several minutes. During this time, the status of Incremental Data Migration may show a latency.

      2. Wait for the Incremental Data Migration status to show Undelayed again. Then, manually stop the migration task.