The Oracle Change Data Capture feature monitors and tracks all changes made in a database so that users can take any required action based on them. This technology is defined to be software design patterns that enable the integration, identification, and delivery of data for all modifications made to the source in enterprise databases. Apart from this, CDC also ensures the integration of data across enterprises and improves the performance and quality of output of databases by speeding up data warehousing.
If you need an efficient tool that uses non-intrusive technology and deploys replication functionalities without any lowering of performance levels, Oracle CDC is what you should opt for. The various tasks that it can perform are migration of databases to the cloud without the downtime and divesting analytics queries from databases in production to data warehouses. You can also extract change and incremental data collated from various sources for moving to a data warehouse.
The complete task of Oracle CDC can be deployed in any database or data repository because one of its primary functions is to capture and preserve the state of the data that is present in a data warehouse environment. You can configure the Oracle CDC to work in various ways ranging from physical storage to application logic or any of their combinations.
The Evolution of Oracle CDC to its Present Form
Oracle first released the Oracle CDC technology along with the 9i version of the database management system. It was used to monitor and record all changes to the data made in the user tables of a database. This modified data was used in ETL applications after being stored in specific change tables and processed and loaded into data warehouses and other databases. The launch version of the Oracle CDC worked through triggers that were placed in the source tables. However, users and database administrators found this method to be very invasive and complex and not conducive to the seamless functioning of the databases.
It was then that Oracle changed the key parameters of the CDC structure and a new version of the feature called Oracle Streams was launched with the 10g version. Instead of triggers, the redo logs of the source database along with the in-built replication tool of Oracle were leveraged to detect and move change data to a target repository without impacting the performance of the system at the source. Oracle Streams was well received by users and proved to be very successful and effective.
Surprisingly, despite its popularity, Oracle discontinued Streams from its Oracle 12c model, and it no longer supported Oracle CDC. Users had no other option but to either use some other tool for Oracle database replication or fall back on Oracle GoldenGate, a paid version that had CDC as out-of-the-box.
The Present Oracle CDC System
The idea of Oracle CDC is founded on the concept that data in one computer (the source database) is changed and another computer (the target database) has to take some action based on those changes. The source and the target databases don’t need to be different as both can co-exist in the same system, and Oracle CDC will function just as effectively.
Types of Oracle CDC Functions
Oracle CDC is helped to identify changes to the data at source by the Oracle Data Integrator that supports modes of Change Data Capture.
Synchronous Mode
Here, the triggers are located in the source database and are set off to capture any changes as soon as they are made at the source. Each SQL statement carries out a DML (Data Manipulation Language) activity which is insert, update, or delete. The changes in the data so captured are a part of the transactions that were responsible for the changes. This mode of the Oracle CDC can be had from the Oracle Standard as well as the Enterprise editions.
Asynchronous Mode
Here, the changes are captured after a SQL statement goes through a DML activity and the data is moved to the redo log files. Since the changed data is not captured as a part of the transactions that changed the source tables, the modified data does not have any effect on the transaction. The three modes in the Asynchronous CDC are HotLog, Distributed HotLog, and AutoLog which are based on the now-discontinued Oracle Streams and provide a relational interface to it.
Configuring and setting up the Oracle Data Integrator is easy and seamless as it is a fully automated system.
Benefits of Extracting Databases with Oracle CDC
Users have a distinct advantage when extracting databases with Oracle CDC. Here are some of the benefits.
- Data extraction: Oracle CDC ensures quick database extraction of Insert, Update, and Delete operations immediately after changes are made to the source tables. On the other hand, without CDC, database extraction is not optimized for Insert activities and is not possible for Update and Delete as the data is no longer available in the table.
- Staging data: In Oracle CDC, flat files are not required for staging data as it is placed directly in relational tables. In any other system, entire tables have to be moved into flat files.
- Interface: Oracle CDC provides an easy-to-publish and subscribes interface through DBMS_LOGMNR_CDC_PUBLISH and DBMS_LOGMNR_CDC_SUBSCRIBE packages, without which extensive manpower would be required to administer this step that would also be prone to errors as done manually.
- Affordability: Even though Oracle CDC is available as a paid service through Oracle GoldenGate, costs are still quite affordable when compared to the price of software from a third-party vendor.
These are some of the advantages of working with Oracle CDC, a technology that has made migration and replication activities quick and automated without the need for human intervention at any stage.