Version 24.3.9120

• Prerequisites
  • Requirements
  • Limitations on Incremental Replication
  • Set Up Oracle for Enhanced CDC
• Add the Oracle Connector
• Authenticate to Oracle
  • Security Identifier (SID)
  • Service Name
  • Data Source
• Complete Your Connection

You can use the Oracle connector from the CData Sync application to capture data from Oracle and move it to any supported destination. To do so, you need to add the connector, authenticate to the connector, and complete your connection.

Note: This connection can be used only as a source for the Enhanced Change Data Capture job type.

Prerequisites

Before you add and set up the Oracle source connector, you need to set up the Oracle database for enhanced change data capture (CDC), as explained in the following sections.

Requirements

Before you set up Oracle for enhanced CDC, ensure that you have one of the following supported versions: 12c, 19c, and 21c.

Limitations on Incremental Replication

The Oracle connector cannot capture data changes from the following schemas:

• appqossys
• audsys
• ctxsys
• dvsys
• dbsfwuser
• dbsnmp
• qsmadmin_internal
• lbacsys
• mdsys
• ojvmsys
• olapsys
• orddata
• ordsys
• outln
• sys
• system
• vecsys (Oracle 23+)
• wmsys
• xdb

The connector also cannot capture changes for certain tables:

• nested tables

• index-organized tables with names that have a prefix of SYS_IOT_OVER_

• spatial tables with names that start with MDRT_, MDRS_, or MDXT_

• Compression Advisor tables that match this pattern: CMP[3|4]$[0-9]+

Set Up Oracle for Enhanced CDC

Perform the following steps to enable Oracle LogMiner for tracking changes via the enhanced CDC capability in Sync:

1. Enable ARCHIVELOG mode on your database. To determine whether ARCHIVELOG mode is enabled already, submit the following statement:

   SELECT LOG_MODE FROM V$DATABASE
   

   If the output from this statement does not show ARCHIVELOG as the log mode, submit the following statements to enable ARCHIVELOG mode:

   SHUTDOWN IMMEDIATE;
   STARTUP MOUNT;
   ALTER DATABASE ARCHIVELOG;
   ALTER DATABASE OPEN;
   

2. Ensure that the database retains backups and archive logs for at least twenty-four hours. CData recommends retaining backups and logs for seven days, as shown in this example:

   RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
   

3. Enable minimal supplemental logging (must be enable at the database level).

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
   

4. Enable supplemental logging for every table.

   ALTER TABLE SchemaName.TableName ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS
   

   Note: You can enable supplemental logging for the entire database, as well. However, CData does not recommend that because doing so generates a significant volume of logs. This behavior affects performance because it substantially increases the time that is required to process those logs.

