Change Data Capture（四）

本系列第二和第三篇文章演示了同步CDC和异步HotLog CDC的实现，这两种模式都是在source database中捕获增量数据，所以配置只涉及到一个数据库，相对来说比较简单。而异步CDC的另外两种模式：Distributed HotLog和AutoLog，需要分别配置source database和staging datase，捕获增量数据主要是在staging database中执行，以尽可能的少影响source database的性能。

异步CDC主要是通过stream的技术来实现的，基本上是将Streams的一些配置步骤做了一层封装。如果对于Streams的配置很熟悉的话，配置异步CDC应该说还是一件比较简单的事情。

本文将演示异步Distribute HotLog的配置。文中Source database=ning，Staging Database=test。

一.版本

SYS @ ning > select * from v $ version ;

BANNER
-------------------------------------------------------------------- --
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL / SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32 - bit Windows : Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

二.设置发布者

1.在source database创建一个用户，并授予相应的权限。

SYS @ ning > create user cdcpub identified by cdcpub ;

User created .

SYS @ ning > grant execute_catalog_role to cdcpub ;

Grant succeeded .

SYS @ ning > grant select_catalog_role to cdcpub ;

Grant succeeded .

SYS @ ning > grant create table to cdcpub ;

Grant succeeded .

SYS @ ning > grant create session to cdcpub ;

Grant succeeded .

SYS @ ning > grant dba to cdcpub ;

Grant succeeded .

SYS @ ning > grant execute on dbms_cdc_publish to cdcpub ;

Grant succeeded .

SYS @ ning > execute dbms_streams_auth . grant_admin_privilege ( grantee => ' cdcpub ' ) ;

PL / SQL procedure successfully completed .

2.在Staging database创建一个用户，并授予相应权限。

SYS @ test > create user cdcpub identified by cdcpub ;

User created .

SYS @ test > grant create session to cdcpub ;

Grant succeeded .

SYS @ test > grant create table to cdcpub ;

Grant succeeded .

SYS @ test > grant unlimited tablespace to cdcpub ;

Grant succeeded .

SYS @ test > grant select_catalog_role to cdcpub ;

Grant succeeded .

SYS @ test > grant execute_catalog_role to cdcpub ;

Grant succeeded .

SYS @ test > grant dba to cdcpub ;

Grant succeeded .

SYS @ test > execute dbms_streams_auth . grant_admin_privilege ( grantee => ' cdcpub ' ) ;

PL / SQL procedure successfully completed .

三.设置Source Database的初始化参数

根据文档推荐，为source database设置如下初始化参数：

COMPATIBLE=9.2.0或者10.1.0或者10.2.0 根据source database的版本确定
JAVA_POOL_SIZE=50000000
OPEN_LINKS=4 或者更高
JOB_QUEUE_PROCESSES=(当前值) + 2
PARALLEL_MAX_SERVERS=(当前值) + (5 * (change set数目))
PROCESSES=(当前值) + (7 * (change set数目))
SESSIONS=(当前值) + (2 * (change set数目))
STREAMS_POOL_SIZE=Max(当前值,50 MB) + ((change set数目) * (21 MB))
UNDO_RETENTION=3600

根据网络配置确定是否需要设置GLOBAL_NAMES=TRUE

如果source database是9iR2则还需要设置如下参数

LOGMNR_MAX_PERSISTENT_SESSIONS=chang source数目

四.设置Staging database的初始化参数

COMPATIBLE=10.2.0 staging database必须是10gR2版本
JAVA_POOL_SIZE=50000000
OPEN_LINKS=4 或者更高
JOB_QUEUE_PROCESSES=(当前值) + 2
PARALLEL_MAX_SERVERS=(当前值) + (5 * (change set数目))
PROCESSES=(当前值) + (7 * (change set数目))
SESSIONS=(当前值) + (2 * (change set数目))
STREAMS_POOL_SIZE=Max(当前值,50 MB) + ((change set数目) * (21 MB))

五.发布变化数据

1.配置网络，一边source database和staging database可以相互访问

1.例如要发布source database用户ning下的sales表

SYS @ ning > desc ning . sales ;
Name Null ? Type
--------------------------------------- -- -------- ----------------------------
ID NUMBER ( 38 )
PRODUCTID NUMBER ( 38 )
PRICE NUMBER ( 10 , 2 )
QUANTITY NUMBER ( 38 )

2.授予cdcpub用户对于该表的权限

SYS @ ning > grant all on ning . sales to cdcpub ;

Grant succeeded .

3.将Source Database置于Force logging

由于异步模式是从redo logfile中获得增量数据的，那么nologging操作就会影响到数据的捕捉，所以最好能

将数据库置于force logging模式。

SYS @ ning > alter database force logging ;

Database altered .

为了捕获update操作中各个column的redo数据，必须在数据库级别启用supplimental日志模式。

SYS @ ning > alter database add supplemental log data ;

Database altered .

同时在源表上为需要捕捉的列创建supplemental日志组

SYS @ ning > alter table ning . sales
2 add supplemental log group log_group_sales
3 ( id , productid , price , quantity ) ;

Table altered .

如果打算捕捉所有列，也可以为所有列创建supplemental日志组

SYS @ ning > alter table ning . sales
2 add supplemental log data ( all ) columns ;

Table altered .

4.在source database创建到staging database的database link

SYS @ ning > conn cdcpub / cdcpub
Connected .
CDCPUB @ ning > create database link test
2 connect to cdcpub identified by cdcpub using ' test ' ;

Database link created .

5.在staging database创建到source database的database link

