Oracle_CDC该怎么部署

今天就跟大家聊聊有关Oracle_CDC该怎么部署，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。SQL>alter system set
job_queue_processes = 100;alter system set
java_pool_size = 50m;alter system set
streams_pool_size=50m;alter system set
undo_retention=3600;alter database
force logging;alter database
add supplemental log data;select
LOG_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;SQL>create table scott.test(id
int,name varchar2(30),mark varchar2(50));SQL>conn / as
sysdba;create
tablespace cdc_tbsp;create user
cdc_publisher identified by cdc_publisher default tablespace cdc_tbsp temporary
tablespace temp;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 CREATE
TABLESPACE TO cdc_publisher;GRANT UNLIMITED
TABLESPACE TO cdc_publisher;GRANT
SELECT_CATALOG_ROLE TO cdc_publisher;GRANT
EXECUTE_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’);grant all on
scott.test to cdc_publisher;grant dba to
cdc_publisher;SQL>create user
cdc_subscriber identified by cdc_subscriber default tablespace cdc_tbsp
temporary tablespace temp;grant create
session TO cdc_subscriber;grant resource
to cdc_subscriber;grant connect to
cdc_subscriber;GRANT CREATE
TABLE TO cdc_subscriber;GRANT CREATE
VIEW TO cdc_subscriber;GRANT UNLIMITED
TABLESPACE TO cdc_subscriber;SQL>conn
cdc_publisher/cdc_publisher;BEGINDBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME
=> ‘scott.test’);END;/SQL>conn
cdc_publisher/cdc_publisher;BEGINDBMS_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_SOURCESQL>conn
cdc_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),MARK
VARCHAR2(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 等。SQL>conn
cdc_publisher/cdc_publisher;BEGIN DBMS_CDC_PUBLISH.ALTER_CHANGE_SET( change_set_name => ‘CDC_SCOTT_TEST’, enable_capture => ‘y’);END;/conn
cdc_publisher/cdc_publisher;grant select
on cdc_test to cdc_subscriber;
备注：
到此cdc_subscriber用户已经可以检测到scott.test表的变更了
测试：$ sqlplus
scott/tigerSQL>insert into scott.test
values(1,’beijing’,’11’);commit;update scott.test
set name=’shanghai’ where id=1;commit;delete scott.test
where id=1;commit;SQL>conn
cdc_subscriber/cdc_subscriberSQL> select
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from cdc_publisher.cdc_test
t;OPERATION$
COMMIT_TIMESTAMP$ ID NAME
MARK———-
———- —— ———– ——————————I 2018/2/8 20:04:58 1 beijing 11UO 2018/2/8 20:04:58 1 beijing 11UN 2018/2/8 20:04:58 1 shanghai 11D 2018/2/8 20:04:58 1 shanghai 11
备注：operation 的意思
此列中的值可以是下列任何一个^脚¹:
I: 指示此行表示插入操作: 指示此行表示以下情况下更新的源表行的前映像:UO
异步更改数据捕获
当更改表包括基于主键的对象 ID, 而不是主键的捕获列已更改时, 同步更改数据捕获。
UU: 指示此行表示更新的源表行的前图像, 用于同步更改数据捕获, 而不是由.UO.
UN: 指示此行表示更新的源表行的后映像。
D: 指示此行表示删除操作。
当发布者发布了相关的改变表后，免费云主机域名会生成一个惟一的发布id( publication ID)，可以查阅视图ALL_PUBLISHED_COLUMNS以获取已经发布的表及字段信息SQL> conn
CDC_PUBLISHER/CDC_PUBLISHER;select
change_set_name,pub_id,source_table_name from ALL_PUBLISHED_COLUMNS ;CHANGE_SET_NAME PUB_ID SOURCE_TABLE_NAME——————————
———- ——————————CDC_SCOTT_TEST 91956 TESTCDC_SCOTT_TEST 91956 TESTCDC_SCOTT_TEST 91956 TESTSQL>conn
cdc_subscriber/cdc_subscriberBEGINdbms_cdc_subscribe.create_subscription(change_set_name=>’CDC_SCOTT_TEST’,description=>’cdc scott subx’,subscription_name=>’CDC_SCOTT_SUB’);END;/
备注：
一次订阅与改变集对应,由于改变集与源表之间是一对多的关系,所以一次订阅就可以订阅多张表.SQL>BEGINdbms_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> select
view_name,text from user_views;VIEW_NAME TEXT—————
—————————————-TEST_TEMP SELECT
OPERATION$, CSCN$, COMMIT_TIMESTAMP$, XIDUSN$, XIDSLT$, XIDSEQ$, DDLDES
备注：
如果改变集中有多个表,需要操作多次SQL>BEGINdbms_cdc_subscribe.activate_subscription(subscription_name=>’CDC_SCOTT_SUB’);END;/SQL>conn
cdc_subscriber/cdc_subscriber;BEGINdbms_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至今的数据.SQL>conn
cdc_subscriber/cdc_subscriber;SQL> select
t.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 1
beijing 11UO 2018/2/8 20:04:58 1 beijing 11UN 2018/2/8 20:04:58 1 shanghai 11D 2018/2/8 20:04:58 1 shanghai 11SQL> conn
scott/tiger;insert into test
values(2,’renqinglei’,’aa’);commit;update test set
mark=’tt’ where id=2;commit;delete test
where id=2;commit;SQL> conn
cdc_publisher/cdc_publisherselect
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from cdc_publisher.cdc_test
t;OP
COMMIT_TIMESTAMP$ ID NAME MARK–
—————— ———- ——————————I 13-JAN-16 1 beijing 11UO
13-JAN-16 1
beijing 11UN
13-JAN-16 1
shanghai 11D 13-JAN-16 1 shanghai 11I 13-JAN-16 2 renqinglei aaUO
13-JAN-16 2
renqinglei aaUN
13-JAN-16 2
renqinglei ttD 13-JAN-16 2 renqinglei ttSQL> conn
cdc_subscriber/cdc_subscriberselect
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from test_temp t;OP
COMMIT_TIMESTAMP$ ID NAME MARK–
—————— ———- ——————————I 13-JAN-16 1 beijing 11UO
13-JAN-16 1
beijing 11UN
13-JAN-16 1 shanghai 11D 13-JAN-16 1 shanghai 11.SQL> conn
cdc_subscriber/cdc_subscriberBEGINdbms_cdc_subscribe.extend_window(subscription_name=>’CDC_SCOTT_SUB’);END;/select
t.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 1 beijing 11UO 2018/2/8 20:04:58 1 beijing 11UN 2018/2/8 20:04:58 1 shanghai 11D 2018/2/8 20:04:58 1 shanghai 11I 2018/2/8 20:26:01 2 renqinglei aaUO 2018/2/8 20:26:01 2 renqinglei aaUN 2018/2/8 20:26:01 2 renqinglei ttD 2018/2/8 20:26:01 2 renqinglei ttSQL> conn
cdc_subscriber/cdc_subscriberBEGINDBMS_CDC_SUBSCRIBE.PURGE_WINDOW(subscription_name => ‘CDC_SCOTT_SUB’);END;/
查看订阅数据为空select
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from test_temp t;
备注:
扩展窗口的数据可以进行清空操作，避免改变数据过多带来的系统负载。conn scott/tiger;insert into test
values(3,’shandong’,’hh’);insert into test
values(4,’diankeyuan’,’hh’);commit;
查看发布信息conn
cdc_publisher/cdc_publisherselect
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from cdc_publisher.cdc_test
t;OPERATION$
COMMIT_TIMESTAMP$ ID NAME MARK———-
—————– —— —————————— —————— 2018/2/8 20:04:58 1 beijing 11UO 2018/2/8 20:04:58 1 beijing 11UN 2018/2/8 20:04:58 1 shanghai 11D 2018/2/8 20:04:58 1 shanghai 11I 2018/2/8 20:26:01 2 renqinglei aaUO 2018/2/8 20:26:01 2 renqinglei aaUN 2018/2/8 20:26:01 2 renqinglei ttD 2018/2/8 20:26:01 2 renqinglei ttI 2018/2/8 20:33:48 3 shandong hhI 2018/2/8 20:33:48 4 diankeyuan hhSQL> conn
cdc_subscriber/cdc_subscriber;BEGINdbms_cdc_subscribe.extend_window(subscription_name=>’CDC_SCOTT_SUB’);END;/
查看订阅信息select
t.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 hhI 2018/2/8 20:33:48 4 diankeyuan hhSQL>conn
cdc_publisher/cdc_publisherselect
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from cdc_publisher.cdc_test
t;OP
COMMIT_TIMESTAMP$ ID NAME MARK–
—————— ———- ——————————I 13-JAN-16 1 beijing 11UO 13-JAN-16 1 beijing 11UN
13-JAN-16 1
shanghai 11D 13-JAN-16 1 shanghai 11I 13-JAN-16 2 renqinglei aaUO
13-JAN-16 2
renqinglei aaUN
13-JAN-16 2
renqinglei ttD 13-JAN-16 2 renqinglei ttI 13-JAN-16 3 shandong hhI 13-JAN-16 4 diankeyuan hhrows selected.
不可truncateSQL> truncate
cdc_test;ERROR at line 1:ORA-03290:
Invalid truncate command – missing CLUSTER or TABLE keyword
删除后无记录SQL>delete cdc_test;commit;select
t.operation$,t.commit_timestamp$,t.id,t.name,t.mark from cdc_publisher.cdc_test
t;看完上述内容，你们对Oracle_CDC该怎么部署有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注百云行业资讯频道，感谢大家的支持。

相关推荐: 使用EXP进行备份时EXP-00091报错解决办法

1.使用EXP 工具进行导出备份或迁移数据时会报出EXP-00091的错误[oracle@auto tmp]$ exp luffy/onepiece full=Y constraints=Y file=/home/oracle/tmp/luffy.dmpEXP-…