Search docs.molo17.com

    Oracle XStream logo Targeting Oracle with LogMiner Agent

    Overview

    This guide explains how to configure Oracle as a target database when using the LogMiner agent (or simply the standard Oracle JDBC connectivity provided by Gluesync).

    How-to guide

    For a step-by-step walkthrough tested on a running Gluesync, see the how-to guide Choose a key strategy: physical, logical, RRN or computed.

    Target Configuration

    Database Requirements

    • Oracle Database (version 11.2.0.4 or higher)

    • User with appropriate write permissions (INSERT, UPDATE, DELETE, CREATE TABLE if auto-creation is needed)

    • Sufficient storage space for target tables

    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/write access to the tables;

    • Password: Password belonging to the given username;

    • Max connections count: Maximum number of connections the pool can instantiate.

    Custom host credentials

    This can be also set via Rest API by specifying customHostCredentials param.

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

    Specific configuration

    This agent has no specific configuration properties for the target role.

    Updates with bulk operations during CDC

    When Use bulk operations during CDC (useBulkOperationsDuringCDC) is on for an entity, the agent does not apply an update as an UPDATE statement. It loads the change into a staging table, deletes the existing row from the target table and inserts the new version of it. With the option off, updates are applied as UPDATE statements.

    For the target table this has the following effects:

    • UPDATE triggers on the target table do not fire, while DELETE and INSERT triggers do;

    • Foreign keys that reference the table with ON DELETE CASCADE delete their child rows on every update, and ON DELETE SET NULL clears the child columns. A foreign key without a delete rule makes the write fail while child rows exist;

    • Tools that read the redo log of the target, such as LogMiner, XStream or GoldenGate, see a delete and an insert instead of an update;

    • The new row gets a new ROWID, and columns that Gluesync does not write, such as a column with a default value, are set again as for a new row.

    Setup via Rest APIs

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

    curl --location --request PUT 'http://core-hub-ip-address/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": "target_user",
            "password": "password",
            "maxConnectionsCount": 100,
            "enableTls": true,
            "trustStorePath": "/opt/gluesync/data/oracle-tls/truststore.jks",
            "trustStorePassword": "your_trust_store_password"
          },
          "customHostCredentials": {
            "defaultConnectionType": "SID"
          }
    }'