oracle 12c cdc,ORACLE 11g同步CDC实验

ORACLE 11g 同步CDC实验

1.创建数据库用户：以管理员账户登录

1)业务操作用户

create user appuser identified by appuser default tablespace users;

grant connect,resource to appuser

grant create view to appuser

2)发布用户

create user cdc_pub identified by cdc default tablespace users;

grant connect,resource to cdc_pub

GRANT SELECT_CATALOG_ROLE TO cdc_pub

GRANT EXECUTE_CATALOG_ROLE TO cdc_pub

GRANT EXECUTE ON DBMS_CDC_PUBLISH TO cdc_pub

grant create job to cdc_pub -- Oracle 11g 中为必须

3)订阅用户

create user cdc_sub identified by cdc default tablespace users;

grant connect,resource to cdc_sub

grant execute on DBMS_CDC_SUBSCRIBE TO CDC_SUB

2.创建业务表：以业务用户账户(APPUSER)登录

create table SalesOrder ( orderId int not null,

customerId int not null, DueDate date not null, deliverTo int not null,

createddttm date default sysdate,

constraint pk_salesOrder primary key(orderId))

create table SalesOrderDetail ( SOLineId int not null,

orderID int not null, itemNumber varchar2(20)not null,

quantity decimal(13,4), linePrice decimal(13,4)w,

constraint pk_SODetail primary key( SoLineID ))

3.创建发布：以发布者登录(CDC_PUB)

1)创建发布集

BEGIN

DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(

change_set_name =>'CDCSET_SO',--改变集

description =>'Change set for SalesOrder, SalesOrderDetail',

change_source_name =>'SYNC_SOURCE');

END;

2)创建发布表：一个发布集对应多个发布的表

发布表即是用于存放变更了的数据的表。以下语句将在发布者(CDC_PUB)名下新建两个发布表：