DataWorks:Oracle data source

Batch data read and write

Real-time data read

• Supported versions:

  11g R2 and later, 12c non-CDB, 18c non-CDB, 19c non-CDB, or 19c CDB.

• Unsupported versions:

  12c CDB and 18c CDB.

• To use an Oracle database as a source for a real-time full-database synchronization task, you must enable the archive log feature. For more information, see Prepare an Oracle environment.

Check the Oracle database version

The supported Oracle database versions vary by synchronization scenario. Before synchronizing data, verify that your Oracle database version is supported.

1. To check your Oracle database version, run either of the following statements.

   • Statement 1:

     select * from v$version;

   • Statement 2:

     select version from v$instance;

2. If your Oracle database version is 12c or 18c, you need to use the following statement to confirm whether the database is a cdb database. DataWorks Data Integration real-time synchronization tasks do not currently support Oracle cdb databases of version 12c or 18c.

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

Create an account and grant privileges

Create a database account with the required privileges in Oracle.

1. For details, see Create an Oracle account.

2. Grant privileges.

   Run the following commands to grant the required privileges to the account. In the following statements, replace 'synchronization account' with the account you created.

   grant create session to 'synchronization account';  // Grants the privilege to log on to the database.
   grant connect to 'synchronization account';  // Grants the privilege to connect to the database.
   grant select on nls_database_parameters to 'synchronization account';  // Grants the privilege to query the nls_database_parameters system configuration.
   grant select on all_users to 'synchronization account';  // Grants the privilege to query all users in the database.
   grant select on all_objects to 'synchronization account';  // Grants the privilege to query all objects in the database.
   grant select on DBA_MVIEWS to 'synchronization account';  // Grants the privilege to view the materialized views of the database.
   grant select on DBA_MVIEW_LOGS to 'synchronization account';  // Grants the privilege to view the materialized view logs of the database.
   grant select on DBA_CONSTRAINTS to 'synchronization account';  // Grants the privilege to view constraint information for all tables in the database.
   grant select on DBA_CONS_COLUMNS to 'synchronization account';  // Grants the privilege to view information about columns in specified constraints for all tables in the database.
   grant select on all_tab_cols to 'synchronization account';  // Grants the privilege to view information about columns in tables, views, and clusters.
   grant select on sys.obj$ to 'synchronization account';  // Grants the privilege to view objects in the database. The sys.obj$ table is a base table in the Oracle data dictionary that stores all Oracle objects.
   grant select on SYS.COL$ to 'synchronization account';  // Grants the privilege to view column definitions in database tables. SYS.COL$ stores column definition information.
   grant select on sys.USER$ to 'synchronization account';  // Grants the privilege to view the sys.USER$ system table, which contains details about database users.
   grant select on sys.cdef$ to 'synchronization account';  // Grants the privilege to view a system table of the database.
   grant select on sys.con$ to 'synchronization account';  // Grants the privilege to view constraint information. sys.con$ records Oracle constraint information.
   grant select on all_indexes to 'synchronization account';  // Grants the privilege to view all indexes in the database.
   grant select on v_$database to 'synchronization account';  // Grants the privilege to view the v_$database view.
   grant select on V_$ARCHIVE_DEST to 'synchronization account';  // Grants the privilege to view the V_$ARCHIVE_DEST view.
   grant select on v_$log to 'synchronization account';  // Grants the privilege to view the v_$log view, which displays log file information from the control file.
   grant select on v_$logfile to 'synchronization account';  // Grants the privilege to view the v_$logfile view, which contains information about redo log files.
   grant select on v_$archived_log to 'synchronization account';  // Grants the privilege to view the v$archived_log view, which contains information about archived logs.
   grant select on V_$LOGMNR_CONTENTS to 'synchronization account';  // Grants the privilege to view the V_$LOGMNR_CONTENTS view.
   grant select on DUAL to 'synchronization account';   // Grants the privilege to view the DUAL table. DUAL is a virtual table used to conform to SELECT syntax rules. In Oracle, DUAL contains only one record.
   grant select on v_$parameter to 'synchronization account';  // Grants the privilege to view the v_$parameter view. v$parameter is a dynamic data dictionary table that stores database parameter settings.
   grant select any transaction to 'synchronization account';  // Grants the privilege to view any transaction in the database.
   grant execute on SYS.DBMS_LOGMNR to 'synchronization account';  // Grants the privilege to use the LogMiner tool, which helps you analyze transactions and recover lost data.
   grant alter session to 'synchronization account';  // Grants the privilege to modify the database session.
   grant select on dba_objects to 'synchronization account';  // Grants the privilege to view all objects in the database.
   grant select on v_$standby_log to 'synchronization account';  // Grants the privilege to view the v_$standby_log view, which contains information about archived logs of the standby database.
   grant select on v_$ARCHIVE_GAP to 'synchronization account';  // Grants the privilege to query for missing archived logs.
   grant select on sys.ICOL$ to 'synchronization account';  // Grants the privilege to view information about index-organized table columns.
   grant select on V_$THREAD to 'synchronization account';  // Grants the privilege to view instance thread information. The V_$THREAD view is a thread status table in Oracle dynamic performance views. It stores the thread status of each instance in a RAC cluster and is used during incremental parsing to determine the instance status and log source.

   To perform offline full synchronization, also run the following statement to grant the synchronization account the privilege to query all tables.

   grant select any table to 'synchronization account'; 

   For Oracle 12c and later versions, run the following statement to grant log mining privileges. This command is not required for versions earlier than Oracle 12c, as log mining is a built-in feature.

   grant LOGMINING TO 'synchronization account';

