Oracle Migration to PostgreSQL

NineData Data Replication supports data migration and synchronization from Oracle to PostgreSQL data sources.

Background Information

As enterprise informatization deepens, databases, as the core of enterprise information systems, become increasingly important. Oracle has long dominated the enterprise database market due to its powerful features and stable performance. However, the high licensing costs of Oracle have led many enterprises to seek more economical and flexible database solutions.

PostgreSQL, an open-source database, has gradually gained favor among enterprises for its advanced features, high performance, robust data integrity, and flexible open-source licensing. Additionally, PostgreSQL has an active open-source community that provides a variety of rich plugins, tools, and timely technical support.

Therefore, many enterprises have the need to migrate from Oracle to PostgreSQL. NineData supports the migration and synchronization of Oracle to PostgreSQL, including structure, full data, and incremental data, helping enterprises quickly complete comprehensive business data migration.

Features

NineData data replication supports high-performance replication of structure, full data, and incremental data between data sources. For MySQL data sources, it also provides bidirectional replication, enabling the quick construction of geo-distributed active-active business architectures.

• Structure: Supports the replication of object structures between homogeneous and heterogeneous data sources, greatly reducing the barrier to data replication between two sources.
• Full Data: Achieves row-level concurrent batch replication through intelligent data sharding, effectively ensuring replication performance. The independently developed novel breakpoint resume technology ensures the accuracy of data in tables without primary keys.
• Incremental Data: Supports incremental data replication of full object types DML|DDL, combined with transaction-level concurrency, hotspot merging, and other technologies to provide robust replication performance while ensuring transaction consistency.
• Bidirectional Real-time Data Replication (only between MySQL instances): Direct bidirectional replication of data between multiple nodes ensures that all node data remains up-to-date.

With the above features, it is easy and efficient to achieve scenarios such as full|incremental data replication, full|incremental data migration, full|incremental data synchronization, data integration, and seamless migration without downtime, providing enterprises with flexible and reliable data replication solutions.

Prerequisites

• The source and target data sources have been added to NineData. For how to add, see Add Data Source.

• The source data source is Oracle, with one of the following versions: 23ai, 21c, 19c, 18c, 12c, 11g.

• The target data source is PostgreSQL, with one of the following versions: 15, 14, 13, 12, 11, 10.

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

  Replication Type	Source Data Source Permissions	Target Data Source Permissions
  Structure 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, ensure the following configurations on the source data source:

  • The Oracle 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. Enable archive log: ALTER DATABASE ARCHIVELOG;
    4. Open the database: ALTER DATABASE OPEN;

  • Supplemental Logging is enabled (not enabled by default).

    Verify if supplemental logging is enabled: SELECT supplemental_log_data_all FROM v$database;
    If it returns NO, enable supplemental logging:
    - ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
    //Enable supplemental logging at the database level, applicable to all tables and 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 supplemental logging for specific tables, applicable to all columns in the table.

• If the source and target data sources are selected in different regions when added to NineData, add the IP addresses of each other's NineData servers to the whitelist of the respective data sources. You can view the IP addresses of both on the page when creating a replication task.

Usage Restrictions

• Before performing data synchronization, evaluate the performance of the source and target data sources. It is recommended to perform data synchronization during off-peak business hours. Otherwise, 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 objects has a primary key or unique constraint and that column names are unique, otherwise, the same data may be synchronized repeatedly.

Operation Steps

Commercialization Notice

NineData’s data replication product has been commercialized. You can still use 10 replication tasks for free, with the following considerations:

• Among the 10 replication tasks, you can include 1 task, with a specification of Micro.

• Tasks with a status of do not count towards the 10-task limit. If you have already created 10 replication tasks and want to create more, you can terminate previous replication tasks and then create new ones.

• When creating replication tasks, you can only select the you have purchased. Specifications that have not been purchased will be grayed out and cannot be selected. If you need to purchase additional specifications, please contact us through the customer service icon at the bottom right of the page.

1. Log in to the NineData Console.

2. In the left navigation bar, click .

3. On the page, click .

4. On the tab, configure the following parameters, then click .

   Parameter	Description
   	Enter the name of the data synchronization task. To facilitate subsequent search and management, use a meaningful name. Supports up to 64 characters.
   	The data source where the synchronization object is located.
   	The data source that receives the synchronization object.
   	Select which database to synchronize the data to on the target.
   	Select the case conversion rule for object names after migration from the source to the target. : Convert to lowercase regardless of the source naming rules. : Follow the source naming rules. : Convert to uppercase regardless of the source naming rules.
   	Select the content to be copied to the target data source. : Only synchronize the schema of the source data source, not the data. : Synchronize all objects and data from the source data source, i.e., full data replication. : After the full synchronization is complete, perform incremental synchronization based on the logs of the source data source. Click the icon to uncheck certain operation types as needed; unchecked operations will be ignored in incremental synchronization.
   (Select when is selected)	: Stop the task if the same name table is detected during the precheck stage. : Continue the task after prompting when the same name table is detected during the precheck stage. Ignore the same name table during structure replication. If data replication is also performed, data will be appended to the same name table without overwriting the original data. : Continue the task after prompting when the same name table is detected during the precheck stage. Delete the same name table in the target database during structure replication and replicate the table structure based on the source database. If data replication is also performed, data will be written after the table structure replication is complete. (Selectable when performing both structure and data replication): Continue the task after prompting when the same name table is detected during the precheck stage. Keep the table structure in the target database, clear the data in the same name table when data replication starts, and then replicate from the original table.
   (Select when is not selected)	: Stop the task if data exists in the target table during the precheck stage. : Ignore the existing data in the target table and append new data during the precheck stage. : Delete existing data in the target table and rewrite it during the precheck stage.

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

   Parameter	Description
   	Select the content to be copied. You can choose to copy all content of the source database, or choose and select the content to be copied from the list, then click > to add it to the list.

