Help Center/ DataArts Studio/ User Guide/ DataArts Migration (Real-Time Jobs)/ Tutorials/ Configuring a Job for Synchronizing Data from Oracle to GaussDB(DWS)

Updated on 2025-08-05 GMT+08:00

View PDF

Configuring a Job for Synchronizing Data from Oracle to GaussDB(DWS)

Supported Source and Destination Database Versions

**Table 1** Supported database versions
Source Database	Destination Database
Oracle database (versions 10, 11, 12, and 19)	GaussDB(DWS) cluster of version 8.1.3, 8.2.0, or a later version, except GaussDB(DWS) 3.0

Database Account Permissions

Before you use DataArts Migration for data synchronization, ensure that the source and destination database accounts meet the requirements in the following table. The required account permissions vary depending on the synchronization task type.

**Table 2** Database account permissions
Type	Required Permissions
Source database account	The account must have the permissions to archive logs, query tables, and parse logs of the Oracle database. For details about how to grant the permissions, see .
Destination database account	The destination database account must have the following permissions for each table in the database: INSERT, SELECT, UPDATE, DELETE, CONNECT, and CREATE.

You are advised to create independent database accounts for DataArts Migration task connections to prevent task failures caused by password modification.
After changing the account passwords for the source or destination databases, modify the connection information in Management Center as soon as possible to prevent automatic retries after a task failure. Automatic retries will lock the database accounts.

Supported Synchronization Objects

The following table lists the objects that can be synchronized using different links in DataArts Migration.

**Table 3** Synchronization objects
Type	Note
Synchronization objects	The following DML operations can be synchronized: INSERT, UPDATE, and DELETE. The DDL operation of adding columns can be synchronized. Only primary key tables can be synchronized. Views, foreign keys, stored procedures, triggers, functions, events, virtual columns, unique constraints, and unique indexes cannot be synchronized. Table structures, common indexes, constraints (primary key, null, and non-null), and comments cannot be synchronized during automatic table creation.

Important Notes

In addition to the constraints on supported data sources and versions, connection account permissions, and synchronization objects, you also need to pay attention to the notes in the following table.

