Oracle Change Data Capture – Evolution, Function, Benefits and Types
By Space Coast Daily // January 27, 2023
This post will take you through the various intricacies of the Oracle CDC (Change Data Capture), its functioning, how it evolved, the benefits, and the forms of this technology.
Needless to say, a very important aspect of the CDC is its ability to reduce overheads and simplify the extraction of data from the Oracle database.
But more of that in detail later.
The Functioning of Oracle CDC
Let’s start with what is CDC regardless of which database management system it is embedded in.
By definition, CDC is software patterns that are available as a built-in feature in most systems. It is used for monitoring and tracking changes in the source database so that the required actions can be taken based on those changes. CDC also helps to integrate data by capturing, identifying, and delivering all changes that are made to enterprise databases.
Now, coming to Oracle CDC, as in the definition, it is the feature that helps in real-time data integration throughout the organization, as well as making data warehousing faster and improving the performance and access to databases.
Additionally, Oracle CDC can carry out multiple replication activities without adversely impacting the performance of the database provided this function is done with the best tools and efficient and non-intrusive methods are used. These activities include migrating databases to the cloud and divesting queries from databases in production to data warehouses or other similar platforms.
Incremental data (changes at the source database after the last run during migration) from multiple sources can also be transferred to a data warehouse with Oracle CDC.
A critical benefit of this technology is that during all processes, the source database remains fully functional and downtime is not required. This is a big boost for large data-driven enterprises that cannot afford downtime for any period without upsetting operating schedules.
Oracle CDC is primarily functional in a data warehouse ecosystem or a data repository platform as one of its key uses is preserving and capturing the state of the data. DBAs can set up this CDC through physical storage or application logic or in any combination of the two.
The Progress of the Oracle CDC Technology After Launch
Oracle Corporation first launched CDC as a built-in feature with the Oracle 9i version. It could track and record all changes made to the user tables in a database which were then stored in change tables to be used in ETL applications. From the records, these changes were identified for processing and loading into other databases and data warehouses.
However, this version of the Oracle CDC was considered to be too invasive as it worked through the creation of triggers in the source databases and did not find favor with the DBAs.
Keeping this in mind, Oracle released a more seamless and more non-intrusive form of CDC along with its 10g version and named it Oracle Streams. This updated CDC technology leveraged the built-in replication tool of Oracle as well as the redo logs of the source database. This CDC was a great improvement over the previous one and could detect and transfer data changes from the source to the target database without affecting the source system performance.
Surprisingly, even though Oracle Streams was very well received, Oracle chose to discontinue CDC as a built-in feature when it released its 12c version. Users could then either pay for Oracle GoldenGate that had the CDC feature or depend on other Oracle replication tools for CDC.
The Current Form of Oracle CDC
Oracle CDC is based on the concept that the source database contains data that has been changed and the target database has to take some action based on those changes. CDC works equally well where the source and the target repositories are different and where both are the same.
The changes made to the data at a source that is used for other applications are identified by Oracle CDC through the Oracle Data Integrator which supports two forms of journalizing modes.
One is the Simple Journalizing Mode and the other is the Consistent Set Journalizing Mode. In the first instance, changes made to individual data stored in a system are tracked. In the second, changes made to a set of data stores are tracked after considering the referential integrity between each data store. The bright point is that DBAs can easily set up Oracle CDC on an Oracle Data Integrator.
Benefits of Oracle CDC
Given here are some of the key benefits of the Oracle CDC.
- All changes to the data like Insert, Update, and Delete as well as the values before and after the changes are captured by the Oracle Change Data Capture.
- Minimal impact on the source database is ensured by configuring Asynchronous CDC.
- CDC has packages that help in easily publishing and subscribing interfaces. These include DBMS_CDC_PUBLISH and DBMS_CDC_SUBSCRIBE packages.
- Overheads for database maintenance are substantially reduced by Oracle Change Data Capture as it streamlines the extraction of changed data from the Oracle database.
Because of these benefits, Oracle CDC is much preferred by most organizations for database replication and migration.
Types of Oracle CDC
There are two types of Oracle CDC
- Synchronous CDC: Here, triggers are placed in records in a change table when data is modified. After a change is identified, these triggers are activated. The downside is that placing triggers often impact the performance of the source database.
- Asynchronous CDC: In this Oracle CDC, redo log files receive data, and any changes to it are captured after a SQL statement performs a DML activity. The types of Asynchronous CDC are HotLog, Distributed HotLog, and AutoLog.
Oracle CDC over the years has played a key role in database migration and replication activities in organizations.