Change Data Capture (CDC) is software design patterns. These are primarily used to track changes in input in the source database so that necessary action can be taken on the changes. This process includes data capture, data identification, and data delivery of all changes made at source to enterprise databases.
Oracle CDC helps facilitate real-time data integration across enterprise networks, improve the availability and performance of databases, and speed up data warehousing activities. When this technology is used combined with advanced and cutting-edge tools and non-intrusive and efficient methods, critical replication activities can be done with Oracle CDC without any performance degradation. Some of these tasks are migrating databases to the cloud without any downtime or stoppage in work and offloading analytics queries from databases in production to data warehouses. Incremental data can also be mined from various sources and transferred to a data warehouse with Oracle CDC.
Preserving and capturing the state of the data is one of the main functions of Oracle CDC. It takes place in a data warehouse ecosystem that can also be used in a data repository or any other database. Hence, it is easy for developers to set up Oracle CDC mechanisms in several ways, such as application logic to physical storage in one or several groupings of system layers.
Oracle CDC Technology – Evolution and the present form
The Oracle CDC was offered as an in-built technology with Oracle databases the first time with the Oracle 9i version. It tracked and recorded all user table changes in a database stored for use in ETL applications in changing tables. This changed data could then be processed and loaded into other databases and data warehouses. The launch of Oracle CDC was based on creating triggers on source tables. However, this method was very invasive by Database Administrators who were not too keen to use it.
Oracle released a less-intrusive form of the CDC technology with their O0racle 10g version. This system worked by leveraging the source database’s redo logs and the built-in tool for Oracle CDC, which was called Oracle Streams. It was a substantial improvement from the earlier technology. It could now detect and transfer change data to a target data storage without negatively impacting the source database’s performance.
Even though there was a favorable response to this form of CDC, Oracle decided to discontinue Oracle Streams after Oracle 12c was released and no longer supported Oracle CDC. Users thereafter were left with no option but to pay for Oracle GoldenGate or opt for any other Oracle replication and solution for CDC.
Presently, the structure of Oracle CDC is as follows. When the data in one computer changes, another computer has to take some type of action based on that change. The first computer is called the source database, and the second is the target database. The two need not be different. Even when they are the same, Oracle CDC will work just as efficiently. It is not uncommon to find several CDC solutions in sync within the same system.
Oracle now has the Oracle Data Integrator. It helps Oracle CDC to track changes made to the source databases that are used for various applications.
Modes of Oracle CDC
Two journalizing modes are supported by the Oracle Data Integrator.
The first is the Synchronous Mode, where triggers are placed at the source database to ensure that any data changes are captured instantly. Here, each SQL statement performs a DML (Data Manipulation Language) activity inserted, updated, or deleted. In this mode, the changed data is captured as a part of the transaction that has been instrumental in changing the source’s data. This mode and CDC feature are provided in the Oracle Standard Edition and the Oracle Enterprise Edition.
The other is the Asynchronous Mode, where data is sent to the redo log files. The changed data is captured after a SQL statement goes through a DML activity. The modified data is not captured as a part of the transactions that changed the source table. It, therefore, does not have any impact on the transaction. The three modes of asynchronous CDC are HotLog, Distributed HotLog, and Autoblog. It has to be noted here that the Asynchronous Change Data Capture is based on and offers a relational interface to the now discontinued Oracle Streams.
Benefits of Oracle CDC
There are several benefits of Oracle CDC.
Oracle Change Data Capture includes Update, Insert, and Delete, and all values that exist before and after the changes have been made. Next, Asynchronous CDC can be configured suitably to ensure minimal impact on the source database. CDC includes DBMS_CDC_PUBLISH and DBMS_CDC_SUBSCRIBE packages which help to easily publish and subscribe interfaces. Finally, Oracle CDC is a time, and cost-effective solution as overheads are reduced through simplifying the extraction of the modified data from the Oracle database.
Oracle CDC has come a long way from its early stages to optimize database administration, replication, and migration activities.