5. Grant your user permission to run Oracle LogMiner by following the instructions below for your specific database.

   Standalone Databases

   Submit the following commands to grant permissions to run Logminer:

   -- connect to the database with 'sys as SYSDBA'
   CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCL/logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
   CREATE USER <Username> IDENTIFIED BY <Password> DEFAULT TABLESPACE logminer_tbs QUOTA UNLIMITED ON logminer_tbs ;    
     
   GRANT CREATE SESSION TO <Username>; 
   GRANT SELECT ON V_$DATABASE to <Username>; 
   GRANT FLASHBACK ANY TABLE TO <Username>; 
   GRANT SELECT ANY TABLE TO <Username>; 
   GRANT SELECT_CATALOG_ROLE TO <Username>; 
   GRANT EXECUTE_CATALOG_ROLE TO <Username>; 
   GRANT SELECT ANY TRANSACTION TO <Username>; 
   GRANT LOGMINING TO <Username>; 
   GRANT CREATE TABLE TO <Username>; 
   GRANT LOCK ANY TABLE TO <Username>; 
   GRANT CREATE SEQUENCE TO <Username>; 
   GRANT EXECUTE ON DBMS_LOGMNR TO <Username>; 
   GRANT EXECUTE ON DBMS_LOGMNR_D TO <Username>; 
   GRANT SELECT ON V_$LOG TO <Username>; 
   GRANT SELECT ON V_$LOG_HISTORY TO <Username>; 
   GRANT SELECT ON V_$LOGMNR_LOGS TO <Username>; 
   GRANT SELECT ON V_$LOGMNR_CONTENTS TO <Username>; 
   GRANT SELECT ON V_$LOGMNR_PARAMETERS TO <Username>; 
   GRANT SELECT ON V_$LOGFILE TO <Username>; 
   GRANT SELECT ON V_$ARCHIVED_LOG TO <Username>; 
   GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO <Username>; 
   GRANT SELECT ON V_$TRANSACTION TO <Username>; 
   GRANT SELECT ON V_$MYSTAT TO <Username>; 
   GRANT SELECT ON V_$STATNAME TO <Username>;
   

   Multi-tenant Databases

   Submit the following commands to grant permissions to run Logminer:

   -- connect to the root container as 'sys as SYSDBA' and create a tablespace
   CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
   
   -- connect to your PDB as 'sys as SYSDBA' and create a tablespace with the same name
   CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
     
   -- reconnect to the root container as 'sys as SYSDBA' and create a common user with access to all containers
   CREATE USER <CommonUser> IDENTIFIED BY <Password> DEFAULT TABLESPACE logminer_tbs QUOTA UNLIMITED ON logminer_tbs CONTAINER=ALL;
   
   GRANT CREATE SESSION TO <CommonUser> CONTAINER=ALL; 
   GRANT SET CONTAINER TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$DATABASE to <CommonUser> CONTAINER=ALL; 
   GRANT FLASHBACK ANY TABLE TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ANY TABLE TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT_CATALOG_ROLE TO <CommonUser> CONTAINER=ALL; 
   GRANT EXECUTE_CATALOG_ROLE TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ANY TRANSACTION TO <CommonUser> CONTAINER=ALL; 
   GRANT LOGMINING TO <CommonUser> CONTAINER=ALL; 
   GRANT CREATE TABLE TO <CommonUser> CONTAINER=ALL; 
   GRANT LOCK ANY TABLE TO <CommonUser> CONTAINER=ALL; 
   GRANT CREATE SEQUENCE TO <CommonUser> CONTAINER=ALL; 
   GRANT EXECUTE ON DBMS_LOGMNR TO <CommonUser> CONTAINER=ALL; 
   GRANT EXECUTE ON DBMS_LOGMNR_D TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$LOG TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$LOG_HISTORY TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$LOGMNR_LOGS TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$LOGMNR_CONTENTS TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$LOGMNR_PARAMETERS TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$LOGFILE TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$ARCHIVED_LOG TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$TRANSACTION TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$MYSTAT TO <CommonUser> CONTAINER=ALL; 
   GRANT SELECT ON V_$STATNAME TO <CommonUser> CONTAINER=ALL; 
   

   Note: An example of a common username is C##LMUSER.

6. Restart your database.

Add the Oracle Connector

To enable Sync to use data from Oracle, you first must add the connector, as follows:

1. Open the Connections page of the Sync dashboard.

2. Click Add Connection to open the Select Connectors page.

3. Click the Sources tab and locate the Oracle (Native) row.

4. Click the Configure Connection icon at the end of that row to open the New Connection page. If the Configure Connection icon is not available, click the Download Connector icon to install the Oracle (Native) connector. For more information about installing new connectors, see Connections.

Authenticate to Oracle

After you add the connector, you need to set the required properties.

On the New Connection page, enter the connection name of your choice.

CData Sync supports authenticating to Oracle in a few ways. Select connection type below to proceed to the relevant section that contains the connection details.

• Service Identifier (default)
• Service Name
• Data Source

Security Identifier (SID)

To connect to Oracle resources by using a SID, specify the following settings:

• User - Enter the username that you use to authenticate to the Oracle database.

• Password - Enter the password that you use to authenticate to the Oracle database.

• Server - Enter the host name or IP address of the server that hosts the Oracle database. The default server is localhost.

• Port - Enter the port number for the Oracle server. The default port is 1521.

• SID - Enter the SID for your Oracle database. The default SID is ORCL.

Service Name

To connect to Oracle resources by using a service name, specify the following settings:

• User - Enter the username that you use to authenticate to the Oracle database.

• Password - Enter the password that you use to authenticate to the Oracle database.

• Server - Enter the host name or IP address of the server that hosts the Oracle database. The default server is localhost.

• Port - Enter the port number for the Oracle server. The default port is 1521.

• Service - Enter the service name for your Oracle database. The default SID is ORCL.

  To obtain the service name, submit the following query from the Oracle SQL*PLUS command line:

  global_name (select * from global_name)

Data Source

To connect to Oracle resources by using a data source, specify the following settings:

• User - Enter the username that you use to authenticate to the Oracle database.

• Password - Enter the password that you use to authenticate to the Oracle database.

• Data Source - Enter the data source for your Oracle database.

Complete Your Connection

To complete your connection:

1. Define advanced connection settings on the Advanced tab. (In most cases, though, you should not need these settings.)

2. Click Create & Test to create your connection.