DataWorks:Oracle

Batch read and write

Real-time read

Supported versions: Oracle 11g R2 and later, 12c non-CDB, 18c non-CDB, 19c non-CDB, and 19c CDB.

Not supported: Oracle 12c CDB and 18c CDB.

Support by operation

Oracle Reader data type mappings

Verify the Oracle database version

Check your Oracle database version to confirm it is supported for your sync type.

1. Run one of the following statements to get the version:

   select * from v$version;

   or:

   select version from v$instance;

2. If the version is 12c or 18c, run the following statement to determine whether the database is a CDB. Real-time Synchronization does not support CDB-type Oracle databases of version 12c or 18c.

   select name, cdb, open_mode, con_id from v$database;

Create an account and grant permissions

Create a dedicated database account for Data Synchronization and grant the required permissions.

1. Create the account. See CREATE USER in the Oracle documentation.

2. Grant the base permissions required for all sync types. Replace <sync_user> with your actual account name.

   grant create session to <sync_user>;
   grant connect to <sync_user>;
   grant select on nls_database_parameters to <sync_user>;
   grant select on all_users to <sync_user>;
   grant select on all_objects to <sync_user>;
   grant select on DBA_MVIEWS to <sync_user>;
   grant select on DBA_MVIEW_LOGS to <sync_user>;
   grant select on DBA_CONSTRAINTS to <sync_user>;
   grant select on DBA_CONS_COLUMNS to <sync_user>;
   grant select on all_tab_cols to <sync_user>;
   grant select on sys.obj$ to <sync_user>;
   grant select on SYS.COL$ to <sync_user>;
   grant select on sys.USER$ to <sync_user>;
   grant select on sys.cdef$ to <sync_user>;
   grant select on sys.con$ to <sync_user>;
   grant select on all_indexes to <sync_user>;
   grant select on v_$database to <sync_user>;
   grant select on V_$ARCHIVE_DEST to <sync_user>;
   grant select on v_$log to <sync_user>;
   grant select on v_$logfile to <sync_user>;
   grant select on v_$archived_log to <sync_user>;
   grant select on V_$LOGMNR_CONTENTS to <sync_user>;
   grant select on DUAL to <sync_user>;
   grant select on v_$parameter to <sync_user>;
   grant select any transaction to <sync_user>;
   grant execute on SYS.DBMS_LOGMNR to <sync_user>;
   grant alter session to <sync_user>;
   grant select on dba_objects to <sync_user>;
   grant select on v_$standby_log to <sync_user>;
   grant select on v_$ARCHIVE_GAP to <sync_user>;
   grant select on sys.ICOL$ to <sync_user>;
   grant select on V$THREAD to <sync_user>;

3. For Batch Full Synchronization, also grant table read access:

   grant select any table to <sync_user>;

4. For Oracle 12c and later, grant Log Mining permissions (not required for earlier versions, where Log Mining is built in):

   grant LOGMINING to <sync_user>;

Enable archive logs and supplemental logging

Real-time synchronization requires archive logs and supplemental logging on the primary database.

1. Enable archive logging:

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

2. Enable supplemental logging. Choose the appropriate scope: Database level (applies to all tables):

   alter database add supplemental log data(primary key) columns;
   alter database add supplemental log data(unique) columns;

   Table level (applies to specific tables):

   alter <schema>.<table> add supplemental log data(primary key) columns;
   alter <schema>.<table> add supplemental log data(unique) columns;

3. Switch the redo log file. Run the following command five times to flush the current log and switch to the next one:

   Note

   Running this command multiple times fills the current log file, forcing a switch. This prevents transaction record loss and simplifies data recovery.

   alter system switch logfile;

Check the database character encoding

Run the following to check the character encoding settings:

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

• NLS_CHARACTERSET: the database character set

• NLS_NCHAR_CHARACTERSET: the national character set

Data Synchronization supports only UTF8, AL32UTF8, AL16UTF16, and ZHS16GBK. If your database uses a different encoding, change it before running any sync task.

Check table data types

To view the data types for a specific table, run:

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

Replace <tablename> with the actual table name.

• COLUMN_NAME: the column name

• DATA_TYPE: the column's data type

• all_tab_columns: a system view containing column metadata for all tables, views, and clusters

Single-table batch synchronization

Configure the task in the Codeless UI or Code Editor.

For a complete parameter reference and script template, see Appendix: Script examples and parameters.

Single-table real-time synchronization

See Configure a real-time synchronization task in DataStudio.

Full-database synchronization

See Configure a real-time full-database synchronization task for batch, real-time, and sharding configurations.

Reader script example

All Reader parameters are set under "category": "reader" in the job script.

{
    "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"
            }
        ]
    }
}

Reader parameters

Writer script example

All Writer parameters are set under "category": "writer" in the job script.

{
    "type": "job",
    "version": "2.0",
    "steps": [
        {
            "stepType": "stream",
            "parameter": {},
            "name": "Reader",
            "category": "reader"
        },
        {
            "stepType": "oracle",
            "parameter": {
                "postSql": [],
                "datasource": "",
                "session": [],
                "column": [
                    "id",
                    "name"
                ],
                "encoding": "UTF-8",
                "batchSize": 1024,
                "table": "",
                "preSql": []
            },
            "name": "Writer",
            "category": "writer"
        }
    ],
    "setting": {
        "errorLimit": {
            "record": "0"
        },
        "speed": {
            "throttle": true,
            "concurrent": 1,
            "mbps": "12"
        }
    },
    "order": {
        "hops": [
            {
                "from": "Reader",
                "to": "Writer"
            }
        ]
    }
}

Writer parameters