Change Data Capture(四)

本文详细介绍了Oracle数据库中异步Distributed HotLog CDC的具体配置步骤,包括版本要求、用户权限设置、初始化参数调整等关键环节。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

本系列第二和第三篇文章演示了同步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(二)中关于订阅变化数据的部分。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值