Skip to main content
Feedback

Oracle CDC overview

Updated 

Data Integration uses the capabilities of Oracle LogMiner to establish a robust and effective Change Data Capture (CDC) feature for Oracle databases. Our internally developed solution maximizes the potential of Oracle LogMiner, ensuring a dependable and precise method for capturing and handling alterations within the Oracle database. By capturing table inserts, updates, and deletes, CDC enables applications to efficiently use real-time data changes.

note

Oracle CDC is available on the Enterprise plan only. To enable this feature, contact your Boomi Account representative.

Oracle Change Data Capture (CDC)

Change Data Capture (CDC) is a feature in Oracle Database that identifies and captures data changes in tables. Instead of querying the entire table to check for modifications, CDC enables you to extract only the changed data, reducing resource usage and improving application performance.

Benefits of CDC

  • Real-time data integration: CDC provides the immediate capture and propagation of data changes, enabling real-time data warehousing and analytics.
  • Reduced load on the source database: By capturing only the changes, CDC minimizes the impact on the source database, improving system performance.
  • Reliable data synchronization: CDC ensures that the target system receives a consistent and accurate representation of data changes.

Working with CDC

The Data Integration CDC solution operates by continuously observing the database's Redo-log, which maintains a comprehensive history of all data modifications. Once a transaction commits, the database produces corresponding redo log entries to record every specific change. Using the LogMiner interface, we extract information from Redo-logs. Oracle manages these archived logs, which effectively record and store the changes made to the data.

CDC Point in Time position feature

The CDC Point in Time Position feature provides deeper insights into the operational details of a Data Flow's streaming process. This feature is essential for data recovery and synchronization, enabling you to locate and retrieve data from a specific point in history using the exact information stored in the CDC log position. For more information, refer to CDC Point in Time position topic.

A Sequence CDC deployment

Discrepancies in transaction records can occur when two users simultaneously execute identical transactions, resulting in conflicts in the timestamp field. Data Integration implemented a sequence Change Data Capture (CDC) mechanism to tackle this issue.

Data Integration has enhanced each emitted record from the database by incorporating two extra metadata fields: __transaction_id and __transaction_order.

  • The __transaction_id field serves as a unique identifier for each transaction, ensuring no two transactions share the same identifier. This uniqueness enables precise identification and differentiation between transactions, thereby mitigating conflicts that arise from identical timestamps.

  • The __transaction_order field denotes the order in which the database emitted the transactions. By incorporating this field, the sequencing of transactions can be accurately maintained, enabling downstream systems such as Apache Kafka or AWS Kinesis to process and order transactions correctly.

The inclusion of these metadata fields guarantees that the ordering of transactions persists throughout the Data Flow. As a result, smooth and accurate transaction flows are achieved, resolving the discrepancies that arose from transactions with identical timestamps.

The following table shows the additional fields: A 'Sequence' CDC deployment

For further details about Change Data Capture (CDC) Metadata Fields, refer to our Database Overview document.

Load tables with log-snapshot tables

Loading your data from a database to a target using CDC has an additional capability of pulling the log snapshot table.

To configure this, open the Data Flow and go to the Schema tab. Under Multi Tables > CDC (Change Data Capture), the table list shows each source table's target table, loading mode, and status. Each Data Flow starts with the status Waiting For Migration and takes everything currently present in the table. Data Integration loads the log snapshot table only after the status changes to Streaming.

Select Tables Definitions to configure how Data Integration handles log snapshot tables and loading behavior:

  • Advanced Source Definitions > Stream Configurations: shows the current log position, which updates constantly based on your database changes. Select Check Log Position to view this position — the same position used by the CDC Point in Time position feature.
  • Advanced Target Definitions > Include Log Snapshot Tables: toggle this on so that Data Integration creates a log snapshot table for each updated table, containing all snapshot changes.
  • Table Prefix (optional): adds a character or phrase to the beginning of the target table name, for example ODS.
  • Default Loading Mode: sets how Data Integration loads the data, for example Upsert Merge.
  • Merge Method: sets how Data Integration merges records, for example Switch - Merge.
  • Filter Logical Key Duplication Between Files: use only when duplicates are expected in the source but not in the target table. This filters out duplications in the current source pull.

