Oracle Database, also known as Oracle DBMS or simply Oracle, is a multi-model database management system marketed and produced by Oracle Corporation. It was released for commercial application in 1979. This database management system is commonly used for running OLTP (Online Transaction Processing), DW (Data Warehousing), and mixed loads OLTP and DW.
There are several reasons why Oracle has been the mainstay of organizations for decades. It is designed to run data warehousing, intricate queries, transaction processing, and many others. It also has enterprise-grade security together with a powerful querying layer, advanced functions for data analysis, access controls, and maximized support. The acceptance of open-source distributed data warehouse systems by businesses has made the combination of a traditional Oracle transactional database and a separate data warehouse into a very popular ETL stack.
One of the key features offered by Oracle is Oracle Change Data Capture which captures any changes like mentions, updates, or deletions that are applied to any Oracle database. This technology is in-built in the Replication feature of Oracle where CDC notes changes and identifies data in a relational format that is suitable for ETL, EAI, and other applications.
Oracle Change Data is an affordable option for enterprises using the Oracle database. It assists in a reduction in data warehousing costs since CDC extracts and transfers data or other data storage incrementally in real-time. Only the changes are considered and there is no need to refresh bulk data or the entire database every time a change is made at source. This leads to substantial savings in time and money.
The Development of Oracle Change Data Capture
Even though Oracle was launched in 1979, it was not until 2001 and the launch of the Oracle 9i version that the Oracle Change Data Capture feature was introduced. This model relied on placing triggers on the tables in the source database, a process that was found too complex and tedious by the Database Administrators. However, Oracle made amends with their 10g version. The whole technology was given a complete overhaul that now used redo logs of the database for CDC. When combined with Oracle Streams, the replication tool of Oracle, data changescould be captured and transmitted without using database triggers.
At that time, the technology of Oracle Change Data Capture was mainly a log-based model of CDC. It did away with the need for modifying the structure of the source tables during CDC implementation. But even though this CDC mode became very popular, Oracle withdrew support to CDC after the release of their Oracle 12c version. Users then had no other option but to change to Oracle GoldenGate, the expensive replication software of Oracle.
How does Oracle Change Data Capture work?
First, it is essential to set up the needed journalizing models and the infrastructure before starting Oracle CDC. It will help to capture and record the changes made to the existing databases. The Oracle Data Integrator supports two journalizing modes. One is the Simple Journalizing model where any changes made to an individual datastore are identified. The other is the Consistent Set Journalizing model. In this mode, changes that have taken place to the data store in a group are identified thereby making sure of the referential integrity of the data stores. Only the group of data stores in the Consistent Set are journalized.
Types of Oracle Change Data Capture
There are two types of Oracle CDC and businesses have to choose one that matches their specific requirements.
- Synchronous Change Data Capture: In this type of CDC, triggers are inserted into entries in the change table whenever the existing data is changed. Thus, at the time when changes are identified, these points are activated. In this process, a user is created who acts as a change data publisher. This user should have access to the tables at the source and the namespace from where all changes made are tracked and captured.
The next step is to create a changeset and tables that will subscribe to the modified data. However, before this can take place, which is copying changes to the target database, a script has to be developed that can add the data to the target database. The downside of this Synchronous Change Data Capture is that the triggers adversely affect the performance of the source database.
- Asynchronous Change Data Capture: The disadvantage of the Synchronous CDC has been taken care of by Oracle in this method. Here, redo logs keep a record of all activities in a database. The benefit here is that the whole CDC process is completed without any drop in speed or performance of the Oracle database.
Both have their uses and it is totally up to organizations which method to implement.
Configuring Oracle Change Data Capture
Configuring Oracle CDC comes with its set of challenges. Several user permissions changes and configuration changes have to be done to initialize and complete it. Further, Oracle CDC is based on enabling and capturing change data. Hence developers have to implement the logic for data processing and insertion into the target database.
Oracle Change Data Capture is not a complex process when the right methodologies and tools are used.