Oracle CDC source connector

Use the Oracle CDC (Change Data Capture) Connector to get data from Oracle Database into Decodable. It captures every INSERT, UPDATE, and DELETE made to a table and sends it to Decodable.

The Oracle CDC Connector is powered by Debezium and uses Oracle’s LogMiner capabilities. Currently, it only works with non-CDB deployments - support for container databases (CDB) is forthcoming.

Features

Delivery guarantee

Exactly once

Write to multiple streams

Yes

Prerequisites

  • The Oracle database must be accessible from the Decodable network.

  • You must have an Oracle user that has permissions to query metadata and run Oracle LogMiner. See below for details.

  • Log archiving must be enabled. See below for details.

  • If you are running Oracle via AWS RDS, you may need to use the rdsadmin package to run the commands below. See the RDS docs for reference.

Steps

If you want to use the Decodable CLI or API to create the connection, you can refer to the Property Name column for information about what the underlying property names are. The connector name is oracle-cdc.

  1. From the Connections page, select Oracle CDC and complete the following fields.

    UI Field

    Property Name

    Description

    Host

    hostname

    The host name for your Oracle database.

    Port

    port

    Optional. The port number to use when connecting to the host.

    Defaults to 1521.

    Service Name

    service-name

    The TNS service name for the database instance or container you are connecting to. This or the SID must be specified, but not both.

    System Identifier (SID)

    sid

    The identifier for the database instance or container you are connecting to. This or the Service Name must be specified, but not both.

    Username

    username

    The username to use to authenticate to Oracle.

    Password

    password

    The password associated with the username. This must be provided as a secret resource.

    Scan Startup Mode

    scan.startup.mode

    Optional. Specifies where in the collection to start reading data when the connection is first started or when it’s restarted. Must be one of the following:

    • initial: At startup, takes an initial snapshot of monitored database tables, then continuously reads the latest binlog entries thereafter.

    • latest-offset: Avoids taking an initial snapshot of monitored database tables upon startup. Instead, reads changes from the end of the binlog, capturing only the modifications made since the connector was initiated or restarted.

    • timestamp: Bypasses the snapshot phase and initiates reading binlog events directly from a specified timestamp.

    Defaults to initial.

    Starting Timestamp

    scan.startup.timestamp-millis

    The timestamp to start reading data from, specified in milliseconds since Unix time.

    Only applicable if Scan Startup Mode is timestamp.

  2. Select Next. The next screen shows a list of tables that Decodable has found from the Oracle host.

  3. Select the tables that you want to ingest data from. Decodable automatically creates a stream for each table selected.

    By default, Decodable uses the schema and table names it reads from your database to construct the stream name as follows: <schema_name>__<table_name>. If a stream already exists with that name, and the schema of the stream matches the schema of the input table, Decodable uses that stream instead.

    Each stream’s schema is automatically translated from Oracle, including:

    • field names

    • data types (See Data Types Mapping for how Oracle types map to Decodable types)

    • primary keys

  4. (Optional) If you want to rename the stream created, you can override the generated stream name.

    1. For example, if you want to limit access to certain streams, you might choose to prefix specific streams and use role-based access control to grant access to certain prefixes. See Roles, groups, and permissions.

  5. Select Next.

  6. Give the newly created connection a Name and Description. Then, select Save.

Setting up the database

The following needs to be done before you can use your Oracle database with the Oracle CDC connector. See each section for detail.

  1. Enable log archiving

  2. Configure user permissions

Log archiving

The Oracle CDC connector uses redo and archive logs to read changes made to your database tables. Follow the instructions below to ensure your database instance is configured correctly. Perform the actions as a user with SYSDBA privileges (or a similarly authorized user).

  1. Ensure that db_recovery_file_dest_size and db_recovery_file_dest are set.

    1. Run the following query to see the current configuration:

      SELECT name, value FROM v$parameter WHERE name LIKE 'db_recovery_%';

      If the fields show empty values it means that they’re not set. Configure them as shown in the next step.

    2. To configure the necessary parameters, run:

      -- NOTE: 10G is our suggestion, but system administrators are free to pick an appropriate value here
ALTER SYSTEM SET db_recovery_file_dest_size = 10G;
ALTER SYSTEM SET db_recovery_file_dest = '<YOUR_FAST_RECOVERY_AREA_DIRECTORY>' scope=spfile;

  2. Ensure log archiving is enabled.

    1. Run the following:

      ARCHIVE LOG LIST;

      It should return Database log mode: Archive Mode. If it doesn’t, see the next step.

    2. If the Database log mode isn’t set to Archive Mode, enable it by running the following:

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

  3. Enable database level supplemental logging:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

User permissions

When creating a connection, the Oracle CDC connector queries the database’s metadata tables to identify tables and their schemas. At runtime, it uses LogMiner to read changes from online redo logs and archive logs.

