DataWorks:Oracle data source

Preparation 1: Check the version of your Oracle database

The supported Oracle database versions vary based on synchronization scenarios. Check whether the version of your Oracle database is supported before you synchronize data from or to the database.

1. You can execute one of the following statements to check the version of your Oracle database:

   • Statement 1:

     select * from v$version;

   • Statement 2:

     select version from v$instance;

2. If the version of the Oracle database is 12c or 18c, you must execute the following statement to check whether the database can serve as a CDB. If the Oracle database can serve as a CDB, this Oracle database is not supported by real-time synchronization tasks in DataWorks.

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

Preparation 2: Prepare an account that has the required permissions

You must prepare an account that is used to log on to the Oracle database. The account must have the required permissions on the Oracle database.

1. Create an account. For more information, see Create an account to access an Oracle database.

2. Grant the required permissions to the account.

   You can execute the following statements to grant the required permissions to the account. Replace Account for data synchronization with the created account when you execute a specific statement.

   grant create session to 'Account for data synchronization';  // Authorize the synchronization account to access the database. 
   grant connect to 'Account for data synchronization';  // Authorize the synchronization account to connect to the database. 
   grant select on nls_database_parameters to 'Account for data synchronization';  // Authorize the synchronization account to query the settings of nls_database_parameters. 
   grant select on all_users to 'Account for data synchronization';  // Authorize the synchronization account to query all users in the database. 
   grant select on all_objects to 'Account for data synchronization';  // Authorize the synchronization account to query all objects in the database. 
   grant select on DBA_MVIEWS to 'Account for data synchronization';  // Authorize the synchronization account to check the materialized view of the database. 
   grant select on DBA_MVIEW_LOGS to 'Account for data synchronization';  // Authorize the synchronization account to view the materialized view logs of the database. 
   grant select on DBA_CONSTRAINTS to 'Account for data synchronization';  // Authorize the synchronization account to view the constraints on all tables in the database. 
   grant select on DBA_CONS_COLUMNS to 'Account for data synchronization';  // Authorize the synchronization account to view information about the columns that meet the specified constraints on all tables in the database. 
   grant select on all_tab_cols to 'Account for data synchronization';  // Authorize the synchronization account to view information about columns in tables, views, and clusters of the database. 
   grant select on sys.obj$ to 'Account for data synchronization';  // Authorize the synchronization account to view objects in the database. sys.obj$ indicates an object table that is contained in the data dictionary table. The object table contains all objects. 
   grant select on SYS.COL$ to 'Account for data synchronization';  // Authorize the synchronization account to view definitions of columns in tables of the database. SYS.COL$ stores definitions of columns in tables. 
   grant select on sys.USER$ to 'Account for data synchronization';  // Authorize the synchronization account to view the system table of the database. sys.USER$ indicates a default user session service. 
   grant select on sys.cdef$ to 'Account for data synchronization';  // Authorize the synchronization account to view the system table of the database. 
   grant select on sys.con$ to 'Account for data synchronization';  // Authorize the synchronization account to view the constraints of the database. sys.con$ records the constraints. 
   grant select on all_indexes to 'Account for data synchronization';  // Authorize the synchronization account to view all indexes of the database. 
   grant select on v_$database to 'Account for data synchronization';  // Authorize the synchronization account to check the v_$database view of the database. 
   grant select on V_$ARCHIVE_DEST to 'Account for data synchronization';  // Authorize the synchronization account to check the V_$ARCHIVE_DEST view of the database. 
   grant select on v_$log to 'Account for data synchronization';  // Authorize the synchronization account to check the v_$log view of the database. v_$log displays log information about control files. 
   grant select on v_$logfile to 'Account for data synchronization';  // Authorize the synchronization account to check the v_$logfile view of the database. v_$logfile contains information about redo log files. 
   grant select on v_$archived_log to 'Account for data synchronization';  // Authorize the synchronization account to check the v$archived_log view of the database. v$archived_log contains information about archived logs. 
   grant select on V_$LOGMNR_CONTENTS to 'Account for data synchronization';  // Authorize the synchronization account to check the V_$LOGMNR_CONTENTS view of the database. 
   grant select on DUAL to 'Account for data synchronization';   // Authorize the synchronization account to view the DUAL table of the database. DUAL is a virtual table that contains SELECT syntax rules. In Oracle, only one record is retained in the DUAL table. 
   grant select on v_$parameter to 'Account for data synchronization';  // Authorize the synchronization account to check the v_$parameter view of the database. v$parameter is a dynamic dictionary table that stores the values of parameters in the database. 
   grant select any transaction to 'Account for data synchronization';  // Authorize the synchronization account to view transactions of the database. 
   grant execute on SYS.DBMS_LOGMNR to 'Account for data synchronization';  // Authorize the synchronization account to use the LOGMNR tool. The LOGMNR tool helps you analyze transactions and retrieve lost data. 
   grant alter session to 'Account for data synchronization';  // Authorize the synchronization account to modify connection settings of the database. 
   grant select on dba_objects to 'Account for data synchronization';  // Authorize the synchronization account to view all objects in the database. 
   grant select on v_$standby_log to 'Account for data synchronization';  // Authorize the synchronization account to check the v_$standby_log view of the database. v_$standby_log contains archived logs of the secondary database. 
   grant select on v_$ARCHIVE_GAP to 'Account for data synchronization';  // Authorize the synchronization account to query missing archived logs.

   To implement batch synchronization of full data, you must also execute the following statement to grant the query permissions on all tables to the synchronization account:

   grant select any table to 'Account for data synchronization';

   For an Oracle database of Oracle 12c or a later version, you must execute the following statement to grant the log mining permissions to the synchronization account. The log mining feature is a built-in feature for Oracle databases of versions earlier than 12c. You do not need to execute the following statement in such databases.

   grant LOGMINING TO 'Account for data synchronization';

