Version 25.3.9396

• Add the Oracle Connector
• Authenticate to Oracle
  • Oracle Server
  • Oracle Transport Network Substrate
  • LDAP
• Complete Your Connection
• Configure Oracle LogMiner and Oracle Flashback Query
  • Oracle LogMiner versus Flashback Query
  • Enable Oracle LogMiner for CData Sync
  • Enable Oracle Flashback Query for CData Sync
• Support for Incremental Replication
• Set Up Oracle for Reverse ETL
  • Create a New User and Schema
    • Step 1: Create the Schema
    • Step 2: Grant Required Roles
    • Step 3: Grant Read Access to Source Tables
  • Grant Permissions to an Existing User
• More Information

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.

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 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 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 via various connection types. Select your connection type below to proceed to the relevant section that contains the authentication details.

• Server
• Oracle Transport Network Substrate
• LDAP

Oracle Server

To authenticate via the Oracle server, specify the following properties:

• Connection Type - Select Server.

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

• Service Name - Enter the service name of the Oracle database. You can obtain this value by submitting the following query from the Oracle SQL*PLUS command line:

  SELECT * FROM global_name;

  Note: The result of this query returns the global database name, which might not always align with the service name that is defined in the Oracle listener. If there is any uncertainty, verify the result with your DBA or review the tnsnames.ora file to confirm the correct service name.

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

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

• Port - Enter the port that you want to use to connect to the server that hosts the Oracle database.

• Wallet (optional) - Enter the location of the client’s Oracle Wallet.

Oracle Transport Network Substrate

To authenticate via Oracle Transport Network Substrate (TNS), specify the following properties:

• Connection Type - Select OracleTNS.

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

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

• Data Source - Enter the Oracle Net Services Name, the Connect Descriptor (also known as the TNS Connect String), or a connection name that identifies the database to which you want to connect. You should be able to find these values in your tnsnames.ora file.

  Example: DataSource='(DESCRIPTION=(ADDRESS=(ProtocolAddressInformation))(CONNECT_DATA= (SERVICE_NAME=ServiceName)))'

LDAP

To authenticate via the LDAP server, specify the following properties:

• Connection Type - Select LDAP.

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

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