SYS @ test > conn cdcpub / cdcpub
Connected .
CDCPUB @ test > create database link ning
2 connect to cdcpub identified by cdcpub using ' ning ' ;

Database link created .

注意database link的名字要和using的tnsname一致，一开始我使用了不同的名字，结果在后面创建change source的时候一直报错

ERROR at line 1:
ORA-26675: cannot create Streams capture process CDC$C_NING
ORA-06512: at “SYS.DBMS_CAPTURE_ADM_INTERNAL”, line 121
ORA-06512: at line 1
ORA-06512: at “SYS.DBMS_CDC_IPUBLISH”, line 133
ORA-06512: at “SYS.DBMS_CDC_PUBLISH”, line 194
ORA-06512: at line 2

郁闷了我大半个小时，后来将database link改成和tnsname一致后就没有这个错误了。

6.在staging database中创建change source

CDCPUB @ test > begin
2 dbms_cdc_publish . create_hotlog_change_source (
3 change_source_name => ' ning ' ,
4 description => ' distributed hotlog source ' ,
5 source_database => ' ning ' ) ;
6 end ;
7 /

PL / SQL procedure successfully completed .

7.在staging database中创建change set

CDCPUB @ test > begin
2 dbms_cdc_publish . create_change_set (
3 change_set_name => ' ning_sales_dhotlog ' ,
4 description => ' dhotlog change set for ning.sales ' ,
5 change_source_name => ' ning ' ,
6 stop_on_ddl => ' y ' ) ;
7 end ;
8 /

PL / SQL procedure successfully completed .

8.在staging database中创建change table

CDCPUB @ test > begin
2 dbms_cdc_publish . create_change_table (
3 owner => ' cdcpub ' ,
4 change_table_name => ' sales_ct_dhotlog ' ,
5 change_set_name => ' ning_sales_dhotlog ' ,
6 source_schema => ' ning ' ,
7 source_table => ' sales ' ,
8 column_type_list => ' id int,productid int,price number(10,2),quantity int ' ,
9 capture_values => ' both ' ,
10 rs_id => ' y ' ,
11 row_id => ' n ' ,
12 user_id => ' n ' ,
13 timestamp => ' n ' ,
14 object_id => ' n ' ,
15 source_colmap => ' n ' ,
16 target_colmap => ' y ' ,
17 options_string => ' tablespace users ' ) ;
18 end ;
19 /
begin
*
ERROR at line 1 :
ORA - 29540 : class oracle / CDC / PublishApi does not exist
ORA - 06512 : at " SYS.DBMS_CDC_PUBLISH " , line 611
ORA - 06512 : at line 2

faint，真是多灾多难，又出错了。Google了下， jlandzpa也遇到过这个问题，他是删除了CDC组件重装后解决的该问题。应该是staging database安装的时候CDC的编译出了问题，有个api的类没有正确编译。

CDCPUB @ test > conn / as sysdba
Connected .
SYS @ test >@$ ORACLE_HOME / rdbms / admin / rmcdc . sql

SYS @ test >@$ ORACLE_HOME / rdbms / admin / initcdc . sql

继续执行，ok

SYS @ test > conn cdcpub / cdcpub
Connected .

CDCPUB @ test > begin
2 dbms_cdc_publish . create_change_table (
3 owner => ' cdcpub ' ,
4 change_table_name => ' sales_ct_dhotlog ' ,
5 change_set_name => ' ning_sales_dhotlog ' ,
6 source_schema => ' ning ' ,
7 source_table => ' sales ' ,
8 column_type_list => ' id int,productid int,price number(10,2),quantity int ' ,
9 capture_values => ' both ' ,
10 rs_id => ' y ' ,
11 row_id => ' n ' ,
12 user_id => ' n ' ,
13 timestamp => ' n ' ,
14 object_id => ' n ' ,
15 source_colmap => ' n ' ,
16 target_colmap => ' y ' ,
17 options_string => ' tablespace users ' ) ;
18 end ;
19 /

PL / SQL procedure successfully completed .

9.在staging database中启用change source

CDCPUB @ test > begin
2 dbms_cdc_publish . alter_hotlog_change_source (
3 change_source_name => ' ning ' ,
4 enable_source => ' Y ' ) ;
5 end ;
6 /

PL / SQL procedure successfully completed .

10.在staging database中启用change set

CDCPUB @ test > begin
2 dbms_cdc_publish . alter_change_set (
3 change_set_name => ' ning_sales_dhotlog ' ,
4 enable_capture => ' y ' ) ;
5 end ;
6 /

PL / SQL procedure successfully completed .

查看alert日志，发现streams的capture和apply进程已经启动成功

Streams APPLY A001 started with pid=14, OS id=2904
Streams Apply Reader started P000 with pid=20 OS id=3912
Streams Apply Server started P001 with pid=21 OS id=2448

11.在staging database中将change table的读取权限授予订阅者

先创建订阅者帐号，然后授权

CDCPUB @ test > create user cdcsub identified by cdcsub ;

User created .

CDCPUB @ test > grant create session to cdcsub ;

Grant succeeded .

CDCPUB @ test > grant create table to cdcsub ;

Grant succeeded .

CDCPUB @ test > grant select on cdcpub . sales_ct_dhotlog to cdcsub ;

Grant succeeded .

五.订阅变化数据

订阅的步骤和同步CDC模式基本一致，这里就不重复了，需要注意的是，在异步分布式HotLog和异步AutoLog中，订阅者和订阅过程都是在staging database中完成的。具体步骤请参考关于Change Data Capture（二）中关于订阅变化数据的部分。