Oracle Database

The Conduit Platform by default supports Oracle Database as a source and a destination.

The Oracle Database source can connect to and emit records from a table.

Required Configurations

Name	Description	Required	Default
url	Connection string for the Oracle database. Example: oracle://admin:[email protected]:1521/orclpdb1	Yes
table	The table the source connector should read from.	Yes
orderingColumn	The name of a column that the connector will use for ordering rows. The values must be unique and suitable for sorting, otherwise, the snapshot won't work correctly.	Yes

Looking for something else? See advanced configurations.

Initial Snapshot

Snapshot mode is enabled by default. When the source connector starts, the source connector obtains a read-only lock on the table, reads all rows of the table in batches using SELECT, FETCH NEXT, and ORDER BY statements. After reading all rows in the initial snapshot, the connector releases its lock and switches to CDC mode.

Note: The default snapshot mode can be disabled by setting snapshot to false in the configuration.

Updates

The source connector utilizes Change Data Capture (CDC) to detect changes in an Oracle Database table by creating a tracking table and trigger. Configuration of an Oracle Database user with the necessary privileges is required.

The tracking table and trigger will inherit the same names as the source table, prefixed with CONDUIT, unless otherwise specified in the configuration. Additionally, the tracking table includes all columns from the source table along with the following additional columns:

Column	Description
CONNECTOR_TRACKING_ID	Auto increment index for the position.
CONNECTOR_OPERATION_TYPE	Operation type: INSERT, UPDATE, or DELETE.
CONNECTOR_TRACKING_CREATED_AT	Date when the event was added to the tracking table.

An event record is generated in the tracking table whenever INSERT, UPDATE, or DELETE operations are executed on an Oracle Database table. The queries retrieving these event records from the tracking table resemble those used in snapshot mode, but with CONNECTOR_TRACKING_ID serving as the ordering column.

An Ack function will collect the CONNECTOR_TRACKING_ID of the event records that have been successfully applied and are later removed from the tracking table every 5 seconds or when the connection is closed.

Position Example

The mode field represents the mode of the iterator (snapshot or cdc).

The last_processed_val field represents the last processed element value, which depends on the iterator mode.

{
  "mode": "snapshot",
  "last_processed_val": 1
}

Key Handling

The keyColumns field is optional. If left empty, the connector requests the database for the list of primary keys of the specified table. In cases where the table lacks primary keys, the orderingColumn field's value serves as the keyColumns value.

User privileges

The Oracle user you provide to the Conduit Platform must have privileges to CREATE TABLE and ADMINISTER DATABASE TRIGGER. To do this, run the following queries against your Oracle Database instance using Oracle SQL developer or SQL*Plus.

1. Create a unique user for the platform. You may replace conduit_user with whatever you prefer.

SQL> CREATE USER conduit_user IDENTIFIED BY very-secure-password;

2. Grant the CREATE TABLE privilege to the user for the table. Replace my_table with your respective table name.

SQL> GRANT CREATE TABLE TO conduit_user ON my_table;

3. Grant the ADMINISTER DATABASE TRIGGER privilege to the user on the table. Replace conduit_user with the user you created in step 1 and my_table with your respective table name.

SQL> GRANT ADMINISTER DATABASE TRIGGER TO conduit_user ON my_table;

If you do not have the privileges to perform above actions, please contact your Oracle administrator.

Advanced Configurations

Name	Description	Required	Default
keyColumns	Comma-separated list of column names to build the sdk.Record.Key. Learn more: Key handling.	No
snapshot	Enable or disable snapshot of entire table before starting CDC mode. Options: true or false.	No	true
columns	The table the database connector should read from.	No
batchSize	Size of rows batch. Min is 1 and max is 100000.	No	1000
snapshotTable	Name of the snapshot table.	No
trackingTable	Name of the tracking table to be used in CDC.	No
trigger	Name of the trigger to be used in CDC.	No

Known Limitations

• Changes occasionally require adjustments to columns in an Oracle Database table. When such changes occur, your Oracle Database administrator must ensure they are also applied to the tracking table.
• All tracking information solely resides within the Oracle Database. If the tracking table is deleted, the tracking process will restart from the beginning by initiating a new snapshot of the table. This could potentially result in unintended replication of data downstream.
• The conversion between Oracle and Go types dictates representing boolean values as 0 or 1 due to Oracle's lack of native support for a boolean type. This representation is achieved in Oracle using NUMBER(1,0), while in Go, it is simply represented as bool.
• Updating the configuration can cause completely unexpected results.

Amazon RDS Setup

Requirements

1. Configure Security Groups
2. Configure Network ACLs (only if your Oracle Database is within a VPC)
3. Enable Oracle Database User Priviledges (required for CDC connection)

Configure Security Groups

To allow access to your Oracle Database from Conduit Platform IPs, follow these steps:

1. Go to Amazon RDS in your AWS account.
2. Go to Databases in the Amazon RDS menu.
3. Click into the Oracle Database instance you'd like to use.
4. In the Connectivity & security section, under Security and VPC security groups, click your security group by clicking the blue hyperlink.
5. You should now see a list of Security Groups, click into the selected security group by clicking the blue hyperlink under Security group ID.
6. Under Inbound rules, click the Edit inbound rules button.
7. Click the Add rule button.
8. Set Protocol to TCP.
9. Set Port to the port of your database. For Oracle Database it's 1521 by default.
10. Set the Source to include the Conduit Platform IPs.

Configure Network ACLs

You only need to update this if your database is within a VPC.

To allow access to your Oracle Database from Conduit Platform IPs, follow these steps:

1. Go to Amazon RDS in your AWS account.
2. Go to Databases in the Amazon RDS menu.
3. Click into the Oracle Database instance you'd like to use.
4. In the Connectivity & security section, under Networking and VPC, click your VPC by clicking the blue hyperlink.
5. You should now see a list of Your VPCs. Click into the selected VPC by clicking the blue hyperlink under VPC ID.
6. In your VPC Details, click the blue hyperlink under Main network ACL.
7. You should now see a list of Network ACLs. Click into the selected Network ACL by clicking the blue hyperlink under Network ACL ID.
8. You will update both the Inbound and Outbound Rules of the Network ACL to contain ALL - 0.0.0.0/0 - ALLOW.
9. If your Inbound and Outbound Rules do not contain ALL - 0.0.0.0/0 - ALLOW—update the Source to allow Conduit Platform IPs both Inbound and Outbound.

Oracle Database User Privileges

When using Amazon RDS you can connect with your Oracle Database in the following two ways. In both cases, you will need an Oracle DB endpoint.

• Oracle SQL Developer
• SQL*Plus

Oracle DB Endpoint in Amazon RDS

Here are instructions on how to find your Amazon RDS Oracle Database endpoint and port.

1. Go to Amazon RDS in your AWS account.
2. Go to Databases in the Amazon RDS menu.
3. Click into the Oracle Database instance you'd like to use.
4. In the Connectivity & security section, under Endpoint & port, find the Endpoint and Port values for your Oracle Database instance.