Oracle Asynchronous CDC (Change Data Capture)

Before this post it will be helpful to read Oracle CDC (Change Data Capture) with Materialized View Log and Oracle Synchronous CDC (Change Data Capture).

Lets start with creating table below.
CREATE TABLE customer (
    cust_id NUMBER,
    cust_name VARCHAR2(50),
    cust_type NUMBER,
    birth_date DATE
); 

select * from change_sets

begin
    dbms_cdc_publish.create_change_set(
        change_set_name => 'HOTLOG_SET',
        description => 'HOTLOG CHANGE SET',
        change_source_name => 'HOTLOG_SOURCE'
    ); 
end;

BEGIN
    DBMS_LOGMNR_CDC_PUBLISH.alter_change_set(change_set_name => 'HOTLOG_SET', enable_capture => 'Y',
        stop_on_ddl     => 'Y');
END;


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        => 'HOTLOG_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 => 'HOTLOG_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;