DataWorks:Oracle data source

Offline read and write

Real-time read

• Supported:

  Databases with versions later than 11g R2, 12c non cdb, 18c non cdb, 19c non cdb, or 19c cdb.

• Not supported:

  12c cdb and 18c cdb databases.

Confirm the Oracle database version

The supported Oracle database versions vary based on the synchronization scenario. Before you synchronize data, check whether your Oracle database version is supported.

1. You can run either of the following statements to view the version of your Oracle database.

   • 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 it is a cdb database. Real-time sync tasks in Data Integration do not support 12c and 18c cdb Oracle databases.

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

Create an account and configure permissions

You need to plan a database logon account for subsequent operations. This account requires the necessary Oracle permissions.

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

2. Configure permissions.

   You can use the following commands to grant permissions to the account. When you run the statements, replace 'sync_account' with the account that you created.

   grant create session to 'sync_account';  // Grants the logon permission to the sync account.
   grant connect to 'sync_account';  // Grants the database connection permission to the sync account.
   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 the constraint information of all tables.
   grant select on DBA_CONS_COLUMNS to 'sync_account';  // Grants the permission to view information about all columns in the specified constraints of all tables.
   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. SYS.COL$ stores column definitions.
   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. v_$log 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. v_$logfile 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. v$archived_log 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 rules. In Oracle, DUAL contains only one record.
   grant select on v_$parameter to 'sync_account';  // Grants the permission to view the v_$parameter view. v$parameter is a dynamic dictionary table that stores database parameter settings.
   grant select any transaction to 'sync_account';  // Grants the permission to view any transaction.
   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 alter database sessions.
   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. v_$standby_log contains archived logs of the secondary 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 Oracle's dynamic performance views. It stores the thread status of each instance in a RAC cluster and is used to determine the instance status and log source during incremental parsing.

   To perform offline full data synchronization, you must also run the following command to grant the query permission on all tables to the sync account.

   grant select any table to 'sync_account'; 

   For Oracle 12c and later versions, run the following command to grant the log mining permission to the sync account. For versions earlier than Oracle 12c, this command is not required 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. The SQL statements are as follows.

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

2. Enable supplemental logging.

   To enable the appropriate supplemental logging, use the following SQL statement.

   // For real-time synchronization to capture complete data changes, you must enable supplemental logging. You can enable it at the database level. The SQL statements are as follows:
   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 the system switches to the next log file when the current log file is full. This process prevents the loss of operation records and helps with subsequent data restoration.

Check the database character encoding

Run the following command in your database to check its character encoding.

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

• v$nls_parameters stores the settings of database parameters.

• NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET are the database character set and the national character set, respectively. They indicate 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 the database tables.

• TABLE_NAME: The name of the target table to query. When you run the statement, replace 'tablename' with the actual 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 a task in the codeless UI and Configure a task in the code editor.

• For all parameters and a script demo for the code editor, see Appendix: Script demo and parameter descriptions.

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 whole-database synchronization

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

Writer script parameters