Oracle_CDC该怎么部署
今天就跟大家聊聊有关Oracle_CDC该怎么部署，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。
1. CDC的实施步骤(异步在线日志)1.1. 数据库初始化SQL>
alter system setjob_queue_processes = 100;
alter system setjava_pool_size = 50m;
alter system setstreams_pool_size=50m;
alter system setundo_retention=3600;
alter databaseforce logging;
alter databaseadd supplemental log data;
selectLOG_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
1.2. 准备测试表SQL>
create table scott.test(idint,name varchar2(30),mark varchar2(50));
1.3. 创建发布者SQL>
conn / assysdba;
createtablespace cdc_tbsp;
create usercdc_publisher identified by cdc_publisher default tablespace cdc_tbsp temporarytablespace temp;
GRANT CREATESESSION TO cdc_publisher;
GRANT CREATE TABLETO cdc_publisher;
grant createsequence TO cdc_publisher;
grant createprocedure TO cdc_publisher;
grant create anyjob TO cdc_publisher;
GRANT CREATETABLESPACE TO cdc_publisher;
GRANT UNLIMITEDTABLESPACE TO cdc_publisher;
GRANTSELECT_CATALOG_ROLE TO cdc_publisher;
GRANTEXECUTE_CATALOG_ROLE TO cdc_publisher;
GRANT EXECUTE ONDBMS_CDC_PUBLISH TO cdc_publisher;
grant execute ONdbms_lock TO cdc_publisher;
executedbms_streams_auth.grant_admin_privilege('CDC_PUBLISHER');
grant all onscott.test to cdc_publisher;
grant dba tocdc_publisher;
1.4. 创建订阅者SQL>
create usercdc_subscriber identified by cdc_subscriber default tablespace cdc_tbsptemporary tablespace temp;
grant createsession TO cdc_subscriber;
grant resourceto cdc_subscriber;
grant connect tocdc_subscriber;
GRANT CREATETABLE TO cdc_subscriber;
GRANT CREATEVIEW TO cdc_subscriber;
GRANT UNLIMITEDTABLESPACE TO cdc_subscriber;
1.5. 发布数据1.5.1. 发布-准备源表SQL>
conncdc_publisher/cdc_publisher;
BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME=> 'scott.test');
END;
/
1.5.2. 发布-创建变更集SQL>
conncdc_publisher/cdc_publisher;
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
change_set_name=> 'CDC_SCOTT_TEST',
description=> 'Change set for product info',
change_source_name=> 'HOTLOG_SOURCE',
stop_on_ddl=> 'y');
END;
/
注意：
change_source_name参数:
同步模式中必须为：SYNC_SOURCE
异步在线日志模式必须为：HOTLOG_SOURCE
1.5.3. 发布-创建变更表SQL>
conncdc_publisher/cdc_publisher;
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
owner => 'cdc_publisher',
change_table_name => 'cdc_test',
change_set_name => 'CDC_SCOTT_TEST',
source_schema => 'SCOTT',
source_table => 'TEST',
column_type_list => 'ID NUMBER(5), NAME VARCHAR2(30),MARKVARCHAR2(50)',
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 => 'TABLESPACE CDC_TBSP');
END;
/
注意：
owner是指发布用户
source_schema是源表所属用户
同步模式需加参数ddl_markers=> 'n'
options_string指定改变表的存储参数，可以使用除partition以外的所有create table中指定的存储参数，如tablespace、pctfree 等。
1.5.4. 发布-激活变更集SQL>
conncdc_publisher/cdc_publisher;
BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
change_set_name => 'CDC_SCOTT_TEST',
enable_capture => 'y');
END;
/
1.5.5. 授权变更表给订阅者conncdc_publisher/cdc_publisher;
grant selecton cdc_test to cdc_subscriber;
备注：
到此cdc_subscriber用户已经可以检测到scott.test表的变更了
测试：
$ sqlplusscott/tiger
SQL>
insert into scott.testvalues(1,'beijing','11');
commit;
update scott.testset name='shanghai' where id=1;
commit;
delete scott.testwhere id=1;
commit;
SQL>
conncdc_subscriber/cdc_subscriber
SQL> selectt.operation$,t.commit_timestamp$,t.id,t.name,t.mark from cdc_publisher.cdc_testt;
OPERATION$COMMIT_TIMESTAMP$ ID NAMEMARK
-------------------- ------ ----------- ------------------------------
I 2018/2/8 20:04:58 1 beijing 11
UO 2018/2/8 20:04:58 1 beijing 11
UN 2018/2/8 20:04:58 1 shanghai 11
D 2018/2/8 20:04:58 1 shanghai 11
备注：operation 的意思
此列中的值可以是下列任何一个脚1:
I: 指示此行表示插入操作
: 指示此行表示以下情况下更新的源表行的前映像:UO
异步更改数据捕获
当更改表包括基于主键的对象 ID, 而不是主键的捕获列已更改时, 同步更改数据捕获。
UU: 指示此行表示更新的源表行的前图像, 用于同步更改数据捕获, 而不是由.UO.
UN: 指示此行表示更新的源表行的后映像。
D: 指示此行表示删除操作。
当发布者发布了相关的改变表后，会生成一个惟一的发布id( publication ID)， 可以查阅视图ALL_PUBLISHED_COLUMNS以获取已经发布的表及字段信息
SQL> connCDC_PUBLISHER/CDC_PUBLISHER;
selectchange_set_name,pub_id,source_table_name from ALL_PUBLISHED_COLUMNS ;
CHANGE_SET_NAME PUB_ID SOURCE_TABLE_NAME
---------------------------------------- ------------------------------
CDC_SCOTT_TEST 91956 TEST
CDC_SCOTT_TEST 91956 TEST
CDC_SCOTT_TEST 91956 TEST
1.6. 订阅1.6.1. 订阅-创建订阅集SQL>
conncdc_subscriber/cdc_subscriber
BEGIN
dbms_cdc_subscribe.create_subscription(
change_set_name=>'CDC_SCOTT_TEST',
description=>'cdc scott subx',
subscription_name=>'CDC_SCOTT_SUB');
END;
/
备注：
一次订阅与改变集对应,由于改变集与源表之间是一对多的关系,所以一次订阅就可以订阅多张表.
1.6.2. 订阅-开始订阅数据SQL>
BEGIN
dbms_cdc_subscribe.subscribe(
subscription_name=>'CDC_SCOTT_SUB',
source_schema=>'SCOTT',
source_table=>'TEST',
column_list=>'ID, NAME,MARK',
subscriber_view=>'TEST_TEMP');
END;
/
SQL> selectview_name,text from user_views;
VIEW_NAME TEXT
-------------------------------------------------------
TEST_TEMP SELECTOPERATION$, CSCN$, COMMIT_TIMESTAMP$, XIDUSN$, XIDSLT$, XIDSEQ$, DDLDES
备注：
如果改变集中有多个表,需要操作多次
1.6.3. 订阅-激活订阅SQL>
BEGIN
dbms_cdc_subscribe.activate_subscription(
subscription_name=>'CDC_SCOTT_SUB');
END;
/
1.6.4. 订阅-扩展订阅窗口SQL>
conncdc_subscriber/cdc_subscriber;
BEGIN
dbms_cdc_subscribe.extend_window(
subscription_name=>'CDC_SCOTT_SUB');
END;
/
备注：
订阅调用DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW过程取得改变数据的集合,如果第一次执行,就取得激活订阅后所有改变数据.每次执行DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW后,扩展窗口只看到上次执行DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW至今的数据.
1.6.5. 查看订阅内容SQL>
conncdc_subscriber/cdc_subscriber;
SQL> selectt.operation$,t.commit_timestamp$,t.id,t.name,t.mark from test_temp t;
OPERATION$COMMIT_TIMESTAMP$ ID NAME MARK
--------------------------- ------ --------------------------------------------------------------------------------
I 2018/2/8 20:04:58 1beijing 11
UO 2018/2/8 20:04:58 1 beijing 11
UN 2018/2/8 20:04:58 1 shanghai 11
D 2018/2/8 20:04:58 1 shanghai 11
1.7. 测试1.7.1. 源表变更SQL> connscott/tiger;
insert into testvalues(2,'renqinglei','aa');
commit;
update test setmark='tt' where id=2;
commit;
delete testwhere id=2;
commit;
1.7.2. 查询数据发布情况SQL> conncdc_publisher/cdc_publisher
selectt.operation$,t.commit_timestamp$,t.id,t.name,t.mark from cdc_publisher.cdc_testt;
OPCOMMIT_TIMESTAMP$ ID NAME MARK
-------------------- ---------- ------------------------------
I 13-JAN-16 1 beijing 11
UO13-JAN-16 1beijing 11
UN13-JAN-16 1shanghai 11
D 13-JAN-16 1 shanghai 11
I 13-JAN-16 2 renqinglei aa
UO13-JAN-16 2renqinglei aa
UN13-JAN-16 2renqinglei tt
D 13-JAN-16 2 renqinglei tt
1.7.3. 查询数据订阅情况SQL> conncdc_subscriber/cdc_subscriber
selectt.operation$,t.commit_timestamp$,t.id,t.name,t.mark from test_temp t;
OPCOMMIT_TIMESTAMP$ ID NAME MARK
-------------------- ---------- ------------------------------
I 13-JAN-16 1 beijing 11
UO13-JAN-16 1beijing 11
UN13-JAN-16 1 shanghai 11
D 13-JAN-16 1 shanghai 11.
1.7.4. 发现订阅的数据没有变化,扩展一下订阅窗口:SQL> conncdc_subscriber/cdc_subscriber
BEGIN
dbms_cdc_subscribe.extend_window(
subscription_name=>'CDC_SCOTT_SUB');
END;
/
selectt.operation$,t.commit_timestamp$,t.id,t.name,t.mark from test_temp t;
OPERATION$COMMIT_TIMESTAMP$ IDNAME MARK
--------------------------- ------ ------------------------------ ----------
I 2018/2/8 20:04:58 1 beijing 11
UO 2018/2/8 20:04:58 1 beijing 11
UN 2018/2/8 20:04:58 1 shanghai 11
D 2018/2/8 20:04:58 1 shanghai 11
I 2018/2/8 20:26:01 2 renqinglei aa
UO 2018/2/8 20:26:01 2 renqinglei aa
UN 2018/2/8 20:26:01 2 renqinglei tt
D 2018/2/8 20:26:01 2 renqinglei tt
1.7.5. 清除变更数据集SQL> conncdc_subscriber/cdc_subscriber
BEGIN
DBMS_CDC_SUBSCRIBE.PURGE_WINDOW(
subscription_name => 'CDC_SCOTT_SUB');
END;
/
查看订阅数据为空
selectt.operation$,t.commit_timestamp$,t.id,t.name,t.mark from test_temp t;
备注:
扩展窗口的数据可以进行清空操作，避免改变数据过多带来的系统负载。
1.7.6. 重新生成变化数据conn scott/tiger;
insert into testvalues(3,'shandong','hh');
insert into testvalues(4,'diankeyuan','hh');
commit;
查看发布信息
conncdc_publisher/cdc_publisher
selectt.operation$,t.commit_timestamp$,t.id,t.name,t.mark from cdc_publisher.cdc_testt;
OPERATION$COMMIT_TIMESTAMP$ ID NAME MARK
--------------------------- ------ ------------------------------ ------------------ 2018/2/8 20:04:58 1 beijing 11
UO 2018/2/8 20:04:58 1 beijing 11
UN 2018/2/8 20:04:58 1 shanghai 11
D 2018/2/8 20:04:58 1 shanghai 11
I 2018/2/8 20:26:01 2 renqinglei aa
UO 2018/2/8 20:26:01 2 renqinglei aa
UN 2018/2/8 20:26:01 2 renqinglei tt
D 2018/2/8 20:26:01 2 renqinglei tt
I 2018/2/8 20:33:48 3 shandong hh
I 2018/2/8 20:33:48 4 diankeyuan hh
SQL> conncdc_subscriber/cdc_subscriber;
BEGIN
dbms_cdc_subscribe.extend_window(
subscription_name=>'CDC_SCOTT_SUB');
END;
/
查看订阅信息
selectt.operation$,t.commit_timestamp$,t.id,t.name,t.mark from test_temp t;
OPERATION$COMMIT_TIMESTAMP$ ID NAME MARK
-------------------- ------ -------------- ---------------------------------
I 2018/2/8 20:33:48 3 shandong hh
I 2018/2/8 20:33:48 4 diankeyuan hh
1.7.7. 删除发布的数据SQL>
conncdc_publisher/cdc_publisher
selectt.operation$,t.commit_timestamp$,t.id,t.name,t.mark from cdc_publisher.cdc_testt;
OPCOMMIT_TIMESTAMP$ ID NAME MARK
-------------------- ---------- ------------------------------
I 13-JAN-16 1 beijing 11
UO 13-JAN-16 1 beijing 11
UN13-JAN-16 1shanghai 11
D 13-JAN-16 1 shanghai 11
I 13-JAN-16 2 renqinglei aa
UO13-JAN-16 2renqinglei aa
UN13-JAN-16 2renqinglei tt
D 13-JAN-16 2 renqinglei tt
I 13-JAN-16 3 shandong hh
I 13-JAN-16 4 diankeyuan hh
rows selected.
不可truncate
SQL> truncatecdc_test;
ERROR at line 1:
ORA-03290:Invalid truncate command - missing CLUSTER or TABLE keyword
删除后无记录
SQL>
delete cdc_test;
commit;
selectt.operation$,t.commit_timestamp$,t.id,t.name,t.mark from cdc_publisher.cdc_testt;
看完上述内容，你们对Oracle_CDC该怎么部署有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