Enable logging and switch redo log files

1. To enable archive logging, run the following SQL statements:

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

2. Enable supplemental logging.

   Enable supplemental logging at the database or table level to meet your business requirements:

   // Enable database-level supplemental logging to capture all data changes for real-time synchronization.
   alter database add supplemental log data(primary key) columns; // Enables supplemental logging for the primary key columns of the database.
   alter database add supplemental log data(unique) columns; // Enables supplemental logging for the unique index columns of the database.
   
   // Alternatively, enable table-level supplemental logging:
   alter schema.table add supplemental log data(primary key) columns; // Enables supplemental logging for the primary key columns of a specified table.
   alter schema.table add supplemental log data(unique) columns; // Enables supplemental logging for the unique index columns of a specified table.

3. Switch redo log files.

   After enabling supplemental logging, run the following command multiple times (five times is recommended) to switch the redo log files.

   alter system switch logfile;

   Note

   Running this command multiple times ensures that the new supplemental logging settings are applied to all redo log files, which is critical for complete data capture and recovery.

Check the database character encoding

Run the following command to check the character encoding of your database.

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

• The v$nls_parameters view stores database parameter settings.

• NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET are the database character set and national character set, respectively. They define the encoding for character data in Oracle.

Check table data types

Use a SELECT statement to query the data types of a table. The following example shows how to query the data types for a table named 'tablename'.

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

• COLUMN_NAME: the name of a column in the table.

• DATA_TYPE: the data type of the corresponding column.

• all_tab_columns: a view that stores information about all columns in the tables of the database.

• TABLE_NAME: the name of the table to query. When you run this statement, replace 'tablename' with the name of your table.

You can also execute select * from 'tablename'; to query all information from the target table and obtain the data types.

Single-table batch synchronization

• See Codeless UI Configuration and Script Mode Configuration.

• For a full list of parameters and a sample script, see Appendix: Sample Script and Parameters.

Single-table real-time synchronization

See Configure a real-time synchronization task in DataStudio.

Full-database synchronization

See Configure real-time full-database synchronization.

Configure a batch synchronization task by using the code editor

If you want to configure a batch synchronization task by using the code editor, you must configure the related parameters in the script based on the unified script format requirements. For more information, see Script mode configuration. The following information describes the parameters that you must configure for data sources when you configure a batch synchronization task by using the code editor.

Reader script example

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

{
    "type":"job",
    "version":"2.0",// The version number.
    "steps":[
        { 
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"oracle",// The plugin name.
            "parameter":{
                "postSql":[],// The SQL statement to execute after the task completes.
                "datasource":"",
                "session":[],// The parameters for the database connection session.
                "column":[// The destination columns.
                    "id",
                    "name"
                ],
                "encoding":"UTF-8",// The encoding format.
                "batchSize":1024,// The number of records to submit in a batch.
                "table":"",// The destination table name.
                "preSql":[]// The SQL statement to execute before the task starts.
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The number of error records.
        },
        "speed":{
            "throttle":true,// Enables or disables throttling. If false, the mbps parameter is ignored.
            "concurrent":1, // The maximum number of parallel threads.
            "mbps":"12"// The maximum rate for throttling. Unit: MB/s.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

Writer parameters