Introduction
This post will take you through the various facets of Oracle CDC (Change Data Capture). It will start with the concept of the Change Data Capture technology and go on to the evolution of Oracle CDC, the functions of this feature, and the types of this technology.
Oracle has been the mainstay of Database Management Systems since the time it was commercially launched more than five decades back. The Oracle CDC technology has been received very well by organizations around the world, mainly due to its ability to reduce operating costs, increase database operating efficiencies, and simplify the extraction of data from the Oracle database.
What is Change Data Capture
Before going into Oracle CDC, it is necessary to understand the general concept of Change Data Capture. CDC extracts and loads incremental data (data generated after the last round of data transfer from a source to a target database) into a data storage repository or a data warehouse in real-time. It is a very effective solution that reduces data warehousing costs.
The benefit of CDC is that only the changes added to a database are considered. It is a time-saving process as it does away with the need to refresh the complete database whenever any changes are made to it.
The Development of the Oracle Change Data Capture Technology
Oracle Corporation launched the Oracle CDC feature out of the box with its 9i version. The process worked as follows.
Change Data Capture tracked and recorded all changes made to user tables in a database to be stored in specific change tables to be used in ETL (Extract, Transfer, and Load) applications. Whenever required, these changes can be identified, processed, and transferred to a data warehouse or a designated target database.
However, though Oracle had made a start in the Change Data Capture niche, CDC was still in its nascent years and not very refined. The trigger-based feature was found to be too invasive by DBAs and could not be optimally used by them. Oracle was quick to act on this feedback of their new Change Data Capture technology and a new and refined version of Oracle CDC was introduced with their 10g version.
This new model of CDC was seamless and non-intrusive too. It leveraged the redo logs of the source database along with the replication tool built into Oracle. This revised version was a huge improvement over the previous one. It had the ability to identify and transfer changes made to data from the source to the target databases without affecting the quality, speed, or performance of the source database.
This Oracle CDC technology had a positive impact on Oracle users and was very well received. However, despite its success, Oracle decided to discontinue this feature out of the box from its 12c version. Users could either use other Oracle tools to access the Change Data Capture feature or pay for Oracle Golden Gate which had Oracle CDC built into it.
The Technology Driving Oracle Change Data Capture
The Oracle CDC technology functions in the following way.
Changes made in the source databases are replicated in the target database. Some action must be taken next based on these changes. The performance of the CDC is optimized regardless of whether the two databases – the source and the target – are the same or different. The Oracle Data Integrator is used by Oracle CDC to track the changes made to the data at source and used by other applications.
The Data Integrator supports two modes of Oracle CDC.
The first is the Simple Journalizing Mode where all changes made to individual data in a database are monitored. The second is the Consistent Set Journalizing Mode where all changes made to a set of data stores are monitored. However, before doing so, it must be ensured that there is referential integrity between the data stores.
Oracle Change Data Capture can be easily set up and installed by DBAs in the Oracle Data Integrator.
How Do Businesses Benefit From Oracle Change Data Capture
There are several benefits that accrue to businesses when they use Oracle CDC.
# As software with design patterns, CDC records all changes made to data in a database. These changes are used by businesses to analyze, integrate, and deliver data. This greatly increases the speed of warehousing activities.
# Databases are replicated by the CDC technology without having any impact on their performance. Business continuity is maintained when migrating databases to the cloud as there is no need to shut them down during the process.
# Oracle CDC can be initiated from any storage repository or database as it can preserve and capture the state of the data. Once the CDC identifies all changes to the source database, it seamlessly and quickly extracts and mirrors all changes made in the source database to the target database.
Forms Of Oracle Change Data Capture
There are two types of Oracle CDC.
# Synchronous Mode where triggers are placed in the source databases. Whenever changes take place, they are automatically set off. SQL statements record the changes through a DML (Data Manipulation Language) function and classify them as Update, Delete, or Insert as the case may be.
# Asynchronous Mode that functions through redo logs. They move data and changes after a DML activity is carried out with a SQL statement. Transactions are not affected by changed data as it does not belong to the transactions that changed the tables in the source database.
Asynchronous Mode is available in three forms namely HotLog, Distributed HotLog, and AutoLog. Once Asynchronous CDC is configured, there is a negligible effect of the changes made on the source database.
Costs for database maintenance are significantly lowered by Oracle Change Data Capture.
