本文主要是通过一个实际的例子演示异步HotLog模式CDC的实现步骤。关于CDC的概念和模式介绍，请参考关于Change Data Capture（一）
一.版本
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创建一个用户作为发布者
User created .
2.授予相应的权限
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表
Name Null ? Type
------------------------------ -- -------- -------------------
ID NUMBER ( 38 )
PRODUCTID NUMBER ( 38 )
PRICE NUMBER ( 10 , 2 )
QUANTITY NUMBER ( 38 )
2.授予cdcpub用户对于该表的权限
Grant succeeded .
3.将数据库置于Force logging
由于异步模式是从redo logfile中获得增量数据的，那么nologging操作就会影响到数据的捕捉，所以最好能将数据库置于force logging模式。
Database altered .
为了捕获update操作中各个column的redo数据，必须在数据库级别启用supplimental日志模式。
Database altered .
同时在源表上为需要捕捉的列创建supplemental日志组
2 add supplemental log group log_group_sales
3 ( id , productid , price , quantity ) ;
Table altered .
如果打算捕捉所有列，也可以为所有列创建supplemental日志组
2 add supplemental log data ( all ) columns ;
Table altered .
4.准备源表
2 dbms_capture_adm . prepare_table_instantiation (
3 table_name => ' ning.sales ' ) ;
4 end ;
5 /
PL / SQL procedure successfully completed .
5.创建chang set
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中读取数据，但还是要求数据库处于归档模式。将数据库至于归档模式以后，再次执行：
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 /
PL / SQL procedure successfully completed .
异步HotLog模式的change source必须是HOTLOG_SOURCE。
6.创建change table
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定义如下
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进程将启动。
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中看到捕获到了变化数据（如果有变化的话）：
COUNT ( * )
-------- --
4
8.将change table的读取权限授予订阅者
Grant succeeded .
五.订阅变化数据
订阅的步骤和同步CDC模式基本一致，这里就不重复了，请参考关于Change Data Capture（二）
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
