Change Data Capture in Oracle

Publisher/Subscriber Approach

1)     SYS

a.       The database should run in Archive Log mode

b.      Force Logging should be on.

c.       Create table space for three users (Albert, Publ_cdc and Subs_cdc). Separate table space is not mandatory and but is recommended for clean administration.

d.      Create three users – owner, publisher and subscriber

e.       Grant create session/table/sequence/procedure to three users. Grant connect/resource to three users.

f.        Grant execute_catalog_role and select_catalog_role to publisher and subscriber

g.       Grant execute permission on following DBMS packages to publisher and subscriber

                                                               i.      DBMS_CDC_PUBLISH

                                                             ii.      DBMS_CDC_SUBSCRIBE

                                                           iii.      DBMS_LOCK

h.      Make publisher as stream administrator

i.         Make user table(s) as capture enabled tables.

j.         Important SQL to check status on various things

2)     User/Owner

a.       Create table whose changes needs to be captured

b.      Grant select permission on the  table to publisher and subscriber

c.       Ensure all changes are recorded in redo logs

3)     Publisher

a.       Define CHANGE_SET, SOURCE TABLE and Publication

b.      Grant access of Change table to Subscriber

4)     Subscriber

a.       Create subscription to the publication

b.      Create subscribers view

c.       Activate subscription

5)     Test Asynchronous CDC

1)     SYS

a.       The database should run in Archive Log mode

b.      Force Logging should be on (below command should be run while database is in mont state (not in open)

                                                                           i.      alter database force logging;

                                                                         ii.      alter database add supplemental log data;

c.       Create table space for three users (Albert, Publ_cdc and Subs_cdc).

CREATE TABLESPACE TS_CDC DATAFILE 'C:\ORACLE\ORADATA\TEST10G\TS_CDC.DBF' SIZE 50M

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

SEGMENT SPACE MANAGEMENT AUTO

LOGGING

FORCE LOGGING;

d.      Create three users – owner, publisher and subscriber

create user albert identified by albert

default tablespace ts_cdc

temporary tablespace temp ;

create user publ_cdc identified by publ_cdc

default tablespace ts_cdc

temporary tablespace temp ;

create user subs_cdc identified by subs_cdc

default tablespace ts_cdc

temporary tablespace temp;

e.       Grant create session/table/sequence/procedure to three users. And connect/resource also.

GRANT create session TO albert;

GRANT create table TO albert;

GRANT create sequence TO albert;

GRANT create procedure TO albert;

GRANT connect TO albert;

GRANT resource TO albert;

GRANT create session TO publ_cdc;

GRANT create table TO publ_cdc;

GRANT create sequence TO publ_cdc;

GRANT create procedure TO publ_cdc;

GRANT connect TO publ_cdc;

GRANT resource TO publ_cdc;

GRANT create session TO subs_cdc;

GRANT create table TO subs_cdc;

GRANT create sequence TO subs_cdc;

GRANT create procedure TO subs_cdc;

GRANT connect TO subs_cdc;

GRANT resource TO subs_cdc;

f.        Grant execute_catalog_role and select_catalog_role to publisher and subscriber since they need to access the dictionary i.e. views in SYS schema-

GRANT execute_catalog_role TO publ_cdc;

GRANT select_catalog_role TO publ_cdc;

GRANT execute_catalog_role TO subs_cdc;

GRANT select_catalog_role TO subs_cdc;

g.       Grant execute permission on following DBMS packages to publisher and subscriber

GRANT execute ON DBMS_CDC_PUBLISH TO publ_cdc;

GRANT execute ON DBMS_CDC_SUBSCRIBE TO publ_cdc;

GRANT execute ON DBMS_LOCK TO publ_cdc; -- not mandatory

GRANT execute ON DBMS_CDC_PUBLISH TO subs_cdc;

GRANT execute ON DBMS_CDC_SUBSCRIBE TO subs_cdc;

GRANT execute ON DBMS_LOCK TO subs_cdc; -- not mandatory

h.      Make publisher as stream administrator

execute dbms_streams_auth.grant_admin_privilege('publ_cdc');

The last statement makes PUBL_CDC a streams Administrator.

Let's see if that’s really true:

SQL> select * FROM dba_streams_administrator;

USERNAME LOC ACC

------------------------------ --- ---

PUBL_CDC YES YES

i.         Make user table(s) as capture enabled tables

exec dbms_capture_adm.prepare_table_instantiation(table_name => 'ALBERT.PERSOON');

j.         Important SQL to check status on various things

                                                                           i.      Check capture enabled tables

SQL> SELECT * FROM dba_capture_prepared_tables;

                                                                         ii.      Check if change set, source table or publication is defined?

SQL> SELECT * FROM DBA_SOURCE_TABLES;

SQL> SELECT * FROM cdc_change_sets$;

SQL> SELECT * FROM cdc_change_tables$;

                                                                       iii.      Check if change set is enabled?

SQL> SELECT * FROM CHANGE_SETS;

SQL> SELECT * FROM DBA_PUBLISHED_COLUMNS;

                                                                       iv.      Check if subscription exists?

SQL> SELECT * FROM cdc_subscribers$;

SQL> SELECT * FROM user_subscriptions;

SQL> SELECT * FROM DBA_SUBSCRIPTIONS;

2)     User/Owner

