ORACLE CDC.docx

1、ORACLE CDCORACLE CDCOracle的增量数据处理方案中联新时代1.CDC简介1.1.CDC是一种数据增量处理技术 在构建数据仓库系统的ETL过程中,增量数据的抽取是一个专门关键的环节.对解决方案一样有两点要求:准确性,能够将业务系统中的数据按一定的频率准确的取到数据仓库中性能,不能对业务系统造成太大的压力,阻碍现有业务 目前，最为常用的ETL增量数据处理方式有三种:时刻戳日志对比(CDC)全面数据对比三种方式各有优劣，时刻戳是目前应用比较普遍的方式。在Oracle=中推出了两种要紧的ETL方案，一种是我们熟悉的物化视图(materialized view),另一种确实是本文将

2、要介绍的CDC组件(Change Data Capture 改变数据捕捉)。CDC 特性是在Oracle9i数据库中引入的。CDC能够关心你识别从上次提取之后发生变化的数据。利用CDC，在对源表进行INSERT、UPDATE或 DELETE等操作的同时就能够提取数据，同时变化的数据被储存在数据库的变化表中。如此就能够捕捉发生变化的数据，然后利用数据库视图以一种可控的方式提供给目标系统。1.2.CDC与传统增量处理方式的对比分析我们对比一下CDC方式与传统的全表对比与时刻戳方式。全表对比使用数据仓库中的当前表与业务系统表进行对比，取得变化了的数据，典型是使用minus语句： SELECT * F

3、ROM new_versionMINUS SELECT * FROM old_version;使用全表对比有以下几方面的问题：需要将业务系统中表全部转输，造成专门高的网络负载需要对两版本的表进行全表扫描，性能代价专门高无法反映数据的历史状态，如无法捕捉库存的历史变化记录还有一种常用的方式是时刻戳,它是以业务表中某一个字段的值，作为判定新旧数据的标志。如，病人费用记录中的登记时刻，每次只抽取上次抽取记录时刻以后产生的数据。时刻戳方式存在以下问题：无法捕捉对时刻戳往常数据的delete和update操作,在数据准确性上受到了一定的限制。而类似于ZLHIS这种业务系统对差不多发生的数据进行updat

4、e和delete操作专门普遍(如划价记录转收费记录)，应用场景受到了限制。要求业务系统的表必须一个能够标识新旧数据的列，而某些表没有设置这种列。1.3.CDC的公布订立模型CDC体系结构基于公布者/订阅者模型。公布者捕捉变化数据并提供给订阅者。订阅者使用从公布者那儿获得的变化数据。通常，CDC系统拥有一个公布者和多个订阅者。公布者第一需要识别捕捉变化数据所需的源表。然后，它捕捉变化的数据并将其储存在专门创建的变化表中。它还使订阅者能够操纵对变化数据的访问。订阅者需要清晰自己感爱好的是哪些变化数据。一个订阅者可能可不能对公布者公布的所有数据都感爱好。 订阅者需要创建一个订阅者视图来访问经公布者授

5、权能够访问的变化数据。CDC有几个重要的差不多概念需要先明确一下：源表(Source Table)，业务数据库的需要捕捉数据的源表变化表(Change Table) ，储存从源表捕捉的变化数据(包括各种DML产生的数据)变化集(Change Set)，是保证事务一致性的数据集合。一个变化集对应多个变化表订阅视图(Subscription View),提供给读取变化表数据的视图订阅窗口(Subscription Window) ,定义了查看变化数据的时刻范畴.就象一个观看变化数据的滑动窗口。变化数据处理完成后，能够对清除订阅窗口。1.4.CDC的同步与异步模式同步模式，实时的捕捉变化数据并储备到

6、变化表中，公布者与订阅都位于同一数据库中。以下图说明了同步模式的差不多架构。异步模式，以Oracle流复制技术为基础，由于流复制比较复杂，本文档不涉及异步模式的CDC1.5.CDC相关的数据库对象 (Package)包(Package)DBMS_CDC_PUBLISH, 用于定义公布操作DBMS_CDC_SUBSCRIBE，用于定义订阅操作角色EXECUTE_CATALOG_ROLESELECT_CATALOG_ROLE CREATE TABLE and CREATE SESSION privileges EXECUTE on the DBMS_CDC_PUBLISH package视图ALL

7、_SOURCE_TABLES 源表ALL_PUBLISHED_COLUMNS 公布的表列All_Subscribed_Columns 订阅的表列All_Subscriptions 所有订阅All_Subscribed_Tables 差不多订阅的表2.CDC的实施步骤 2.1.初始化2.1.1.设置初始化参数由于CDC需要在后台开启作业，需要将参数JOB_QUEUE_PROCESSES增加2个，java_pool_size至少在50M以上。SQL alter system set job_queue_processes = 14;System alteredSQL alter system se

