Oracle Migration Synchronization to Dameng

NineData data replication supports data synchronization between Oracle and Dameng data sources.

Prerequisites

The source and target data sources have been added to NineData. For instructions on how to add them, see Add Data Source.
The source data source is Oracle, with a version number of 23ai, 21c, 19c, 18c, 12c, or 11g. The supported Oracle versions (product lines) are as follows:
- Oracle Enterprise Edition
- Oracle Standard Edition
- Oracle Express Edition
- Oracle Personal Edition
The target data source is Dameng database.

You must have the following permissions for the source and target data sources:

Replication Type	Source Data Source	Target Data Source
Structure Replication	select connect select any dictionary select any table select_catalog_role	ALL Privileges ON database
Full Replication	select connect select any dictionary select any table select_catalog_role	ALL Privileges ON database
Incremental Replication	Please refer to this article Appendix 2: Oracle Incremental Replication Account Permission Description.	ALL Privileges ON database

If you need to perform incremental replication, ensure the following configurations are made in the source data source:

Oracle's log mode is set to ARCHIVELOG mode (default is NOARCHIVELOG).

Verify the current log mode: SELECT log_mode FROM v$database;
If the log mode is NOARCHIVELOG, perform the following steps:
1. Shut down the database: SHUTDOWN IMMEDIATE;
2. Start and mount the database: STARTUP MOUNT;
3. Execute the archive command: ALTER DATABASE ARCHIVELOG;
4. Start the database: ALTER DATABASE OPEN;

Supplemental Log, i.e., additional logs, have been enabled (not enabled by default).

Verify if additional logs are enabled: SELECT supplemental_log_data_all allc FROM v$database;
If it returns NO, execute the command to enable additional logs:
- ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
//Enable additional logs at the database level, affecting all tables and all columns, which will consume more resources.
- ALTER TABLE <database_name>.<table_name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
- ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
//Enable additional logs on specific tables, affecting all columns in the table.

Usage Limitations

Before performing data synchronization, assess the performance of the source and target data sources, and it is recommended to perform data synchronization during off-peak business hours. Otherwise, the full data initialization will occupy certain read and write resources of the source and target data sources, leading to increased database load.
Ensure that each table in the synchronization object has a primary key or unique constraint, and column names are unique; otherwise, the same data may be synchronized repeatedly.

Operation Steps

Log in to the NineData Console.
Click on Replication in the left navigation bar.
On the Replication page, click on the Create Replication in the top right corner.

On the Source & Target tab, configure according to the table below, and click on Next.

Parameter	Description
Name	Enter the name of the data synchronization task. For easy retrieval and management, use a meaningful name, up to 64 characters.
Source	The data source where the sync object is located.
Target	The data source that receives the sync object.
Type	Choose the content to be copied to the target data source. Schema: Only syncs the table structure of the source data source, without syncing data. Full: Syncs all objects and data from the source data source, i.e., full data replication. The switch on the right is for toggling periodic full replication. For more information, please refer to Periodic Full Replication. Incremental: After full sync, perform incremental sync based on the log of the source data source. The icon represents incremental operation type configuration (not supported by some replication links). You can deselect certain operation types as needed. Once deselected, these operations will be ignored during incremental synchronization.
Target Table Preparation	If target table already exists (selected when Schema is selected) Pre-Check Error and Stop Task: Stop the task when a table with the same name is detected during precheck. Skip and Continue Task: Send a prompt when a table with the same name is detected during precheck and continue the task. During structure replication, ignore this table with the same name. If you are also performing data replication, data will be appended to this table with the same name without overwriting existing data. Delete Objects and Rewrite: Send a prompt when a table with the same name is detected during precheck and continue the task. During structure replication, delete the table with the same name in the target database and replicate the table structure based on the source database. If you are also performing data replication, data will be written after the table structure replication is completed. Keep Schema and Clear Data, then Rewrite (optional when performing both structure and data replication): Send a prompt when a table with the same name is detected during precheck and continue the task. During structure replication, keep the table structure in the target database and, when data replication starts, clear the data in the table with the same name and then replicate from the original table. Target Table Exists Data (required when Schema is not selected) Pre-Check Error and Stop Task: Stop the task when data exists in the target table during precheck. Ignore the existing data and append : Ignore this part of the data when data is detected in the target table during precheck and append other data. Clear the existing data before write: Delete this part of the data when data is detected in the target table during precheck and rewrite it.

On the Objects tab, configure the following parameters, and then click on Next.

Parameter	Description
Replication Objects	Choose what to copy. You can choose All Objects to copy all contents of the source database, or choose Customized Object to select the contents to be copied in the Source Object list and click > to add them to the right Target Object list.

