Typically, the Change Data Capture (CDC) technology monitors and tracks change in a database based on which any necessary action may be taken. Hence, the Oracle CDC technology represents software design patterns that greatly manage the integration, identification, and delivery of data for all changes that are made to a source database in an organization. Further, by quickening data warehousing facilities and enabling real-time data integration, Oracle CDC enhances the quality and performance of databases.
The Oracle Change Data Capture tool is a highly optimized and non-intrusive method for replicating databases. This technology performs a wide range of activities including migration of databases from on-premises to the cloud while keeping the source databases fully functional and offloading queries for analytics from databases in use to the data warehouses. Incremental data can also be extracted with CDC from various sources and moved to a data warehouse.
Preserving and capturing the state of the data within a data warehouse environment is one of the most important activities of the Oracle CDC. It is possible to configure Oracle CDC either by using application logic or choosing physical storage or even a combination of the two.
The Development of the Oracle CDC Technology
The technology behind Change Data Capture was launched by Oracle Corporation and came out of the box with its 9i version. It was very useful for tracking and monitoring all changes made in user tables in a database. These changes were later stored in change tables to be used in ETL (Extract, Transform, Load) applications and then processed and migrated to various databases or data warehouses.
The first form of the Oracle CDC technology worked through triggers placed in the source tables. This was found to be quite complex by Database Administrators, thereby forcing Oracle to release a modified version along with its 10g version. It was named Oracle Streams and was based on redo logs of the source database instead of triggers as before. This technology became hugely popular because of the simplicity of operations as any change in a data storage repository could be detected without affecting the performance or speed of the source database.
Surprisingly, despite the growing popularity of this Oracle CDC technology and its acceptance by users in general, Oracle discontinued Streams from its 12c version and it was no longer available as a built-in feature of the Oracle database system. However, users could either look for some other form of tool that matched the functioning of the Oracle CDC or work with Oracle GoldenGate which has Oracle CDC as a built-in feature.
The Current Form of Oracle CDC
The technology of Oracle CDC is based on the principle that whenever there is any change in the source database a corresponding action has to be taken in the target database based on that change. The advantage here is that the source and the target databases need not be two separate entities. Oracle Change Data Capture works equally well if both the source and the target databases are the same. This is because some CDC solutions exist in the same system.
Modes of the Oracle Change Data Capture
Changes to the data at the source are recognized by Oracle CDC through the Oracle Data Integrator. Two modes are available here.
Synchronous Mode
In the Synchronous Mode, triggers are placed in the source database. These capture any changes made at the source immediately. A Data Manipulation Language (DML) activity is carried out by each SQL statement. The changes can be categorized as Insert, Update, or Delete. The changed data captured forms a part of the transactions that have created the change to the data at the source. The Oracle Standard and Enterprise editions have the Synchronous Mode of the Oracle CDC.
Asynchronous Mode
In the Asynchronous Mode, data is moved to the redo logs first, and later, the changes are captured after the SQL statement is taken through a DML activity. The main difference with the previous mode is that here, there is no effect on the transactions of the modified data since it has not been captured as a part of the transactions that had led to the changes in the source table. The three types of Asynchronous Change Data Capture are HotLog, Distributed HotLog, and AutoLog. This mode is based on Oracle Streams, a version of Oracle that had the Change Data Capture feature but was later discontinued.
The Synchronous and Asynchronous Modes of the Oracle Data Integrator have fully automated operations and are easy to set up and configure.
Extraction of Databases with Oracle CDC – the Benefits
There are several benefits of extracting data with Oracle CDC.
- As soon as any changes are made to the source tables, Oracle CDC starts immediate extraction for Insert, Delete, and Update activities in real-time. Without CDC, Insert activity is not possible for database extraction and is a very complex and tedious process for Update and Delete activities.
- Oracle CDC places flat files directly in relational tables and hence they are not required for the staging of data.
- The user-friendly interface of Oracle CDC is provided with DBMS_LOGMNR_CDC_PUBLISH and DBMS_LOGMNR_CDC_SUBSCRIBE packages. Without these, CDC would be a long-drawn-out process requiring extensive manpower.
As seen before, the Oracle CDC is now a paid tool and forms a part of Oracle GoldenGate. It is optimized for all activities that show changes to the source database including database migration and replication as well as all DML activities. However, because of stringent security norms, the application and configuration of CDC need several user and access permissions. Only after they are incorporated into the system can changes made to the source database be initialized and completed.
Read more interesting articles at Admin Wells