a.       Create table whose changes needs to be captured

create table PERSOON

(

userid number,

name varchar(30),

lastname varchar(30),

constraint pk_userid primary key (userid)

);

               SQL> insert into PERSOON  values (1,'john','west');

                               SQL> insert into PERSOON  values (2,'mary','east');

b.      Grant select permission on the  table to publisher and subscriber

GRANT SELECT ON PERSOON TO publ_cdc;

GRANT SELECT ON PERSOON TO subs_cdc;

c.       Ensure all changes are recorded in redo logs

      ALTER TABLE persoon ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

3)     Publisher

a.       Define CHANGE_SET, SOURCE TABLE and Publication

exec DBMS_CDC_PUBLISH.CREATE_CHANGE_SET('CDC_DEMO_SET', 'CDC Demo 2 Change Set', 'HOTLOG_SOURCE', 'Y', NULL, NULL);

b.      Create Change Table

SQL> BEGIN

DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE('publ_cdc', 'CDC_PERSOON', 'CDC_DEMO_SET',

 'ALBERT', 'PERSOON', 'userid number, name varchar(30), lastname varchar(30)',

 'BOTH', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'Y', 'TABLESPACE TS_CDC');

 END;

 /

c.       Grant Select on Change Table to subscriber

SQL> GRANT select ON CDC_PERSOON TO subs_cdc;

d.      Enable Capture on Change Table

SQL> exec DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(change_set_name=>'CDC_DEMO_SET', enable_capture=> 'Y');

4)     Subscriber

a.       Create subscription to the publication

exec DBMS_CDC_SUBSCRIBE.create_subscription('CDC_DEMO_SET', 'cdc_demo subx', 'CDC_DEMO_SUB');

b.      Create subscribers view

SQL> BEGIN

 DBMS_CDC_SUBSCRIBE.subscribe('CDC_DEMO_SUB', 'ALBERT', 'PERSOON',

 'userid, name, lastname', 'CDC_DEMO_SUB_VIEW');

 END;

 /

c.       Activate subscription

exec DBMS_CDC_SUBSCRIBE.activate_subscription('CDC_DEMO_SUB');

5)     Logon to user (Albert) and make an insert –

First let’s see what is there in PRASOON table-

SQL> select * from PERSOON;

 USERID NAME LASTNAME

---------- ------------------------------ --------------

 1 john west

 2 mary east

Two records.

Now make an isert –

SQL> insert into PERSOON values (3,'harry','north');

1 row created.

SQL> commit;

connect subs_cdc/sub_cdc

Now lets see what we have and what we can retrieve from the CDC objects:

SQL> select * from PUBL_CDC.CDC_PERSOON;

OP CSCN$ COMMIT_TI XIDUSN$ XIDSLT$ XIDSEQ$ RSID$ ROW_ID$ USERNAME$

-- ---------- --------- ---------- ---------- ---------- ---------- ------------------ -------------

I 1176467 29-MAR-08 2 19 488 1 AAAMlmAAGAAAAAgAAA ALBERT

Indeed, Albert inserted 1 record, and so that information must be recorded in the Change Table CDC_PERSOON.  But we cannot see anything yet from the Subscribers View:

SQL> select * from CDC_DEMO_SUB_VIEW;

no rows selected