If you need to create multiple identical replication links, you can create a configuration file and import it when creating a new task. Click Import Config at the top right corner, then click Download Template to download the configuration file template to your local machine. After editing, click Upload to upload the configuration file for batch import.

Parameter	Description
`source_table_name`	Name of the source table containing the objects to be synchronized.
`destination_table_name`	Name of the target table receiving the synchronized objects.
`source_schema_name`	Name of the source schema containing the objects to be synchronized.
`destination_schema_name`	Name of the target schema receiving the synchronized objects.
`source_database_name`	Name of the source database containing the objects to be synchronized.
`target_database_name`	Name of the target database receiving the synchronized objects.
`column_list`	List of columns to be synchronized.
`extra_configuration`	Additional configuration information can be set here: `column_rules`: Used to define column mapping and value rules. Field descriptions: `column_name`: Original column name. `destination_column_name`: Target column name. `column_value`: Value to assign, which can be an SQL function or a constant. `filter_condition`: Used to specify row-level filtering conditions; only rows that meet the criteria will be copied.

tip

An example of the extra_configuration content is as follows:

{
  "extra_config": {
    "column_rules": [
      {
        "column_name": "created_time", // Original column name to map.
        "destination_column_name": "migrated_time", // Target column name mapped to "migrated_time".
        "column_value": "current_timestamp()" // Change the column value to the current timestamp.
      }
    ],
    "filter_condition": "id != 0" // Only rows where ID is not 0 will be synchronized.
  }
}

For a complete example of the configuration file, refer to the downloaded template.

On the Mapping tab, select different actions based on the selected replication type. If updates occur in the source and target data sources during the configuration mapping phase, you can click the Refresh Metadata button in the upper right corner of the page to refresh the information of the source and target data sources.
- Includes Schema: Configure the table name after the target table is synchronized to the target data source, and click Save and Pre-Check.
- Excludes Schema: The system automatically selects the same name database in the target data source by default. If it does not exist, you need to manually select the target database. The table names and column names in the target database need to be consistent with the sync objects. If they are not consistent, you can manually map the table names and column names.
You can also perform the following operations:
- Click on the on the right side of the page to customize the column names after they are synchronized to the target data source.
- On the page, click on , and configure the filtering conditions through comparison expressions. Only data that meets the filtering conditions will be synchronized to the target data source. For example, if you set the filtering condition to emp_no>=10005, then data in the emp_no column that is less than 10005 will not be synchronized to the target data source.
- Click on the icon next to to search for table names and replace them with target names.
- Enter the table name in the text box at the top right to quickly locate the target table.
- Click in the upper right corner to define general rules in bulk (such as converting table and column name case, adding prefixes or suffixes, replacements, etc.), enabling unified mapping configuration for a large number of tables and columns.
On the Pre-check tab, wait for the system to complete the precheck. After the precheck passes, click on Launch.
tip
- You can check Enable data consistency comparison. After the sync task is completed, automatically initiate data consistency comparison based on the source data source to ensure data consistency on both ends. Depending on your choice of Type, the timing of starting Enable data consistency comparison is as follows:
  - Schema: Initiated after structure replication is completed.
  - Schema + Full, Full: Initiated after full replication is completed.
  - Schema + Full + Incremental, Incremental: Initiated when the incremental data is first consistent with the source data source and Delay is 0 seconds. You can click on View Details to view the synchronization delay on the Details page.
- If the precheck fails, click on Details on the right side of the target check item to investigate the cause of the failure, manually fix it, and then click Check Again to re-execute the precheck until it passes.
- Items in Result with Warning can be repaired or ignored depending on the specific situation.
On the Launch page, when prompted with Launch Successfully, the sync task begins to run. At this point, you can perform the following operations:
- Click on View Details to view the execution status of each stage of the sync task.
- Click on Back to list to return to the Replication task list page.

View Synchronization Results

Log in to the NineData Console.
Click on Replication > Data Replication in the left navigation bar.

Click on the Task ID of the target synchronization task on the Replication page, and the page description is as follows.

oracle_result

