Oracle Replication to SingleStore

NineData data replication supports data synchronization from Oracle to SingleStore data sources.

Background Information

SingleStore is a powerful and scalable relational database management system that supports high-performance operational analytics, transaction processing, and traditional analytical applications. Compared to Oracle, SingleStore demonstrates higher performance in handling big data and real-time analytics. Replicating data from Oracle to SingleStore allows offloading data analysis tasks to SingleStore, enabling Oracle to focus on the workload of transactional operations, distributing server pressure, and maximizing the value of business data.

Prerequisites

• Both source and target data sources have been added to NineData. For instructions on how to add data sources, please refer to Adding Data Sources.

• The source data source is Oracle, with a version of 23c, 21c, 19c, 18c, 12c, or 11g.

• The target data source is SingleStore, with a version of 8.1, 8.0, 7.8, 7.6, or 7.5.

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

  Replication Type	Source Data Source	Target Data Source
  Full Replication	Select any table Execute any procedure	ALL Privileges ON database
  Incremental Replication	DBA	ALL Privileges ON database

• For incremental replication, ensure the following configurations are done in the source data source:

  • Oracle's log mode is set to ARCHIVELOG mode (by default, it is NOARCHIVELOG).

    sqlCopy code
    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 command to enable archiving: ALTER DATABASE ARCHIVELOG;
    4. Start the database: ALTER DATABASE OPEN;

  • Supplemental Log, i.e., additional logging, is enabled (not enabled by default).

    sqlCopy code
    Verify if supplemental log is enabled: SELECT supplemental_log_data_all allc FROM v$database;
    If it returns NO, execute the command to enable supplemental log:
    - ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
    // Enables supplemental log at the database level, affecting all tables and columns, consuming more resources.
    - ALTER TABLE <database_name>.<table_name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
    // Enables supplemental log on specific table, affecting all columns in that table.

Usage Limitations

• Assess the performance of both source and target data sources before executing data synchronization, and it is advisable to perform data synchronization during business off-peak hours. Otherwise, full data initialization may consume significant read and write resources on both the source and target data sources, leading to an increase in database load.
• Ensure that each table in the synchronized objects has a primary key or unique constraint, and column names are unique to avoid potential duplicate synchronization of the same data.

Operation Steps

1. Log in to the NineData Console.

2. Click on > in the left navigation bar.

3. On the page, click on .

4. On the tab, configure according to the table below and click .

   Parameter	Description
   	Enter a name for the data synchronization task. For ease of later retrieval and management, use a meaningful name with a maximum of 64 characters.
   	The data source where the synchronized objects reside.
   	The data source that receives the synchronized objects.
   	Choose the case conversion rule for object names after migrating from the source to the target. : Convert all object names to lowercase letters. : Keep the case consistent with the source object names. : Convert all object names to uppercase letters.
   	Choose what to replicate to the target data source. : Only synchronize the schema of tables from the source data source without synchronizing data. : Synchronize all objects and data from the source data source, i.e., full data replication. : After full synchronization, perform incremental synchronization based on the log of the source data source.
   (Selected when is selected)	: Stop the task if a table with the same name is detected during the pre-check stage. : Display a prompt if a table with the same name is detected during the pre-check stage and continue the task. Ignore the same-name table during structure replication. If data replication is also performed, the data will be appended to the same-name table without overwriting existing data. : Display a prompt if a table with the same name is detected during the pre-check stage and continue the task. During structure replication, delete the same-name table in the target database and re-replicate the table structure based on the source database. If data replication is also performed, the data will be written after the table structure replication is completed. (Optional when both structure and data replication are performed): Display a prompt if a table with the same name is detected during the pre-check stage and continue the task. During structure replication, keep the table structure in the target database, clear the data in the same-name table when data replication begins, and then replicate from the original table.
   (Required when is not selected)	: Stop the task if data exists in the target table during the pre-check stage. : Ignore the data in the target table if it exists during the pre-check stage, and append other data. : Delete the data in the target table if it exists during the pre-check stage and rewrite it.

5. On the tab, configure the following parameters and click .

   Parameter	Description
   	Choose the content to replicate. You can choose to replicate all content from the source database, or choose . In the list, select the content to be replicated, and click > to add it to the right list.

6. On the tab, perform different actions based on the selected replication type.

   • Including : Configure the table name, Shard key, Primary key, and Sort key after the target table is synchronized to the target data source. Click . By default, the key definitions used after copying to SingleStore according to the different types of source tables are as follows. You can also customize the selection of different columns as the Primary key, Shard key, and Sort key based on the actual situation.

     Oracle Table Type	Primary key	Shard key	Sort key
     With primary key and no unique key	Primary key column of the source table	Primary key column of the source table	Primary key column of the source table
     With primary key and unique key	Primary key column of the source table	Primary key column of the source table	Primary key column of the source table
     Without primary key and with unique key	Unique key column of the source table	Unique key column of the source table	Unique key column of the source table
     Without primary key and without unique key	Empty	Empty	Empty

     tip

     You can click on the right side of the page to customize the names of columns after synchronization to the target data source. In addition, you can set to synchronize only the data that meets the filtering conditions to the target data source.

   • Excluding : The system defaults to selecting the same-name database in the target data source. 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 synchronized objects. If they are inconsistent, you can manually map the table names and column names.

