AnalyticDB:Migrate data from a self-managed Oracle database to a cloud-native data warehouse AnalyticDB for PostgreSQL

Prerequisites

Before you begin, ensure that you have:

• A self-managed Oracle database running version 9i, 10g, 11g, 12c, 18c, or 19c

• The Oracle database running in ARCHIVELOG mode — DTS uses LogMiner to read archived redo log files and capture incremental changes. Without this mode enabled, incremental data migration is not possible

• Supplemental logging enabled, including SUPPLEMENTAL_LOG_DATA_PK and SUPPLEMENTAL_LOG_DATA_UI — this ensures LogMiner captures enough column data to reconstruct DML operations accurately. For details, see Supplemental Logging

• A destination AnalyticDB for PostgreSQL instance already created. For details, see Create an instance

Billing

Migration types

Usage notes

• During full data migration, DTS uses read and write resources on both the source and destination databases, which may increase server load. Run migrations during off-peak hours to reduce impact.

• If a data migration task fails and is automatically resumed by DTS, stop or release the task before switching workloads to the destination database. Otherwise, data from the source overwrites data in the destination after the task resumes.

• If the self-managed Oracle database uses a Real Application Cluster (RAC) architecture and connects to DTS over an Alibaba Cloud virtual private cloud (VPC), 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 to ensure the DTS task runs as expected. See Connect a data center to DTS by using VPN Gateway.

Permissions required for database accounts

To create database accounts and grant permissions, see:

• Self-managed Oracle database: CREATE USER and GRANT

• AnalyticDB for PostgreSQL instance: Create a database account

Enable logging and grant fine-grained permissions (when DBA permissions are unavailable)

If you cannot grant DBA permissions to the Oracle database account, follow these steps to enable archive logging and supplemental logging, then grant the minimum required permissions.

-- Create a database account named rdsdt_dtsacct and grant permissions to the account.
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;

-- Switch to the pluggable database (PDB).
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 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;
grant select_catalog_role TO rdsdt_dtsacct;

-- Switch to the CDB$ROOT (root container of the container database).
ALTER SESSION SET container = CDB$ROOT;
-- Modify the default Oracle parameters to allow user creation in CDB$ROOT.
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;

-- Create a database account named rdsdt_dtsacct and grant permissions to the account.
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;

Create the migration task

1. Log on to the DTS console.

   If you are redirected to the Data Management (DMS) console, click the icon in the to return to the previous version of the 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 resides.

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

5. Configure the source and destination databases.

   Section	Parameter	Description
   N/A	Task Name	The task name DTS generates automatically. Specify a descriptive name to identify the task easily. Task names do not need to be unique.
   Source Database	Instance Type	The access method for the source database. In this example, User-Created Database in ECS Instance is selected. Important If you select a different instance type, set up the required environment for the self-managed database first. See Preparation overview.
   	Instance Region	The region where the Elastic Compute Service (ECS) instance hosting the source Oracle database resides.
   	ECS Instance ID	The ID of the ECS instance hosting the source Oracle database.
   	Database Type	The type of the source database. Select Oracle.
   	Port Number	The service port of the source Oracle database. Default: 1521.
   	Instance Type	The architecture of the source Oracle database. Select Non-RAC Instance to specify the SID parameter, or RAC or PDB Instance to specify the Service Name parameter. This example uses Non-RAC Instance.
   	SID	The system ID (SID) of the source Oracle database.
   	Database Account	The account for the source Oracle database. For required permissions, see Permissions required for database accounts.
   	Database Password	The password for the database account. After entering the source database details, click Test Connectivity next to Database Password to verify the connection. Passed confirms valid credentials; if Failed appears, click Check to review and correct the details.
   Destination Database	Instance Type	The type of the destination database. Select AnalyticDB for PostgreSQL.
   	Instance Region	The region where the destination AnalyticDB for PostgreSQL instance resides.
   	Instance ID	The ID of the destination AnalyticDB for PostgreSQL instance.
   	Database Name	The name of the destination database.
   	Database Account	The account for the destination AnalyticDB for PostgreSQL instance. For required permissions, see Permissions required for database accounts.
   	Database Password	The password for the database account.

   

