Oracle to SingleStore Data Replication

NineData Data Replication supports data replication from Oracle to SingleStore.

Overview

SingleStore is a distributed SQL database for transactional and analytical workloads. Use this guide to replicate Oracle data into SingleStore, keep the target current, and move analytics workloads off Oracle when needed.

Before you begin

Add the source and target data sources to NineData. For instructions, see Add Data Source.
The source data source is Oracle 23ai, 21c, 19c, 18c, 12c, or 11g.
The target data source is SingleStore 8.1, 8.0, 7.8, 7.6, or 7.5.

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

Replication Type	Source Data Source	Target Data Source
Full Replication	select connect select any dictionary select any table select_catalog_role	ALL privileges on the database
Incremental Replication	See Appendix 3: Oracle incremental replication account permissions.	ALL privileges on the database

For incremental replication, configure the source Oracle database as follows:
- Set the Oracle log mode to ARCHIVELOG. The default is NOARCHIVELOG.
  To check the current log mode, run:
```
SELECT log_mode FROM v$database;
```
  If the log mode is NOARCHIVELOG, run the following commands:
```
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
```
- Enable supplemental logging. It is off by default.
  To check whether supplemental logging is enabled, run:
```
SELECT supplemental_log_data_all allc FROM v$database;
```
  If the result is NO, use the command that applies to your scenario:
```
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE <database_name>.<table_name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
```
  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS enables database-level supplemental logging and can consume more resources. ALTER TABLE <database_name>.<table_name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS enables supplemental logging for a specific table.

Restrictions

Before you run a replication task, assess the performance of the source and target data sources. Run the task during off-peak hours when possible because full initialization consumes read and write resources.
Each replicated table must have a primary key or unique constraint, and column names must be unique. Otherwise, duplicate rows may be replicated.

Procedure

Sign in to the NineData Console.
In the navigation menu, click Replication > Data Replication.
On the Replication page, click Create Replication.

On the Source & Target tab, set the following fields, then click Next.

Parameter	Description
Name	Enter a meaningful task name. Up to 64 characters are supported.
Source	Select the Oracle data source that contains the objects to replicate.
Target	Select the SingleStore data source that receives the replicated objects.
Target Object Name	Choose how to convert object names after they are copied from source to target. Convert all to Lowercase: Convert all object names to lowercase letters. Consistent with Source: Keep the same case as the source object names. Convert all to Uppercase: Convert all object names to uppercase letters.
Type	Select what to replicate to the target data source. Schema: Replicate only source table schemas. Data is not replicated. Full: Replicate all selected source objects and data. This is full data replication. Incremental: After full synchronization, replicate incremental changes based on source logs.
If target table already exists (Selected when Schema is selected)	Pre-Check Error and Stop Task: Stop the task if the precheck detects a table with the same name. Skip and Continue Task: Continue the task after a message appears. During schema replication, skip the same-name table. If data replication is also enabled, append data to the existing table without overwriting existing data. Delete Objects and Rewrite: Continue the task after a message appears. During schema replication, delete the same-name table in the target database and replicate the table structure from the source again. If data replication is also enabled, write data after schema replication completes. Keep Schema and Clear Data, then Rewrite (Optional when both schema and data replication are selected): Continue the task after a message appears. During schema replication, keep the target table structure. When data replication starts, clear the data in the same-name table and then replicate data from the source table again.
Target Table Exists Data (Required when Schema is not selected)	Pre-Check Error and Stop Task: Stop the task if the precheck detects existing data in the target table. Ignore existing target data and append to it.: Ignore existing data in the target table and append other data. Clear target existing data before write: Delete existing data in the target table before writing new data.

On the Objects tab, configure the following parameter, then click Next.

Parameter	Description
Replication Objects	Select the objects to replicate. Select All Objects to replicate all objects in the source database, or select Customized Object, choose objects in the Source Object list, and click > to move them to the Target Object list.

On the Mapping tab, choose the action that matches the selected replication type.

If Schema is included: Configure the target table name, Shard key, Primary key, and Sort key after synchronization, and then click Save and Pre-Check. By default, NineData uses the following key definitions in SingleStore based on the source table type. Choose different Primary key, Shard key, or Sort key columns if needed.

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

Click Mapping & Filtering to customize target column names after synchronization. Set Data Filter so that only data that matches the filter is synchronized to the target data source.

:::

If Schema is not included: NineData selects the same-name database in the target data source by default. If no same-name database exists, select the target database manually. The table and column names in the target database must match the synchronized objects. If they do not match, map them manually.

On the Pre-check tab, wait for NineData to complete the precheck. After the precheck passes, click Launch.
tip
- Select Enable data consistency comparison to compare target data with source data after the synchronization task completes. NineData starts the comparison based on the source data source. Depending on the selected Type, Enable data consistency comparison starts as follows:
  - Schema: Starts after structure replication completes.
  - Schema + Full, Full: Starts after full replication completes.
  - Schema + Full + Incremental, Incremental: Starts when incremental data is consistent with the source for the first time and Delay is 0 seconds. Click View Details to view synchronization delay on the Details page.
- If the precheck fails, click Details for the failed check item, troubleshoot the cause, fix it manually, and then click Check Again to run the precheck again.
- For check items whose Result is Warning, fix the issue or ignore the warning when appropriate.
On the Launch page, after Launch Successfully appears, the synchronization task starts. Use these actions to review the task:
- Click View Details to view each synchronization stage.
- Click Back to list to return to the Replication task list.

View replication results

Sign in to the NineData Console.
In the navigation menu, click Replication > Data Replication.

On the Replication page, click the Task ID of the target synchronization task. The task details page contains the following areas.

oracle_result

Number	Function	Description
1	Synchronization Delay	Shows the synchronization delay between the source and target data sources. `0` seconds means there is no delay. At that point, switch business traffic to the target data source for a smooth migration.
2	Configure Alerts	Sends alerts when the task fails. For more information, see Introduction to operational monitoring.
3	More	Pause: Pause a task whose status is Running. Terminate: Terminate a task that is unfinished or still running incremental synchronization. A terminated task cannot be restarted. Proceed with caution. If the synchronized objects contain triggers, the trigger replication option appears. Delete: Delete the task. Deleted tasks cannot be restored. Proceed with caution.
4	Structure Replication (shown when structure replication is included)	Shows structure replication progress and details. Click Logs to view structure replication logs. Click to view the latest information. Click View DDL in the Operation column of a target object to view SQL playback.
5	Full Replication (shown when full replication is included)	Shows full replication progress and details. Click Monitor to view metrics during full replication. During full replication, click Rate Limit Settings on the monitoring page to limit the write rate to the target data source. Click Logs to view full replication logs. Click to view the latest information.
6	Incremental Replication (shown when incremental replication is included)	Shows incremental replication metrics. Click View Threads to view the operations currently running in the replication task, including: Thread ID: The task uses multiple threads for replication. This item shows the current thread number. Execute SQL: Details of the SQL statement that the current thread is executing. Response Time: The response time of the current thread. If this value increases, the thread may be blocked. Event Time: The timestamp when the current thread started. Status: The current thread status. Click Rate Limit Settings to limit the write rate to the target data source. The unit is rows per second. Click Logs to view incremental replication logs. Click to view the latest information.
7	Modify Object	Shows modification records for synchronized objects. Click Modify Synchronized Objects to configure synchronized objects. Click to view the latest information.
8	Data Comparison	Shows data comparison results between the source and target data sources. If data comparison is not enabled, click Enable Data Comparison on the page. Click Recompare to compare the current source and target data again. Click the date to view all comparison results, and click a result item to open its details. Click Logs to view consistency comparison logs. Click Monitoring to view the comparison RPS trend chart. Click Details to view earlier records. Click in the Operation column of the comparison list (shown when data is inconsistent) to view source and target comparison details. Click in the Operation column of the comparison list (shown when data is inconsistent) to generate change SQL. Copy it to the target data source and run it to fix the inconsistency.
9	Expand	Shows current replication task details, including Replication Type, Replication Objects, Start Time, and more.

Appendix 1: Oracle to SingleStore Data Type Mapping

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

Type	Oracle Data Type	SingleStore Data Type
Built-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 > 21,844
	NVARCHAR2(size)	VARCHAR[(size)]: source size <= 21,844 TEXT: source size > 21,844
	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)]	DATETIME
	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: Precheck items

Check item	What NineData checks
Source data source connection check	Checks the source gateway status, instance accessibility, and username and password.
Target data source connection check	Checks the target gateway status, instance accessibility, and username and password.
Target database permission check	Checks whether the target database account has the required permissions.
Source database permission check	Checks whether the source database account has the required permissions.
Target data source trigger check	Checks whether triggers exist in the target database.
Target database data existence check	Checks whether target objects already contain data.
Target same-name object check	Checks whether objects with the same names already exist in the target database.
Source database archive log mode check	Checks whether the source database log mode is `ARCHIVELOG`.
Source database supplemental logging check	Checks whether supplemental logging is enabled and set to `ALL`.

Appendix 3: Oracle incremental replication account permissions

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 to SingleStore Data Replication

Overview​

Before you begin​

Restrictions​

Procedure​

View replication results​

Appendix 1: Oracle to SingleStore Data Type Mapping​

Appendix 3: Oracle incremental replication account permissions​

Related links​