This post will make an in-depth study of the various aspects of Oracle CDC (Change Data Capture), its functioning, types, and the benefits that it brings to the table. However, for a better understanding of the subject, let us first dive into what Change Data Capture (CDC) is in general.
What is Change Data Capture (CDC)
Change Data Capture is a software pattern and a method to track and capture changes, such as insert, delete, and update, made to a database. Records of these changes are stored in downstream systems, helping businesses to take up real-time data replication across different systems.
By identifying and recording any changes made to data in a database, it helps to broadcast these changes to other systems in real time, leading to minimum data latency and data synchronization.
Data replication is the key function of Change Data Capture. CDC replicates data from a source database to a target database or any other storage repository, such as a data warehouse. This ensures that all systems have the latest data in them.
Further, CDC loads incremental and changed data only. There is no need to load entire databases whenever a change occurs, thereby reducing processing time and improving database operating efficiency.
What is Oracle CDC (Change Data Capture)
The Oracle Database Management System (DBMS), designed by Oracle Corporation, was first launched in 1979. Since then, it has been the foundation of database management the world over, providing OTP (Online Transaction Processing) and data warehousing platforms, or a mix of both.
Oracle CDC was first introduced by Oracle in its 9i version of the Oracle Database. Its primary function was to track and capture deletes, updates, and inserts made to user tables in an Oracle database. These changes were to be used in ETL (Extract, Transform, and Load) applications that processed and formatted the changes and moved them to other storage repositories such as target databases or data warehouses.
The initial versions of Oracle CDC had triggers placed in databases that were set off whenever a change occurred. This technology, though, did not find favor with DBAs who found it very complex and intrusive. Based on this feedback, Oracle made the required changes and released a new form of Oracle CDC with the 10g version, naming it Oracle Streams.
This version of Oracle CDC had technologically-advanced features, entirely different from the previous model. It worked by combining a replication tool built into Oracle Streams in tandem with the redo logs of the source database.
This worked well and met all expectations of DBAs, as all changes made to data could be replicated to the target database without adversely affecting the performance of the source database. Replication could be done without shutting down the functioning of the source database, a big help for data-driven organizations.
Unfortunately, despite the success of this form of Oracle CDC, Oracle Streams was discontinued from the 12c release of the Oracle DBMS. Users had to either look for other CDCD options or pay for Oracle Golden Gate, which offered Oracle CDC out of the box.
Functioning of Oracle Change Data Capture
The technology on which Oracle CDC works is simple and straightforward, with the primary function being to track and record all changes made in a database. These changes are used by businesses for analytics and help them make fast and optimized operational decisions. Apart from increasing the speed of data warehousing actions, Oracle CDC also gives the performance of databases a big boost.
A massive benefit of Oracle CDC is that databases can be replicated without lowering their performance in any way. There is no need to shut down source systems or do away with queries from the source to the target database when using CDC to move databases to the cloud. This is possible as any data generated after the last replication run (incremental data) can be extracted from several sources to be transferred to a data warehouse.
However, the most critical function of Oracle CDC is to preserve the state of the data after capturing the changes. Even though the entire process is restricted to a specific data warehouse environment, it can be started through any database or data storage repository.
For businesses that run Oracle CDC on the Oracle DBMS, their database performance gets a boost while the cost of running them is lowered. This is possible because Change Data Capture extracts data and replicates all changes to the target databases in real time whenever they are identified in the source database.
Types of Oracle Change Data Capture
There are two types of Oracle Change Data Capture.
Synchronous Mode
This type of Oracle CDC has triggers placed in the source database that are set off automatically and record changes whenever they are detected. Each SQL statement carries out a DML (Data Manipulation Language) activity classified as Insert, Update, or Delete.
The change recorded and captured is a part of the data that helped to create the change at the source. The Synchronous Mode of Oracle CDC is available only with the Enterprise and Standard versions of the Oracle DBMS.
Asynchronous Mode
This type of Oracle CDC functions through redo logs that move the data with changes captured only after a SQL statement starts a DML activity. The changed data does not affect the transactions in any way, as they have no role to play in the transactions that changed the tables in the source database. The three versions of the Asynchronous Mode are HotLog, Distributed HotLog, and AutoLog.
The Oracle CDC scores over other tools in the Change Data Capture niche as it can capture all changes, such as Insert, Delete, and Update, by preserving the before change and after change values.