7. On the tab, wait for the system to complete the pre-check. After the pre-check passes, click .

   tip

   • You can check . After the synchronization task is completed, automatically start data consistency comparison based on the source data source to ensure data consistency on both ends. According to the you choose, the timing of starting is as follows:
     • : Start after structure replication is completed.
     • + , : Start after full replication is completed.
     • + + , : Start when incremental data is first consistent with the source data source and is 0 seconds. You can click to view the synchronization delay on the page.
   • If the pre-check fails, you need to click on the right side of the target check item, troubleshoot the failure reasons, manually fix them, and click to re-run the pre-check until it passes.
   • For check items with as , you can fix or ignore them based on the specific situation.

8. On the page, when prompted with , the synchronization task starts running. At this point, you can:

   • Click on to view the execution status of each stage of the synchronization task.
   • Click on to return to the task list page.

View Synchronization Results

1. Log in to the NineData Console.

2. Click on > in the left navigation bar.

3. On the page, click on the of the target synchronization task, and the page is explained as follows.

   

   Number	Function	Description
   1	Synchronization Delay	The data synchronization delay between the source data source and the target data source. 0 seconds means there is no delay between the two ends. At this point, you can choose to switch the business to the target data source for smooth migration.
   2	Configure Alerts	After configuring alerts, the system will notify you in the way you choose when the task fails. For more information, see Introduction to Operational Monitoring.
   3	More	Pause: Pause the task. Only tasks with the status Running can be selected. Terminate: End tasks that are incomplete or in progress (i.e., in incremental synchronization). After terminating the task, it cannot be restarted, so operate with caution. If the synchronized objects include triggers, the trigger replication options will pop up, so choose as needed. Delete: Delete the task. Once the task is deleted, it cannot be recovered, so operate with caution.
   4	Structure Replication (Displayed in scenarios including structure replication)	Show the progress and details of structure replication. Click on the 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 Operation column on the right of the target object in the list: You can view SQL playback.
   5	Full Replication (Displayed in scenarios including full replication)	Show the progress and details of full replication. Click on Monitoring on the right side of the page: View various monitoring indicators during full replication. During full replication, you can also click Rate Limit Settings on the right side of the monitoring indicators 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 in scenarios including incremental replication)	Display various monitoring indicators of incremental replication. Click on on the right side of the page: View the operations currently being performed by the current replication task, including: : The replication task is performed in multiple threads, showing the current thread number in progress. : Details of the SQL statement currently being executed by the current thread. : The response time of the current thread. If this value increases, it may indicate that the current thread is stuck for some reason. : The timestamp when the current thread is opened. : The status of the current thread. Click on Rate Limit Settings on the right side: 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 Object	Show the modification records of synchronized objects. Click on Modify Synchronized Objects on the right side of the page to configure synchronized objects. Click on on the right side of the page: View the latest information.
   8	Data Comparison	Show the results of data comparison between the source data source and the target data source. If you have not enabled data comparison, click Enable Data Comparison in the page. Click on Recompare on the right side of the page: Reinitiate the comparison of data between the current source and target. Click on the date to view the list of all comparison results, and click on the target list item to switch to the details of this comparison result. Click on Logs on the right side of the page: View the execution logs of consistency comparison. Click on Monitoring on the right side of the page: View the trend chart of comparison RPS (records per second). Click Details to view records from earlier. Click on in the Operation column on the right of the comparison list (displayed in case of inconsistency): View the comparison details between the source and target. Click on in the Operation column on the right of the comparison list (displayed in case of inconsistency): Generate change SQL, which you can copy directly to the target data source to execute and modify inconsistent content.
   9	Expand	Show detailed information of the current replication task, including Replication Type, Replication Objects, Start Time, etc.

Appendix 1: Data Type Mapping Table

During the data replication process, Oracle data types are mapped to corresponding SingleStore data types.

Type	Oracle Data Type	SingleStore Data Type
Build-in data type	CHAR [(size [BYTE | CHAR])]	VARCHAR[(size)]
	NCHAR[(size)]	VARCHAR[(size)]
	VARCHAR2(size [BYTE | CHAR])	VARCHAR[(size)]：Source size <= 21,844 TEXT：Source size > 21844
	NVARCHAR2(size)	VARCHAR[(size)]：Source size <= 21,844 TEXT：Source size > 21844
	CLOB	LONGTEXT
	NCLOB	LONGTEXT
	LONG	LONGTEXT
	NUMBER[(p[,s])]	DECIMAL[(M[,D])]
	FLOAT[(p)]	FLOAT[(p)]
	DATE	DATE
	BINARY_FLOAT	FLOAT
	BINARY_DOUBLE	DOUBLE
	TIMESTAMP [(fractional_seconds_precision)]	DATATIME
	TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE	TIMESTAMP
	TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE	TIMESTAMP
	INTERVAL YEAR [(year_precision)] TO MONTH	VARCHAR(32)
	INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]	VARCHAR(32)
	RAW(size)	VARBINARY(size)
	LONG RAW	LONGBLOB
	BLOB	LONGBLOB

### Appendix 2: Pre-Check Item Checklist

Check Item	Check Content
Source Data Source Connection Check	Check the status of the source data source gateway, instance accessibility, and accuracy of username and password
Target Data Source Connection Check	Check the status of the target data source gateway, instance accessibility, and accuracy of username and password
Target Database Permission Check	Check if the account permissions in the target database meet the requirements
Source Database Permission Check	Check if the account permissions in the source database meet the requirements
Target Data Source Trigger Check	Check if triggers exist in the target database
Target Database Data Existence Check	Check if data exists for the objects to be replicated in the target database
Target Database Same Name Object Existence Check	Check if the objects to be replicated already exist in the target database
Source Database Log Archiving Mode Check	Check if the log archiving mode of the source database is ARCHIVELOG
Source Database Additional Logging Check	Source database additional logging must be enabled and set to ALL

Related Documentation

Introduction to Data Replication