An example of implementing Async CDC


/* I found this article via the Internet, I liked it, I would like to share with you*/

In this example, we build an Asynchroneous CDC system from scratch. Before we can actually start with CDC,  we need to evaluate what Oracle init.ora (spfile) settings are needed (or desired), 
and we will then create the users who will perform the roles of publisher, subscriber, and source table owner. Then we are ready to implement an Async CDC system. 

1. Oracle database settings 

If you use a small 10g (10.2.0.x) test database, chances are that your init.ora/spfile.ora settings are not optimized for Async CDC. For example, the various pools in the SGA can be automatically managed by Oracle 10g. With the initialization parameter "SGA_TARGET=" you tell Oracle to automanage memory. It's our observation,  and confirmed in nummerous CDC docs and notes, that on a small test system, its probably better to manage the buffers and pools yourself.  Secondly, the database needs to run in archive mode. Thirdly, forced logging needs to be enabled. 
Therefore, the following settings were in effect on my test system. 

1.1 The most relevant init.ora (spfile) initialisation parameters are: 

log_archive_format=ARC%S_%R.%T # Archive log settings 
LOG_ARCHIVE_DEST=c:\oracle\oradata\log # just choose an appropriate location 

db_cache_size =268435456 # small values are listed below. You can make them bigger ofcourse. 
java_pool_size = 67108864 
shared_pool_size = 67108864 
java_pool_size = 67108864 
streams_pool_size = 67108864 # you need to explicitly define a streams pool 
aq_tm_processes = 3 
global_names = true 
job_queue_processes = 10 
compatible = 10.2.0.1.0 
processes = 300 
PARALLEL_MAX_SERVERS = 10 

Once that set in my init.ora (or spfile) I can mount my database (but not open) as follows: 

SQL> startup mount pfile=c:\oracle\admin\test10g\pfile\init.ora 
ORACLE instance started. 

Total System Global Area 473956352 bytes 
Fixed Size 1249656 bytes 
Variable Size 201330312 bytes 
Database Buffers 268435456 bytes 
Redo Buffers 2940928 bytes 
Database mounted. 

Now I want the database to function in archivelog mode, and forced logging to work, so I will enter the following commands: 

alter database archivelog; 
archive log start; 
alter database force logging; 
alter database add supplemental log data; 
alter database open; 

SQL> archive log start; 
Statement processed. 
SQL> alter database force logging; 
Database altered. 
SQL> alter database add supplemental log data; 
Database altered. 
SQL> alter database open; 
Database altered. 

The database is open, and we can proceed to create the various users. 

2. Create the users (publisher, subscriber, owner) and dedicated tablespace 

It is not mandatory to create a separate tablespace to hold the objects (like the changetable(s)), but we like a clean administration, so we create the following tablespace in our test environment: 

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; 

We now create the users: ALBERT = Source table owner 
 PUBL_CDC = The Publisher 
 SUBS_CDC = The Subscriber 

create user albert identified by albert 
default tablespace ts_cdc 
temporary tablespace temp 
QUOTA 10M ON sysaux 
QUOTA 20M ON users 
QUOTA 50M ON ts_cdc 
; 

create user publ_cdc identified by publ_cdc 
default tablespace ts_cdc 
temporary tablespace temp 
QUOTA 10M ON sysaux 
QUOTA 20M ON users 
QUOTA 50M ON TS_CDC 
; 

create user subs_cdc identified by subs_cdc 
default tablespace ts_cdc 
temporary tablespace temp 
QUOTA 10M ON sysaux 
QUOTA 20M ON users 
QUOTA 50M ON TS_CDC 
; 

Now give the various users the right permissions. Be sure to be logged on as SYS (as sysdba). 

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; 

PUBL_CDC and SUBS_CDC need to access the Dictionary, so we give them permissions to do so: 

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; 

The following GRANTS are permisions on the most important DBMS packages: 

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 

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 

3. Setup Async CDC 

