ORACLE_CDC.doc

Oracle CDC操作手册
Oracle的增量数据处理方案
中联新时代

1. CDC简介
1.1. CDC是一种数据增量处理技术
在构建数据仓库系统的ETL过程中,增量数据的抽取是一个非常关键的环节.对解决方案一般有两点要求:
l 准确性,能够将业务系统中的数据按一定的频率准确的取到数据仓库中
l 性能,不能对业务系统造成太大的压力,影响现有业务
目前，最为常用的ETL增量数据处理方式有三种:
l 时间戳
l 日志对比(CDC)
l 全面数据对比
三种方式各有优劣，时间戳是目前应用比较普遍的方式。在Oracle=中推出了两种主要的ETL方案，一种是我们熟悉的物化视图(materialized view),另一种就是本文将要介绍的CDC组件(Change Data Capture 改变数据捕获)。
CDC 特性是在Oracle9i数据库中引入的。CDC能够帮助你识别从上次提取之后发生变化的数据。利用CDC，在对源表进行INSERT、UPDATE或 DELETE等操作的同时就可以提取数据，并且变化的数据被保存在数据库的变化表中。这样就可以捕获发生变化的数据，然后利用数据库视图以一种可控的方式提供给目标系统。
1.2. CDC与传统增量处理方式的对比分析
我们对比一下CDC方式与传统的全表对比与时间戳方式。
全表对比使用数据仓库中的当前表与业务系统表进行对比，取得变化了的数据，典型是使用minus语句：
SELECT * FROM new_version
MINUS SELECT * FROM old_version;
使用全表对比有以下几方面的问题：
l 需要将业务系统中表全部转输，造成很高的网络负载
l 需要对两版本的表进行全表扫描，性能代价非常高
l 无法反映数据的历史状态，如无法捕捉库存的历史变化记录
还有一种常用的方式是时间戳, 它是以业务表中某一个字段的值，作为判断新旧数据的标志。如，”病人费用记录”中的登记时间，每次只抽取上次抽取记录时间以后产生的数据。时间戳方式存在以下问题：
l 无法捕获对时间戳以前数据的delete和update操作,在数据准确性上受到了一定的限制。而类似于ZLHIS这种业务系统对已经发生的数据进行update和delete操作非常普遍(如划价记录转收费记录)，应用场景受到了限制。
l 要求业务系统的表必须一个可以标识新旧数据的列，而某些表没有设置这种列。

1.3. CDC的发布订立模型
CDC体系结构基于发布者/订阅者模型。发布者捕捉变化数据并提供给订阅者。订阅者使用从发布者那里获得的变化数据。通常，CDC系统拥有一个发布者和多个订阅者。发布者首先需要识别捕获变化数据所需的源表。然后，它捕捉变化的数据并将其保存在特别创建的变化表中。它还使订阅者能够控制对变化数据的访问。订阅者需要清楚自己感兴趣的是哪些变化数据。一个订阅者可能不会对发布者发布的所有数据都感兴趣。 订阅者需要创建一个订阅者视图来访问经发布者授权可以访问的变化数据。
CDC有几个重要的基本概念需要先明确一下：
l 源表(Source Table)，业务数据库的需要捕获数据的源表
l 变化表(Change Table) ，保存从源表捕获的变化数据(包括各种DML产生的数据)
l 变化集(Change Set)，是保证事务一致性的数据集合。一个变化集对应多个变化表
l 订阅视图(Subscription View),提供给读取变化表数据的视图
l 订阅窗口Subscription Window) ,定义了查看变化数据的时间范围.就象一个观察变化数据的滑动窗口。变化数据处理完成后，可以对清除订阅窗口。

1.4. CDC的同步与异步模式
l 同步模式，实时的捕获变化数据并存储到变化表中，发布者与订阅都位于同一数据库中。下图说明了同步模式的基本架构。

l 异步模式，以Oracle流复制技术为基础，由于流复制比较复杂，本文档不涉及异步模式的CDC

1.5. CDC相关的数据库对象 (Package)
l 包(Package)
n DBMS_CDC_PUBLISH, 用于定义发布操作
n DBMS_CDC_SUBSCRIBE，用于定义订阅操作
l 角色
n EXECUTE_CATALOG_ROLE
n SELECT_CATALOG_ROLE
n CREATE TABLE and CREATE SESSION privileges
n EXECUTE on the DBMS_CDC_PUBLISH package
l 视图
n ALL_SOURCE_TABLES 源表
n ALL_PUBLISHED_COLUMNS 发布的表列
n All_Subscribed_Columns 订阅的表列
n All_Subscriptions 所有订阅
n 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 altered
SQL> alter system set java_pool_size = 50m;
System altered
SQL>
2.1.2. 创建订阅、发布用户、表空间
SQL> create tablespace ts_cdcpub datafile 'E:\oracle\product\10.2.0\oradata\orc1\ts_cdcpub.dbf' size 100m;
Tablespace created
SQL> create user cdc_publisher identified by cdc_publisher default tablespace ts_cdcpub;
User created
SQL> create user cdc_subcriber identified by cdc_subcriber default tablespace ts_cdcpub;
User created
2.1.3. 授予相关权限
SQL> GRANT CREATE SESSION TO cdc_publisher;
Grant succeeded
SQL> GRANT CREATE TABLE TO cdc_publisher;
Grant succeeded
SQL> GRANT CREATE TABLESPACE TO cdc_publisher;
Grant succeeded
SQL> GRANT UNLIMITED TABLESPACE TO cdc_publisher;
Grant succeeded
SQL> GRANT SELECT_CATALOG_ROLE TO cdc_publisher;
Grant succeeded
SQL> GRANT EXECUTE_CATALOG_ROLE TO cdc_publisher;
Grant succeeded
SQL> GRANT EXECUTE ON DBMS_CDC_PUBLISH TO cdc_publisher;
Grant succeeded
SQL> grant create session to cdc_subcriber;
授权成功。
2.1.4. 在ZLHIS用户下，创建测试表
SQL> Create Table person (id Number,Name Varchar(10),sex Varchar(2));
Table created
SQL> insert into person values(0,'毛泽东','男');
1 row inserted
SQL> insert into person values(0,'彭德怀','男');
1 row inserted
SQL> insert into person values(0,'邓颖操','女');
1 row inserted
SQL> commit;
Commit complete
SQL> grant all on person to cdc_publisher;
Grant succeeded
SQL> grant all on person to cdc_subscriber;
Grant succeeded

