DATA WAREHOUSE – CHANGE DATA CAPTURE (CDC)

CDC (Change Data Capture)

It can be called the process of defining and capturing changes made in the database. It is also referred to as a design pattern to identify and track changes in this type of database.

Unlike traditional methods, it finds and updates changes in data instantaneously instead of batch processing. Differences between CDC and batch processing can be as follows.

CDC

– Updates the target database instantly.

– Continuously monitors changes in the source database.

– Uses the process flow to provide instant changes.

Batch Processing

– Data are not synchronized immediately.

– Allocating resources to maintain synchronization slows production.

– Replication process takes place during batch Windows.

First state and last state information can be logged from modern databases. The current sysdate is printed in a field in the form of Update date.  Triggers from the source database can be used to capture data with CDC changes.

CDC can be used in two ways. It can be divided into asynchronous and asynchronous.

Synchronous Change Data Capture 

Represents the SYNC SOURCE source database. Triggers run after performing DML operations on source tables populate the SYNC_SOURCE change resource.

 

Asynchronous  HotLog  Change Data Capture

HOTLOG_SOURCE represents online log files in the source database. It cannot be changed or deleted.

We can use the table below to query on source db.

 SELECT * FROM ALL_CHANGE_SOURCES.

Asynchronous CDC has different methods. In summary, Oracle offers us as follows. The method can be used according to the need.

 

Factors Influencing Choice of Change Data Capture Mode

ModeLocation of, Hardware, and Software on Staging DatabaseCapture MechanismSource Database

Performance Impact

SynchronousLocation must be the same as the source database and

therefore hardware, operating system, and Oracle Database release are the same as source system.

Change data is automatically committed as part of the same transaction it reflects.Adds overhead to source

database transactions to

perform change data capture.

Asynchronous HotLogLocation must be the same as the source database and therefore hardware, operating system, and Oracle Database release are the same as source system.Change data is captured from the current online redo log file. Change sets are populated automatically as new transactions are committed.Minimal impact on source

database transactions to

perform supplemental

logging. Additional source

database overhead to perform change data capture.

Asynchronous Distributed HotLogLocation is remote from the

source database. Hardware,

operating system, and Oracle database release can be different from the source system.

Change data is captured from the current online redo log file. The change set is populated automatically as new committed transactions arrive on the staging database.Minimal impact on source

database transactions to

perform supplemental

logging.

 

Some overhead on the source database is incurred when mining the online redo log files.

Asynchronous AutoLog

Online

Location is remote from the

source database. Hardware,

operating system, and Oracle database release are the same as source system.

Change data is captured from the standby redo log files. The change set is populated automatically as new committed transactions arrive on the staging database.Minimal impact on source

database transactions to

perform supplemental

logging.

 

Minimal source database

overhead for redo transport

services.

Asynchronous AutoLog

Archive

Location is remote from the

source database. Hardware,

operating system, and Oracle database release are the same as source system.

Change data is captured from archived redo log files. Change sets are populated automatically as archived redo log files arrive on the staging database.Minimal impact on source

database transactions to

perform supplemental

logging.

Minimal source database

overhead for redo transport

services

 

It will be practical in the following articles…

If you any question. You can send the mail or comment this post..

 12,545 views last month,  64 views today

About Onur Cirkin

I work as a data warehouse specialist. I have 3 years of experience oracle business intelligence, oracle data integrator, tableau. I have 2 years of experience oracle data migration and also continues. I also work on python machine learning. I am currently continue my graduate education. If you want to ask any question. You can send me mail address [email protected]

Leave a Reply

IT Tutorial