This is correct behavior. The Subscriber needs to use the DBMS_CDC_SUBSCRIBE.extend_window and DBMS_CDC_SUBSCRIBE.purge_window  procedures for populating and purging the view that shows the changes to Albert's PERSOON table.

                                                                           i.      So Subscriber uses following statement to populate the view

SQL> exec dbms_cdc_subscribe.extend_window('CDC_DEMO_SUB');

SQL> select * from CDC_DEMO_SUB_VIEW;

OP CSCN$ COMMIT_TI XIDUSN$ XIDSLT$ XIDSEQ$ RSID$ ROW_ID$ USERNAME$

-- ---------- --------- ---------- ---------- ---------- ---------- ------------------ -------------

I 1176467 29-MAR-08 2 19 488 1 AAAMlmAAGAAAAAgAAA ALBERT

Now here is the trick: The Subscriber should use the two forementioned procedures in a "loop" so to speak:

use DBMS_CDC_SUBSCRIBE.extend_window Be very "careful" and "disciplined"

 in using extend_window and purge_window

 use the information from the view for obvious reasons.

use DBMS_CDC_SUBSCRIBE.purge_window

If the Subscriber now purges the window, by using DBMS_CDC_SUBSCRIBE.purge_window

the view does not show entries anymore.

SQL> exec dbms_cdc_subscribe.purge_window('CDC_DEMO_SUB');

PL/SQL procedure successfully completed.

SQL> select * from CDC_DEMO_SUB_VIEW;

no rows selected

Now Albert connects again and does another insert.

connect albert/albert

SQL> select * from PERSOON;

 USERID NAME LASTNAME

---------- ------------------------------ --------------

 1 john west

 2 mary east

 3 harry north

SQL> insert into PERSOON

 2 values

 3 (4,'betty','south');

1 row created.

SQL> commit;

Commit complete.

connect subs_cdc/sub_cdc

SQL> select * from CDC_DEMO_SUB_VIEW;

no rows selected

There is nothing to view here, because the Subscriber needs to execute DBMS_CDC_SUBSCRIBE.extend_window

SQL> exec dbms_cdc_subscribe.extend_window('CDC_DEMO_SUB');

PL/SQL procedure successfully completed.

SQL> select * from CDC_DEMO_SUB_VIEW;

OP CSCN$ COMMIT_TI XIDUSN$ XIDSLT$ XIDSEQ$ RSID$ ROW_ID$ USERNAME$

-- ---------- --------- ---------- ---------- ---------- ---------- ------------------ -------------

I 1184151 29-MAR-08 10 6 511 10001 AAAMlmAAGAAAAAdAAA ALBERT

Now we are able to view the new change, that is record no 4, inserted by Albert.

Note that the Change Table does not get purged.

SQL> select * from publ_cdc.cdc_PERSOON;

OP CSCN$ COMMIT_TI XIDUSN$ XIDSLT$ XIDSEQ$ RSID$ ROW_ID$ USERNAME$

-- ---------- --------- ---------- ---------- ---------- ---------- ------------------ -------------

I 1176467 29-MAR-08 2 19 488 1 AAAMlmAAGAAAAAgAAA ALBERT

I 1184151 29-MAR-08 10 6 511 10001 AAAMlmAAGAAAAAdAAA ALBERT

Which shows the two changes from Albert since the activation of Async CDC

Now there REALLY is some sort of a "timing issue" with CDC, which you need to be very carefull of.  According to Oracle, you are supposed to use the data from the Subscribers View.

Ofcourse, you could use the information from the Change table(s), because its just another table  which you could query as usual. But that is NOT recommended.  Now, the view, or the Window, gets populated and purged. So, if you use the view(s), for example to populate Data Warehouse tables, you need to be very disciplined in using "extend_window" and "purge_window".

A "good" way of dealing with this potential problem, is in creating functions or procedures that work like

..

BEGIN

dbms_cdc_subscribe.extend_window();

..

YOUR CODE

..

dbms_cdc_subscribe.purge_window();

EXCEPTION

..

END;

Note: Above steps are for Oracle RDBMS as source. What if Source is non-Oracle RDBMS or a flat file??

Below is link implementing CDC using Informatica utilizing CheckSUM.

http://www.disoln.org/2012/12/Change-Data-Capture-CDC-Implementation-Using-CHECKSUM-Number.html