构建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 =>'[email protected]',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();
[email protected](
    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 =>'[email protected]',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();
[email protected](
    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;