What is Oracle CDC and How to Perform It?

Almost all companies today, irrespective of their market position or size, leverage the data collected to analyze their business & customers, making smarter and informed business decisions that help drive business growth and profitability. Oracle CDC (Change Data Capture) is a technology that identifies and captures data added to, updated, and deleted from Oracle tables, making it available for applications to consume.

Hence, businesses are constantly looking for ways to manage their data better. One of the most widely used practices is Change Data Capture (CDC). This article will help you understand CDC and how to set up Change Data Capture (CDC) in Oracle.

What is Oracle CDC?

Oracle Change Data Capture (CDC) is a feature in Oracle databases that captures data manipulation language (DML) changes made to tables and publishes those changes to a message queue. It provides asynchronous replication of data changes from one Oracle database to another in a distributed environment.

The key characteristics of Oracle CDC are:

1. Capture Changes: It captures DML changes (INSERT, UPDATE, DELETE) made to source database tables and writes them to change data capture change tables.
2. Publish Changes: The captured changes are then published and made available in the form of logical change records (LCRs) to subscribing applications or databases.
3. Asynchronous Replication: Changes are replicated asynchronously, allowing source databases to continue operations without waiting for subscribers to apply changes.
4. Data Distribution: It enables data distribution and replication across different databases, making it useful for data integration, data warehousing, and real-time reporting scenarios.
5. Low Impact: CDC minimally impacts the performance of the source database as it captures changes asynchronously.

The Need for Oracle CDC

• Real-time data integration: The CDC allows data to be integrated or harmonized across different systems or databases. Applications such as keeping data up to date in data warehouses, lakes, or related systems are important.
• Improved Data Replication: Because the CDC captures only the changes to the data, it reduces the volume of data replicated. This could lower the strain on your network and make data replication much more efficient.
• Data Warehousing and ETL: CDC is useful in the ETL process of data warehousing because it helps extract only the changed data that could be further processed and loaded into the data warehouse, hence improving ETL performance.
• Audit and Compliance: The CDC also allows for tracking data changes for auditing purposes. This is highly important in many cases for complying with various regulations and standards, which call for detailed records of data modification.

What are Oracle Replication Types with CDC?

• Using Timestamps
• Using Triggers
• Using Transaction Logs (Log-based CDC)

1. Using Timestamps

Audit Timestamps can be used to replicate changed records because they record the Date and Time when a record was entered or updated.

Timestamp Disadvantages:

• Since deleted records cannot be detected, this technique is unsuitable for most applications.
• This method has a high overhead because queries must be conducted continuously on the source Oracle Database and tables.
• The Source Application may not reliably capture Audit Dates and Timestamp Columns, resulting in improper replication. For the CDC, there are concerns with Timestamps and Triggers.

2. Using Triggers

Triggers can be defined for the tables that will be replicated. These are triggered by every insert, update, and deletion, and the Timestamps can be gathered into another change table. The Audit Table can then be used for replication.

Disadvantages:

Triggers also increase the overhead of the Oracle Database by putting a burden on it.

3. Using Transaction Logs (Log-based CDC)

Log-based CDC is the best Oracle CDC method. This involves minimal load on the Oracle source system and can replicate all the changes accurately. This method uses transaction logs, mines the Oracle redo-logs, and the archive logs, which helps Oracle recover independently during a failure. After the logs are mined, the transactions can be applied to the destination similar to the source. This provides a real-time view of the data on the destination. All these data can be accessed on the destination or the target. CDC with transactional logs works on all Oracle versions, including 12c and 19c.

Why Has There Been a Shift from Trigger-Based to Log-Based?

Oracle 9i initially provided users of its database with a built-in mechanism for tracking and storing changes, just like native CDC. The major drawback of this initial version was that it depended on the placement of triggers on tables in the source database. So, DBAs were reluctant to use this technology. Oracle later released Oracle 10g with database redo logs and a built-in Oracle replication tool for capturing and transmitting data changes without invasive database triggers.

The new form was a log-based form of CDC that was lightweight and did not require changing the structure of source tables. Though it has been a popular database feature, Oracle no longer supports CDC with the release of Oracle 12c. This encourages users to switch to the Oracle replication software solution called Oracle GoldenGate.

