Oracle CDC配置:Asynchronous HotLog模式

来源:互联网 发布:上海博物馆淘宝 编辑:程序博客网 时间:2024/03/29 07:28

今天参照一篇网上文档成功配置了CDC的Asynchronous HotLog模式。原文在:

http://psoug.org/reference/cdc_demo2.html

另外还有一篇,虽然是讲Synchronous模式的,也很有帮助:

http://www.open-open.com/doc/view/1b463da961c94862bcd0bcb9ca7dad6f

不过我也没有完全按照原文的做法。

把经过我精简、修改之后的操作步骤记录下来,自己备忘,也分享给大家。

Oracle关于CDC的官方文档:http://docs.oracle.com/cd/B19306_01/server.102/b14223/cdc.htm

最好先看看,至少知道CDC是个什么玩意,5个模式大概是什么意思。

准备工作

首先,我们需要配置Oracle。另外,由于Asynchromous模式要解析Redo Log,所以我们要设定Log。

打开pfile,添加下面内容:

job_queue_processes = 5global_names=TRUEstreams_pool_size=200Mundo_retention=3600java_pool_size=200M

然后,进入sqlplus,重启数据库并设置archivelog:

conn /as sysdbashutdown immediatestartup mountalter database archivelog;alter database force logging;alter database add supplemental log data;alter database open;


添加用户并设置权限

创建发布者:

conn /as sysdbaCREATE TABLESPACE cdc_tbsp datafile '%DATA_PATH%\cdctbsp01.dbf' SIZE 50M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;CREATE USER cdc_publisher IDENTIFIED BY cdc_publisher DEFAULT TABLESPACE cdc_tbsp TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON cdc_tbsp;GRANT create session TO cdc_publisher;GRANT create table TO cdc_publisher;GRANT create sequence TO cdc_publisher;GRANT create procedure TO cdc_publisher;GRANT create any job TO cdc_publisher;GRANT execute_catalog_role TO cdc_publisher;GRANT select_catalog_role TO cdc_publisher;GRANT execute ON dbms_cdc_publish TO cdc_publisher;GRANT execute ON dbms_lock TO cdc_publisher;execute dbms_streams_auth.grant_admin_privilege('CDC_PUBLISHER');

创建订阅者:

CREATE USER cdc_subscriber IDENTIFIED BY cdc_subscriber DEFAULT TABLESPACE cdc_tbsp TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON cdc_tbsp;GRANT create session TO cdc_subscriber;

另外,这里假定操作数据库的用户为hr/hr,并且有一张表叫employee,里面有ID,NAME这样的基本信息。后面我们就用这张表做演示。


发布

以发布者登录:

conn cdc_publisher/cdc_publisher

准备源表:

BEGINdbms_capture_adm.prepare_table_instantiation(table_name=>'HR.EMPLOYEE');END;/

创建Change Set:

BEGINdbms_cdc_publish.create_change_set(change_set_name=>'CDC_HR_SET', description=>'CDC Demo Change Set', change_source_name=>'HOTLOG_SOURCE', stop_on_ddl=>'Y', begin_date=>NULL, end_date=>NULL);END;/

创建Change Table:

BEGINdbms_cdc_publish.create_change_table(owner=>'CDC_PUBLISHER',change_table_name=>'EMPLOYEE_CT',change_set_name=>'CDC_HR_SET',source_schema=>'HR',source_table=>'EMPLOYEE',column_type_list=>'ID VARCHAR2(30), NAME VARCHAR2(100)',capture_values=>'BOTH',rs_id=>'Y',row_id=>'Y',user_id=>'N',timestamp=>'N',object_id =>'N',source_colmap=>'N',target_colmap=>'Y',options_string=>NULL,ddl_markers=>'Y');END;/

赋予订阅者Change Table的SELECT权限:

GRANT select ON EMPLOYEE_CT to cdc_subscriber;

设定change set:

BEGINdbms_cdc_publish.alter_change_set(change_set_name=>'CDC_HR_SET', enable_capture=> 'Y');END;/


订阅

以订阅者登录:

conn cdc_subscriber/cdc_subscriber

创建订阅:

BEGINdbms_cdc_subscribe.create_subscription(change_set_name=>'CDC_HR_SET', description=>'cdc hr subx', subscription_name=>'CDC_HR_SUB');END;/

开始订阅:

BEGINdbms_cdc_subscribe.subscribe(subscription_name=>'CDC_HR_SUB', source_schema=>'HR', source_table=>'EMPLOYEE',column_list=>'ID, NAME',subscriber_view=>'CDC_EMPLOYEE_VIEW');END;/

激活订阅:

BEGINdbms_cdc_subscribe.activate_subscription(subscription_name=>'CDC_HR_SUB');END;/

查看变更数据

以HR用户登录,对EMPLOYEE表进行INSERT/UPDATE/DELETE等操作,记得要commit。

然后以订阅者登录:

conn cdc_subscriber/cdc_subscriber
扩展查看窗口:

BEGINdbms_cdc_subscribe.extend_window(subscription_name=>'CDC_HR_SUB');END;/

查看数据:

SELECT * FROM CDC_EMPLOYEE_VIEW;

就可以看到记录的变更数据。Asynchronous模式由于是解析Redo Log,所以延迟还是挺大的,一般要好几秒,甚至几分钟也有可能。所以如果刚开始没有效果,先不要急,等一等观察一下。或者看一看Oracle的alter日志,没有报错的话应该没问题。