Preparation 3: Enable archive logging and supplemental logging, and switch to another redo log file

1. Execute the following SQL statements to enable archive logging:

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

2. Enable supplemental logging.

   SQL statements:

   alter database add supplemental log data(primary key) columns; // Enable supplemental logging for primary key columns. 
   alter database add supplemental log data(unique) columns; // Enable supplemental logging for unique index columns.

3. Switch to another redo log file.

   After you enable supplemental logging, you must execute the following statement multiple times to switch to another redo log file. We recommend that you execute the following statement for five times:

   alter system switch logfile;

   Note

   This ensures that data can be written to the next log file after the current log file is full. Records of performed operations will not be lost. This facilitates data recovery.

Preparation 4: Check character encoding formats of your Oracle database

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

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

• v$nls_parameters stores values of parameters in the database.

• NLS_CHARACTERSET indicates a database character set. NLS_NCHAR_CHARACTERSET indicates a national character set. These two sets are used to store data of the respective character type.

Preparation 5: Check the data types of tables in your Oracle database

You can execute the SELECT statement to query the data types of tables in the database. Sample statement that is executed to query the data types of the 'tablename' table:

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

• COLUMN_NAME: the name of the column.

• DATA_TYPE: the data type of the column.

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

• TABLE_NAME: the name of the table that you want to query. When you execute the preceding statement, replace 'tablename' with the name of the table that you want to query.

You can also execute the select * from 'tablename'; statement to query the information about the desired table and obtain the data types of columns in the table.

Configure a batch synchronization task to synchronize data of a single table

• For more information about the configuration procedure, see Configure a batch synchronization task by using the codeless UI and Configure a batch synchronization task by using the code editor.

• For information about all parameters that are configured and the code that is run when you use the code editor to configure a batch synchronization task, see Appendix: Code and parameters.

Configure a real-time synchronization task to synchronize data of a single table

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

Configure synchronization settings to implement batch synchronization of all data in a database, real-time synchronization of full data or incremental data in a database, and real-time synchronization of data from sharded tables in a sharded database

For more information about the configuration procedure, see Configure a synchronization task in Data Integration.

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.