What are the Modes of Change Data Capture?

Change Data Capture comes with synchronous and asynchronous modes for capturing change data. In this section, you will understand how each mode of Change Data Capture is performed with Oracle Change Data Capture Example.

What is Synchronous CDC (Change Data Capture) in Oracle?

A Synchronous Oracle CDC (Change Data Capture) is implemented using triggers that inserts entries to a Change Capture table every time a data modification operation is executed. Triggers are hooks that get executed as part of the transactions. The first step is creating a user to serve as the Change Data Publisher. This user must have all access to the namespace and the table from which change data is to be captured.

Assuming this user is created, you will now go to the core part of this process, i.e., creating a Change Set, Change Table, and subscribing to changes.

What are the Steps to Set up Synchronous Oracle CDC (Change Data Capture)?

Users can set up Synchronous Oracle CDC (Change Data Capture) by implementing the following steps:

• Step 1: Creating a Change Set
• Step 2: Creating a Change Table
• Step 3: Creating a Subscription to Capture Data
• Step 4: Subscribing to Changes on Source Table
• Step 5: Activating the Subscription
• Step 6: Loading Data from Subscribed Table
• Step 7: Using Select Query to Load Changes

Step 1: Creating a Change Set

To create a change set, users can execute the following command in Oracle:

BEGIN
    DBMS_CDC_PUBLISH.CREATE_CHANGE_SET( 
    change_set_name    => 'PRODUCT_SET', 
    description        => 'Change set for product change info', 
    change_source_name => 'SYNC_SOURCE');
END;

In the above query, SYNC_SOURCE is a predefined source defined by Oracle to implement Synchronous CDC (Change Data Capture).

Step 2: Creating a Change Table

Using the Change Set defined previously, create a Change Table that will capture information from the Triggers by executing the following query:

BEGIN
   DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(

  owner             => 'cdcuser',
   change_table_name => 'products_ct', 
   change_set_name   => 'PRODUCT_SET',
   source_schema     => 'SH',
   source_table      => 'PRODUCTS',
   column_type_list  => 'ID NUMBER(6), 
                         NAME VARCHAR2(50),
                         PRICE NUMBER(8,2)', 
   capture_values    => 'both',
   rs_id             => 'y', 
   row_id            => 'n',
   user_id           => 'n',
   timestamp         => 'n',
   object_id         => 'n',
   source_colmap     => 'y',
   target_colmap     => 'y',
   options_string    => 'TABLESPACE TS_PRODUCT');
END;

In the above command, column_type_list attribute captures the list of columns for which changes will be captured.

Step 3: Creating a Subscription to Capture Data

The next step is to create a Subscription to capture the data that is being inserted to the Change Table. This can be done by executing the following query:

BEGIN
       DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION(
       change_set_name   => 'PRODUCT_SET',
       description       => 'Change data for PRODUCTS',
       subscription_name => 'PRODUCT_SUB');
END;

Step 4: Subscribing to Changes on Source Table

Subscribe to the source table changes using the subscription object created in the previous step by executing the following query:

BEGIN
       DBMS_CDC_SUBSCRIBE.SUBSCRIBE(
       subscription_name => 'PRODUCT_SUB',
       source_schema     => 'SH',
       source_table      => 'PRODUCTS',
       column_list       => 'ID, NAME,PRICE',
       subscriber_view   => 'PRODUCT_VIEW');
END;

Step 5: Activating the Subscription

You can run the following query to activate the subscription:

BEGIN
   DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION(
       subscription_name => 'PRODUCT_SUB');
END;

Step 6: Loading Data from Subscribed Table

Loading data from the subscribed table is done based on a collection window for the next available set of change data. Every time the following command is called, new change data that was created since the last call of this command is added to the subscription window.

BEGIN
   DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
       subscription_name => 'PRODUCT_SUB');
END;

Step 7: Using Select Query to Load Changes

You can now use a select query to get the new changes and process them as per the target database.

SELECT ID, NAME, PRICE FROM SALES_VIEW;

To copy data to another destination database, the above action will have to be performed using a script that can process these records and insert to the target database.

