In this post, you will get deep coverage of the various aspects of Oracle CDC (Change Data Capture), starting from the launch and evolution to the benefits and the types of CDC that power this technology. The reason behind the immense popularity of Oracle Change Data Capture is its ability to reduce costs and overheads in database management and simplify data extraction from the Oracle database. Both factors are key to efficient operations in today’s business ecosystem.
Before diving into Oracle CDC, a quick word on Change Data Capture will help in understanding the concept better. CDC is a cost-effective solution that reduces data warehousing costs by extracting and loading incremental data into a data warehouse or any other data storage repository in real-time. The benefit here is that since only added changes to the database are considered, there is no need to refresh entire databases whenever any changes are made to them in the future.
The Launch and Evolution of Oracle CDC
The Oracle Database Management System (DBMS) or simply the Oracle Database as it is commonly known was designed by the Oracle Corporation and released commercially in 1979. Since then, it has been the driving force for most data-driven organizations for database management providing Online Transaction Processing (OLTP) and data warehousing platforms and even a combination of the two.
Oracle CDC was launched by Oracle Corporation with the 9i version and was available out of the box. This Change Data Capture feature tracked and recorded all changes made to a database in user tables. These changes were stored in specific change tables for use in ETL applications. Later, these changes could be identified and whenever necessary, processed and loaded into target databases and data warehouses.
However, this technology behind Oracle CDC was still in its formative years and was not well-received by users and DBAs. They found this trigger-based feature to be too invasive for optimized use.
Based on this feedback, Oracle implemented several changes in its CDC feature which was released with the 10g version. This mode of Oracle CDC was non-intrusive and seamless and leveraged the redo logs of the source database and the built-in replication tool of Oracle. It was a quantum improvement over the previous version as it could identify and transfer data changes from the source to the target databases without degrading or affecting the speed and performance of the source database.
This technology was very well-received by users but Oracle, for some unexplained reasons decided to discontinue Oracle CDC as a built-in feature from its 12c release. Users were left with a choice of using other Oracle tools to get the Change Data Capture features or pay for Oracle Golden Gate that came with Oracle CDC.
The Functioning of Oracle Change Data Capture
The functioning of the Change Data Capture feature is based on the logic that changes made in the source database are reflected in a target database that must take some action based on those changes. CDC works optimally regardless of whether the source and the target databases are the same or different. The changes made to the data at source and used for other applications are tracked by Oracle CDC through the Oracle Data Integrator.
There are two modes that are supported by the Oracle Data Integrator.
- Simple Journalizing Mode: In this mode, all changes made to individual data present in a database are tracked.
- Consistent Set Journalizing Mode: Here, all changes made to a set of data stores are tracked. This is done after the referential integrity between the data stores is considered.
It is possible to easily and seamlessly set up Oracle CDC on the Oracle Data Integrator by DBAs.
Here are some cutting-edge uses of Oracle CDC.
- It captures all changes made to data such as Insert, Update, or Delete together with their values before and after the changes.
- After Asynchronous CDC is configured, there is a minimum effect of the changes made on the source database.
- Oracle CDC packages help in publishing and subscribing interfaces such as DBMS_CDC_PUBLISH and DBMS_CDC_SUBSCRIBE packages.
- Costs for database maintenance are significantly lowered by Oracle Change Data Capture as data extraction from the Oracle database is greatly simplified and streamlined.
Benefits of the Oracle Change Data Capture Technology
There are several benefits of the Oracle CDC feature.
- CDC is software with design patterns that records changes made to the data in a database. Since these changes are used by businesses for analytics and integrating and delivering data, the speed of warehousing activities is exponentially increased.
- CDC replicates databases without affecting their performance. When moving databases to the cloud, they are not required to be shut down and hence business continuity is maintained.
- Oracle CDC captures and preserves the state of the data and can be started from any database or storage repository. CDC identifies all changes made to the source database and immediately extracts and replicates all changes from the source to the target database.
Types of Oracle Change Data Capture
There are two types of Oracle CDC.
- Synchronous Mode: Here, triggers placed in the source database are immediately set off when any changes are made. These changes are recorded through SQL statements that carry out a DML (Data Manipulation Language) function and categorize them as Insert, Update, or Delete.
- Asynchronous Mode: This mode functions through redo logs that moves data and changes after a DML activity is done with a SQL statement. The changed data does not affect transactions in any way as it is not a part of the transactions that changed the tables in the source database. There are three Asynchronous Modes namely HotLog, Distributed HotLog, and AutoLog.
Oracle CDC has a great role to play in improving database performance.