We will create an Async CDC system, with the following characteristics: 

SOURCE TABLE OWNER: ALBERT 
SOURCE TABLE : PERSOON 
PUBLISHER : publ_cdc 
CDC_SET : CDC_DEMO_SET 
SUBSCRIBER : subs_cdc 
CHANGE TABLE : CDC_PERSOON 
CHANGE_SOURCE : HOTLOG_SOURCE 

First logon as ALBERT, in order to create the source table. This table will "emulate" a regular production table  as would be the case in reality. 

connect albert/albert 

create table PERSOON 
( 
userid number, 
name varchar(30), 
lastname varchar(30), 
constraint pk_userid primary key (userid) 
); 

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

1 row created. 

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

1 row created. 

SQL> commit; 

The Publisher needs to access Albert's table, and in this case Albert gives both the Publisher and Subscriber access: 

GRANT SELECT ON PERSOON TO publ_cdc; 
GRANT SELECT ON PERSOON TO subs_cdc; 

Albert makes sure that all changes will be recorded in the redologs, so he enters: 

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

connect sys as sysdba 
Now we quickly logon as SYS, to view what the present situation is. 

SQL> SELECT table_name, scn, supplemental_log_data_pk, supplemental_log_data_ui, 
 2 supplemental_log_data_fk, supplemental_log_data_all 
 3 FROM dba_capture_prepared_tables; 

no rows selected 

Table PERSOON is not yet a CAPTURE PREPARED TABLE, so SYS does the following: 

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

PL/SQL procedure successfully completed. 

SQL> SELECT table_name, scn, supplemental_log_data_pk, supplemental_log_data_ui, 
 2 supplemental_log_data_fk, supplemental_log_data_all 
 3 FROM dba_capture_prepared_tables; 

TABLE_NAME SCN SUPPLEME SUPPLEME SUPPLEME SUPPLEME 
------------------------------ ---------- -------- -------- -------- -------- 
PERSOON 1125337 IMPLICIT IMPLICIT IMPLICIT EXPLICIT 

As a sidestep, we can investigate whether a CHANGE_SET or SOURCE TABLE or Publication is defined 
which ofcourse are not installed yet. 

SQL> SELECT * FROM DBA_SOURCE_TABLES; 

no rows selected 

SQL> SELECT set_name,capture_name,queue_name,queue_table_name,capture_enabled 
 2 FROM cdc_change_sets$; 

SET_NAME CAPTURE_NAME QUEUE_NAME QUEUE_TABLE_NAME 
------------------------------ ------------------------------ ------------------------------ ------- 
SYNC_SET 

SQL> select * from DBA_PUBLISHED_COLUMNS; 

no rows selected 

So, there is no Publication yet, which we ofcourse already knew. 

Next, we will logon as the Publisher, and create the CHANGE SET, PUBLICATION and CHANGE TABLE. 

connect publ_cdc/publ_cdc 

The Publisher uses DBMS_CDC_PUBLISH.CREATE_CHANGE_SET to create the Change Set: 

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

PL/SQL procedure successfully completed. 

(Note the HOTLOG_SOURCE) 

connect sys as sysdba 
Now lets take a look as SYS en see if the CHANGE SET exists.. 

SQL> SELECT SET_NAME,CHANGE_SOURCE_NAME,BEGIN_SCN,END_SCN,CAPTURE_ENABLED,PURGING,QUEUE_NAME 
 2 FROM CHANGE_SETS; 

SET_NAME CHANGE_SOURCE_NAME BEGIN_SCN END_SCN C P QUEUE_NAME 
------------------------------ ------------------------------ ---------- ---------- - - ------------ 
SYNC_SET SYNC_SOURCE Y N 
CDC_DEMO_SET HOTLOG_SOURCE N N CDC$Q_CDC_DEMO_SET 

SQL> SELECT set_name, capture_name, queue_name, queue_table_name 
 2 FROM cdc_change_sets$; 