6. On the tab, you can map the source tables to the target tables, then click .

   • Includes : Configure the target table name after synchronization to the target data source, then click .

     tip

     You can click on the right side of the page to customize the column names after they are synchronized to the target data source. Additionally, you can set to configure filtering conditions through comparison expressions. Only data that meets the filtering conditions will be synchronized to the target data source. For example, setting the filtering condition to emp_no>=10005 will prevent data in the emp_no column that is less than 10005 from being synchronized to the target data source.

   • Does not include : The system will default to selecting the same-named database in the target data source. If it does not exist, you will need to manually select the target database. The table names and column names in the target database must match those of the synchronization objects. If they do not match, you can manually map the table names and column names.

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

   • You can select to automatically start a data consistency comparison between the source and target data sources after the synchronization task completes, ensuring data consistency between both ends. Depending on your selected , the timing for starting is as follows:
     • : Starts after the structure replication is completed.
     • + , : Starts after the full replication is completed.
     • + + , : Starts when the incremental data first matches the source data source and is 0 seconds. You can click to view the synchronization delay on the page.
   • If the pre-check does not pass, click in the column on the right side of the target check item to investigate the cause of the failure. After manually fixing it, click to re-execute the pre-check until it passes.
   • For check items where is , you can choose to fix or ignore them based on the specific situation.

8. On the page, a prompt will appear, and the synchronization task will begin. At this point, you can perform the following actions:

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

View Synchronization Results

1. Log in to the NineData Console.

2. Click on the left navigation bar.

3. On the page, click the of the target synchronization task. The page description is as follows.

   

   No.	Feature	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 point, you can choose to switch your business to the target data source for a smooth migration.
   2	Configure Alerts	After configuring alerts, the system will notify you of task failures through your selected method. For more information, see Maintenance and Monitoring Overview.
   3	More	Pause: Pause the task. This option is available only for tasks in the running state. Terminate: End unfinished or monitoring (incremental synchronization) tasks. Once terminated, tasks cannot be restarted, so proceed with caution. If the synchronization object includes triggers, trigger replication options will appear. Choose as needed. Delete: Delete the task. Once deleted, tasks cannot be recovered, so proceed with caution.
   4	Structure Replication (shown when structure replication is included)	Displays the progress and detailed information of the structure replication. Click Logs on the right side of the page to view the execution logs of the structure replication. Click on the right side of the page to view the latest information. Click View DDL in the Actions column on the right side of the target object in the list to view the SQL replay.
   5	Full Replication (shown when full replication is included)	Displays the progress and detailed information of the full replication. Click Monitor on the right side of the page to view various monitoring metrics during the full replication process. During the full replication process, you can also click Throttling Settings on the right side of the monitoring metrics page to limit the write rate to the target data source per second. Click Logs on the right side of the page to view the execution logs of the full replication. Click on the right side of the page to view the latest information.
   6	Incremental Replication (shown when incremental replication is included)	Displays various monitoring metrics of incremental replication. Click on the right side of the page to view the ongoing operations of the current replication task, including: : The replication task is executed in multiple threads, showing the current thread number. : Details of the SQL statements currently being executed by the 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 started. : The status of the current thread. Click Throttling Settings on the right side of the page to limit the write rate to the target data source per second, measured in rows/second. Click Logs on the right side of the page to view the execution logs of the incremental replication. Click on the right side of the page to view the latest information.
   7	Modify Objects	Displays modification records of the synchronization objects. Click Modify Synchronization Object on the right side of the page to configure the synchronization objects. Click on the right side of the page to view the latest information.
   8	Data Comparison	Displays the data comparison results between the source and target data sources. If you have not enabled data comparison, click Enable Data Comparison on the page. Click Re-compare on the right side of the page to re-initiate a comparison of the current source and target data. Click the date to view all comparison result lists, and click the target list item to switch to the details of that comparison result. Click Logs on the right side of the page to view the execution logs of the consistency comparison. Click Monitor on the right side of the page to view the RPS (Records Per Second) comparison trend chart. Click Details to view earlier records. In the Actions column on the right side of the comparison list, click (shown in case of discrepancies) to view the detailed comparison between the source and target ends. In the Actions column on the right side of the comparison list, click (shown in case of discrepancies) to generate a change SQL, which you can directly copy to the target data source to execute, modifying the inconsistent content.
   9	Expand	Displays detailed information of the current replication task, including Replication Type, Replication Object, Start Time, etc.

Appendix 1: Data Type Mapping Table

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

Category	Oracle Data Type	PostgreSQL Data Type
Build-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 PRECISIONBIGINT
	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
Oralce supplied Data Type	ANYDATA	VARCHAR
Spatial Data Type	SDO_GEOMETRY	VARCHAR

Appendix 2: Pre-check Item List

Check Item	Check Content
Source Data Source Connection	Check the status of the source data source gateway, instance accessibility, and the accuracy of username and password
Target Data Source Connection	Check the status of the target data source gateway, instance accessibility, and the accuracy of username and password
Target Database Permissions	Check if the target database account permissions meet the requirements
Source Database Permissions	Check if the source database account permissions meet the requirements
Target Data Source Triggers	Detect if triggers exist in the target database
Target Database Data Presence	Check if data of the objects to be replicated already exists in the target database
Target Database Name Conflict	Check if objects with the same names as the ones to be replicated already exist in the target database
Source Database Archive Mode	Check if the archive mode of the source database is set to ARCHIVELOG
Source Database Supplemental Logging	Check if supplemental logging is enabled and set to ALL for the source database

Appendix 3: Oracle Incremental Replication Account Permission Description

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;

Related Documentation

Introduction to Data Replication