oracle_cdc环境配置

1 同步

1.1 系统用户设置

-- 系统设置===============================================================
-- 开启归档模式
alter system set log_archive_start=true  scope=spfile; 
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;

-- 修改系统参数
alter system set java_pool_size=50000000;

-- 创建表空间
create tablespace cdc_space datafile 'c:\app\init\oradata\orcl\cdc_space.dbf' size 100m;


-- 发布者设置==============================================================
-- 发布者的创建
create user cdcpub identified by z123 default tablespace cdc_space quota unlimited on cdc_space;
alter user cdcpub quota unlimited on system;
alter user cdcpub quota unlimited on sysaux;

-- 发布者与授权
grant unlimited tablespace to cdcpub;

grant create session to cdcpub;
grant create table to cdcpub;
grant create sequence to cdcpub; 
grant create procedure to cdcpub;  
grant create any job to cdcpub;

grant execute on dbms_cdc_publish to cdcpub;
grant execute on dbms_lock to cdcpub;  

grant execute_catalog_role to cdcpub;
grant select_catalog_role to cdcpub;

-- execute dbms_streams_auth.grant_admin_privilege('cdcpub');  
begin
    dbms_streams_auth.grant_admin_privilege('cdcpub');  
end;

grant all on scott.emp to cdcpub;


-- 订阅者设置===============================================================
-- 订阅者的创建
create user cdcsub identified by z123 default tablespace cdc_space;

-- 订阅者的授权
grant create session to cdcsub;
grant create table to cdcsub;

grant select on dba_source_tables to cdcsub;
grant select on dba_published_columns to cdcsub;
grant execute on dbms_cdc_subscribe to cdcsub;

1.2 发布用户设置

-- 发布者设置===============================================================
-- 创建变更集
begin
    dbms_cdc_publish.create_change_set(
        change_set_name => 'cdc_sym_emp_cn',
        description => 'change set for scott.emp info',
        change_source_name => 'HOTLOG_SOURCE',
        stop_on_ddl => 'y');
end;

-- 创建变更表
begin
    dbms_cdc_publish.create_change_table(
        owner => 'cdcpub',
        change_table_name => 'cdc_sym_emp_ct',
        change_set_name => 'cdc_sym_emp_cn',
        source_schema => 'scott',
        source_table => 'emp',
        column_type_list =>'empno number,ename varchar2(20)',
        capture_values => 'both',
        rs_id => 'y',
        row_id => 'n',
        user_id => 'n',
        timestamp => 'n',
        object_id => 'n',
        source_colmap => 'n',
        target_colmap => 'y',
        options_string => '');
end;

-- 激活变更集
begin
    dbms_cdc_publish.alter_change_set(
        change_set_name => 'cdc_sym_emp_cn',
        enable_capture => 'y');
end;

-- 授权给订阅者
grant select on cdcpub.cdc_sym_emp_ct to cdcsub;

1.3 订阅用户设置

-- 订阅者设置===============================================================
-- 创建订阅集
begin
    dbms_cdc_subscribe.create_subscription(
        change_set_name => 'cdc_sym_emp_cn',
        description => 'change data for scott.emp info',
        subscription_name => 'cdc_sym_emp_sn'); 
end;

-- 订阅源表及源表中的相关字段
begin
    dbms_cdc_subscribe.subscribe(
        subscription_name => 'cdc_sym_emp_sn',
        source_schema => 'scott',
        source_table => 'emp',
        column_list => 'empno,ename',
        subscriber_view => 'sym_emp_view');
end;

-- 激活订阅
begin
    dbms_cdc_subscribe.activate_subscription(
        subscription_name => 'cdc_sym_emp_sn'); 
end;

-- 订阅者使用===============================================================
-- 扩展订阅
BEGIN
    DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
        subscription_name => 'cdc_sym_emp_sn');
end;

-- 删除订阅
begin
    dbms_cdc_subscribe.drop_subscription(
        subscription_name => 'cdc_sym_emp_sn');
end;

1.4 测试

conn scott/tiger
insert into emp(empno,ename) values(8888,'z8888');
insert into emp(empno,ename) values(9999,'z9999');
delete from emp where empno = 8888;
update emp set ename = 'new9999' where empno = '9999';
commit;

conn cdcsub/z123
-- 扩展订阅窗口
BEGIN
    DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
        subscription_name => 'cdc_sym_emp_sn');
end;

-- 查看订阅视图内容
select OPERATION$,COMMIT_TIMESTAMP$,empno,ename from sym_emp_view;