No.	Function	Description
1	Synchronization Delay	The data synchronization delay between the source and target data sources, 0 seconds means there is no delay between the two ends, at this time you can choose to switch the business to the target data source, achieving smooth migration.
2	Configure Alerts	After configuring alerts, the system will notify you through your chosen method when the task fails. For more information, please refer to Introduction to Operations Monitoring.
3	More	Pause: Pause the task, only tasks in Running status are selectable. Terminate: End unfinished or listening (i.e., incremental synchronization) tasks, terminated tasks cannot be restarted, please operate with caution. If the synchronization object includes triggers, trigger replication options will pop up, please choose as needed. Delete: Delete the task, tasks cannot be recovered after deletion, please operate with caution.
4	Structure Replication (displayed when structure replication is included)	Display the progress and detailed information of structure replication. Click on Logs on the right side of the page: View the execution logs of structure replication. Click on on the right side of the page: View the latest information. Click on View DDL in the Operations column on the right side of the target object in the list: You can view the SQL replay.
5	Full Replication (displayed when full replication is included)	Display the progress and detailed information of full replication. Click on Monitoring on the right side of the page: View various monitoring metrics during the full replication process. During the full replication process, you can also click on Throttle Settings on the right side of the monitoring metric page to limit the rate of writing to the target data source per second. Click on Logs on the right side of the page: View the execution logs of full replication. Click on on the right side of the page: View the latest information.
6	Incremental Replication (displayed when incremental replication is included)	Display various monitoring metrics of incremental replication. Click on View Threads on the right side of the page: View the operations currently being executed by the current replication task, including: Thread ID: The replication task is executed by multiple threads, displaying the current thread number. Execute SQL: Details of the SQL statement currently being executed by the current thread. Response Time: The response time of the current thread, if this value increases, it means the current thread may be stuck due to some reason. Event Time: The timestamp when the current thread started. Status: The status of the current thread. Click on Throttle Settings on the right side of the page: Limit the rate of writing to the target data source per second. The unit is rows/second. Click on Logs on the right side of the page: View the execution logs of incremental replication. Click on on the right side of the page: View the latest information.
7	Modify Objects	Display the modification records of synchronization objects. Click on Modify Synchronization Objects on the right side of the page to configure synchronization objects. Click on on the right side of the page: View the latest information.
8	Data Comparison	Display the results of data comparison between the source and target data sources. Includes Structure Comparison and Data Comparison. If you have not enabled data comparison, please click on Enable Data Comparison on the page. Click on Re-compare on the right side of the page: Re-initiate comparison for the current source and target data. Click on the date to view the list of all comparison results, click on the target list item to switch to the details of that comparison result. Click on Logs on the right side of the page: View the execution logs of consistency comparison. Click on Monitoring (displayed only for data comparison) on the right side of the page: View the trend chart of comparison RPS (records compared per second). Click on Details to view records from earlier times. Click on in the Operations column on the right side of the comparison list: View the comparison details of the source and target ends. Click on (displayed when inconsistent) in the Operations column on the right side of the comparison list: Generate change SQL, you can directly copy this SQL to the target data source for execution, modifying the inconsistent content.
9	Expand	Display detailed information of the current replication task, including Replication Type, Replication Objects, Start Time, etc.

Appendix: Pre-Check Item List

Check Items	Check Contents
Verify if there are no primary key tables in the source database	Check if the object to be migrated does not have a primary key or unique key
Source datasource connection check	Check the status of the gateway of the source datasource, database connectable, and verify the username and password
Target datasource connection check	Check the status of the gateway of the target datasource, database connectable, and verify the username and password
Target database privilege check	Check whether the account privileges of the target database meet the requirements
Source database privilege check	Check whether the account privileges of the source database meet the requirements
Target database data existence check	Check whether the object to be replicated not empty in the target database
Objects with the same name in the target database existence check	Check whether the object to be replicated already exists in the target database
Cyclic replication check	Check for replication loops
Source datasource log archive mode check	Source datasource log archive mode must be ARCHIVELOG
Source datasource supplemental log check	Source datasource supplemental log must be enabled and set to be ALL

Appendix 2: Oracle Incremental Replication Account Permission Description

Below Oracle 12c
Oracle 12c and above

Designate a user with DBA privileges for incremental data replication tasks with Oracle as the source.

-- Create NineData sync account
CREATE USER test_dba IDENTIFIED BY "ninedatasync";
-- Grant DBA privileges to the account
GRANT dba to test_dba;

Designate a user with DBA privileges for incremental data replication tasks with Oracle as the source.

-- Create NineData sync account in the global environment
CREATE USER c##test_dba IDENTIFIED BY "ninedatasync";
-- Grant DBA and connect privileges to the account, specifying the scope of the privileges
GRANT dba, connect to c##nine_data container=all;

Introduction to Data Replication

Oracle Migration Synchronization to Dameng

Prerequisites​

Usage Limitations​

Operation Steps​

View Synchronization Results​

Appendix: Pre-Check Item List​

Appendix 2: Oracle Incremental Replication Account Permission Description​

Related Documents​