Edit

Add Oracle Database CDC source to an eventstream (preview)

This article shows you how to add an Oracle Database Change Data Capture (CDC) source to an eventstream.

The Oracle Database CDC source connector for Microsoft Fabric eventstream allows you to capture a snapshot of the current data in an Oracle database. The connector then monitors and records any future row-level changes to this data. After the changes are captured in the eventstream, you can process this CDC data in real-time and send it to different destinations within Fabric for further processing or analysis. The connector supports Oracle databases whether they're on-premises or in the cloud.

Prerequisites

  • Access to a workspace in the Fabric capacity license mode or the Trial license mode with Contributor or higher permissions.
  • A running Oracle database server (on-premises or cloud-hosted).
  • Your Oracle database should be publicly accessible and not behind a firewall or secured in a virtual network. If it resides in a protected network, connect to it by using Eventstream connector virtual network injection.
  • Oracle LogMiner enabled on your database for CDC capture.
  • A database user with the required permissions for CDC operations.

Supported Oracle versions

The Oracle Database CDC connector supports the following Oracle database versions:

  • Oracle Database 12c (12.1) and later
  • Oracle Database 19c
  • Oracle Database 21c
  • Oracle Autonomous Database

Enable CDC in your Oracle Database

To capture change data from Oracle, you need to enable supplemental logging and configure LogMiner.

Enable supplemental logging

  1. Connect to your Oracle database as a user with DBA privileges.

  2. Enable supplemental logging at the database level by running the following SQL commands:

    -- Enable minimal supplemental logging at the database level
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Enable supplemental logging for all columns (recommended for CDC)
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

  3. Verify that supplemental logging is enabled:

    SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;

    The query should return YES for both columns.

Enable archivelog mode

For CDC to work properly, the database must be in ARCHIVELOG mode.

  1. Check the current archive log mode:

    SELECT LOG_MODE FROM V$DATABASE;

  2. If not already in ARCHIVELOG mode, enable it:

    -- Shut down the database
SHUTDOWN IMMEDIATE;

-- Start in mount mode
STARTUP MOUNT;

-- Enable archivelog mode
ALTER DATABASE ARCHIVELOG;

-- Open the database
ALTER DATABASE OPEN;

Create a CDC user with required privileges

Create a dedicated user for the CDC connector with the necessary permissions:

-- Create a user for CDC
CREATE USER fabric_cdc IDENTIFIED BY <your_password>;

-- Grant basic privileges
GRANT CREATE SESSION TO fabric_cdc;
GRANT SELECT ON V_$DATABASE TO fabric_cdc;
GRANT SELECT ON V_$LOG TO fabric_cdc;
GRANT SELECT ON V_$LOGFILE TO fabric_cdc;
GRANT SELECT ON V_$ARCHIVED_LOG TO fabric_cdc;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO fabric_cdc;

-- Grant LogMiner privileges
GRANT EXECUTE ON DBMS_LOGMNR TO fabric_cdc;
GRANT EXECUTE ON DBMS_LOGMNR_D TO fabric_cdc;
GRANT SELECT ANY TRANSACTION TO fabric_cdc;
GRANT LOGMINING TO fabric_cdc;

-- Grant SELECT on tables you want to capture
GRANT SELECT ON <schema>.<table_name> TO fabric_cdc;

Replace <your_password>, <schema>, and <table_name> with your actual values.

Launch the Select a data source wizard

If you haven't added any source to your eventstream yet, select the Connect data sources tile. You can also select Add source > Connect data sources on the ribbon.

Screenshot that shows the selection of the tile for using an external source.

If you're adding the source to an already published eventstream, switch to Edit mode. On the ribbon, select Add source > Connect data sources.

Screenshot that shows selections for adding external sources.

On the Select a data source page, search for and select Connect on the Oracle DB (CDC) tile.

Screenshot that shows the selection of Oracle Database (CDC) as the source type in the Get events wizard.

Configure and connect to Oracle Database CDC

  1. On the Connect page, in Server, enter your Oracle endpoint.

    You can provide only the host name, or include host, port, and service/SID in a single value. For example: your-oracle-host.example.com:1521/ORCL.

  2. Under Connection credentials, select an existing connection or create a new one:

    • To create a new connection, select New connection and enter:
      • Connection name: A name for this connection.
      • Username: The Oracle database user with CDC permissions.
      • Password: The password for the database user.

  3. Select Connect.

    Screenshot of the new connection page.

  4. After the connection is established, choose the tables to capture by selecting All tables or Enter table name(s). If you choose Enter table name(s), enter an optional comma-separated list of full table identifiers (schemaName.tableName) for the Oracle database tables to monitor.

  5. You can expand Advanced settings to access more configuration options for the Oracle Database CDC source:

    • Snapshot locking mode: Controls whether and for how long the connector holds a table lock. Table locks prevent certain types of table operations from occurring while the connector performs a snapshot.
      • Shared (default): Enables concurrent access to the table, but prevents any session from acquiring an exclusive table lock. The connector acquires a ROW SHARE level lock while it captures table schema.
      • None: Prevents the connector from acquiring any table locks during the snapshot. Use this setting only if no schema changes might occur during snapshot creation.
    • Decimal handling mode: Specifies how the connector should handle floating-point values for NUMBER, DECIMAL, and NUMERIC columns. You can set one of the following options:
      • Precise (default): Represents values precisely by using java.math.BigDecimal values represented in change events in binary form.
      • Double: Represents values by using double values. Using double values is easier, but can result in a loss of precision.
      • String: Encodes values as formatted strings. Using the String option is easier to consume, but results in a loss of semantic information about the real type.
    • Snapshot mode: Specifies the mode that the connector uses to take snapshots of a captured table.
      • Initial (default): The snapshot includes the structure and data of the captured tables. Specify this value to populate topics with a complete representation of the data from the captured tables. If the snapshot completes successfully, the connector doesn't run the snapshot again at the next start.
      • InitialOnly: The snapshot includes the structure and data of the captured tables. The connector performs an initial snapshot and then stops, without processing any subsequent changes.
      • NoData: The snapshot includes only the structure of captured tables. Specify this value if you want the connector to capture data only for changes that occur after the snapshot.

  6. Select Next to continue.

    Screenshot of the Oracle CDC source Advanced settings page.

  7. On Review + connect, review your settings.

  8. Select Add (Eventstream) or Connect (Real-Time hub).

    Screenshot of the Oracle CDC source Review + connect page.

View updated eventstream

  1. You can see the Oracle Database CDC source added to your eventstream in Edit mode.

    Screenshot of streaming Oracle Database CDC source in Edit view.

  2. To implement this newly added Oracle Database CDC source, select Publish. After you complete these steps, your Oracle Database CDC source is available for visualization in the Live view.

    Screenshot of streaming Oracle Database CDC source in Live view.

Related content

Other connectors:

  • Last updated on