The biggest disadvantage of the above approach is that Triggers affect the performance of the source database. For cases where this cannot be afforded, Oracle provides another alternative to implement change data capture in Oracle. 

What is Asynchronous Oracle CDC (Change Data Capture)?

Asynchronous Oracle CDC (Change Data Capture) is implemented using Redo Logs. Redo Logs are logs that keep a track of all activities in a database. As the name suggests, this is an asynchronous process and does not cause any performance hit.

What are the Steps to Set up Asynchronous Oracle CDC (Change Data Capture)?

Users can set up Asynchronous CDC (Change Data Capture) by implementing the following steps:

• Step 1: Configuring Database
• Step 2: Enabling Supplemental Logging
• Step 3: Enabling Logging for Columns
• Step 4: Preparing Source Tables
• Step 5: Creating Change Set
• Step 6: Creating Change Table
• Step 7: Enabling Change Set

Step 1: Configuring Database

The first step is to enable FORCE LOGGING mode on your database. You can do so by executing the following query:

ALTER DATABASE FORCE LOGGING;

Step 2: Enabling Supplemental Logging

Supplemental Logging allows the database to create entries in the log for all changes to the table including UPDATE statements. It can be enabled by executing the following query:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Step 3: Enabling Logging for Columns

First, you would need to identify the columns that you need to capture changes from. If you choose to capture changes from all columns, you can run the following query:

ALTER TABLE ts.products ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Step 4: Preparing Source Tables

You can prepare source tables for CDC (Change Data Capture) by executing the following query:

BEGIN DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME => 'ts.products'); END;

Step 5: Creating Change Set

You can create a Change Set with the source as HOTLOG_SOURCE by executing the following query:

BEGIN
       DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
       change_set_name => 'PRODUCT_SET',
       description => 'Change set for product info',
       change_source_name => 'HOTLOG_SOURCE',
       stop_on_ddl => 'y',
       begin_date => sysdate,
       end_date => sysdate+5);
END;

HOTLOG_SOURCE is a predefined source defined by Oracle for changing data capture Asynchronously.

Step 6: Creating Change Table

You can create a Change Table by executing the following query:

BEGIN
   DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
   owner              => 'cdcpub',
   change_table_name  => 'products_ct', 
   change_set_name    => 'PRODUCT_SET',
   source_schema      => 'TS',
   source_table       => 'PRODUCTS',
   column_type_list   => 'ID NUMBER(6), NAME VARCHAR2(50),
      PRICE NUMBER(8,2)',
   capture_values     => 'both',
   rs_id              => 'y',
   row_id             => 'n',
   user_id            => 'n',
   timestamp          => 'n',
   object_id          => 'n',
   source_colmap      => 'n',
   target_colmap      => 'y',
   options_string     => 'TABLESPACE TS_PRODUCT_SET');
END;

Step 7: Enabling Change Set

Finally, you have to enable the Change Set by executing the following query:

BEGIN
   DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
      change_set_name => 'PRODUCT_SET',
      enable_capture => 'y');
END;

That’s it, you now have set up Oracle CDC (Change Data Capture) for your tables which means that all the changed data will be captured in the changing table. However, you still need to implement a mechanism that creates a subscription to this table and process accordingly.

Oracle CDC Alternatives

1. Oracle 12c: Oracle Streams Deprecation

Oracle Streams was a native CDC utility for Oracle Databases that was free and could be used for real-time data transport. Oracle Streams can push transactional updates automatically to a queue to be replicated. Oracle Streams is no longer supported as of Oracle 12c. Oracle encourages consumers to upgrade to Oracle GoldenGate, a premium version with substantial licensing fees.

Advantages of Oracle Streams (Before Deprecation)

• Free to use: Included with Oracle Database at no extra cost.
• Real-time replication: Enabled low-latency data updates.
• Simple to configure: Built into Oracle’s ecosystem.

Disadvantages of Oracle Streams

• Requires additional tools: Needed supplemental tools for full data integration.
• Deprecated: No longer supported in Oracle 12c and later.
• Limited scalability: Not as powerful as newer replication solutions.

