Oracle CDC source connector

Features

Connector name

oracle-cdc

Delivery guarantee

Exactly once

Supported task sizes

M, L

Multiplex capability

A single instance of this connector can read from multiple tables and schemas in a single database.

See Ingesting data from multiple tables.

Supported stream types

Change stream

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

  • 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. Contact Decodable support for more details.

  • 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.

Configuration properties

If you are using the CLI to create or edit a connection with this connector, you must use the declarative approach. You can generate the connection definition for the tables that you want to ingest using decodable connection scan.

Use the properties below to configure the connector. Refer to the documentation for instructions on creating and editing connections.

Property Description Required Default

Basic

host

The host of the Oracle server

Yes

port

The port of the Oracle server

—

1521

service-name

The TNS service name for the database instance or container you are connecting to.

One of service-name or sid must be specified, but not both.

sid

The identifier for the database instance or container you are connecting to.

username

The username to use to authenticate to Oracle

Yes

password

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

Yes

Prerequisites

  • The Oracle database must be accessible from the Decodable network. Connectivity options include AWS PrivateLink, SSH tunnels, and allowing connections from the Decodable published IP addresses.

  • 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.

  • 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.

Ingesting data from multiple tables

A single instance of this connector can read from multiple tables and schemas in a single database.

If you are using the CLI to create or edit a connection with this connector, you must use the declarative approach. You can generate the connection definition for the tables that you want to ingest using decodable connection scan.

Resource specifier keys

The following resource specifier keys are available:

Name Description

schema-name

The schema name

table-name

The table name

Redo log retention

If the connection is stopped or in a failed state for longer than the redo log’s retention period, the connection will fail when it’s restarted. This is because for CDC to work it needs a contiguous series of redo log entries.

If you want to restart the connection in this situation you must discard its current state. By doing this, the initial snapshot of the required tables will be taken again and then the redo log used for subsequent reads.

To do this do, one of the following:

  1. In the Decodable Web UI, select Start and under Starting State select Reset current state and start from the initial state

  2. In the Decodable CLI, do one of the following:

    1. Use connection activate and add the --force flag, for example:

      decodable connection activate cef0e708 --force

      or

    2. Use query with a suitable specifier for the connection (such as --name) and add the --operation reset-state argument, for example:

      decodable query --name customers-source --operation reset-state

Setting up the Oracle database for CDC

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.

Data types mapping

The following table shows the Decodable data types that are generated from the corresponding Oracle 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.