Search

Oracle

Version 23.4.8843

Oracle

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 Flashback Query and Oracle LogMiner

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

  • Oracle Flashback Query

  • Oracle LogMiner

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

Oracle Flashback Query versus Oracle LogMiner

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.

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.

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.

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

  4. Ensure that the user has the correct permissions for viewing the LogMiner output.

  5. Restart your database.

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