This post is a comprehensive guide to Oracle CDC (Change Data Capture), starting from understanding the CDC concept, the evolution of Oracle CDC, functions, and types.
So, let us dive straight in.
The Change Data Capture Concept
Change Data Capture, or CDC for short, is a software pattern. It is commonly used to track and monitor any changes made in a source database so that organizations can take actions related to their operating efficiency later based on those changes. With CDC, you can also integrate data after identifying the captured data. The data changes made at the source are delivered to all databases in an organization.
What is Oracle CDC (Change Data Capture)
Oracle CDC (Change Data Capture) has a multi-faceted technology that works in various ways. It speeds up data warehousing performance, simplifies real-time data integration across businesses, and boosts the performance and availability of databases.
Additionally, Oracle CDC undertakes replication tasks without adversely impacting database performance. Among the most critical ones in this regard are divesting queries from the database in production to data warehouses or other analytical systems and migrating databases to the cloud. The most common use of Oracle CDC is to extract incremental data (changes made to the data after the last data migration run) and move it to a data warehouse.
This feature of Oracle Change Data Capture, of capturing and preserving the state of the data in a data warehouse system, is a critical component of this software pattern. It offers developers the option of configuring this Oracle CDC feature in multiple ways, stretching from application logic to physical storage in either a single or several combinations of system layers.
The Development of Oracle Change Data Capture
Oracle was released for commercial application in 1977, but it was more than a decade later, in 2001, that Oracle CDC was launched as a built-in feature with the 9i version. It tracked and captured changes in user tables in a database, which were stored in tables that were to be used exclusively in ETL applications. The changes to the database were stored in data warehouses or databases after being processed and formatted as per business needs.
The first version of Oracle CDC had triggers in the source database that were set off when a change took place. This technology was considered too intrusive by DBAs and was not received favorably.
Heeding this feedback, Oracle made changes to the software and relaunched it with the 10g version after naming it Oracle Streams. Streams could detect changes to a source database and transfer them to a target location without lowering the performance of the source database.
Unfortunately, even though Oracle Streams was very well-received by users, Oracle decided to discontinue Streams as a built-in feature from its 12c version. After Oracle withdrew its support for CDC, users could seek another matching solution or pay for Oracle Golden Gate, which offered Oracle CDC.
The Functioning of Oracle Change Data Capture
Let us now consider the critical points for Oracle CDC. There are two systems here: one that stores data that has changed, and the other where some action is taken based on those changes. The first is the source of the data, and the other is the target or destination where the data is to be transferred.
Typically, though the source and the target databases are different, Oracle CDC functions just as efficiently if both are the same, with several CDC solutions in the same system.
The Oracle Data Integrator of the Oracle CDC identifies changes made to the source database and other applications. The Data Integrator is compatible with two journalizing modes as follows:
- Simple Journalizing Mode: It is ideal for monitoring changes made to individual data that is stored in the system.
- Consistent Set Journalizing Mode: This mode works best for tracking changes made to a series of data stores by considering the referential integrity between individual data stores.
Integrating the Oracle Data Integrator with Oracle CDC is an easy and seamless activity.
Types of Oracle Change Data Capture
There are two types of Oracle CDC, and users can choose to implement one based on their specific operational requirements.
Synchronous Change Data Capture
In this form of Oracle Change Data Capture, triggers are placed in records in a source change table. Once a change occurs and is identified, these triggers are activated.
The working of Synchronous Change Data Capture is quite simple. A user is the change data publisher with access granted to the source tables from where the changes need to be tracked and captured. Next, a changeset and tables are created that subscribe to these changes by using a script that copies data, develops records, and transfers the changed data to the target location.
The only drawback here is that triggers placed often lead to a slowing down of databases and adversely affect their performance.
Asynchronous Change Data Capture
In this type of Oracle Change Data Capture, data is moved to the redo log files. Changes made to the data can only be tracked and captured after a SQL statement performs a DML activity. Since the change data is not recorded as a component of the transaction that changed the source table, Change Data Capture does not have any effect on the transaction.
This type of Oracle CDC is structured on Oracle Streams and is a relational interface. The three types of Asynchronous Change Data Capture are HotLog, Distributed HotLog, and AutoLog.
Summing Up
Should you invest in getting to use Oracle CDC? The answer is yes since this product from Oracle has played a critical role over the years in optimizing replication and migration activities and taking them to the next level.