If you don’t have one already, create a user with the ability to query metadata and run LogMiner as shown below.

Create user (if needed)

As the SYSDBA (or a similarly authorized user), run the following. It will create a suitably privileged user decouser with a tablespace decodable_tblspace.

-- NOTE: 25M is our suggestion, but system administrators are free to pick an appropriate value here
CREATE TABLESPACE decodable_tblspace
       DATAFILE '<YOUR_TABLESPACE_DATAFILE>'
       SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

CREATE USER decouser
       IDENTIFIED BY <YOUR_PASSWORD>
       DEFAULT TABLESPACE decodable_tblspace
       QUOTA UNLIMITED ON decodable_tblspace;

Configure user permissions

This assumes that you’re using the decouser created above. Substitute the username if you are using a different one.

GRANT CREATE SESSION TO decouser;
GRANT SET CONTAINER TO decouser;
GRANT FLASHBACK ANY TABLE TO decouser;
GRANT SELECT ANY TABLE TO decouser;
GRANT SELECT_CATALOG_ROLE TO decouser;
GRANT EXECUTE_CATALOG_ROLE TO decouser;
GRANT SELECT ANY TRANSACTION TO decouser;
GRANT LOGMINING TO decouser;

GRANT CREATE TABLE TO decouser;
GRANT LOCK ANY TABLE TO decouser;
GRANT ALTER ANY TABLE TO decouser;
GRANT CREATE SEQUENCE TO decouser;

GRANT EXECUTE ON DBMS_LOGMNR TO decouser;
GRANT EXECUTE ON DBMS_LOGMNR_D TO decouser;

GRANT SELECT ON V_$DATABASE to decouser;
GRANT SELECT ON V_$LOG TO decouser;
GRANT SELECT ON V_$LOG_HISTORY TO decouser;
GRANT SELECT ON V_$LOGMNR_LOGS TO decouser;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO decouser;
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO decouser;
GRANT SELECT ON V_$LOGFILE TO decouser;
GRANT SELECT ON V_$ARCHIVED_LOG TO decouser;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO decouser;

Not granting all of the above permissions may cause the connector to fail.

Notes

  • The Oracle CDC connector reads change data from both online redo logs and archive logs. It’s the system administrator’s responsibility to ensure that redo/archive log retention and space are sufficiently large enough to handle the volume of data change on the server.

  • The connector doesn’t capture changes that are made in the following schemas: 'SYS', 'SYSTEM', 'DBSNMP', 'APPQOSSYS', 'DBSFWUSER', 'CTXSYS', 'DVSYS', 'OJVMSYS', 'GSMADMIN_INTERNAL', 'ORDDATA', 'MDSYS', 'OLAPSYS', 'LBACSYS', 'OUTLN', 'XDB', 'WMSYS', 'ORDSYS', 'RMAN'

  • Currently, the Oracle CDC connector can’t handle schema changes (column addition/deletion to existing tables) or the addition of new tables to a connection without force restarting the connection. Doing so will discard the connection’s state and force a re-snapshot of all tables (new and existing) on the connection.

  • Oracle GoldenGate isn’t needed to run this connector.

Data Types Mapping

The table below shows the mapping from Oracle data types to Decodable data types. Oracle also allows columns to be declared with ANSI data type names but uses a corresponding Oracle data type internally. For more information, see the Oracle docs.

Oracle Type Decodable Type

NUMBER

DOUBLE

NUMBER(p, s)

(s > 0)

DECIMAL(p, s)

NUMBER(p, s)

(s <= 0, p - s < 3)

TINYINT

NUMBER(p, s)

(s <= 0, 3 <= p - s < 5)

SMALLINT

NUMBER(p, s)

(s <= 0, 5 <= p - s < 10)

INT

NUMBER(p, s)

(s <= 0, 10 <= p - s < 19)

BIGINT

NUMBER(p, s)

(s <= 0, 19 <= p - s < 38)

DECIMAL(p - s, 0)

BINARY_FLOAT

FLOAT

FLOAT(p) (p < 25)

FLOAT

FLOAT(p) (25 <= p < 54)

DOUBLE

BINARY_DOUBLE

DOUBLE

DATE

TIMESTAMP(0)

TIMESTAMP(p)

TIMESTAMP(p)

TIMESTAMP(p) WITH LOCAL TIME ZONE

TIMESTAMP_LTZ(p)

NVARCHAR2(w)

VARCHAR(w) [1]

VARCHAR(w)

VARCHAR(w) [1]

VARCHAR2(w)

VARCHAR(w) [1]

XMLTYPE

STRING

CHAR(w)

CHAR(w) [1]

NCHAR(w)

CHAR(w) [1]

INTERVAL DAY TO SECOND

BIGINT

INTERVAL YEAR TO MONTH

BIGINT
1. The length of the Decodable column is based on the character length of the Oracle column, not its byte length.