2.2. 发布数据
2.2.1. 创建改变集
在同步方式的CDC中，change_source_name参数必须使用默认的改变源, SYNC_SOURCE
SQL> 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 completed
SQL>
2.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 => '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 completed
SQL>
options_string，指定改变表的存储参数options_string参数可以使用除partition以外的所有create table 中指定的存储参数，如tablespace、pctfree 等。
2.2.3. 将改变表(Change Table)的权限授权给订阅者

SQL> grant select on person_ct to cdc_subscriber;
Grant succeeded

2.3. 订阅数据
2.3.1. 查看发布信息
当发布者，发布了相关的改变表后，会生成一个惟一的 发布id( publication ID)， 可以查阅视图ALL_PUBLISHED_COLUMNS 以获取已经发布的表及字段信息
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 PERSON
PERSON_CHANGE_SET 70403 PERSON
PERSON_CHANGE_SET 70403 PERSON
6rows selected
SQL>
2.3.2. 创建订阅 ---------------------以下操作以订阅者用户执行
使用DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION创建订阅操作,一次订阅与改变集对应,由于改变集与源表之间是一对多的关系,所以一次订阅就可以订阅多张表.
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 completed
2.3.3. 订阅表,如果改变集中有多个表,需要操作多次
SQL> BEGIN
2 DBMS_CDC_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 completed
SQL>

2.4. 激活订阅
订阅需要使用DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION进行激活,才能生生效,激活订阅后CDC就开始捕获改变数据.
SQL> Begin
2 dbms_cdc_subscribe.activate_subscription(subscription_name => 'PERSON_SUB');
3 END;
4 /
PL/SQL procedure successfully completed
2.5. 扩展窗口的应用(查看增量的数据)
在源表数据变化后，变化的数据在订阅端需要执行extend_window后才能看见，订阅都调用DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW过程取得改变数据的集合,如果第一次执行,就取得激活订阅后所有改变数据.每次执行DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW后,扩展窗口只看到上次执行DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW至今的数据.
SQL> BEGIN
2 DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
3 subscription_name => 'PERSON_SUB');
4 END;
5 /
PL/SQL procedure successfully completed
2.6. 测试及查看改变数据
--以zlhis登录,改变测试表数据
SQL> connect zlhis/his@117orc1;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as zlhis

SQL> select * from person;
ID NAME SEX
---------- ---------- ---
0 毛泽东 男
0 彭德怀 男
0 邓颖操 女

SQL> insert into person values('1','刘少奇','男');

1 row inserted

SQL> update person set name='毛主席' where name='毛泽东';

1 row updated

SQL> delete from person where name ='刘少奇';

1 row deleted

SQL> commit;

Commit complete

--以订立用户查看改变数据
SQL> connect cdc_subscriber/cdc_subscriber@117orc1;
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
---------- ---------- ----------------- ---------- --------------------------------------------------------------------
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 FE03000000000000000000000 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>
可以看到订阅视图中包括了改变数据、操作类型(update、delete或insert)，操作时间，我们可以根据自己的需要提取改变数据进行处理。
2.7. 清空扩展窗口
使用扩展窗口的数据可以进行清空操作，避免改变数据过多带来的系统负载。
SQL> BEGIN
2 DBMS_CDC_SUBSCRIBE.PURGE_WINDOW(
3 subscription_name > 'PERSON_SUB');
4 END;
5 /
PL/SQL procedure successfully completed
SQL>
总结一下，同步模式下的CDC配置还是比较简单的：
l 修改初始化参数
l 创建订阅、发布用户和存储的表空间
l 授予相关权限
l 创建改变集
l 建立改变表
l 订阅发布数据，创建订阅
l 订阅发布的表
l 扩展窗口，以查阅改变数据

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;
已创建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 object_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_objects

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.03 0.02 0 0 0 0
Execute 2 0.60 19.50 24 25089 118728 115348
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.64 19.52 24 25089 118728 115348

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57

插入Person_Temp表的trace记录
insert into person_temp select object_id,substr(object_name,1,0),'s' from dba_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.26 0.25 0 12671 1634 57674
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.26 0.26 0 12671 1634 57674
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57
从结果来看，CDC对插入操作的性能影响还是比较明显的，但由于业务系统中一次插入近6万行数据的可能性不大，这种性能影响还是在可接受的范围内。
参考资料：
《Oracle 10g Data Warehousing Guide》