DataWorks:Oracle data source

Offline read and write

Real-time read

• Supported:

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

• Not supported:

  12c CDB and 18c CDB databases.

Confirm the Oracle database version

The supported Oracle database versions vary depending on the synchronization scenario. Before you start, verify that your Oracle database version is supported.

1. Run either of the following statements to view the Oracle database version.

   • Statement 1:

     select * from v$version;

   • Statement 2:

     select version from v$instance;

2. If the database version is 12c or 18c, run the following statement to check whether the database is a CDB. Data Integration real-time sync tasks do not support 12c and 18c CDB versions of Oracle databases.

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

Create an account and configure permissions

You need to create a database logon account that has the required permissions for Oracle.

1. Create an account. For more information, see Create an Oracle account.

2. Configure permissions.

   Run the following commands to grant permissions to the account. Replace 'sync_account' with the account that you created.

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

   To perform offline full data synchronization, run the following command to grant the account the permission to query all tables.

   grant select any table to 'sync_account'; 

   For Oracle 12c and later, run the following command to grant the account the log mining permission. For versions earlier than Oracle 12c, you do not need to run this command because the log mining feature is built-in.

   grant LOGMINING TO 'sync_account';

Enable archived logs and supplemental logging, and switch Redo log files

1. Enable archived logs by running the following SQL statements.

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

2. Enable supplemental logging.

   You can enable supplemental logging as needed by running the following SQL statements.

   // To capture complete data changes during real-time synchronization, enable supplemental logging. You can enable it at the database level by running the following SQL statements:
   alter database add supplemental log data(primary key) columns; // Enables supplemental logging for primary key columns of the database.
   alter database add supplemental log data(unique) columns; // Enables supplemental logging for unique index columns of the database.
   
   // You can also enable it at the table level:
   alter schema.table add supplemental log data(primary key) columns; // Enables supplemental logging for primary key columns of a specific table.
   alter schema.table add supplemental log data(unique) columns; // Enables supplemental logging for unique index columns of a specific table.

3. Switch Redo log files.

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

   alter system switch logfile;

   Note

   Running the command multiple times ensures that when the current log file is full, the system switches to the next one. This prevents the loss of operation records and facilitates data restoration.

Check the database character encoding

Run the following statement to check the character encoding formats of the database:

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

• v$nls_parameters stores the settings for database parameters.

• NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET are the database character set and the national character set. They specify the storage class for the two main types of character data in Oracle.

Check the data types of database tables

You can use a SELECT statement to query the data types in a database table. The following example shows how to view the data types in the 'tablename' table.

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

• COLUMN_NAME: The name of the table column.

• DATA_TYPE: The data type of the corresponding column.

• all_tab_columns: A view that stores information about all columns in database tables.

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

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

Guide to configuring an offline sync task for a single table

• For more information about the procedure, see Configure using the codeless UI and Configure using the code editor.

• For a full list of parameters and a script demo for the code editor, see Appendix: Script demo and parameter description.

Guide to configuring a real-time sync task for a single table

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

Guide to configuring synchronization for an entire database, including offline, real-time full and incremental, and sharded databases

For more information about the procedure, see Configure a real-time sync task for an entire database.

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 Configure a batch synchronization task by using the code editor. 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 demo

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

Writer script demo

{
    "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 data synchronization task.
                "datasource":"",
                "session":[],// The database connection session parameters.
                "column":[// The fields.
                    "id",
                    "name"
                ],
                "encoding":"UTF-8",// The encoding format.
                "batchSize":1024,// The number of records in a batch.
                "table":"",// The table name.
                "preSql":[]// The SQL statement to execute before the data synchronization task.
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The number of error records.
        },
        "speed":{
            "throttle":true,// If throttle is false, the mbps parameter is ignored and throttling is disabled. If throttle is true, throttling is enabled.
            "concurrent":1, // The number of concurrent jobs.
            "mbps":"12"// The throttling rate. 1 mbps = 1 MB/s.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

Writer script parameters