**Table 4** Important notes
Type	Restriction
Database	The names of the source databases, tables, and fields cannot contain periods (.), hyphens (-), or non-ASCII characters. You are advised to use common characters to avoid a failure. The name of an object in the destination database must contain 1 to 63 characters, start with a letter or underscore (_), and can contain letters, digits, underscores (_), and dollar signs ($).
Usage	General: During real-time synchronization, the IP addresses, ports, accounts, and passwords cannot be changed. It is recommended that Oracle archive logs be retained for more than three days. Otherwise, the task may fail because logs cannot be obtained. In some special cases, data may be inconsistent or lost. resetlogs operations cannot be performed on the source Oracle database. Otherwise, data cannot be synchronized, and the job cannot be recovered. The username (schema name) of the source Oracle database cannot be changed, neither by modifying the USER$ dictionary table in versions earlier than 11.2.0.2 nor by running the ALTER USER username RENAME TO new_username command in 11.2.0.2 and later versions. If the source is an Oracle database, CLOB, NCLOB, and BLOB data cannot be migrated. Oracle RAC clusters cannot be the source. If the source is an Oracle database, it can connect to the standby Oracle database of a single instance but cannot connect to a standby database in an RAC cluster. Only archive logs can be read from the standby database, and standby logs cannot be read from the standby database. When connecting to the standby database, you are advised to configure scheduled archiving to reduce the data synchronization latency. It is recommended that the network bandwidth be greater than 100 Mbit/s. Full synchronization phase: During task startup and full data synchronization, do not perform DDL operations on the source database. Otherwise, the task may fail. Incremental synchronization phase: DML operations INSERT, UPDATE, and DELETE can be synchronized. The DDL operation of adding columns can be synchronized. Mixed partitioned tables are not supported. When data in the external partition of a mixed partitioned table changes, no DML log is generated. As a result, the change information cannot be obtained during incremental data synchronization, which may cause data inconsistency. The table name and column name can contain a maximum of 30 characters. Oracle LogMiner is used to read Oracle logs. The table name and column name can contain a maximum of 30 characters. For details, see Using LogMiner to Analyze Redo Log Files. During incremental startup, select a time point. Ensure that the Oracle database is in the same time zone as the host where the database is located to ensure the accuracy of the incremental time point. Distributed transactions (XA transactions) and PARALLEL DML on Oracle cannot be incrementally synchronized. During incremental synchronization, Oracle extended characters are not supported. The standard character set cannot parse Oracle customized extended characters. During incremental synchronization, triggers cannot be synchronized or migrated. You must disable the triggers on the destination Oracle database. Data with foreign key constraints cannot be incrementally synchronized or migrated. Data imported to the source database using Oracle Data Pump cannot be incrementally synchronized or migrated. Troubleshooting: If any problem occurs during task creation, startup, full synchronization, incremental synchronization, or completion, rectify the fault by referring to .
Other	Tables in the destination database can contain more columns than those in the source database. The following failures must be avoided: Assume that extra columns in the destination database cannot be null and have no default values. If newly inserted data records are synchronized from the source database to the destination database, the extra columns will become null, which does not meet the requirements of the destination database and will cause the task to fail. Assume that extra columns in the destination database must be fixed at a default value and have a unique constraint. If newly inserted data records are synchronized from the source database to the destination database, the extra columns will contain default values. That does not meet the requirements of the destination database. During automatic table creation, the length of the char, varchar, nvarchar, enum, and set characters in the source database is automatically increased by byte in the destination GaussDB(DWS) database. For a full and incremental job or an incremental job that migrates data from an Oracle database, if you want to synchronize tables in the PDB database, you need to enter the username and password of the CDB database in the Oracle connection. This is because Oracle logs are stored in the CDB database and Oracle LogMiner can run only in the CDB database. To delete a table using DDL, run the drop table test_table_name purage command. Deleting a table in the Oracle database is a high-risk operation by default. When the drop table test_table_name command is executed, the Oracle database converts the command to rename table test_table_name as xxxxx. That is, the table is renamed as a table to be processed in the Oracle temporary tablespace. The original table is not deleted, and DataArts Migration ignores the syntax by default. The data deletion statement drop table test_table_name purage of the Oracle database completely deletes the table. DataArts Migration automatically identifies and delivers the deleted table to the downstream. During incremental synchronization from the PDB database, all PDBs must be enabled due to restrictions of Oracle LogMiner. Tables with no primary keys are not supported. Data control language (DCL) operations are not supported. Consecutive RENAME TABLE operations cannot be synchronized or migrated. Otherwise, the task may fail. Global temporary tables cannot be synchronized or migrated (the task runs properly). Tables that contain default value functions cannot be synchronized or migrated. Otherwise, data will be inconsistent. Tables with default values that contain expressions cannot be synchronized or migrated. Foreign tables cannot be synchronized or migrated. Compute columns and encrypted columns cannot be synchronized or migrated. Virtual private databases (VPDs) cannot be synchronized or migrated. Jobs created by dbms_scheduler and dbms_job cannot be synchronized or migrated. Schema name changes cannot be synchronized or migrated. Nested tables cannot be synchronized or migrated. Otherwise, an error will occur. Materialized views cannot be synchronized or migrated. DDL operations with attribute names that contain keywords or special characters cannot be synchronized or migrated. ROWID change operations (such as split partition, table move, table shrink, and move partition key) are not supported. Otherwise, data inconsistency or task failures may occur. The Secure Sockets Layer (SSL) encrypted transmission mode is not supported. The Oracle Label Security mode is not supported.

Procedure

This section uses real-time synchronization from Oracle to GaussDB(DWS) as an example to describe how to configure a real-time data migration job. Before that, ensure that you have read the instructions described in Check Before Use and completed all the preparations.

