Oracle

Oracle Database is a powerful enterprise-grade relational database management system known for its scalability, reliability, and performance in large-scale applications.

Supported Versions and Architectures

• Versions: Oracle 9i, 10g, 11g, 12c, 18c, 19c, 21c+
• Architectures: Single instance, RAC, Data Guard, Active Data Guard, ASM

Supported Data Types

Category	Data Types
Strings	VARCHAR2, CHAR, NVARCHAR2, NCHAR, CLOB, NCLOB, LONG
Numbers	NUMBER, INTEGER, FLOAT, BINARY_FLOAT, BINARY_DOUBLE
Date & Time	DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
Others	RAW, LONG RAW, BLOB, XMLTYPE (19c+)

Limitations

• LogMiner performance: ~20,000 records/second parsing speed
• Name length: Table and column names limited to 30 characters for CDC
• Virtual columns: May require manual target table creation
• XMLTYPE: Only supported in Oracle 19c+
• BFILE: Not supported

Quick Setup Guide

1. Create Database User

Standard mode

CREATE USER clickpipes IDENTIFIED BY your_password;

Multitenant mode

ALTER SESSION SET CONTAINER=cdb$root;
CREATE USER C##clickpipes IDENTIFIED BY your_password CONTAINER=all;

2. Grant Permissions

Full sync only

GRANT CREATE SESSION, SELECT ANY TABLE TO clickpipes;

Full + incremental sync

GRANT CREATE SESSION,
      ALTER SESSION,
      EXECUTE_CATALOG_ROLE,
      SELECT ANY DICTIONARY,
      SELECT ANY TRANSACTION,
      SELECT ANY TABLE
TO clickpipes;

-- For Oracle 12c+
GRANT LOGMINING TO clickpipes;

3. Configure for Incremental Sync

Enable archive log mode:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Enable supplemental logging:

-- For tables with primary keys
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

-- For tables without primary keys
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- Apply changes
ALTER SYSTEM SWITCH LOGFILE;

4. Important Settings

Check connection timeout:

SELECT resource_name, limit FROM dba_profiles
WHERE profile=(SELECT profile FROM dba_users WHERE username = 'CLICKPIPES')
AND RESOURCE_NAME='CONNECT_TIME';

Ensure adequate archive log storage (recommended: 3+ days).