Change Data Capture（三）

最新推荐文章于 2025-07-19 00:40:46 发布

最新推荐文章于 2025-07-19 00:40:46 发布 · 470 阅读

文章标签：

#数据库 #操作系统 #java

本文通过实例演示了在Oracle数据库中如何设置异步HotLog模式的变更数据捕获(CDC)，包括版本检查、发布者设置、初始化参数配置、发布及订阅变化数据等关键步骤。

关于Change Data Capture（三）

作者：NinGoo | 【转载须以超链接形式标明文章原始出处和作者信息】

本文主要是通过一个实际的例子演示异步HotLog模式CDC的实现步骤。关于CDC的概念和模式介绍，请参考关于Change Data Capture（一）

一.版本

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 .

2.授予相应的权限

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 .

三.设置初始化参数

异步CDC除了要用到java pool，还需要使用stream pool，因为异步CDC和streams一样，都是采用logminer技术来从日志中获得数据。如果stream pool过小，可能导致logminer无法成功，可能在alert中出现如下错误：

LOGMINER: Begin mining logfile: E:/ORACLE/ORA10/RDBMS/ARC00662_0626260062.001
Wed Jul 11 14:38:53 2007
krvxerpt: Errors detected in process 27, role preparer.
Wed Jul 11 14:38:53 2007
krvxmrs: Leaving by exception: 4031
Wed Jul 11 14:38:53 2007
Errors in file e:/oracle/ora10/admin/ning/bdump/ning_p004_3252.trc:
ORA-04031: unable to allocate 76 bytes of shared memory (”streams pool”,”unknown object”,”Logminer LCR c”,”krvufa”)

Wed Jul 11 14:38:59 2007
Streams CAPTURE C001 with pid=29, OS id=864 stopped
Wed Jul 11 14:38:59 2007
Errors in file e:/oracle/ora10/admin/ning/bdump/ning_c001_864.trc:
ORA-01304: subordinate process error. Check alert and trace logs
ORA-04031: unable to allocate bytes of shared memory (”",”",”",”")

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

COMPATIBLE=10.2.0
JAVA_POOL_SIZE=50000000
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

四.发布变化数据

1.例如要发布用户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.将数据库置于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.准备源表

SYS @ ning > begin
2 dbms_capture_adm . prepare_table_instantiation (
3 table_name => ' ning.sales ' ) ;
4 end ;
5 /

PL / SQL procedure successfully completed .

5.创建chang set

SYS @ ning > begin
2 dbms_cdc_publish . create_change_set (
3 change_set_name => ' ning_sales_hotlog ' ,
4 description => ' hotlog change set for ning.sales ' ,
5 change_source_name => ' HOTLOG_SOURCE ' ,
6 stop_on_ddl => ' y ' ,
7 begin_date => sysdate ,
8 end_date => sysdate + 5 ) ;
9 end ;
10 /
begin
*
ERROR at line 1 :
ORA - 00258 : manual archiving in NOARCHIVELOG mode must identify log
ORA - 06512 : at " SYS.DBMS_CAPTURE_ADM_INTERNAL " , line 121
ORA - 06512 : at line 1
ORA - 06512 : at " SYS.DBMS_CDC_PUBLISH " , line 560
ORA - 06512 : at line 2

看来异步HotLog模式虽然是从online redo logfile中读取数据，但还是要求数据库处于归档模式。将数据库至于归档模式以后，再次执行：

异步HotLog模式的change source必须是HOTLOG_SOURCE。

6.创建change table

SYS @ ning > begin
2 dbms_cdc_publish . create_change_table (
3 owner => ' cdcpub ' ,
4 change_table_name => ' sales_ct_hotlog ' ,
5 change_set_name => ' ning_sales_hotlog ' ,
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 .

创建好的change table定义如下

SYS @ ning > desc cdcpub . sales_ct_hotlog
Name Null ? Type
----------------------------- -- -------- -------------------------
OPERATION $ CHAR ( 2 )
CSCN $ NUMBER
COMMIT_TIMESTAMP $ DATE
XIDUSN $ NUMBER
XIDSLT $ NUMBER
XIDSEQ $ NUMBER
RSID $ NUMBER
TARGET_COLMAP $ RAW ( 128 )
ID NUMBER ( 38 )
PRODUCTID NUMBER ( 38 )
PRICE NUMBER ( 10 , 2 )
QUANTITY NUMBER ( 38 )

7.启用change set

异步CDC的change set创建完以后默认是disable的，必须手工启用。启用后，oracle stream capture和apply进程将启动。

SYS @ ning > begin
2 dbms_cdc_publish . alter_change_set (
3 change_set_name => ' ning_sales_hotlog ' ,
4 enable_capture => ' y ' ) ;
5 end ;
6 /

PL / SQL procedure successfully completed .

启用后，logminer进程将开始分析日志，直到alert出现如下记录，说明已经分析完所有需要的归档日志(从prepare_table_instantiation的一刻起，这也是需要将数据库置于归档模式的原因)，并且开始分析当前联机日志：

LOGMINER: Begin mining logfile: E:/ORACLE/ORA10/RDBMS/ARC00698_0626260062.001
Wed Jul 11 14:54:34 2007
LOGMINER: End mining logfile: E:/ORACLE/ORA10/RDBMS/ARC00698_0626260062.001
Wed Jul 11 14:54:34 2007
LOGMINER: Begin mining logfile: D:/ORACLE/ORADATA/NING/REDO03.LOG
Wed Jul 11 14:54:35 2007
LOGMINER: End mining logfile: D:/ORACLE/ORADATA/NING/REDO03.LOG
Wed Jul 11 14:54:35 2007
LOGMINER: Begin mining logfile: D:/ORACLE/ORADATA/NING/REDO01.LOG
Wed Jul 11 14:54:35 2007
LOGMINER: End mining logfile: D:/ORACLE/ORADATA/NING/REDO01.LOG
Wed Jul 11 14:54:35 2007
LOGMINER: Begin mining logfile: D:/ORACLE/ORADATA/NING/REDO02.LOG

这时可以在change table中看到捕获到了变化数据（如果有变化的话）：