Data Transmission Service:create an oracle change tracking task

Prerequisites

Before you begin, make sure that:

• The Oracle database version is 9i, 10g, or 11g. Real Application Cluster (RAC) and pluggable database (PDB) instances are not supported.

• The database is running in ARCHIVELOG mode. To verify, run:

  SELECT log_mode FROM v$database;

  If the result is NOARCHIVELOG, enable ARCHIVELOG mode before continuing.

• Supplemental logging is enabled, with SUPPLEMENTAL_LOG_DATA_PK and SUPPLEMENTAL_LOG_DATA_UI set to Yes. To verify, run:

  SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui FROM v$database;

  If supplemental_log_data_min returns NO, enable supplemental logging before continuing. See Set up the Oracle database account.

• Archived log files are accessible with a retention period of more than 24 hours. If redo logs and archived logs are retained for less than 24 hours, DTS may fail to retrieve them, causing the task to fail or resulting in data loss. Ensure the retention period meets this requirement; otherwise, the service reliability and performance stated in the Service Level Agreement (SLA) of DTS may not be guaranteed.

• The server running the Oracle database has sufficient egress bandwidth. Insufficient bandwidth reduces change tracking speed.

• If the ApsaraDB RDS for MySQL instance is of the classic network type, an internal endpoint is configured for the ApsaraDB RDS for MySQL instance.

Limitations

Source Database

• A single change tracking task supports up to 500 tables. To track more than 500 tables, configure multiple tasks in batches, or select the entire database as the tracking object instead of individual tables.

• Source tables must have PRIMARY KEY or UNIQUE constraints with all fields unique. Without these constraints, some tracked changes may be duplicated.

• Both redo logging and archive logging must be enabled.

Oracle RAC

• If the source is an Oracle RAC database connected over Express Connect, specify a virtual IP address (VIP) when configuring the task—not a Single Client Access Name (SCAN) IP address.

• If the source is an Oracle RAC database hosted on Elastic Compute Service (ECS), or connected over Express Connect, VPN Gateway, Smart Access Gateway, Database Gateway, or Cloud Enterprise Network (CEN), use a single VIP rather than a SCAN IP. After you specify a VIP, node failover is not supported for the Oracle RAC database.

Data types

• For FLOAT and DOUBLE columns, DTS uses ROUND(COLUMN, PRECISION) to retrieve values. If no precision is specified, DTS defaults to 38 digits for FLOAT and 308 digits for DOUBLE. Verify that these defaults meet your requirements before starting the task.

Set up the Oracle database account

Create a database account and grant it the required permissions. Choose one of the following options.

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;

-- Enable minimal supplemental logging
alter database add supplemental log data;

-- Enable primary key and unique key supplemental logging
alter database add supplemental log data (primary key, unique index) columns;

-- Enable minimal supplemental logging (required for both methods below)
alter database add supplemental log data;

-- Method A: Enable primary key supplemental logging for a specific table
alter table table_name add supplemental log data (primary key) columns;

-- Method B: Enable supplemental logging for all columns of a specific table
alter table tb_name add supplemental log data (all) columns;

-- Create the account (example account name: rdsdt_dtsacct)
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;

Create a change tracking task

1. Go to the Change Tracking 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) > Change Tracking.

   In Simple Mode, move the pointer over the icon in the upper-left corner, then choose All functions > DTS > Change Tracking. Alternatively, open the new DTS console directly.

2. To the right of Change Tracking Tasks, select the region for the task.

   In the new DTS console, select the region from the drop-down list to the right of Workbench.