2. Oracle 19c: Oracle LogMiner Continuous Mining Deprecation

The most recent long-term support release of the Oracle Change Data Capture 19c, known as Oracle Database 19c. It brings in a number of improvements and features meant to boost manageability, security, and performance.

Oracle LogMiner is a program supplied by Oracle to Oracle Database purchasers that allows them to query logged database changes, mostly through SQL commands referencing data in Oracle redo logs. DBAs use it largely to query transaction logs. Oracle LogMiner includes a functionality that allows you to get real-time changes from Oracle redo logs. Most Third-Party CDC programs use this functionality to replicate Oracle data for their customers. Oracle has also deprecated this feature in version 19c in order to (you guessed it!) drive consumers to their paid service Oracle GoldenGate, which has significant license charges.

Advantages of LogMiner (Before Deprecation)

• Free to use: Built into Oracle Database without additional licensing.
• SQL-based: Provided an easy way for DBAs to analyze transaction logs.
• Integration with third-party CDC tools: Enabled external replication solutions to extract changes.

Disadvantages of LogMiner

• Limited built-in automation: Required additional scripting and tools for automated replication.
• Real-time mining deprecated: Continuous mining is no longer available in 19c.
• Performance overhead: Could slow down performance on busy databases.

3. Oracle GoldenGate Replication

Oracle GoldenGate is a premium replication tool that enables real-time, heterogeneous data replication. It is an add-on license for Oracle Enterprise Edition and can be used separately for non-Oracle databases. GoldenGate provides Change Data Capture (CDC) with high throughput and low impact on source systems.

Oracle GoldenGate will deliver CDC (Change Data Capture) replication in real-time, with incremental updates merged on the destination. CDC is known for its high throughput and low impact on source systems. Oracle GoldenGate for Big Data is required for big data targets such as Amazon S3 or Snowflake. 

Advantages of Oracle GoldenGate

• Real-time replication: Provides near-instant data synchronization.
• Supports heterogeneous databases: Can replicate across different database platforms.
• Low impact on source systems: Uses an efficient log-based CDC approach.
• Scalability: Handles high volumes of data efficiently.
• Enterprise-grade features: Offers robust monitoring, security, and reconciliation tools.

Disadvantages of Oracle GoldenGate

• Additional tools required: Needs Oracle Data Integrator (ODI) for ETL and GoldenGate for Big Data to support cloud storage like Amazon S3 or Snowflake.
• Expensive: Requires multiple licenses (e.g., Oracle Veridata, GoldenGate Studio, GoldenGate Management Pack, etc.).
• Complex implementation: Requires specialized expertise and integration with other Oracle products.

4. Oracle XStream

The real-time data integration feature Oracle XStream consists of the Capture, Propagate, and Apply components. However, Oracle has refocused on GoldenGate, a more complete solution with more platform compatibility and features.

This change suggests that users are urged to use GoldenGate for any upcoming data integration requirements. Moving to GoldenGate guarantees access to advanced capabilities and long-term compatibility with Oracle’s data integration strategy, even though XStream may still be supported.

Advantages of Oracle XStream

• Real-time data integration: Supports fast and efficient replication.
• Built-in Oracle feature: Available as part of Oracle Database.

Disadvantages of Oracle XStream

• Requires additional tools: May not be as feature-rich as GoldenGate without supplementary components.
• Limited support: Oracle is moving users toward GoldenGate for long-term compatibility.

5. Oracle RAC (Real Application Clusters) Replication

Oracle Real Application Clusters (RAC) is an Oracle Database Environment that is highly available and scalable. It enables customers to share storage and execute a single Oracle database across numerous machines. If one of the cluster’s servers fails, the Database instance will continue to function on the cluster’s remaining servers or nodes. Customers can also start small and scale up as needed by adding Nodes or Servers.

Advantages of Oracle RAC Replication

• High availability: Ensures database continuity in case of node failure.
• Scalability: Easily scales by adding more nodes.
• Shared storage: Provides efficient resource utilization.

Disadvantages of Oracle RAC Replication

