Static Oracle Triggers Setup Guide

Prerequisites

Before setting up the Triggers agent, ensure you have:

  • Oracle Database (version 11.2g or higher)

  • A database user with permissions to:

    • Create tables (for the audit log)

    • Create triggers

    • Create sequences

    • Read target tables

To create a valid user for Gluesync on your Oracle database to run GDC you can run/adapt the following query: 
CREATE USER GSUSER IDENTIFIED BY password;

ALTER USER GSUSER quota unlimited on USERS;

GRANT CREATE SESSION TO GSUSER;
GRANT CREATE TABLE TO GSUSER;
GRANT SELECT ANY TABLE TO GSUSER;
GRANT CREATE TRIGGER TO GSUSER;
GRANT CREATE ANY TRIGGER TO GSUSER;
GRANT CREATE ANY SEQUENCE TO GSUSER;

Setup via Web UI

  • Hostname / IP Address: DNS record or IP Address of your server;

  • Port: Optional, defaults to 1521;

  • Database name: Name of your database;

  • Username: Username with read access to the source tables;

  • Password: Password belonging to the given username.

After the connection is successfully established, the agent will create the following table within your database:

  • a tablename_CDC table for each table you are replicating.

It will also create the following sequences within your database:

  • a GS_SEQUENCE_schemaname_tablename_CDC for each table you are replicating.

and lastly a trigger for each of the tables you are replicating.

Custom properties

  • Timeout seconds: (optional, defaults to 60 seconds) Number of seconds to set as timeout for operations involving communication with the server;

  • CDB name: (optional, defaults to NULL) Name of the container database of your scope, required in case of multi-tenant;

  • PDB name: (optional, defaults to NULL) Name of the pluggable database that contains the data you need to replicate, required in case of multi-tenant;

  • Connection type: (optional, defaults to SID) Connection type used to connect to Oracle. Available types are: SID or SERVICE_NAME;

  • Xstream server name: (defaults to GSXOUT) The name of the Xstream outbound server used by Gluesync to detect table changes;

  • Source change retention: (optional, defaults to 5) Number of retention days preserved in the Gluesync GDC CDC table inside your database;

  • Use partition: (optional, defaults to false) Enables partitioning at CDC table, this ensures that no locks are being made while purging old records from that table as well as preserving the Oracle database well in shape. Partitioning is available only on Oracle Enterprise editions, check compatibility with your Oracle database instance before turning on this functionality;

  • Trigger page size: (optional, default to 10000) The max buffer size of rows within a single transaction that a trigger is capable of buffering before writing actions to CDC table, works only starting from version 11.2g and above;

  • Thin net connect timeout: (optional, defaults to 60) The connect timeout controls how much time is allowed to connect the socket to the database;

  • Thin read timeout: (optional, defaults to 60) Read timeout while reading from the socket;

  • Down hosts timeout: (optional, defaults to 60) To specify the amount of time in seconds that information about the down state of server hosts is kept in the driver’s cache;

  • Thin outbound connect timeout: (optional, defaults to 60) The outbound connect timeout controls the time allowed to connect the socket, let the server accept the connection to the desired service, negotiate the NS protocol as well as complete the ASO negotiation.

Specific configuration

This agent has no specific configuration properties.

Setup via Rest APIs

Here following an example of calling the CoreHub’s Rest API via curl to setup the connection for this Agent.

Connect the agent

curl --location --request PUT 'http://core-hub-ip-address:1717/pipelines/{pipelineId}/agents/{agentId}/config/credentials' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--data '{
        "hostCredentials": {
        "connectionName": "myAgentNickName",
        "host": "host-address",
        "port": 1521,
        "databaseName": "db_name",
        "username": "",
        "password": "",
        "maxConnectionsCount": 100,
        "enableTls": true,
        "certificatePath": "/myPath/cert.pem"
      }
}'

Addition to standard CDC table

Transaction consumed column

Oracle GDC-based source connector comes with an additional column named TRANSACTION_CONSUMED, this column helps the Gluesync engine identify changes that are coming into the CDC table as deferred commits (seen happening when also other CDC tools are using this data source as a target database).

Here following are explained the logical steps to better understand how this column is being used:

  1. Gluesync reads transactions from the CDC table with a transaction ID greater than the last transaction ID read and with TRANSACTION_CONSUMED = 0;

  2. For each transaction read the column TRANSACTION_CONSUMED = -1 is set;

  3. The data is copied to the target and the writing confirmation is waited for;

  4. Gluesync updates the state preservation table with the latest synchronized transaction;

  5. Gluesync updates the column TRANSACTION_CONSUMED = 1 for all transactions with value TRANSACTION_CONSUMED = -1 and transaction ID less than/equal to the last transaction saved on state preservation;

  6. If Gluesync goes into error the flow is reset, all rows with TRANSACTION_CONSUMED = -1 are reset to TRANSACTION_CONSUMED = 0 and Gluesync starts reading again from the last confirmed transaction on the state preservation table.

Working with Before & After images

Before & after images are a feature that allows Gluesync to track the changes that have occurred in your database and comparing them with their previous values. This enables Gluesync to apply only the changes that have occurred, saving bandwidth and processing time.

This agent does not support Before & After images.