3. Click Create Task. On the page that appears, configure the source database and consumer network type.

   Warning

   Read the Limits displayed at the top of the page before proceeding. Skipping this step may cause the task to fail or prevent tracked data from being consumed.

   Source database settings

   Parameter	Description
   Task Name	A descriptive name to identify the task. Task names do not need to be unique.
   Select an existing database connection	Choose whether to reuse an existing connection. If selected, DTS applies the saved parameter settings automatically.
   Database Type	Select Oracle.
   Access Method	The type of source instance. Select Self-managed Database on ECS for this example. For other access methods, see Set up the Oracle database account.
   Instance Region	The region where the Oracle database resides.
   ECS Instance ID	The ID of the ECS instance hosting the Oracle database.
   Port Number	The service port of the Oracle database. Default: 1521.
   Oracle Type	Non-RAC Instance: specify a system ID (SID). RAC or PDB Instance: specify a Service Name. RAC and PDB instances are not supported—select Non-RAC Instance for this example.
   Database Account	The account created in Set up the Oracle database account.
   Database Password	The password for the database account.

   Consumer Network Type

   Parameter	Description
   Network Type	Fixed to VPC. Select a VPC and a vSwitch. If the change tracking client is deployed in a VPC, select the same VPC and vSwitch to minimize network latency. These settings cannot be changed after the task is configured. For more information, see VPCs.

4. Click Test Connectivity and Proceed. DTS automatically adds its server CIDR blocks to the security settings of the source database:

   • ECS-hosted database: CIDR blocks are added to the ECS instance's security group rules.

   • Alibaba Cloud database instance (such as ApsaraDB RDS for MySQL or ApsaraDB for MongoDB): CIDR blocks are added to the instance whitelist.

   • Self-managed database in a data center or on a third-party cloud: Add DTS server CIDR blocks manually to the database security settings. For the full CIDR block list, see Add the CIDR blocks of DTS servers.

   Warning

   Adding DTS server CIDR blocks to a whitelist or security group creates potential security risks. Before proceeding, take preventive measures, including but not limited to: enforcing strong credentials, limiting exposed ports, authenticating API calls, regularly reviewing whitelist and security group rules, and removing unauthorized CIDR blocks. For a more secure connection, use Express Connect, VPN Gateway, or Smart Access Gateway.

5. Configure the objects for change tracking and advanced settings.

   Basic settings

   Parameter	Description
   Data Change Types	Data Update: tracks INSERT, DELETE, and UPDATE operations on selected objects. Schema Update: tracks create, delete, and modify operations on all object schemas of the source instance. Use the change tracking client to filter the data to consume.
   Source Objects	Select objects from the Source Objects section and click the icon to move them to the Selected Objects section. Select a database to track all its objects including newly added ones. Select specific tables to track only those tables—if you need to add more tables later, see Modify the objects for change tracking.

   Advanced settings

   Parameter	Description
   Monitoring and Alerting	No: disables alerting. Yes: enables alerting. Specify an alert threshold and alert contacts to receive notifications when the task fails or latency exceeds the threshold.
   Retry Time for Failed Connections	The time window during which DTS retries failed connections. Valid values: 10–1440 minutes. Default: 120 minutes. Set this to more than 30 minutes. If DTS reconnects within the window, the task resumes; otherwise, the task fails. If an instance is used by multiple tasks, the shortest retry window takes precedence. DTS charges apply during retries—set this value based on your business needs and release the instance when it is no longer required.
   Configure ETL	Yes: enables ETL. Enter your policy in the code editor. No: disables ETL. For more information, see What is ETL?.

6. Click Next: Save Task Settings and Precheck. DTS runs a precheck automatically before the task can start.

   • If a check item fails, click View Details next to the item, resolve the issue based on the error message, then click Precheck Again.

   • If an alert is generated for an item that can be ignored, click Confirm Alert Details, then click Ignore in the dialog box. Click OK to confirm, then click Precheck Again. Ignoring alerts may result in data inconsistency.

   To preview the OpenAPI parameters for this configuration, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.

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

8. On the Purchase page, select a billing method.

   Parameter	Description
   Billing method	Subscription: pay upfront for a fixed duration. More cost-effective for long-term use, with lower prices for longer durations. Pay-as-you-go: billed hourly. Suitable for short-term use—release the instance when no longer needed to stop charges.
   Resource Group Settings	The resource group for the instance. Default: default resource group. See What is Resource Management?.
   Subscription Duration	Available only for the Subscription billing method. Options: 1–9 months, 1 year, 2 years, 3 years, or 5 years.

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

10. Click Buy and Start. Monitor the task progress in the task list.

What's next

When the task is running, create consumer groups to consume the tracked data.

1. Create consumer groups

2. Choose a consumption method:

   • Use the SDK demo to consume tracked data

   • Use flink-dts-connector to consume tracked data

   • Use a Kafka client to consume tracked data