The Oracle data source lets you read data from and write data to Oracle. You can configure synchronization tasks in wizard or script mode. This topic describes Oracle synchronization capabilities supported by DataWorks.
Supported versions
Batch reads and writes
Version | Batch read (Oracle Reader) | Batch write (Oracle Writer) |
Oracle 11.2 or 11gR2 | Supported | Supported |
Oracle 12.1 or 12cR1 | Supported | Supported |
Oracle 12.2 or 12cR2 | Supported (new-version features are unsupported) | Supported (new-version features are unsupported) |
Oracle 18.3 | Supported (new-version features are unsupported) | Supported (new-version features are unsupported) |
Oracle 19.x | Supported (new-version features are unsupported) | Supported (new-version features are unsupported) |
Oracle 21.1 | Supported (new-version features are unsupported) | Supported (new-version features are unsupported) |
Oracle Writer uses the ojdbc6-12.1.1.jar driver.
Real-time reads
Supported:
11g R2 and later,12c non cdb,18c non cdb,19c non cdb, or19c cdbdatabases.Unsupported:
12c cbdand18c cbddatabases.When Oracle is the source of a full-database real-time task, enable archive logging. For details, see Prepare the Oracle environment.
A container database (CDB) is an Oracle 12c and later feature that contains multiple pluggable databases (PDBs).
Limits
NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET for the Oracle instance must be one of AL32UTF8, AL16UTF16, ZHS16GBK, or UTF8.
An Oracle instance supports at most 500 GB of incremental data synchronized in real time per day.
DataWorks real-time reads from an Oracle physical standby only. It reads archived logs, so lag is greater than when reading the primary and depends on how fast archive logs are generated.
Real-time synchronization does not support views; batch synchronization supports reading views.
DataWorks real-time Oracle reads use Oracle LogMiner. Enable supplemental and archive logging on the primary for either primary or standby reads.
Index-organized tables are not supported.
Tables whose columns are all LOB types (BLOB, CLOB, or NCLOB) are not supported.
Chinese characters in Oracle table or column names are not supported.
Oracle table names longer than 30 bytes are not supported.
Supported field types
Field type | Batch read (Oracle Reader) | Batch write (Oracle Writer) | Real-time reads |
NUMBER | Supported | Supported | Supported |
BINARY FLOAT | Supported | Supported | Supported |
BINARY DOUBLE | Supported | Supported | Supported |
CHAR | Supported | Supported | Supported |
NCHAR | Supported | Supported | Supported |
VARCHAR2 | Supported | Supported | Supported |
NVARCHAR2 | Supported | Supported | Supported |
DATE | Supported | Supported | Supported |
TIMESTAMP | Supported | Supported | Supported |
TIMESTAMP WITH TIME ZONE | Supported | Supported | Unsupported |
TIMESTAMP WITH LOCAL TIME ZONE | Supported | Supported | Unsupported |
CLOB | Supported | Supported | Supported |
BLOB | Supported | Supported | Supported |
RAW | Supported | Supported | Supported |
ROWID | Unsupported | Unsupported | Supported |
UROWID | Unsupported | Unsupported | Supported |
FLOAT | Supported | Supported | Supported |
INTERVAL DAY TO SECOND | Unsupported | Unsupported | Supported |
INTERVAL YEAR TO MONTH | Unsupported | Unsupported | Supported |
BFILE | Unsupported | Unsupported | Unsupported |
LONG | Unsupported | Unsupported | Unsupported |
LONG RAW | Unsupported | Unsupported | Unsupported |
NCLOB | Supported | Supported | Unsupported |
STRUCT | Supported | Supported | Unsupported |
User-Defined Types | Unsupported | Unsupported | Unsupported |
AnyType | Unsupported | Unsupported | Unsupported |
AnyData | Unsupported | Unsupported | Unsupported |
AnyDataSet | Unsupported | Unsupported | Unsupported |
XmlType | Unsupported | Unsupported | Unsupported |
Spatial Types | Unsupported | Unsupported | Unsupported |
Media Types | Unsupported | Unsupported | Unsupported |
The following table lists Oracle type mappings for Oracle Reader.
Type category | Oracle data type |
Integer | NUMBER, RAWID, INTEGER, INT, and SMALLINT |
Floating-point | NUMERIC, DECIMAL, FLOAT, DOUBLE PRECISIOON, and REAL |
String | LONG, CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR2, CLOB, NCLOB, CHARACTER, CHARACTER VARYING, CHAR VARYING, NATIONAL CHARACTER, NATIONAL CHAR, NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING, and NCHAR VARYING |
Date and time | TIMESTAMP and DATE |
Boolean | BIT and BOOL |
Binary | BLOB, BFILE, RAW, and LONG RAW |
Before synchronization: Prepare the Oracle environment
Before synchronizing Oracle data in DataWorks, prepare the Oracle environment so that tasks can be configured and run normally.
Check the Oracle database version
Supported Oracle versions vary by synchronization scenario. Check that your version is supported.
Use either of these statements to check the version:
Statement 1:
select * from v$version;Statement 2:
select version from v$instance;
If the Oracle version is
12c, or18c, use this statement to check whether it is acdbdatabase. Data Integration real-time synchronization does not currently support12cand18cversions ofcdbOracle databases.select name,cdb,open_mode,con_id from v$database;
Create an account and grant permissions
Plan a database login account for later operations. It needs the relevant Oracle permissions.
Create an account. For the procedure, see Create an Oracle account.
Grant permissions.
Use these commands to grant permissions. Replace
'sync_user'with the account created above.grant create session to 'sync_user'; //Allow the synchronization account to log on to the database. grant connect to 'sync_user'; //Allow the synchronization account to connect to the database. grant select on nls_database_parameters to 'sync_user'; //Allow the synchronization account to query the nls_database_parameters system settings. grant select on all_users to 'sync_user'; //Allow the synchronization account to query all database users. grant select on all_objects to 'sync_user'; //Allow the synchronization account to query all database objects. grant select on DBA_MVIEWS to 'sync_user'; //Allow the synchronization account to view materialized views. grant select on DBA_MVIEW_LOGS to 'sync_user'; //Allow the synchronization account to view materialized view logs. grant select on DBA_CONSTRAINTS to 'sync_user'; //Allow the synchronization account to view constraints on all database tables. grant select on DBA_CONS_COLUMNS to 'sync_user'; //Allow the synchronization account to view columns in specified constraints on all database tables. grant select on all_tab_cols to 'sync_user'; //Allow the synchronization account to view column information for tables, views, and clusters. grant select on sys.obj$ to 'sync_user'; //Allow the synchronization account to view objects. sys.obj$ is the Oracle dictionary base table containing all objects. grant select on SYS.COL$ to 'sync_user'; //Allow the synchronization account to view column definitions. SYS.COL$ stores table column definitions. grant select on sys.USER$ to 'sync_user'; //Allow the synchronization account to view system tables. sys.USER$ is the default service for user sessions. grant select on sys.cdef$ to 'sync_user'; //Allow the synchronization account to view system tables. grant select on sys.con$ to 'sync_user'; //Allow the synchronization account to view constraints. sys.con$ records Oracle constraint information. grant select on all_indexes to 'sync_user'; //Allow the synchronization account to view all indexes. grant select on v_$database to 'sync_user'; //Allow the synchronization account to view v_$database. grant select on V_$ARCHIVE_DEST to 'sync_user'; //Allow the synchronization account to view V_$ARCHIVE_DEST. grant select on v_$log to 'sync_user'; //Allow the synchronization account to view v_$log, which shows log file information in the control file. grant select on v_$logfile to 'sync_user'; //Allow the synchronization account to view v_$logfile, which contains redo log file information. grant select on v_$archived_log to 'sync_user'; //Allow the synchronization account to view v$archived_log, which contains archive log information. grant select on V_$LOGMNR_CONTENTS to 'sync_user'; //Allow the synchronization account to view V_$LOGMNR_CONTENTS. grant select on DUAL to 'sync_user'; //Allow the synchronization account to view DUAL, a one-row virtual table used in SELECT syntax. grant select on v_$parameter to 'sync_user'; //Allow the synchronization account to view v_$parameter, a dynamic dictionary view of database parameter values. grant select any transaction to 'sync_user'; //Allow the synchronization account to view any transaction. grant execute on SYS.DBMS_LOGMNR to 'sync_user'; //Allow the synchronization account to use LogMiner for transaction analysis and data recovery. grant alter session to 'sync_user'; //Allow the synchronization account to alter the database session. grant select on dba_objects to 'sync_user'; //Allow the synchronization account to view all database objects. grant select on v_$standby_log to 'sync_user'; //Allow the synchronization account to view v_$standby_log, which contains standby archive log information. grant select on v_$ARCHIVE_GAP to 'sync_user'; //Allow the synchronization account to query missing archive logs. grant select on sys.ICOL$ to 'sync_user'; //Allow the synchronization account to view objects. sys.obj$ is the Oracle dictionary base table containing all objects. grant select on V_$THREAD to 'sync_user'; //Allow the synchronization account to view instance thread information. V_$THREAD is an Oracle dynamic performance view of RAC instance thread status used to identify instance state and log sources for incremental parsing.For batch synchronization of full data, also grant the account permission to query all tables:
grant select any table to 'sync_user';Oracle 12c and later require this command to let the account use LogMiner. Earlier versions provide built-in LogMiner and do not require it.
grant LOGMINING TO 'sync_user';
Enable archive and supplemental logging and switch redo log files
Enable archive logging:
shutdown immediate; startup mount; alter database archivelog; alter database open;Enable supplemental logging.
Choose supplemental logging as needed. Example SQL:
//Enable supplemental logging to capture complete changes for real-time synchronization. For database-level logging, run: alter database add supplemental log data(primary key) columns; //Enable supplemental logging for primary key columns in the database. alter database add supplemental log data(unique) columns; //Enable supplemental logging for unique-index columns in the database. //You can instead enable it at the table level: alter schema.table add supplemental log data(primary key) columns; //Enable supplemental logging for primary key columns in the specified table. alter schema.table add supplemental log data(unique) columns; //Enable supplemental logging for unique-index columns in the specified table.Switch redo log files.
After enabling supplemental logging, run the following command multiple times (five times is generally recommended) to switch redo log files.
alter system switch logfile;NoteSwitching multiple times ensures that the current log file can roll over after filling up, preserving operation records for later recovery.
Check database character encoding
Run the following command in the database to check its character encoding:
select * from v$nls_parameters where PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');v$nls_parameters stores database parameter settings.
NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET are the database and national character sets, indicating storage formats for two categories of Oracle character data.
Data Integration currently supports UTF8, AL32UTF8, AL16UTF16, and ZHS16GBK when synchronizing data. Change unsupported character encodings before synchronization.
Check table data types
Use a SQL SELECT statement to query the types of table columns. To inspect 'tablename', run:
select COLUMN_NAME,DATA_TYPE from all_tab_columns where TABLE_NAME='tablename'; COLUMN_NAME: Table column name.
DATA_TYPE: Data type of the column.
all_tab_columns: View containing information about all table columns.
TABLE_NAME: Name of the target table. Replace 'tablename' with the actual table name in the statement.
You can also run select * from 'tablename'; to query all data and obtain types for the target table.
Add a data source
Before you develop a synchronization task in DataWorks, you must add the required data source to DataWorks by following the instructions in Data source configuration. You can view parameter descriptions in the DataWorks console to understand the meanings of the parameters when you add a data source.
Develop synchronization tasks: Oracle workflow
For information about the entry point for and the procedure of configuring a synchronization task, see the following configuration guides.
Configure a single-table batch synchronization task
For the procedure, see Codeless UI configuration, Script mode configuration.
For all script mode parameters and sample scripts, see Appendix: Sample scripts and parameters.
Configure a single-table real-time synchronization task
For the procedure, see Configure a real-time synchronization task (legacy).
Configure full-database batch, full and incremental real-time, and sharded-table real-time synchronization
For the procedure, see Configure real-time full-database synchronization tasks.
FAQs
Real-time synchronization tasks for Oracle, PolarDB, and MySQL repeatedly report errors
Data recovery on a primary-standby setup
In a primary-standby disaster recovery setup, after the primary fails over to a standby, the standby continuously recovers data from the primary through binlogs. Replication can lag, especially because of network delay. Therefore, standby data can differ significantly from the primary and may not be a complete snapshot at the current time.
Consistency constraints
Oracle is an RDBMS that can provide strongly consistent query interfaces. For example, if another writer updates the database after a synchronization task starts, Oracle Reader does not capture the new data because of the database snapshot behavior.
This consistency behavior applies to a single-threaded Oracle Reader. Concurrent extraction based on your configuration cannot strictly guarantee consistency.
When Oracle Reader splits data by splitPk, it starts multiple concurrent tasks. They do not share a read transaction and start at different times, so the resulting data is not a complete, consistent snapshot.
A consistent snapshot across multiple threads is not currently technically achievable. The following engineering options involve trade-offs; choose one based on your needs.
Use single-thread synchronization without data splitting. It is slower but provides good consistency.
Stop other writers so the data remains static, for example by locking the table or stopping standby replication. This may affect online services.
Database encoding
Oracle Reader uses JDBC to extract data. JDBC handles and converts different encodings internally, so you need not specify an encoding.
Incremental synchronization
Oracle Reader extracts data with JDBC SELECT statements. You can use
SELECT…WHERE…for incremental extraction:When an online application writes to the database, it sets modify to the change timestamp for inserts, updates, and logical deletes. Oracle Reader can use a WHERE condition after the prior synchronization timestamp.
For append-only records, use a WHERE condition based on the maximum auto-increment ID from the previous stage.
If no business field distinguishes inserts and updates, Oracle Reader cannot synchronize incrementally and must synchronize all data.
SQL security
Oracle Reader provides querySql so that you can write a SELECT extraction statement. Oracle Reader does not validate querySql for security.
Appendix: Sample scripts and parameters
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 sample 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 script parameters
Parameter | Description | Required | Default value |
datasource | Data source name. In script mode, you can add a data source. The value must match the name of the added data source. | Yes | None |
selectedDatabase | Schema of the database to synchronize. | Yes | None |
table | Name of the table to synchronize, in the form Note For example, if selectedDatabase is | Yes | None |
column | Columns to synchronize from the configured table, described as a JSON array. By default, all columns are selected, for example, ["*"].
| Yes | None |
splitFactor | A split factor that sets the number of data shards. With concurrent tasks, data is split into concurrency × splitFactor shards. For example, concurrency = 5 and splitFactor = 5 produce 5 × 5 = 25 shards across five concurrent threads. Note Recommended range: 1 to 100. An excessively large value can cause an out-of-memory error. | No | 5 |
splitMode | Split mode options:
Note Use splitMode together with splitPk.
| No | randomSampling |
splitPk | When Oracle Reader extracts data, specifying splitPk means you want to split it by the field represented by splitPk. Synchronization starts concurrent tasks to improve efficiency.
Note ROWID cannot be used as splitPk for a view. | No | None |
where | A filter condition. Oracle Reader assembles SQL based on the specified column, table and where condition and extracts data with that SQL. For testing, set WHERE to row_number().
| No | None |
querySql (advanced mode only; wizard mode unsupported) | When a WHERE condition cannot describe your filter, use querySql to write custom SQL. Data Integration then ignores table and column and other settings, filtering data directly with querySql. For example, to join multiple tables, use | No | None |
fetchSize | Number of records retrieved in each batch by the plug-in and database server. This controls network interactions and can substantially improve extraction performance. Note An excessively large fetchSize (> 2,048) can cause an out-of-memory (OOM) error during synchronization. | No | 1,024 |
Writer sample script
{
"type":"job",
"version":"2.0",//Version number.
"steps":[
{
"stepType":"stream",
"parameter":{},
"name":"Reader",
"category":"reader"
},
{
"stepType":"oracle",//Plug-in name.
"parameter":{
"postSql":[],//SQL statements to run after synchronization.
"datasource":"",
"session":[],//Database connection session parameters.
"column":[//Fields.
"id",
"name"
],
"encoding":"UTF-8",//Encoding format.
"batchSize":1024,//Number of records submitted in one batch.
"table":"",//Table name.
"preSql":[]//SQL statements to run before synchronization.
},
"name":"Writer",
"category":"writer"
}
],
"setting":{
"errorLimit":{
"record":"0"//Number of error records.
},
"speed":{
"throttle":true,//When throttle is false, mbps is ignored and throughput is unlimited; when true, throughput is limited.
"concurrent":1, //Job concurrency.
"mbps":"12"//Rate limit. Here, 1 mbps = 1 MB/s.
}
},
"order":{
"hops":[
{
"from":"Reader",
"to":"Writer"
}
]
}
}Writer script parameters
Parameter | Description | Required | Default value |
datasource | Data source name. In script mode, you can add a data source. The value must match the name of the added data source. | Yes | None |
table | Destination table name. If the table schema differs from the configured username, use schema.table as the table format. | Yes | None |
writeMode | Choose the import mode. Only INSERT INTO is supported. On a primary key or unique index conflict, the row is not written and is reported as dirty data. | No | insert into |
column | Destination table fields to write, separated by commas. Example: | Yes | None |
preSql | SQL statements to run before the synchronization task. Wizard mode allows one statement; script mode supports multiple statements, such as clearing old data. | No | None |
postSql | SQL statements to run after the synchronization task. Wizard mode allows one statement; script mode supports multiple statements, such as adding a timestamp. | No | None |
batchSize | Number of records submitted in one batch. This greatly reduces network interactions between Data Integration and Oracle and improves throughput. An excessively large value can cause an out-of-memory (OOM) error during synchronization. | No | 1,024 |