How To Track Database Changes with Oracle Change Data Capture

Oracle Database Management system was first released commercially in 1979 and offers Online Transaction Processing (OLTP) and data warehousing platforms.

Oracle Change Data Capture (CDC) was first launched along with the 9i version to record and track all changes made by users in database tables. The changes were stored in change tables for use in ETL applications for later processing and storing in other data warehouses and databases.

The launch version of Oracle Change Data Capture worked through triggers that were placed in source databases. This technology was considered too invasive and did not find favor with DBAs. Eventually, Oracle released a modified form with its 10g version and named it Oracle Streams. The model of the Oracle Change Data Capture used the redo logs of the source database along with an in-built tool for replication present in Oracle Streams.

This mode of Oracle Change Data Capture turned out to be a highly successful method to detect and move change data to a target database without affecting the speed or performance of the source database. Most importantly, the source database remains fully functional during database migration and there is no need for downtime.

Even though this technology of Oracle Change Data Capture became very popular, Oracle discontinued Oracle Streams from its 12c release. Users now had no option but to implement other solutions or pay for Oracle GoldenGatethat had the Oracle Change Data Capture out of the box.

In its present form, Oracle CDC is carried out when the data in the source database is changed and any other data repository has to take some action based on those changes. The source and the target databases can be the same too and the CDC technology works equally efficiently when several CDC solutions are present in one system.

Oracle Change Data Capture recognizes all changes made to the data at source via the Oracle Data Integrator. There are two modes that are supported by the Integrator.

The first is the Synchronous Mode that functions through triggers that are placed in the source database. This ensures that all changes are captured immediately. Changes like Insert, Update, and Delete carried out by each SQL statement are known as the Data Manipulation Language (DML) activity. These types of change data captured are a part of the transactions that change data at the source. Oracle Change Data Capture is available as a standard feature in the Standard and Enterprise editions of Oracle.

The second is the Asynchronous Mode that has different functioning. It works through redo logs via which data movement and changes are captured after a SQL statement is taken through a specific DML activity.

The working of both the modes of Oracle Change Data Capture can be easily done after setting up and configuring the Oracle Data Integrator.