The platform records each action performed on the log snapshot table as a new entry, along with when the change occurred (in the __ts_ms column) and whether the platform deleted the record (in the __deleted column). The __deleted column is available only when a delete action occurs.

The platform treats _log tables as append-only, and in the event of a connection issue, the platform pulls data from the last successful pull to prevent any data loss. As a result, _log snapshot tables may contain duplicate records.

The original table:

The platform treats log tables as append-only, and in the

In the DBO.DEPARTMENTS table, there are two columns: DEPT_NO and DEPT_NAME. Additionally, there is a calculated expression in the target's mapping which uses current_timestamp().

Before enabling CDC (Change Data Capture) and migrating the Data Flows, the automatically included fields __deleted and __ts_ms have null values for all records.

Oracle CDB and PDB in integration with CDC

In the context of real-time Integration with Change Data Capture (CDC), Oracle's Container Database (CDB) and Pluggable Database (PDB) architecture plays a crucial role.

Oracle CDB-PDB architecture

Container Database (CDB)

The Container Database serves as the root container, holding multiple Pluggable Databases. It provides a centralized and shared infrastructure for managing and maintaining database resources. In the context of real-time data integration, the CDB provides efficient resource use and a unified approach to Change Data Capture.

Pluggable Database (PDB)

Pluggable Databases are available within the Container Database and operate as separate, fully functional databases. Each PDB has its own data dictionary, table spaces, and schema, enabling a multi-tenant architecture. Real-time integration with CDC uses the isolation and independence of Pluggable Databases (PDBs) to capture and process changes at a granular level.

Integration with Change Data Capture (CDC)

The CDB-PDB architecture is well-suited for CDC scenarios. Each PDB can independently enable Change Data Capture based on its specific requirements. With multiple PDBs operating independently, the CDB-PDB architecture provides scalability in capturing and processing changes, making it suitable for large-scale integration scenarios.

CDC setup in Oracle CDB-PDB

Within Oracle's multi-tenant architecture, when managing redo logs within a Pluggable Database (PDB) setting, a user with administrative privileges designated as 'sys' with the role 'sysdba' is required to establish a new user for Data Integration applications. This user should have the capability to use the LogMiner API for redo log operations on Pluggable Databases (PDBs), as employed by Data Integration for streaming changes.

To get guidance on setting up, review the Oracle CDC Configuration topic. For more information about Container Databases (CDBs) and Pluggable Databases (PDBs), refer to Oracle's - CDBs and PDBs topic.

Working with Oracle CDC in Data Integration

Data Integration integrates seamlessly with Oracle CDC by leveraging CDC capabilities provided by Oracle Database. Data Integration CDC integration enables you to set up data replication tasks that capture and propagate real-time changes from Oracle Database to their desired data Targets.

You can set up Oracle CDC in Data Integration.

Procedure

  1. Oracle CDC Configuration: To use this feature, you must activate the Archivelog mode in your Oracle Database. Activating this mode is essential for the effective operation of the CDC mechanism, as it guarantees the preservation and retrieval of all data changes.
  2. Connect to Oracle Database: Establish a connection between Data Integration and the Oracle Database containing the source data.
  3. Enable CDC: Within the following console tour on using Oracle CDC extraction mode, the console guides you through the process of:
    • Enabling CDC in Oracle
    • Selecting a schema for CDC
    • Choosing the desired frequency for the Data Flow to run
    • Use the CDC can by ensuring the table has a key.

Current stream position in your database

To confirm the stream position, run the following command on the server:

SELECT CURRENT_SCN
FROM V$DATABASE;
On this Page