Create a real-time migration job by following the instructions in Creating a Real-Time Migration Job and go to the job configuration page.
Select the data connection type. Select Oracle for Source and DWS for Destination.

Figure 1 Selecting the data connection type
Select a job type. The default migration type is Real-time. The migration scenario is Entire DB.

Figure 2 Setting the migration job type

For details about synchronization scenarios, see Synchronization Scenarios.
Configure network resources. Select the created Oracle and GaussDB(DWS) data connections and the migration resource group for which the network connection has been configured.

Figure 3 Selecting data connections and a migration resource group

If no data connection is available, click Create to go to the Manage Data Connections page of the Management Center console and click Create Data Connection to create a connection. For details, see Configuring DataArts Studio Data Connection Parameters.

Configure source parameters.

Select the databases and tables to be synchronized based on the following table.

**Table 5** Selecting the Kafka topics to be synchronized
Synchronization Scenario	Configuration Method
Entire DB	Select the Oracle databases and tables to be migrated. Figure 4 Selecting databases and tables Both databases and tables can be customized. You can select one database and one table, or multiple databases and tables.

Configure destination parameters.

Set Database and Table Matching Policy.

For details about the matching policy between source and destination databases and tables in each synchronization scenario, see the following table.

**Table 6** Database and table matching policy
Synchronization Scenario	Configuration Method
Entire DB	Schema Matching Policy Same name as the source database: Data will be synchronized to the GaussDB(DWS) schema with the same name as the source Oracle database. Custom: Data will be synchronized to the GaussDB(DWS) schema you specify. Table Matching Policy Same name as the source table: Data will be synchronized to the GaussDB(DWS) table with the same name as the source Oracle table. Custom: Data will be synchronized to the GaussDB(DWS) table you specify. Figure 5 Database and table matching policy in the entire database migration scenario NOTE: When you customize a matching policy, you can use built-in variables #{source_db_name} and #{source_table_name} to identify the source database name and table name. The table matching policy must contain #{source_table_name}.

Configure GaussDB(DWS) parameters.

For details, see the following table.

Figure 6 GaussDB(DWS) parameters
Click to enlarge

**Table 7** GaussDB(DWS) parameters
Parameter	Default Value	Unit	Description
Write Mode	UPSERT	N/A	UPSERT MODE: batch update COPY MODE: DWS-dedicated high-performance batch import
Maximum Data Volume for Batch Write	50000	Count	Number of data records written to GaussDB(DWS) in a batch. You can adjust the value based on the table data size and job memory usage.
Scheduled Batch Write Interval	3	Second	Interval at which data is written to GaussDB(DWS)
Advanced Settings	N/A	N/A	Some advanced functions can be configured using parameters. For details, see GaussDB(DWS) advanced parameters.

**Table 8** GaussDB(DWS) advanced parameters
Parameter	Type	Default Value	Unit	Description
sink.buffer-flush.max-size	int	512	MB	Maximum number of bytes in each batch of data written to GaussDB(DWS). You can adjust the value based on the memory and data size configured for the job.
sink.keyby.enable	boolean	true	N/A	Whether to enable data distribution. If this function is enabled in multi-concurrency scenarios, data can be distributed to different processes based on specific rules and written to the destination, which improves the write performance.
sink.keyby.mode	string	table	N/A	Data distribution mode. The following modes are available: pk: Data is distributed by primary key value. table: Data is distributed by table name. NOTE: In multi-concurrency scenarios, if DDL is enabled, data can be distributed only by table name. Otherwise, data may be inconsistent. If there is no DDL, you can select pk, which improves the write performance in multi-concurrency scenarios.
sink.field.name.case-sensitive	boolean	true	N/A	Whether to enable case sensitivity for data synchronization. If this function is enabled, the database names, table names, and field names are case sensitive during data synchronization.
sink.verify.column-number	boolean	false	N/A	Whether to verify the number of data columns. By default, the link synchronizes data in the same-name mapping mode. The system does not check whether all columns are synchronized. If this function is enabled and the number of columns at the source is different from that at the destination, the system determines that data is inconsistent. As a result, the job is abnormal.
sink.server.timezone	string	Local time zone	N/A	Session time zone specified for connecting to the destination database. The standard time zone format is supported, for example, UTC+08:00.
logical.delete.enabled	boolean	false	N/A	Whether to enable logical deletion
logical.delete.column	string	logical_is_deleted	N/A	Name of the logical deletion column. The default value is logical_is_deleted. You can customize the value.