8、t java_pool_size = 50m;System alteredSQL2.1.2.创建订阅、公布用户、表空间SQL create tablespace ts_cdcpub datafile E:oracleproduct10.2.0oradataorc1ts_cdcpub.dbf size 100m;Tablespace createdSQL create user cdc_publisher identified by cdc_publisher default tablespace ts_cdcpub;User createdSQL create user cdc_subcrib

9、er identified by cdc_subcriber default tablespace ts_cdcpub;User created2.1.3.授予相关权限SQL GRANT CREATE SESSION TO cdc_publisher;Grant succeededSQL GRANT CREATE TABLE TO cdc_publisher;Grant succeededSQL GRANT CREATE TABLESPACE TO cdc_publisher;Grant succeededSQL GRANT UNLIMITED TABLESPACE TO cdc_publis

10、her;Grant succeededSQL GRANT SELECT_CATALOG_ROLE TO cdc_publisher;Grant succeededSQL GRANT EXECUTE_CATALOG_ROLE TO cdc_publisher;Grant succeededSQL GRANT EXECUTE ON DBMS_CDC_PUBLISH TO cdc_publisher;Grant succeeded2.1.4. 在ZLHIS用户下，创建测试表SQL Create Table person (id Number,Name Varchar(10),sex Varchar(

11、2);Table createdSQL insert into person values(0,毛泽东,男);1 row insertedSQL insert into person values(0,彭德怀,男);1 row insertedSQL insert into person values(0,邓颖操,女);1 row insertedSQL commit;Commit completeSQL grant all on person to cdc_publisher;Grant succeededSQL grant all on person to cdc_subscriber;G

12、rant succeeded 2.2.公布数据2.2.1.创建改变集在同步方式的CDC中，change_source_name参数必须使用默认的改变源, SYNC_SOURCESQL BEGIN 2 DBMS_CDC_PUBLISH.CREATE_CHANGE_SET( 3 change_set_name = person_change_set, -改变集 4 description = Change set for person info, 5 change_source_name = SYNC_SOURCE); 6 END; 7 /PL/SQL procedure successfully

13、 completedSQL2.2.2.创建改变表 指定源table,和源模式,注意区别owner与source_schema，owner是指公布用户，而不是源表的所有者 SQL BEGIN 2 DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE( 3 owner = cdc_publisher, 4 change_table_name = person_ct, 5 change_set_name = person_change_set, 6 source_schema = zlhis, 7 source_table = person, 8 column_type_list

14、 = ID number,NAME VARCHAR2(10),sex varchar2(2), 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 = y, 16 target_colmap = y, 17 options_string = TABLESPACE TS_CDCPUB); 18 END; 19 /PL/SQL procedure successfully completedSQL opti

15、ons_string，指定改变表的储备参数。options_string参数能够使用除partition以外的所有create table中指定的储备参数，如tablespace、pctfree等。2.2.3. 将改变表(Change Table)的权限授权给订阅者SQL grant select on person_ct to cdc_subscriber;Grant succeeded2.3.订阅数据2.3.1.查看公布信息当公布者，公布了相关的改变表后，会生成一个惟一的 公布id( publication ID)，能够查阅视图ALL_PUBLISHED_COLUMNS 以猎取差不多公布的

16、表及字段信息SQL select change_set_name,pub_id,source_table_name from ALL_PUBLISHED_COLUMNS ;CHANGE_SET_NAME PUB_ID SOURCE_TABLE_NAME- - - PERSON_CHANGE_SET 70403 PERSONPERSON_CHANGE_SET 70403 PERSONPERSON_CHANGE_SET 70403 PERSON6rows selectedSQL2.3.2.创建订阅使用DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION创建订阅操作,一次订阅

17、与改变集对应,由于改变集与源表之间是一对多的关系,因此一次订阅就能够订阅多张表.SQL BEGIN 2 DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION( 3 change_set_name = PERSON_CHANGE_SET, 4 description = Change data for person, 5 subscription_name = PERSON_SUB); 6 END; 7 /PL/SQL procedure successfully completed2.3.3.订阅表,假如改变集中有多个表,需要操作多次SQL BEGIN 2 DBMS_C

18、DC_SUBSCRIBE.SUBSCRIBE( 3 subscription_name = PERSON_SUB, 4 source_schema = zlhis, 5 source_table = person, 6 column_list = id,name,sex, 7 subscriber_view = PERSON_VIEW); 8 END; 9 /PL/SQL procedure successfully completedSQL2.4.激活订阅订阅需要使用DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION进行激活,才能生生效,激活订阅后CDC就开始捕

19、捉改变数据.SQL Begin 2 dbms_cdc_subscribe.activate_subscription(subscription_name = PERSON_SUB); 3 END; 4 /PL/SQL procedure successfully completed2.5.扩展窗口的应用(查看增量的数据)订阅都调用DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW过程取得改变数据的集合,假如第一次执行,就取得激活订阅后所有改变数据.每次执行DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW后,扩展窗口只看到上次执行DBMS_CDC_SUBSCRIBE

20、.EXTEND_WINDOW至今的数据.SQL BEGIN 2 DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW( 3 subscription_name = PERSON_SUB); 4 END; 5 /PL/SQL procedure successfully completed2.6.测试及查看改变数据-以zlhis登录,改变测试表数据SQL connect zlhis/his117orc1;Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as zlhisSQ

21、L select * from person; ID NAME SEX- - - 0 毛泽东 男 0 彭德怀 男 0 邓颖操 女SQL insert into person values(1,刘少奇,男);1 row insertedSQL update person set name=毛主席 where name=毛泽东;1 row updatedSQL delete from person where name =刘少奇;1 row deletedSQL commit;Commit complete-以订立用户查看改变数据SQL connect cdc_subscriber/cdc_sub

22、scriber117orc1;Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as cdc_subscriber-直截了当查看改变数据视图是看不到的,必须执行dbms_cdc_subscribe.extend_window(PERSON_SUB),以确定扩展窗口SQL select * from person_view;OPERATION$ CSCN$ COMMIT_TIMESTAMP$ RSID$ SOURCE_COLMAP$ TARGET_COLMAP$ ID NAME SEX

23、- - - - -SQL begin 2 dbms_cdc_subscribe.extend_window(PERSON_SUB); 3 end; 4 /PL/SQL procedure successfully completed-捕捉的改变数据SQL select * from person_view;OPERATION$ CSCN$ COMMIT_TIMESTAMP$ RSID$ SOURCE_COLMAP$ TARGET_COLMAP$ ID NAME SEX- - - - - - - -I 10588077 2007-6-12 上午 11 1 0E000000000000000 FE

24、03000000000000000000000 1 刘少奇 男UU 10588077 2007-6-12 上午 11 2 04000000000000000 7E00000000000000000000000 0 毛泽东 男UN 10588077 2007-6-12 上午 11 2 04000000000000000 7E01000000000000000000000 0 毛主席 男D 10588077 2007-6-12 上午 11 3 00000000000000000 7E00000000000000000000000 1 刘少奇 男SQL能够看到订阅视图中包括了改变数据、操作类型(up

25、date、delete或insert)，操作时刻，我们能够依照自己的需要提取改变数据进行处理。2.7.清空扩展窗口使用扩展窗口的数据能够进行清空操作，幸免改变数据过多带来的系统负载。SQL BEGIN 2 DBMS_CDC_SUBSCRIBE.PURGE_WINDOW( 3 subscription_name = PERSON_SUB); 4 END; 5 /PL/SQL procedure successfully completedSQL总结一下，同步模式下的CDC配置依旧比较简单的：修改初始化参数创建订阅、公布用户和储备的表空间授予相关权限创建改变集建立改变表订阅公布数据，创建订阅订阅公

26、布的表扩展窗口，以查阅改变数据3.CDC的性能测试我们使用sql_trace工具对比应用了CDC捕捉改变表数据与未设置CDC的表插入同样的数据进行性能对比。-创建与测试表结构相同的表SQL create table person_temp as select * from person;表已创建。SQL alter session set sql_trace = true;会话已更换。-开启sql-trace，分别插入相同的数据SQL insert into person select object_id,substr(object_name,1,0),s from dba_objects;已

27、创建57674行。统计信息- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 0 bytes sent via SQL*Net to client 0 bytes received via SQL*Net from client 0 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 57674 rows processed SQL insert into person_temp select obje

28、ct_id,substr(object_name,1,0),s from dba_objects;已创建57674行。统计信息- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 0 bytes sent via SQL*Net to client 0 bytes received via SQL*Net from client 0 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 57674 rows processed SQL commit; 从统计信息来看，两者并无差别。我们再看一下两者的sql_trace记录： 插入Person表的trace记录insert into person select object_id,substr(object_name,1,0),s from dba_objectscall count cpu elapsed disk query current rows- - - - - - - -Parse 2 0.03 0.02 0 0 0 0Execute 2 0.60 19.50 24 25089 118728