oracle stream 系列文章- 3-测试案例，建立基于表的复制

显示全部楼层 · 2014-2-19 11:55:14

测试目标：建立以表到表的复制
1.创建queue
db01
declare
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'streamadmin.str_source_queue_table',
queue_name => 'streamadmin.str_source_queue');
END;
/
db02
declare
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'streamadmin.str_dest_queue_table',
queue_name => 'streamadmin.str_dest_queue');
END;
/

千问 · 2014-2-19 11:55:14

2.创建 propagation
on db01
BEGIN
DBMS_STREAMS_ADM.add_table_propagation_rules(
table_name => 'test.t1',
streams_name => 'db01_db02',
source_queue_name => 'streamadmin.str_source_queue',
destination_queue_name => 'streamadmin.str_dest_queue@db02',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'db01',
inclusion_rule => true,
queue_to_queue => true);
END;
/

千问 · 2014-2-19 11:55:14

3.create apply process
on db02
declare
BEGIN
DBMS_STREAMS_ADM.ADD_table_RULES(
table_name => 'test.t1',
streams_type => 'apply',
streams_name => 'apply_db02',
queue_name => 'streamadmin.str_dest_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'db01',
inclusion_rule => true);
END;
/

declare
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name=> 'apply_stream',
parameter => 'disable_on_error',
value => 'n');
END;
/

declare
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_db02');
END;
/

千问 · 2014-2-19 11:55:14

4.创建capture

on db01
declare
BEGIN
DBMS_STREAMS_ADM.add_table_rules(
table_name => 'test.t1',
streams_type => 'capture',
streams_name => 'capture_db01',
queue_name => 'streamadmin.str_source_queue',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/

exp test/test@db01 consistent=y file=test.dmp log=test_exp.log tables=t1
imp test/test@db02file=test.dmp log=test_imp.log STREAMS_INSTANTIATION=y rows=n ignore=y

千问 · 2014-2-19 11:55:14

5.启动captures
on db01
declare
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_db01');
END;
/
6.test
on db01
insert into test.t1 values(10,'1');
commit;

千问 · 2014-2-19 11:55:14

学习

千问 · 2014-2-19 11:55:14

标题该是4 了

千问 · 2014-2-19 11:55:14

bucuo