Refresh and check the mapping between the source and destination tables. In addition, you can modify table attributes, add additional fields, and use the automatic table creation capability to create tables in the destination GaussDB(DWS) database.

Figure 7 Mapping between source and destination tables
Click to enlarge

Edit additional fields: Click Additional Field in the Operation column to add custom fields to the destination GaussDB(DWS) table. For a new table, you can add additional fields to the existing fields in the source table. You can customize the field name, select the field type, and enter the field value.

Field Name: name of the new field in the destination GaussDB(DWS) table
Field Type: type of the new field in the destination GaussDB(DWS) table

Field Value: Value source of the new field in the destination GaussDB(DWS) table

**Table 9** Additional field value obtaining mode
Type	Example
Constant	Digits, letters, and special characters are supported. Color emoticons may cause a job submission failure.

Automatic table creation: Click Auto Table Creation to automatically create tables in the destination database based on the configured mapping policy. After the tables are created, Existing table is displayed for them.
Figure 8 Automatic table creation
- DataArts Migration supports only automatic table creation. You need to manually create databases and schemas at the destination before using this function.
- For details about the field type mapping for automatic table creation, see Field Type Mapping.

Configure DDL message processing rules.

Real-time migration jobs can synchronize data manipulation language (DML) operations, such as adding, deleting, and modifying data, as well as some table structure changes using the data definition language (DDL). You can set the processing policy for a DDL operation to Normal processing, Ignore, or Error.
- Normal processing: When a DDL operation on the source database or table is detected, the operation is automatically synchronized to the destination.
- Ignore: When a DDL operation on the source database or table is detected, the operation is ignored and not synchronized to the destination.
- Error: When a DDL operation on the source database or table is detected, the migration job throws an exception.
  Figure 9 DDL configuration

Configure task parameters.

**Table 10** Task parameters
Parameter	Description	Default Value
Execution Memory	Memory allocated for job execution, which automatically changes with the number of CPU cores	8GB
CPU Cores	Value range: 2 to 32 For each CPU core added, 4 GB execution memory and one concurrency are automatically added.	2
Maximum Concurrent Requests	Maximum number of jobs that can be concurrently executed. This parameter does not need to be configured and automatically changes with the number of CPU cores.	1
Custom attributes	You can add custom attributes to modify some job parameters and enable some advanced functions. For details, see Job Performance Optimization.	-

Submit and run the job.

After configuring the job, click Submit in the upper left corner to submit the job.

Figure 10 Submitting the job
Click to enlarge

After submitting the job, click Start on the job development page. In the displayed dialog box, set required parameters and click OK.

Figure 11 Starting the job
Click to enlarge

**Table 11** Parameters for starting the job
Parameter	Description
Offset Parameter	Incremental synchronization: Incremental data synchronization starts from a specified time point. Full and incremental synchronization: All data is synchronized first, and then incremental data is synchronized in real time.
Time	This parameter must be set for incremental synchronization, and it specifies the start time of incremental synchronization. NOTE: If you set a time that is earlier than the earliest binlog time, the latest log time is used.

Monitor the job.

On the job development page, click Monitor to go to the Job Monitoring page. You can view the status and log of the job, and configure alarm rules for the job. For details, see Real-Time Migration Job O&M.

Figure 12 Monitoring the job