SET_NAME CAPTURE_NAME QUEUE_NAME QUEUE_TABLE_NAME 
------------------------------ ------------------------------ ------------------------------ ------- 
SYNC_SET 
CDC_DEMO_SET CDC$C_CDC_DEMO_SET CDC$Q_CDC_DEMO_SET CDC$T_CDC_DEMO_SET 

Yes, its there.  Please also observe that the DBMS_CDC_PUBLISH.CREATE_CHANGE_SET statement issued by the Pulisher also  setup the queue and queue table. 

 Note: To drop a CHANGE SET: 

 if you need to drop a change set, use: 
 DBMS_CDC_PUBLISH.DROP_CHANGE_SET(change_set_name IN VARCHAR2); 

Back to the session of the Publisher. 

connect publ_cdc/publ_cdc 

The Publisher now needs to create the CHANGE TABLE CDC_PERSOON that will be associated to ALBERT.PERSOON.  The CDC_PERSOON will capture all changes, that is, all delta's, to the table PERSOON.  In order to create the Change Table, the Publisher uses the DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE procedure. 

SQL> BEGIN 
 2 DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE('publ_cdc', 'CDC_PERSOON', 'CDC_DEMO_SET', 
 3 'ALBERT', 'PERSOON', 'userid number, name varchar(30), lastname varchar(30)', 
 4 'BOTH', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'Y', 'TABLESPACE TS_CDC'); 
 5 END; 
 6 / 

PL/SQL procedure successfully completed. 

The Subscriber needs access to the change table, so the Publisher does the following: 

SQL> GRANT select ON CDC_PERSOON TO subs_cdc; 