6. In the lower-right corner, click Set Whitelist and Next.

   Warning

   Adding DTS server CIDR blocks to your database whitelist or ECS security group rules creates security risks. Before proceeding, take preventive measures such as enforcing strong credentials, limiting exposed ports, authenticating API calls, auditing whitelist rules regularly, and preferring Express Connect, VPN Gateway, or Smart Access Gateway to connect your database to DTS.

7. Select the migration types, operation types, and objects to migrate.

   Setting	Description
   Migration types	To perform only schema and full data migration, select Schema Migration and Full Data Migration. To keep your applications running during migration, also select Incremental Data Migration. Note If Incremental Data Migration is not selected, avoid writing data to the source database during full data migration to maintain data consistency.
   Operation types	Select the types of operations to migrate during incremental data migration. All operation types are selected by default.
   Objects to migrate	Select objects from the Available section and click the icon to move them to the Selected section. You can select columns, tables, or schemas. Object names remain unchanged in the destination by default. To rename objects in the destination, use the object name mapping feature. See Object name mapping. Note Renaming an object may cause dependent objects to fail migration.
   Object name mapping	Use this feature to rename objects in the destination instance. See Object name mapping.
   Retry time range	If DTS cannot connect to the source or destination database, it retries for up to 12 hours by default. Adjust this range based on your requirements. DTS resumes the migration task if reconnected within the retry window; otherwise, the task fails. Note You are charged for the DTS instance during the retry window. Release the DTS instance promptly after the source and destination instances are released.
   Enclose object names in quotation marks	If selected and the following conditions apply, DTS encloses object names in single (') or double (") quotation marks during schema migration and incremental data migration: the source business environment is case-sensitive and the database name contains both uppercase and lowercase letters; a source table name does not start with a letter or contains characters other than letters, digits, underscores (_), number signs (#), or dollar signs ($); schema, table, or column names are keywords, reserved keywords, or invalid characters in the destination database.

   

8. Specify the primary key columns and distribution keys for the tables being migrated to AnalyticDB for PostgreSQL.

   For details on primary key columns and distribution keys, see Define constraints and Define table distribution. If DTS detects tables without primary keys, the option Set Primary Keys and Distribution Keys of All Tables Without Primary Keys to ROWID appears. Selecting it adds ROWID as the primary key and distribution key for those destination tables.

   

9. In the lower-right corner, click Precheck.

   DTS runs a precheck before starting the migration task. The task can only start after passing the precheck. If any item fails, click the icon to view the details, fix the issue based on the cause, and rerun the precheck. You can also ignore failed items and rerun the precheck if the failures are not critical.

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

11. In the Confirm Settings dialog box, set the Channel Specification parameter and select Data Transmission Service (Pay-As-You-Go) Service Terms.

12. Click Buy and Start to start the migration task.

Stop the migration and cut over

How you stop the migration depends on the migration types you selected.

Schema migration and full data migration only

Do not stop the task manually. Wait for the task to stop automatically. Stopping early may leave the destination database with incomplete data.

Schema migration, full data migration, and incremental data migration

Incremental migration tasks do not stop automatically. Stop the task manually when your data is fully in sync.

To cut over:

1. Wait until the progress bar shows Incremental Data Migration and the status The migration task is not delayed.

2. Stop writing data to the source database for a few minutes. The incremental migration latency may increase briefly.

3. Wait until the status returns to The migration task is not delayed.

4. Manually stop the migration task.

   

5. Redirect your application traffic to the destination AnalyticDB for PostgreSQL instance.

What's next

• Define table distribution — optimize query performance by configuring distribution keys on the destination instance

• Create a database account — manage access to the destination database

• Billing overview — understand DTS charges for incremental migration tasks