Oracle to PostgreSQL Data Replication

NineData Data Replication supports schema replication, full replication, and incremental replication from Oracle to PostgreSQL.

Overview

Use NineData to move Oracle data into PostgreSQL for migration, downstream reporting, or ongoing synchronization.

NineData data replication supports schema, full data, and incremental data replication between data sources. For supported data sources, it also supports bidirectional replication for geo-distributed active-active architectures.

• Schema replication: Replicates object structures between homogeneous and heterogeneous data sources.
• Full data replication: Uses data sharding and row-level concurrent batch replication to improve throughput. Breakpoint resume helps preserve data accuracy, including for tables without primary keys.
• Incremental data replication: Replicates DML and DDL changes for supported object types. Row-level concurrency and hotspot merge processing help maintain replication throughput.
• Bidirectional real-time data replication (only between MySQL instances): Replicates changes in both directions between nodes so data can stay current across participating nodes.

Use these capabilities for full or incremental data replication, migration, synchronization, data integration, and low-downtime migration workflows.

Before you begin

• Add both data sources to NineData. For instructions, see Add Data Source.

• The source data source must be Oracle 23ai, 21c, 19c, 18c, 12c, or 11g.

• The target data source must be PostgreSQL 15, 14, 13, 12, 11, or 10.

• Make sure the source and target accounts have the following permissions.

  Replication type	Source data source permissions	Target data source permissions
  Schema Replication	select connect select any dictionary select any table select_catalog_role	Schema Owner
  Full Replication	select connect select any dictionary select any table select_catalog_role	Schema Owner
  Incremental Replication	See Appendix 3: Oracle Incremental Replication Account Permissions in this document.	Schema Owner

• For incremental replication, configure the following settings on the source data source:

  • Set the Oracle log mode to ARCHIVELOG mode. The default is NOARCHIVELOG.

    To check the current log mode, run:

    SELECT log_mode FROM v$database;

    If the log mode is NOARCHIVELOG, run these commands:

    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    ALTER DATABASE ARCHIVELOG;
    ALTER DATABASE OPEN;

  • Enable supplemental logging. It is disabled 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.

• If the source and target data sources are in different regions, add each NineData server IP address to the other side's whitelist. Find both IP addresses on the Source & Target page when you create the task.

Restrictions

• Check source and target performance before you start. Run the task during off-peak hours when possible because full initialization reads and writes both data sources and can increase load.
• Make sure each replicated table has a primary key or unique constraint, and that column names are unique. Otherwise, the same data may be copied more than once.

Procedure

1. Sign in to the NineData Console.

2. Go to Replication.

3. On the Replication page, click Create Replication.

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

   Parameter	Description
   Name	Enter a task name that is easy to recognize later. Up to 64 characters are supported.
   Source	Select the source data source.
   Target	Select the target data source.
   Target DB	Select the target database.
   Target Object Name	Choose how object names are cased after migration. Convert all to Lowercase: Force all target object names to lowercase. Consistent with Source: Keep the source naming rule. Convert all to Uppercase: Force all target object names to uppercase.
   Type	Choose what to copy to the target data source. Schema: Copy only the source schema, not the data. Full: Copy all objects and data from the source. This is full replication. Incremental: After full replication finishes, sync changes from the source logs. Click the icon to clear any operation types you do not need. Cleared operation types are ignored during incremental replication.
   If target table already exists (Select when Schema is selected)	Pre-Check Error and Stop Task: Stop the task if the precheck finds an object with the same name. Skip and Continue Task: Show a message and continue the task if the precheck finds an object with the same name. Skip the object during schema replication. If data replication is also enabled, append data to the existing table without overwriting it. Delete Objects and Rewrite: Show a message and continue the task if the precheck finds an object with the same name. During schema replication, delete the object with the same name in the target database and copy the schema from the source again. If data replication is also enabled, write data after the schema is copied. Keep Schema and Clear Data, then Rewrite (Selectable when both schema and data replication are enabled): Show a message and continue the task if the precheck finds an object with the same name. Keep the target table schema, clear the data when data replication starts, and then copy from the source table again.
   Target Table Exists Data (Select when Schema is not selected)	Pre-Check Error and Stop Task: Stop the task if the precheck finds existing data in the target table. Ignore existing target data and append to it.: Ignore the existing data in the target table and append new data. Clear target existing data before write: Delete the existing data in the target table and rewrite it.

5. On the Objects tab, set the following fields, then click Next.

   Parameter	Description
   Replication Objects	Select the objects to copy. Choose All Objects to copy everything, or choose Customized Object, select objects in the Source Object list, and click > to move them to the Target Object list.

6. On the Mapping tab, map source tables to target tables, then click Next.

   • If Schema is included: Set the target table name after synchronization, then click Save and Pre-Check.

     tip

     Use Mapping & Filtering to rename columns after synchronization. Set Data Filter with comparison expressions to filter rows. For example, emp_no>=10005 excludes rows whose emp_no value is less than 10005.

   • If "Schema" is not included: NineData selects the same-named database in the target data source by default. If it does not exist, select the target database manually. The table and column names in the target database must match the replication objects. If they do not match, map them manually.

7. On the Pre-check tab, wait for NineData to complete the precheck. After the precheck passes, click Launch.

   • Select Enable data consistency comparison to compare target data with source data after the replication task finishes. The start time of Enable data consistency comparison depends on the selected Type:
     • Schema: Starts after schema replication finishes.
     • Schema + Full, Full: Starts after full replication finishes.
     • Schema + Full + Incremental, Incremental: Starts when incremental data first matches the source and Delay is 0 seconds. Click View Details to view the synchronization delay on the Details page.
   • If the precheck fails, click Details in the Actions column for the failed item, fix the issue, and click Check Again until the precheck passes.
   • For check items whose Result is Warning, fix the issue or ignore the warning when appropriate.

8. On the Launch page, after Launch Successfully appears, the replication task starts running. Use these actions to review the task:

   • Click View Details to view the status of each replication phase.
   • Click Back to list to return to the Replication task list.

Result

1. Sign in to the NineData Console.

2. Go to Replication.

3. On the Replication page, click the Task ID of the target replication task. The page shows the following information.

   

   No.	Feature	Description
   1	Synchronization Delay	The delay between the source and target data sources. A value of 0 seconds means there is no delay. At that point, switch business traffic to the target data source for a smooth cutover.
   2	Configure Alerts	Configure alerts to get task-failure notifications by the method you choose. 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 monitoring incremental changes. A terminated task cannot be restarted. Proceed with caution. If the replication object includes triggers, trigger replication options appear. Choose the option that matches your task. Delete: Delete the task. Deleted tasks cannot be recovered. Proceed with caution.
   4	Schema Replication (shown when schema replication is included)	Shows the progress and details of schema replication. Click Logs to view schema replication logs. Click to view the latest information. Click View DDL in the Actions column for the target object to view the SQL replay.
   5	Full Replication (shown when full replication is included)	Shows the progress and details of full replication. Click Monitor to view full replication metrics. During full replication, click Throttling Settings to limit the write rate to the target data source. The unit is rows per second. 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 replication task runs in multiple threads. This shows the current thread number. Execute SQL: Details of the SQL statement the thread is currently running. Response Time: The current thread response time. If this value increases, the thread may be stuck. Event Time: The timestamp when the current thread started. Status: The current thread status. Click Throttling 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 Objects	Shows change records for the replication objects. Click Modify Synchronization Object to update the replication objects. Click to view the latest information.
   8	Data Comparison	Shows the comparison results between the source and target data sources. If data comparison is not enabled, click Enable Data Comparison on the page. Click Re-compare to compare the current source and target data again. Click the date to view all comparison result lists, then click a target list item to switch to that result's details. Click Logs to view consistency comparison logs. Click Monitor to view the RPS (records per second) comparison trend chart. Click Details to view earlier records. Click in the Actions column of the comparison list (shown when there are differences) to view the detailed comparison between source and target. Click in the Actions column of the comparison list (shown when there are differences) to generate a change SQL statement. Copy it to the target data source and run it to fix the inconsistency.
   9	Expand	Shows detailed information about the current replication task, including Replication Type, Replication Object, Start Time, and more.

Appendix 1: Oracle to PostgreSQL data type mapping

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

Category	Oracle Data Type	PostgreSQL Data Type
Built-in data type	CHAR [(size [BYTE | CHAR])]	CHAR(size)
	NCHAR[(size)]	CHAR(size)
	VARCHAR2(size [BYTE | CHAR])	VARCHAR
	NVARCHAR2(size)	VARCHAR
	CLOB	TEXT
	NCLOB	TEXT
	LONG	TEXT
	NUMBER [ (p [, s]) ]	DOUBLE PRECISION / BIGINT
	FLOAT [(p)]	DOUBLE PRECISION
	DATE	TIMESTAMP(0)
	BINARY_FLOAT	NUMERIC
	BINARY_DOUBLE	NUMERIC
	TIMESTAMP [(fractional_seconds_precision)]	TIMESTAMP
	TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE	TIMESTAMP WITH TIME ZONE
	TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE	TIMESTAMP WITH TIME ZONE
	INTERVAL YEAR [(year_precision)] TO MONTH	INTERVAL YEAR TO MONTH
	INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]	INTERVAL DAY TO SECOND((fractional_seconds_precision))
	RAW(size)	BYTEA
	LONG RAW	BYTEA
	BLOB	BYTEA
	BFILE	Custom Type
User-defined data type	REF	Custom Type
	VARRAY	Custom Type
	NESTED TABLE	Custom Type
Oracle-supplied data type	ANYDATA	VARCHAR
Spatial data type	SDO_GEOMETRY	VARCHAR

Appendix 2: Precheck items

Check item	What NineData checks
Source data source connection	Checks the source gateway status, instance accessibility, and username and password accuracy.
Target data source connection	Checks the target gateway status, instance accessibility, and username and password accuracy.
Target database permissions	Checks whether the target database account permissions meet the requirements.
Source database permissions	Checks whether the source database account permissions meet the requirements.
Target data source triggers	Checks whether triggers exist in the target database.
Target database data presence	Checks whether the objects to be replicated already have data in the target database.
Target database name conflict	Checks whether objects with the same names already exist in the target database.
Source database archive mode	Checks whether the source database archive mode is ARCHIVELOG.
Source database supplemental logging	Checks whether supplemental logging is enabled and set to ALL for the source database.

Appendix 3: Oracle incremental replication account permissions

Below Oracle 12c

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;

Oracle 12c and above

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;

Next steps

• Data Replication overview