Grant succeeded. 

 Note: To drop a change table use: 

 -- drop the change table 

 DBMS_CDC_PUBLISH.DROP_CHANGE_TABLE( 
 owner IN VARCHAR2, 
 change_table_name IN VARCHAR2, 
 force_flag IN CHAR); 

 exec DBMS_CDC_PUBLISH.drop_change_table('publ_cdc','CDC_PERSOON','Y'); 
 exec DBMS_CDC_PUBLISH.drop_change_table(tdba_cdc','cdc_CRWREQUEST','Y'); 

connect sys as sysdba 
Now lets take a look as SYS en see if the CHANGE TABLE is indeed there. 

SQL> select CHANGE_TABLE_NAME,CHANGE_TABLE_SCHEMA,SOURCE_TABLE_NAME,SOURCE_SCHEMA_NAME 
 2 from cdc_change_tables$; 

CHANGE_TABLE_NAME CHANGE_TABLE_SCHEMA SOURCE_TABLE_NAME SOURCE_SCHEMA_NAME 
------------------------------ ------------------------------ ------------------------------ ------- 
CDC_PERSOON PUBL_CDC PERSOON ALBERT 

Yes, also the Change Table in the schema of the Publisher is there. 

connect publ_cdc/publ_cdc 

The Publisher will now enable the capture. 

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

PL/SQL procedure successfully completed. 

connect sys as sysdba 
Now lets again take a look as SYS en see if the CHANGE SET is indeed enabled for capture. 

SQL> SELECT SET_NAME,CHANGE_SOURCE_NAME,BEGIN_SCN,END_SCN,CAPTURE_ENABLED,PURGING,QUEUE_NAME 
 2 FROM CHANGE_SETS; 

SET_NAME CHANGE_SOURCE_NAME BEGIN_SCN END_SCN C P QUEUE_NAME 
------------------------------ ------------------------------ ---------- ---------- - - ------------ 
SYNC_SET SYNC_SOURCE Y N 
CDC_DEMO_SET HOTLOG_SOURCE Y N CDC$Q_CDC_DEMO_SET 

SQL> select CHANGE_SET_NAME,CHANGE_TABLE_NAME,PUB_ID,COLUMN_NAME from DBA_PUBLISHED_COLUMNS; 

CHANGE_SET_NAME CHANGE_TABLE_NAME PUB_ID COLUMN_NAME 
------------------------------ ------------------------------ ---------- -------------------- 
CDC_DEMO_SET CDC_PERSOON 51633 USERID 
CDC_DEMO_SET CDC_PERSOON 51633 LASTNAME 
CDC_DEMO_SET CDC_PERSOON 51633 NAME 

Now the Subscriber needs to create a subscription to the Publication that was just created. 

connect subs_cdc/sub_cdc 

Create the Subscription: 

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

PL/SQL procedure successfully completed. 

 Note: 

 DBMS_CDC_SUBSCRIBE.create_subscription( 
 change_set_name IN VARCHAR2, 
 description IN VARCHAR2, 
 subscription_name IN VARCHAR2); 

 Note: 
 If you want to drop a subscription, use: 

 DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIPTION(subscription_name IN VARCHAR2); 

connect sys as sysdba 
Now lets again take a look as SYS en see if the Subscription exists. 

SQL> SELECT subscription_name, handle, set_name, username, earliest_scn, description 
 2 FROM cdc_subscribers$; 

SUBSCRIPTION_NAME HANDLE SET_NAME USERNAME EARLIEST_SCN 
------------------------------ ---------- ------------------------------ --------------------------- 
CDC_DEMO_SUB 1 CDC_DEMO_SET SUBS_CDC 1 

SQL> SELECT SET_NAME,STATUS,EARLIEST_SCN,LATEST_SCN,to_char(LAST_PURGED, 'DD-MM-YYYY;HH24:MI'), 
 2 to_char(LAST_EXTENDED, 'DD-MM-YYYY;HH24:MI'),SUBSCRIPTION_NAME 
 3 FROM DBA_SUBSCRIPTIONS; 

SET_NAME S EARLIEST_SCN LATEST_SCN TO_CHAR(LAST_PUR TO_CHAR(LAST_EXT SUBSCRIPTION_NAME 
------------------------------ - ------------ ---------- ---------------- ---------------- --------- 
CDC_DEMO_SET N 1 0 CDC_DEMO_SUB 

connect subs_cdc/sub_cdc 

SQL> BEGIN 
 2 DBMS_CDC_SUBSCRIBE.subscribe('CDC_DEMO_SUB', 'ALBERT', 'PERSOON', 
 3 'userid, name, lastname', 'CDC_DEMO_SUB_VIEW'); 
 4 END; 
 5 / 

PL/SQL procedure successfully completed. 

At this point, the Subscription and the Subscribers View exists. 

SQL> SELECT set_name, subscription_name, status 
 2 FROM user_subscriptions; 

SET_NAME SUBSCRIPTION_NAME S 
------------------------------ ------------------------------ - 
CDC_DEMO_SET CDC_DEMO_SUB N 

We can see that the subscription exists, but it is not active. 

SQL> exec DBMS_CDC_SUBSCRIBE.activate_subscription('CDC_DEMO_SUB'); 

PL/SQL procedure successfully completed. 

SQL> SELECT set_name, subscription_name, status 
 2 FROM user_subscriptions; 

SET_NAME SUBSCRIPTION_NAME S 
------------------------------ ------------------------------ - 
CDC_DEMO_SET CDC_DEMO_SUB A 

The Subscription exists and is active, but the Subscribers view does not contain anything yet. The capturing has just starten. Only if Albert's table PERSOON gets INSERTS, UPDATES or DELETES, 
we may expect the view to show the changes. 

So lets logon as Albert and do a couple of inserts: 

connect albert/albert 

SQL> select * from PERSOON; 

 USERID NAME LASTNAME 
---------- ------------------------------ -------------- 
 1 john west 
 2 mary east 

SQL> insert into PERSOON 
 2 values 
 3 (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 behaviour. 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. 

So the Subscribers uses the following statement to "populate" the view: 

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 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;




Capture
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: