About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Saturday, August 31, 2013

How to implement Change Data Capture in Oracle

Change Data Capture is a feature in Oracle Database which identifies data change and keep record of it whether the change is a insert , update or delete. This Change Data which is recorded is available for applications or individuals.

Prior to Change Data Capture there are many ways through which Change data can be captured like Table Differencing and Change Value Selection . Performing Table Differencing computational cost is too high to generate the change data.

Capturing of change data using Change data Capture can be done in following ways :-

1. Synchronous :- In this mode whenever any DML(insert , update or delete) is performed on the source table triggers on the source database allow change data to capture immediately. In this mode, transaction is not closed until change data has not been captured.

2. Asynchronous :- In this mode change data will be captured once transaction has been completed means its not a part of transaction. Change data will be captured with the help of redo logs. There are three modes of asynchronous change data capture.

There are three modes of asynchronous change data capture : Hot Log, Distributed Hot Log and Autolog. PFB details:-

a. Hot Log :- In this mode change data is captured from the online redo log files . There is a time lag between committing source tables transactions and arrival of change data to change tables. Change tables in this mode has to be present in source database.

b. Distributed Hot Log :- In distributed hot log Source tables are in Source database and change tables are in staging database. In this mode also change data is captured through online logs. Source and Staging database can be on different platforms and can have different OS installed.

c. Auto Log :- In Autolog mode change data is captured from a set of redo log files managed by Redo transport services.  It controls the automatic transfer of redo log files from source database to staging database. In this mode change data can be captured from online logs as well as from archive logs. These modes are called as Asynchronous Autolog online and Asynchronous Autolog archive. 

Change data Capture have one publisher who publishes change data to change tables from source tables. Multiple Applications and individuals who want to access that change data are called as subscribers. 

PFB steps to implement Change Data Capture in Asynchronous Hot Log mode in Oracle :-

1. Create a tablespace for Change Data Capture

SQL> create tablespace ts_cdcpub datafile '/amit/TEST/ts_cdcpub01.dbf' size 300m;

Tablespace created.

2. Create a publisher user 

SQL> CREATE USER cdcpub IDENTIFIED by cdcpub DEFAULT TABLESPACE ts_cdcpub QUOTA UNLIMITED ON SYSTEM QUOTA UNLIMITED ON SYSAUX;

User created.

3. Grant privileges to Publisher user :-

 SQL> GRANT CREATE SESSION TO cdcpub;

Grant succeeded.

SQL> GRANT CREATE TABLE TO cdcpub;

Grant succeeded.

SQL> GRANT CREATE TABLESPACE TO cdcpub;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO cdcpub;

Grant succeeded.

SQL> GRANT SELECT_CATALOG_ROLE TO cdcpub;

Grant succeeded.

SQL> GRANT EXECUTE_CATALOG_ROLE TO cdcpub;

Grant succeeded.

SQL> GRANT CREATE SEQUENCE TO cdcpub;

Grant succeeded.

SQL> GRANT DBA TO cdcpub;

Grant succeeded.

SQL> GRANT EXECUTE on DBMS_CDC_PUBLISH TO cdcpub;

Grant succeeded.

SQL> EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE => 'cdcpub');

PL/SQL procedure successfully completed.

4. Check that the source table and column datatypes supports Change Data Capture

 SQL> BEGIN
               DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME => 'amit.testing');
           END;
          /
PL/SQL procedure successfully completed.

5. Create a Change set using DBMS_CDC_PUBLISH package

SQL> BEGIN
              DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
              change_set_name => 'AMIT_TEST',
              description => 'TESTING_PURPOSE',
              change_source_name => 'HOTLOG_SOURCE',
              stop_on_ddl => 'y',
              begin_date => sysdate,
             end_date => sysdate+100);
          END;
         /
PL/SQL procedure successfully completed.

This change set will capture the changes starting from today and stops capturing after 100 days.

SQL> conn amit/amit
Connected.
SQL> desc testing
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(12)
 NAME                                               VARCHAR2(23)

6. Create Change table that will contain changes to source tables. Here Source table is TESTING :-

SQL> conn /as sysdba
Connected.

SQL> BEGIN
  2     DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
  3     owner              => 'cdcpub',
  4     change_table_name  => 'test_ct',
  5     change_set_name    => 'AMIT_TEST',
  6     source_schema      => 'AMIT',
  7     source_table       => 'TESTING',
  8  column_type_list   => 'ID NUMBER(12), NAME VARCHAR2(23)',
  9     capture_values     => 'both',
 10     rs_id              => 'y',
 11     row_id             => 'n',
 12     user_id            => 'n',
 13     timestamp          => 'n',
   object_id          => 'n',
 14   15     source_colmap      => 'n',
 16     target_colmap      => 'y',
 17     options_string     => 'TABLESPACE TS_CDCPUB');
 18  END;
 19  /
PL/SQL procedure successfully completed.

7. Enable the Change set :-