• Complex CDC implementation: Each node has separate transaction logs, requiring additional processing for data consistency.
• Expensive: Requires additional Oracle licensing and infrastructure costs.
• Configuration complexity: Requires expertise to manage clustering and replication effectively.

Choosing the Right Oracle CDC Solution

Feature	Oracle Streams (Deprecated)	Oracle LogMiner (Deprecated)	Oracle GoldenGate	Oracle XStream	Oracle RAC Replication
Cost	Free	Free	High	Moderate	High
Real-time CDC	Yes	Yes	Yes	Yes	Yes
Oracle Support	No (deprecated)	No (deprecated)	Yes	Limited	Yes
Third-party Integration	Limited	Yes	Yes	Limited	No
Complexity	Low	Moderate	High	Moderate	High
Scalability	Limited	Moderate	High	Moderate	High

Recommendations

• For high availability and fault tolerance, Oracle RAC is ideal but adds significant complexity.
• For free solutions, neither Streams nor LogMiner is viable due to deprecation.
• For enterprise-grade replication, Oracle GoldenGate is the best option but comes at a high cost.
• For real-time integration, Oracle XStream offers an alternative but lacks long-term support.

What to look for in an Oracle Replication Tool?

Hand-coding is much slower and more difficult than using an automated Oracle Replication Tool. Maintaining a hand-coded solution for future Oracle versions can also be problematic. As a result, employing an Oracle Replication Tool is a more straightforward option to consider.

Here are some things to think about while choosing an Oracle replication tool: 

• Upgradation  to Oracle 19c 
• Tool Operations with Oracle 12c and Oracle 19c 
• High Throughput
• Catch up and Recovery in the event of an Outage 
• Enormous CDC volumes without difficulty 
• Remote Work
• Oracle Replication in Real-Time.

Explore CDC tools designed for scalable and reliable data pipelines, enhancing your data management strategy. More details at Best CDC Solutions.

Limitations of Oracle CDC (Change Data Capture)

• Setting up Oracle CDC (Change Data Capture) requires numerous configuration and user permission changes to initialize and complete this process. Hence, it might be tough to implement for someone who does not have in-depth knowledge of Oracle.
• Although Oracle CDC provides efficient data capture, but understanding how it Oracle compares to PostgreSQL’s approach helps in choosing the right database solution.
• The methods covered in this article are only about enabling and capturing change data. You still need to implement the logic for processing the change data and insert it into your target databases.
• Implementing a complete CDC (Change Data Capture) Pipeline needs expert Oracle admin skills, target database skills, and programming skills to implement the logic. This would consume a considerable amount of engineering resources in development and maintenance.
• A critical feature of Oracle CDC is its reliance on redo logs, which helps capture and manage all changes occurring in the database.

Best Practices For Implementing Oracle Change Data Capture

To ensure an efficient and scalable CDC implementation, consider the following best practices:

• Use Parallel Processing: Distribute data capture workloads across multiple threads or processes to improve efficiency.
• Choose the Right CDC Method: Evaluate deprecation status, licensing costs, and scalability before selecting a CDC solution.
• Minimize Performance Impact: Use log-based CDC (e.g., GoldenGate) instead of trigger-based methods to reduce database load.
• Ensure Data Consistency: Implement proper error handling and reconciliation mechanisms to avoid data inconsistencies.
• Optimize Log Retention Policies: Properly configure redo and archive logs to prevent excessive storage consumption.

Learn More About:

Oracle Change Tracking

Conclusion

This article helped you understand how you can set up Oracle CDC (Change Data Capture) with ease, including how to enable CDC in Oracle. It provides in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently.

Most businesses today, however, have an extremely high volume of data with a dynamic structure. Creating a Data Pipeline from scratch for such data is a complex process since businesses will have to utilize a high amount of resources to develop it and then ensure that it can keep up with the increased data volume and Schema variations. Businesses can instead use automated platforms like Hevo.

Hevo Data helps you directly transfer data from a source of your choice to a Data Warehouse or desired destination in a fully automated and secure manner without having to write the code or export data repeatedly. It will make your life easier and make data migration hassle-free. It is User-Friendly, Reliable, and Secure.

Sign up for a 14-day free trial and see the difference!

Frequently Asked Questions