构建streams双向复制

1.初始化参数(所有节点)

 

alter system set aq_tm_processes=2 scope=both;
alter system set global_names=true scope=both;
alter system set job_queue_processes=10 scope=both;
alter system set parallel_max_servers=20 scope=both;
alter system set streams_pool_size=500M scope=spfile;
alter system set utl_file_dir='*' scope=spfile;
alter system set open_links=4 scope=spfile;

 

 

2.创建streams管理用户(所有节点)

 

create user stradmin identified by stradmin default tablespace users;

 

迁移logminer日志位置

 

exec dbms_logmnr_d.set_tablespace('USERS');

 

授权

 

grant dba,aq_administrator_role to stradmin;

 

exec dbms_streams_auth.grant_admin_privilege(grantee =>'stradmin',grant_privileges =>true);

 

3.创建dblink(所有节点)

 

create public database link gc.lab.com connect to system identified by "xxx" using 'gc';

create database link gc.lab.com connect to stradmin identified by stradmin;

 

craete public database link orcl.lab.com connect to system identified by "xxx" using 'orcl';

create database link orcl.lab.com connect to stradmin identified by stradmin;

 

4.创建streams queue(所有节点)

 

orcl

exec dbms_streams_adm.set_up_queue(queue_table =>'stradmin.orcl_queue_table',queue_name =>'orcl_queue');

 

gc

exec dbms_streams_adm.set_up_queue(queue_table =>'stradmin.gc_queue_table',queue_name =>'gc_queue');

 

5.创建schema级的rule并创建capture(orcl)

 

exec dbms_streams_adm.add_schema_rules(schema_name =>'test',streams_type =>'capture',streams_name =>'orcl_capture',queue_name =>'stradmin.orcl_queue',include_ddl =>true,include_dml =>true,include_tagged_lcr =>false,source_database =>'orcl.lab.com',inclusion_rule =>true);

 

查看创建的rules

 

select * from dba_rules where rule_name in
(select rule_name from dba_rule_set_rules where rule_set_name in (select rule_set_name from dba_capture));

 

6.创建schema级的propagation rule并创建propagation(orcl)

 

exec dbms_streams_adm.add_schema_propagation_rules(schema_name =>'test',streams_name =>'orcl_to_gc',source_queue_name =>'stradmin.orcl_queue',destination_queue_name =>'[e-mail]',include_ddl =>true,include_dml =>true,include_tagged_lcr =>false,source_database =>'orcl.lab.com',inclusion_rule =>true);

 

查看创建的rules

 

select * from dba_rules where rule_name in
(select rule_name from dba_rule_set_rules where rule_set_name in (select rule_set_name from dba_propagation));

exec dbms_aqadm.alter_propagation_schedule(queue_name =>'stradmin.orcl_queue',destination =>'gc.lab.com',latency =>0);

 

7.创建schema级的rule并创建apply(gc)

 

exec dbms_streams_adm.add_schema_rules(schema_name =>'test',streams_type =>'apply',streams_name =>'gc_apply',queue_name =>'stradmin.gc_queue',include_ddl =>true,include_dml =>true,include_tagged_lcr =>false,source_database =>'orcl.lab.com',inclusion_rule =>true);

 

查看创建的rules

 

select * from dba_rules where rule_name in
(select rule_name from dba_rule_set_rules where rule_set_name in (select rule_set_name from dba_apply));

 

 

8.instantiation(gc)

 

DECLARE
v_scn NUMBER;
BEGIN
v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
(
    source_schema_name   => 'TEST',
   source_database_name => 'ORCL.LAB.COM',
   instantiation_scn    => v_scn,
   recursive            => true);
END;
/

 

9.启动capture,propagation,apply进程并测试

 

exec dbms_capture_adm.start_capture('orcl_capture');
exec dbms_propagation_adm.start_propagation('orcl_to_gc');

 

exec dbms_apply_adm.start_apply('gc_apply');

 

10 配置反向复制

 

创建schema级的rule并创建capture(gc)

 

exec dbms_streams_adm.add_schema_rules(schema_name =>'test',streams_type =>'capture',streams_name =>'gc_capture',queue_name =>'stradmin.gc_queue',include_ddl =>true,include_dml =>true,include_tagged_lcr =>false,source_database =>'gc.lab.com',inclusion_rule =>true);

 

查看创建的rules

 

select * from dba_rules where rule_name in
(select rule_name from dba_rule_set_rules where rule_set_name in (select rule_set_name from dba_capture));

 

 

创建schema级的propagation rule并创建propagation(gc)

 

exec dbms_streams_adm.add_schema_propagation_rules(schema_name =>'test',streams_name =>'gc_to_orcl',source_queue_name =>'stradmin.gc_queue',destination_queue_name =>'[e-mail]',include_ddl =>true,include_dml =>true,include_tagged_lcr =>false,source_database =>'gc.lab.com',inclusion_rule =>true);

 

查看创建的rules

 

select * from dba_rules where rule_name in
(select rule_name from dba_rule_set_rules where rule_set_name in (select rule_set_name from dba_propagation));

 

exec dbms_aqadm.alter_propagation_schedule(queue_name =>'stradmin.gc_queue',destination =>'orcl.lab.com',latency =>0);

 

创建schema级的rule并创建apply(orcl)

 

exec dbms_streams_adm.add_schema_rules(schema_name =>'test',streams_type =>'apply',streams_name =>'orcl_apply',queue_name =>'stradmin.orcl_queue',include_ddl =>true,include_dml =>true,include_tagged_lcr =>false,source_database =>'gc.lab.com',inclusion_rule =>true);

 

查看创建的rules

 

select * from dba_rules where rule_name in
(select rule_name from dba_rule_set_rules where rule_set_name in (select rule_set_name from dba_apply));

 

instantiation(orcl)

 

DECLARE
v_scn NUMBER;
BEGIN
v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
(
    source_schema_name   => 'test',
   source_database_name => 'gc.lab.com',
   instantiation_scn    => v_scn,
   recursive            => true);
END;
/

 

启动capture,propagation,apply并测试

 

select * from dba_capture;

select * from dba_propagation;

select * from dba_apply;

select * from dba_apply_error;

select * from dba_apply_instantiated_objects;

select * from dba_rule_set_rules;

select * from dba_rule_sets;

select * from dba_rules;