江枫渔火愁对面

在关于Change Data Capture（一）中介绍了CDC的一些基本概念和类型。这篇文章主要是通过一个实际的例子来演示实现同步模式的CDC的基本步骤。

一.版本

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 execute on dbms_cdc_publish to cdcpub;

Grant succeeded.

三.设置初始化参数

同步CDC，需要将java_pool_size设置为合适的大小，估计是其内部是采用java存储过程来实现的。

SYS@ning>alter system set java_pool_size=48M;

System altered.


四.发布变化数据

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.创建chang set

SYS@ning>begin
2 dbms_cdc_publish.create_change_set(
3 change_set_name =>’ning_sales’,
4 description =>’change set for ning.sales’,
5 change_source_name =>’SYNC_SOURCE’);
6 end;
7 /

PL/SQL procedure successfully completed.

同步CDC的chang source必须是SYNC_SOURCE。

4.创建change table

如果要将change table创建到users表空间

SYS@ning>alter user cdcpub quota unlimited on users;

User altered.

SYS@ning>begin
2 dbms_cdc_publish.create_change_table(
3 owner =>’cdcpub’,
4 change_table_name=>’sales_ct’,
5 change_set_name=>’ning_sales’,
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=>’y',
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;
Name Null? Type
—————————————– ——– —————————-
OPERATION$ CHAR(2)
CSCN$ NUMBER
COMMIT_TIMESTAMP$ DATE
RSID$ NUMBER
SOURCE_COLMAP$ RAW(128)
TARGET_COLMAP$ RAW(128)
ID NUMBER(38)
PRODUCTID NUMBER(38)
PRICE NUMBER(10,2)
QUANTITY NUMBER(38)

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

先创建一个订阅者帐号

SYS@ning>create user cdcsub identified by cdcsub;

User created.

SYS@ning>grant create session to cdcsub;

Grant succeeded.

SYS@ning>grant create table to cdcsub;

Grant succeeded.

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

Grant succeeded.

如果这里没有将change table授权给订阅者，那么在后面订阅的时候，就会报错:

ORA-31466: no publications found
ORA-06512: at “SYS.DBMS_CDC_SUBSCRIBE”, line 19
ORA-06512: at line 2

五.订阅变化数据

1.查看可以订阅的源数据表

SYS@ning>conn cdcsub/cdcsub;
Connected.

CDCSUB@ning>select * from all_source_tables;

SOURCE_SCHEMA_NAME SOURCE_TABLE_NAME
—————————— ——————————
NING SALES

2.查看可以订阅的change set和column

CDCSUB@ning>select distinct change_set_name,column_name,pub_id
2 from all_published_columns
3 where source_schema_name=’NING’ and source_table_name=’SALES’;

CHANGE_SET COLUMN_NAME PUB_ID
———- ——————– ———-
NING_SALES ID 52590
NING_SALES PRICE 52590
NING_SALES PRODUCTID 52590
NING_SALES QUANTITY 52590

3.创建订阅

CDCSUB@ning>begin
2 dbms_cdc_subscribe.create_subscription(
3 change_set_name=>’ning_sales’,
4 description=>’change data for sales’,
5 subscription_name=>’sales_sub’);
6 end;
7 /

PL/SQL procedure successfully completed.

4.订阅具体的source table和column

一个订阅可以同时包含同一个change set中的多个source table

CDCSUB@ning>begin
2 dbms_cdc_subscribe.subscribe(
3 subscription_name=>’sales_sub’,
4 source_schema=>’NING’,
5 source_table=>’SALES’,
6 column_list=>’id,productid,price,quantity’,
7 subscriber_view=>’sales_view’);
8 end;
9 /

PL/SQL procedure successfully completed.

订阅会创建一个叫做sales_view的订阅视图，通过该视图，我们可以获得源表的变化数据。

CDCSUB@ning>desc sales_view;
Name Null? Type
—————————————– ——– —————————-
OPERATION$ CHAR(2)
CSCN$ NUMBER
COMMIT_TIMESTAMP$ DATE
RSID$ NUMBER
SOURCE_COLMAP$ RAW(128)
TARGET_COLMAP$ RAW(128)
ID NUMBER(38)
PRICE NUMBER(10,2)
PRODUCTID NUMBER(38)
QUANTITY NUMBER(38)

5.激活订阅

不管订阅包含一个source table还是多个，只需要执行一次激活即可。

CDCSUB@ning>begin
2 dbms_cdc_subscribe.activate_subscription(
3 subscription_name=>’SALES_SUB’);
4 end;
5 /

PL/SQL procedure successfully completed.

6.扩展订阅窗口

在源表数据变化后，变化的数据在订阅端需要执行extend_window后才能看见

CDCSUB@ning>begin
2 dbms_cdc_subscribe.extend_window(
3 subscription_name=>’SALES_SUB’);
4 end;
5 /

PL/SQL procedure successfully completed.

如果是第一次执行该过程，则会在订阅段看到所有的变化数据，以后每次执行都只增加到从上次执行以来的新数据，相当于物化视图的增量刷新。

7.查询订阅视图中的数据

CDCSUB@ning>select id,productid,price,quantity from sales_view;

ID PRODUCTID PRICE QUANTITY
———- ———- ———- ———-
1 1 20.2 100
2 1 20.2 200
3 2 40.8 50

9.清除当前窗口中的变化数据

如果当前变化数据已经不再需要，可以清除其数据

CDCSUB@ning>begin
2 dbms_cdc_subscribe.purge_window(
3 subscription_name=>’SALES_SUB’);
4 end;
5 /

PL/SQL procedure successfully completed.

清除后，在订阅视图中就看不到之前的变化数据了

CDCSUB@ning>select id,productid,price,quantity from sales_view;

no rows selected

10.停止订阅

CDCSUB@ning>begin
2 dbms_cdc_subscribe.drop_subscription(
3 subscription_name=>’SALES_SUB’);
4 end;
5 /

PL/SQL procedure successfully completed.

订阅视图也同时被删除

CDCSUB@ning>select id,productid,price,quantity from sales_view;
select id,productid,price,quantity from sales_view
*
ERROR at line 1:
ORA-00942: table or view does not exist