Version 24.2.9064

• Establish a Connection
• Configure Oracle LogMiner and Oracle Flashback Query
  • Oracle Oracle LogMiner versus Flashback Query
  • Enable Oracle LogMiner for CData Sync
  • Enable Oracle Flashback Query for CData Sync
• Support for Incremental Replication
• 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.

Establish a Connection

To allow Sync to use data from Oracle, you first must establish a connection to Oracle. Follow these steps to connect Oracle to your Sync account:

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. If you do not see the Configure Connection icon, you need to add the connector according to the instructions in Connections.

5. Enter connection settings on the Settings tab:

   • Connection Name - Enter a connection name of your choice.

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

   • 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.

   • Service Name - Enter the service name of the Oracle database. To obtain this name, submit the following query from the Oracle SQL*PLUS command line:

     global_name (select * from global_name)

6. Click Create & Test to create the connection.

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

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 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 TABLE SchemaName.TableName ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS
   

   Note: 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.

More Information

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