Oracle Synchronous CDC (Change Data Capture)

 
Datawarehouses are designed to generate analytical reports and analysis. We can ofcourse do these analysis and reports on oltp system but this can decrease operational system performance. Usually end-of-day data is enough for analysis and analytical reports. So it is enough to load end-of-day data to datawarehouse. To load datawahouse it is not a good solution to retrieve all records from source tables to destination tables. And this can take much time. For example yo can't transfer 15 million data from source to destination table every day. It takes time and is also unnnecassary. A better way than transporting table data is extracting data that has changed and processing these changes to destination tables. Oracle Synchronous CDC (Change Data Capture) is one of data extraction methods.
Don't forget that companies usually has more than one operational system. That means you had to extract data from multiple oltp systems to your datawarehouse.
You may also want to look at CDC (Change Data Capture) with Materialized View Log.

Lets start with creating table below.
CREATE TABLE customer (
    cust_id NUMBER,
    cust_name VARCHAR2(50),
    cust_type NUMBER,
    birth_date DATE
);
This code creates a  change table for customer table. Change table stores all dml statements that executed on main table.
BEGIN
   DBMS_LOGMNR_CDC_PUBLISH.create_change_table
                                           (owner                  => sys_context('USERENV','SESSION_USER') ,
                                            source_schema          => sys_context('USERENV','SESSION_SCHEMA') ,
                                            change_set_name        => 'SYNC_SET',
                                            capture_values         => 'both',
                                            rs_id                  => 'y',
                                            row_id                 => 'n',
                                            user_id                => 'y',
                                            TIMESTAMP              => 'y',
                                            object_id              => 'n',
                                            source_colmap          => 'n',
                                            target_colmap          => 'n',
                                            options_string         => NULL,
                                            source_table           => 'CUSTOMER',
                                            change_table_name      => 'CDC_CUSTOMER',
                                            column_type_list       => 'CUST_ID NUMBER,CUST_NAME VARCHAR2(50),CUST_TYPE NUMBER,BIRTH_DATE DATE'
                                           );
END;
Lets execute some dml statements on customer table.
INSERT INTO customer(cust_id, cust_name, cust_type, birth_date)
     VALUES (1,'John', 1, TO_DATE ('25/02/1978', 'DD/MM/YYYY'))

INSERT INTO customer(cust_id, cust_name, cust_type, birth_date)
     VALUES (2,'Gabriel', 5, TO_DATE ('25/02/1985', 'DD/MM/YYYY'))

UPDATE customer
   SET cust_type = 7,
       birth_date = TO_DATE ('25/02/1982', 'DD/MM/YYYY')
 WHERE cust_id = 1

DELETE FROM customer
      WHERE cust_id = 2

We can easily see changing records in cdc table.
select * from customer
select * from cdc_customer
 
We also need to create a subscription for this change table.

declare
    vSubhandle NUMBER;       
    v_subscription_description VARCHAR2(1000):= 'SUB_CUSTOMER';
    col_names VARCHAR2(32767);
   
BEGIN
    DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION(CHANGE_SET_NAME => 'SYNC_SET',
             DESCRIPTION => v_subscription_description, SUBSCRIPTION_NAME => v_subscription_description);            
    SELECT handle INTO vSubhandle FROM all_subscriptions
        WHERE description = v_subscription_description;   
     
    col_names := 'CUST_ID NUMBER,CUST_NAME VARCHAR2(50),CUST_TYPE NUMBER,BIRTH_DATE DATE';
     
    DBMS_LOGMNR_CDC_SUBSCRIBE.SUBSCRIBE (vSubhandle
        , sys_context('USERENV','SESSION_SCHEMA'), 'CUSTOMER', col_names);
     
    DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION(vSubhandle);
END;

We can view subscription in all_subscriptions table.
SELECT handle, set_name, created, status, earliest_scn, latest_scn,
       last_purged, last_extended
  FROM all_subscriptions

We have to extend our subscription to capture change data . After extending we can retrieve records from cdc_customer table that have cscn$ between earliest_scn and latest_scn. These change set can be executed on destination database. After a successful execution we should purge the subscription.

Extending subscription
declare
    vSubhandle NUMBER;
    v_subscription_description VARCHAR2(1000):= 'SUB_CUSTOMER';
BEGIN
    SELECT handle INTO vSubhandle FROM all_subscriptions
        WHERE description = v_subscription_description;
    -- Extend the window for subscription
    DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(SUBSCRIPTION_HANDLE=>vSubhandle);
END;


Purging subscription
declare
    vSubhandle NUMBER;
    v_subscription_description VARCHAR2(1000):= 'SUB_CUSTOMER';
BEGIN
    vSubhandle :=0;
    SELECT handle INTO vSubhandle FROM all_subscriptions
        WHERE description = v_subscription_description;
    DBMS_CDC_SUBSCRIBE.PURGE_WINDOW(
        SUBSCRIPTION_HANDLE=> vSubhandle);
END;