关于Change Data Capture（一）

lynx286 · 发表于 2007-7-10 11:39

学习。

love2008 · 发表于 2007-7-10 13:52

学习

vongates · 发表于 2007-7-10 14:50

最初由 NinGoo 发布
[B]

谢谢，这个文章不错，讲解了在OWB中实际使用CDC来执行增量抽取的ETL流程和实现。不过OWB实在是过于臃肿，性能也不好 [/B]

要看你怎么用了哟。在什么样的需求中用了。

ignu · 发表于 2007-7-11 14:26

ORA-7445 and ora-3113 when either creating / altering or dropping an object and CDC is enabled.
Doc ID: Note:232306.1

EBS中一般都不用CDC，有时引起ORA-7445错误的

NinGoo · 发表于 2007-7-11 15:22

关于Change Data Capture（三）
作者：NinGoo | 【转载时请务必以超链接形式标明文章原始出处和作者信息及本声明】
引用：http://www.ningoo.net/2007/07/11 ... ure_3.htm/trackback

本文主要是通过一个实际的例子演示异步HotLog模式CDC的实现步骤。关于CDC的概念和模式介绍，请参考关于Change Data Capture（一）http://www.ningoo.net/2007/07/09/about_change_data_capture_1.htm

一.版本

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无法成功，可能在alter中出现如下错误：

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中读取数据，但还是要求数据库处于归档模式。将数据库至于归档模式以后，再次执行：

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  /

PL/SQL procedure successfully completed.

异步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的一刻起)，并且开始分析当前联机日志：

<blockquote>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</blockquote>

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

SYS@ning>select count(*) from cdcpub.sales_ct_hotlog;

  COUNT(*)
----------
      4

8.将change table的读取权限授予订阅者

SYS@ning>grant select on cdcpub.sales_ct_hotlog to cdcsub;

Grant succeeded.

五.订阅变化数据

订阅的步骤和同步CDC模式基本一致，这里就不重复了，请参考关于Change Data Capture（二）http://www.ningoo.net/2007/07/10/about_change_data_capture_2.htm

NinGoo · 发表于 2007-7-11 15:25

最初由 ignu 发布
[B]ORA-7445 and ora-3113 when either creating / altering or dropping an object and CDC is enabled.
Doc ID: Note:232306.1

EBS中一般都不用CDC，有时引起ORA-7445错误的 [/B]

这个bug是9i的吧，10g的CDC同9i相比，有了比较大的改进，不知道在10g中是否还有该bug？没有在生产库中实际用过，目前还没有碰到

vongates · 发表于 2007-7-11 16:04

之前测试过9i到10的，因为10的功能更强呀

NinGoo · 发表于 2007-7-13 10:04

关于Change Data Capture（四）

作者：NinGoo | 【转载时请务必以超链接形式标明文章原始出处和作者信息及本声明】
引用：http://www.ningoo.net/2007/07/13 ... _4htm.htm/trackback

本系列第二和第三篇文章演示了同步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也遇到过这个问题

(http://jlandzpa.itpub.net/post/13/222494)，他是删除了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（二）

http://www.ningoo.net/2007/07/10/about_change_data_capture_2.htm

vecentli · 发表于 2007-7-13 10:28

给个精华，楼猪继续。

NinGoo · 发表于 2007-7-13 10:51

再传一篇关于异步CDC的文档