DataWorks:Oracle data source

Supported Oracle versions

Batch data read and write

Real-time data read

• Supported Oracle database versions:

  Versions later than 11g R2, 12c non cdb, 18c non cdb, and 19c non cdb

• Unsupported Oracle database versions:

  12c cdb, 18c cdb, and 19c cdb

Limits

• The following encoding formats are supported for data synchronization: UTF-8, AL32UTF8, AL16UTF16, and ZHS16GBK.

• A maximum of 500 GB incremental data can be synchronized from an Oracle instance in real time every day.

• Real-time synchronization of data from an Oracle data source in Data Integration is implemented based on the Oracle LogMiner utility that is used to analyze log files.

• Data of views can be read during batch synchronization.

Data types

Prepare an Oracle environment before data synchronization

Before you use DataWorks to synchronize data from or to an Oracle data source, you must prepare an Oracle environment. This ensures that a data synchronization task can be configured and can synchronize data from or to the Oracle data source as expected. The following information describes how to prepare an Oracle environment for data synchronization from or to an Oracle data source.

Preparation 1: Check the version of your Oracle database

Preparation 2: Prepare an account that has the required permissions

Preparation 3: Enable archive logging and supplemental logging, and switch to another redo log file

1. Execute the following SQL statements to enable archive logging:

   shutdown immediate;
   startup mount;
   alter database archivelog;
   alter database open;

2. Enable supplemental logging.

   SQL statements:

   alter database add supplemental log data(primary key) columns; // Enable supplemental logging for primary key columns. 
   alter database add supplemental log data(unique) columns; // Enable supplemental logging for unique index columns.

3. Switch to another redo log file.

   After you enable supplemental logging, you must execute the following statement multiple times to switch to another redo log file. We recommend that you execute the following statement for five times:

   alter system switch logfile;

   Note

   This ensures that data can be written to the next log file after the current log file is full. Records of performed operations will not be lost. This facilitates data recovery.

Preparation 4: Check character encoding formats of your Oracle database

select * from v$nls_parameters where PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

Preparation 5: Check the data types of tables in your Oracle database

select COLUMN_NAME,DATA_TYPE from all_tab_columns where TABLE_NAME='tablename'; 

Develop a data synchronization task

For information about the entry point for and the procedure of configuring a data synchronization task, see the following sections. For information about the parameter settings, view the infotip of each parameter on the configuration tab of the task.

Add a data source

Before you configure a data synchronization task to synchronize data from or to a specific data source, you must add the data source to DataWorks. For more information, see Add and manage data sources.

Configure a batch synchronization task to synchronize data of a single table

• For more information about the configuration procedure, see Configure a batch synchronization task by using the codeless UI and Configure a batch synchronization task by using the code editor.

• For information about all parameters that are configured and the code that is run when you use the code editor to configure a batch synchronization task, see Appendix: Code and parameters.

Configure a real-time synchronization task to synchronize data of a single table

For more information about the configuration procedure, see Configure a real-time synchronization task in DataStudio.

Configure synchronization settings to implement batch synchronization of all data in a database, real-time synchronization of full data or incremental data in a database, and real-time synchronization of data from sharded tables in a sharded database

For more information about the configuration procedure, see Configure a synchronization task in Data Integration.

Additional information

• Why are errors repeatedly reported when a real-time synchronization task is run to synchronize data from Oracle, PolarDB, or MySQL?

• Data synchronization between primary and secondary databases

  A secondary Oracle database can be deployed for disaster recovery. The secondary database continuously synchronizes data from the primary database based on binary logs. Data latency between the primary and secondary databases cannot be prevented. This may result in data inconsistency.