• LDAP Uri - Enter the LDAP URI that you use to connect to the LDAP server.

  Example: `LDAPUri=ldap://myldap.com:389/SERVICE_EXMPL,cn=OracleContext,dc=example,dc=com; User=OracleUser; Password=OracleUserPassword;

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.

Configure Oracle LogMiner and Oracle Flashback Query

Oracle offers two methods for tracking the changes from your source database:

• Oracle LogMiner

• Oracle Flashback Query

Note: If both of these methods are enabled on a table, Sync uses Oracle LogMiner.

The following sections explain these two methods as well as how to enable them.

Oracle LogMiner versus Flashback Query

Oracle LogMiner tracks changes in the redo logs, allowing Sync to read those logs to determine the changes to a specific table. The main difference between LogMiner and Flashback is that Sync does not interact with the source table when you use LogMiner. Instead, Sync reads directly from logs, which positively impacts performance because the demand on the source database is lower.

Oracle Flashback Query is a lightweight solution that provides an efficient tracking mechanism for CData Sync. After you configure Flashback Query on your tables, Sync can execute Flashback queries on the source table to determine what changes were made to the table since the previous run.

Enable Oracle LogMiner for CData Sync

1. Enable ARCHIVELOG mode on your database. To determine whether ARCHIVELOG mode is already enabled, 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. Note that 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 supplemental logging either at the database or table level by submitting the appropriate statement below. Supplemental logging is required for only one of those levels.

   Database Level

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS
   

   Table Level

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

   Notes:

   • In order for LogMiner to work correctly, you must add supplemental logging for the database at the table level. Although you can enable supplemental logging either for specific tables or for a whole database, CData does not recommend enabling it directly on a database 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.

   • To use Oracle LogMiner, each table must have at least one primary key.

4. Grant your user permission to run LogMiner by following the instructions below for your specific database type:

   Standalone Databases

   Submit the following commands to grant permissions to run Logminer:

   GRANT SELECT ON SYS.V_$DATABASE TO <Username>;
   GRANT SELECT ON SYS.V_$PARAMETER TO <Username>;
   GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO <Username>;
   GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO <Username>;
   GRANT EXECUTE ON DBMS_LOGMNR TO <Username>;
   GRANT EXECUTE ON DBMS_LOGMNR_D TO <Username>;
   GRANT SELECT ANY TRANSACTION TO <Username>;
   GRANT EXECUTE_CATALOG_ROLE TO <Username>;
      
   

   Multitenant Container Databases

   1. Execute the following command before you grant permissions to LogMiner:

      ALTER SESSION SET CONTAINER=CDB$ROOT;

   2. Submit the follow commands to grant permissions to run LogMiner:

      GRANT SELECT ON SYS.V_$DATABASE TO <Username>;
      GRANT SELECT ON SYS.V_$PARAMETER TO <Username>;
      GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO <Username>;
      GRANT SELECT ON SYS.V_$ARCHIVE_DEST TO <Username>;
      GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO <Username>;
      GRANT EXECUTE ON DBMS_LOGMNR TO <Username>;
      GRANT EXECUTE ON DBMS_LOGMNR_D TO <Username>;
      GRANT SELECT ANY TRANSACTION TO <Username>;
      GRANT EXECUTE_CATALOG_ROLE TO <Username>;
      

5. (For Oracle 12 and higher) Submit the following command to grant logmining permissions to your user:

   GRANT LOGMINING TO <Username>;
   

6. Restart your database.

7. Create a job in Sync with you Oracle source and select the Change Data Capture option.

Enable Oracle Flashback Query for CData Sync

1. Set up the tablespace by submitting the following statement. Note: You need to connect as the system user.

   CREATE TABLESPACE TablespaceName DATAFILE 'YourLocalFilepath' SIZE 100M AUTOEXTEND ON MAXSIZE 200M;
   

   In the statement above, YourLocalFilepath specifies the location on your local machine where you want to create your data files. The filename in your path must have the .dbf extension, as shown in this example:

   CREATE TABLESPACE UserData DATAFILE 'C:\Oracle19c\oradata\YourDataFile.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 200M;
   

2. Create an archive by submitting the following statement:

   CREATE FLASHBACK ARCHIVE ArchiveName TABLESPACE TablespaceName RETENTION 7 DAY;
   

   In this statement, RETENTION specifies the time period for which archive information is kept in your database. As a best practice, set a larger retention window so that Sync has time to resolve conflicts and errors. If the last successful job run is outside the retention period (for example, RETENTION is set to seven days, but the last successful synchronization was eight days earlier), Sync automatically replicates the full table to ensure that no changes are missed.

3. Enable Flashback Query on each table by submitting the following statement. (You need a separate statement for each table on which you want to enable Flashback Query.)

   ALTER TABLE TableName FLASHBACK ARCHIVE ArchiveName;
   

   Note: To use Flashback Query, each table must have at least one primary key.

4. Restart your database.

5. Create a job in Sync with you Oracle source and select the Change Data Capture option.

Support for Incremental Replication

The Oracle source supports incremental replication. Incremental replication reduces the workload tremendously and minimizes bandwidth use and latency of synchronization. Moving data in increments offers great flexibility when you are dealing with slow APIs or daily quotas.

You must configure the incremental check column based on the Oracle source columns.

For details about how to set up incremental replication, see Incremental Replication.

Set Up Oracle for Reverse ETL

To use Oracle as a data source for Reverse ETL, you first need to create a new user and pre-create the CDATA_SYNC_DELTA_SNAPSHOT user and schema and add some permissions. As an alternative, you can also grant permissions to an existing user. Both of these methods are explained in the following sections.

Create a New User and Schema

Step 1: Create the Schema

Create a new Oracle user named CDATA_SYNC_DELTA_SNAPSHOT. This action automatically creates a schema with the same name.

Step 2: Grant Required Roles

Grant the following roles to the new user:

• CONNECT - Allows the user to connect to the database.

• RESOURCE - Grants general privileges, including the ability to create objects and perform DDL operations within the schema.

Step 3: Grant Read Access to Source Tables

To enable the new user to read from source tables, grant SELECT privileges. You can do this in two ways:

Per Table

GRANT SELECT on <LocalUser>.<TableName> TO CDATA_SYNC_DELTA_SNAPSHOT

For All Tables in a Tablespace

BEGIN FOR t IN (SELECT table_name FROM user_tables where TABLESPACE_NAME=<TBS_Name>) LOOP EXECUTE IMMEDIATE 'GRANT SELECT ON <LocalUser>.' || t.table_name || ' TO CDATA_SYNC_DELTA_SNAPSHOT'; END LOOP; END; / 

Grant Permissions to an Existing User

As an alternative, you can grant an existing user permissions to create, alter, and delete tables in the CDATA_SYNC_DELTA_SNAPSHOT schema. Then, you can configure Sync to connect to Oracle with that user. However, this method requires the user to have the CREATE ANY TABLE system privilege, which allows table creation in any schema.

More Information

For more information about interactions between CData Sync and Oracle, see Oracle Connector for CData Sync.