SQL> BEGIN
             DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
             change_set_name => 'AMIT_TEST',
             enable_capture => 'y');
          END;
         /
 PL/SQL procedure successfully completed.

SQL> grant select on cdcpub.test_ct to amit;

Grant succeeded.

SQL> conn amit/amit
Connected.
SQL> SELECT * FROM ALL_SOURCE_TABLES;

no rows selected

8. Find the source tables for which subscribers has access privileges :-

SQL> conn cdcpub/cdcpub
Connected.

9. Find change sets and columns for which subscribers have access privileges 

SQL> SELECT UNIQUE CHANGE_SET_NAME, COLUMN_NAME, PUB_ID
FROM ALL_PUBLISHED_COLUMNS
WHERE SOURCE_SCHEMA_NAME ='AMIT' AND SOURCE_TABLE_NAME = 'TESTING';
  2    3
CHANGE_SET_NAME                COLUMN_NAME                        PUB_ID
------------------------------ ------------------------------ ----------
AMIT_TEST                      NAME                                75247
AMIT_TEST                      ID                                  75247

10. Create a subscription which can access the change data in the Change tables :-

SQL>     BEGIN
                 DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION(
                change_set_name   => 'AMIT_TEST',
                description       => 'Change data for test',
                subscription_name => 'SUBS_TEST');
              END;
             /
PL/SQL procedure successfully completed.

11. Subscribe the subscriber to source table and columns in source table :-

 SQL>  BEGIN
  2          DBMS_CDC_SUBSCRIBE.SUBSCRIBE(
  3          subscription_name => 'SUBS_TEST',
  4          source_schema     => 'AMIT',
  5          source_table      => 'TESTING',
  6          column_list       => 'ID,NAME',
  7          subscriber_view   => 'SUBS_VIEW');
  8        END;
  9       /
PL/SQL procedure successfully completed.

12. Activate the subscription 

SQL> BEGIN
            DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION(
           subscription_name => 'SUBS_TEST');
          END;
         /
PL/SQL procedure successfully completed.

13. This PL/SQL block will get you the next set of change data. Whenever data changed in source table and captured in Change table then this PL/SQL block will get you data in subscription window.

 SQL> BEGIN
             DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
             subscription_name => 'SUBS_TEST');
           END;
           /
PL/SQL procedure successfully completed.

14. Check that the Changes made to the source table reflects in subscription window :-

SQL> SELECT id,name FROM SUBS_VIEW;

no rows selected

SQL> conn amit/amit
Connected.
SQL>
SQL> insert into testing values('1','AMITRATH');

1 row created.

SQL> insert into testing values('2','ORACLE');

1 row created.

SQL> insert into TESTING values('3','CHANGEDATACPATURE');

1 row created.

SQL> commit;

Commit complete.

SQL> conn cdcpub/cdcpub;
Connected.

SQL> SELECT id,name FROM SUBS_VIEW;

no rows selected

SQL> BEGIN
            DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
            subscription_name => 'SUBS_TEST');
          END;
          /
 PL/SQL procedure successfully completed.

SQL> select  OPERATION$,to_char(COMMIT_TIMESTAMP$,'dd-mon-yyyy hh24:mi:ss'),ID,NAME from subs_view;

OP TO_CHAR(COMMIT_TIMESTAMP$,'DD         ID NAME
-- ----------------------------- ---------- -----------------------
I  28-aug-2013 10:56:30                   1 AMITRATH
I  28-aug-2013 10:56:30                   2 ORACLE
I  28-aug-2013 10:56:30                   3 CHANGEDATACPATURE

SQL> update testing set name='AMIT_RATH' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL>
SQL> conn cdcpub/cdcpub
Connected.

SQL>  BEGIN
             DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
             subscription_name => 'SUBS_TEST');
           END;
          /
PL/SQL procedure successfully completed.

SQL> select  OPERATION$,to_char(COMMIT_TIMESTAMP$,'dd-mon-yyyy hh24:mi:ss'),ID,NAME from subs_view;

OP TO_CHAR(COMMIT_TIMESTAMP$,'DD         ID NAME
-- ----------------------------- ---------- --------------------------------------------------
I  28-aug-2013 10:56:30                   1                     AMITRATH
I  28-aug-2013 10:56:30                   2                     ORACLE
I  28-aug-2013 10:56:30                   3                     CHANGEDATACPATURE
UO 28-aug-2013 15:12:55               1                     AMITRATH
UN 28-aug-2013 15:12:55               1                     AMIT_RATH

Whatever changes we have made to source tables reflects in change table and listed in subscription window.
If we insert data in Source table, all records details will be reflected in Change tables. If we update then both data before update and data after update will be recorded in Change tables and reflected in subscription window.

I hope this article helped you.

Regards,
Amit Rath

2 comments:

  1. Hi,

    How do I find out if a table is part of Synchronous or Asynchronous Subscription. Please tell me.

    Regards,
    Shaik.

    ReplyDelete