• Data consistency control

  Oracle is a relational database management system (RDBMS) that supports strong consistency for data queries. A database snapshot is created before a synchronization task starts. Oracle Reader reads data from the database snapshot. Therefore, if new data is written to the database during data synchronization, Oracle Reader cannot obtain the new data.

  Data consistency cannot be ensured if you enable Oracle Reader to use parallel threads to read data in a synchronization task.

  Oracle Reader shards the source table based on the value of the splitPk parameter and uses parallel threads to read data. These parallel threads belong to different transactions and read data at different points in time. Therefore, the parallel threads observe different snapshots.

  Data inconsistencies cannot be prevented if parallel threads are used for a synchronization task. The following workarounds can be used:

  • Enable Oracle Reader to use a single thread to read data in a synchronization task. This indicates that you do not need to specify a shard key for Oracle Reader. This way, data consistency is ensured, but data is synchronized at low efficiency.

  • Make sure that no data is written to the source table during data synchronization. This ensures that the data in the source table remains unchanged during data synchronization. For example, you can lock the source table or disable data synchronization between primary and secondary databases. This way, data is efficiently synchronized, but your ongoing services may be interrupted.

• Character encoding

  Oracle Reader uses Java Database Connectivity (JDBC) to read data. This enables Oracle Reader to automatically convert the encoding format of characters. Therefore, you do not need to specify the encoding format.

• Incremental data synchronization

  Oracle Reader connects to a database by using JDBC and uses a SELECT statement with a WHERE clause to read incremental data.

  • For batch data, incremental add, update, and delete operations (including logically delete operations) are distinguished by timestamps. Specify the WHERE clause based on a specific timestamp. The time indicated by the timestamp must be later than the time indicated by the latest timestamp in the previous synchronization.

  • For streaming data, specify the WHERE clause based on the ID of a specific record. The ID must be greater than the maximum ID involved in the previous synchronization.

  If the data that is added or modified cannot be distinguished, Oracle Reader can read only full data.

• Syntax validation

  Oracle Reader allows you to specify custom SELECT statements by using the querySql parameter but does not verify the syntax of these statements.

Appendix: Code and parameters

Appendix: Configure a batch synchronization task by using the code editor

If you use the code editor to configure a batch synchronization task, you must configure parameters for the reader and writer of the related data source based on the format requirements in the code editor. For more information about the format requirements, see Configure a batch synchronization task by using the code editor. The following information describes the configuration details of parameters for the reader and writer in the code editor.

Code for Oracle Reader

{
    "type": "job",
    "version": "2.0",
    "steps": [
        {
            "stepType": "oracle",
            "parameter": {
                "selectedDatabase": "AUTOTEST",
                "indexes": [],
                "datasource": "oracle_test",
                "envType": 0,
                "useSpecialSecret": true,
                "column": [
                    "id"
                ],
                "where": "",
                "splitPk": "id",
                "encoding": "UTF-8",
                "table": "AUTOTEST.table01"
            },
            "name": "Reader",
            "category": "reader"
        },
        {
            "stepType": "odps",
            "parameter": {
            },
            "name": "Writer",
            "category": "writer"
        },
        {
            "name": "Processor",
            "stepType": null,
            "category": "processor",
            "copies": 1,
            "parameter": {
                "nodes": [],
                "edges": [],
                "groups": [],
                "version": "2.0"
            }
        }
    ],
    "setting": {
        "executeMode": null,
        "errorLimit": {
            "record": ""
        },
        "speed": {
            "concurrent": 2,
            "throttle": false
        }
    },
    "order": {
        "hops": [
            {
                "from": "Reader",
                "to": "Writer"
            }
        ]
    }
}

Parameters in code for Oracle Reader

Code for Oracle Writer

{
    "type":"job",
    "version":"2.0",// The version number. 
    "steps":[
        { 
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"oracle",// The plug-in name. 
            "parameter":{
                "postSql":[],// The SQL statement that you want to execute after the synchronization task is run. 
                "datasource":"",
                "session":[],// The settings of the session to the database. 
                "column":[// The names of the columns. 
                    "id",
                    "name"
                ],
                "encoding":"UTF-8",// The encoding format. 
                "batchSize":1024,// The number of data records to write at a time. 
                "table":"",// The name of the table. 
                "preSql":[]// The SQL statement that you want to execute before the synchronization task is run. 
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The maximum number of dirty data records allowed. 
        },
        "speed":{
            "throttle":true,// Specifies whether to enable throttling. The value false indicates that throttling is disabled, and the value true indicates that throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
            "concurrent":1, // The maximum number of parallel threads. 
            "mbps":"12"// The maximum transmission rate. Unit: MB/s. 
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

Parameters in code for Oracle Writer