表级STREAM复制搞定

在源库： SQL>   exec dbms_streams_adm.remove_streams_configuration; PL/SQL 过程已成功完成。 SQL> select script_id from dba_recoverable_script; SCRIPT_ID -------------------------------- 24C1BF1E40BD495FB7229E7DA01DE823 SQL> SQL> SQL> exec dbms_streams_adm.RECOVER_OPERATION('&1','PURGE'); 输入 1 的值:  24C1BF1E40BD495FB7229E7DA01DE823 PL/SQL 过程已成功完成。 SQL> show user USER 为 "ORCL" SQL> spool primary.out SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(); PL/SQL 过程已成功完成。 SQL> BEGIN   2    DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(   3      table_name              => 'rep.wen',   4      streams_name            => 'str1_to_str2',   5      source_queue_name       => 'strmadmin.streams_queue',   6      destination_queue_name  => 'strmadmin.streams_queue@centledb.su.net'   7      include_dml             => true,   8      include_ddl             => true,   9      source_database         => 'orcl.sun.net',  10      inclusion_rule          => true,  11      queue_to_queue          => true);  12  END;  13  / BEGIN * 第 1 行出现错误: ORA-24010: QUEUE "STRMADMIN"."STREAMS_QUEUE" 不存在 ORA-06512: 在 "SYS.DBMS_STREAMS_ADM", line 836 ORA-06512: 在 "SYS.DBMS_STREAMS_ADM", line 765 ORA-06512: 在 line 2 SQL> BEGIN   2    DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(   3      table_name              => 'rep.wen',   4      streams_name            => 'str1_to_str2',   5      source_queue_name       => 'orcl.streams_queue',   6      destination_queue_name  => 'centledb.streams_queue@centledb.sun.net'   7      include_dml             => true,   8      include_ddl             => true,   9      source_database         => 'orcl.sun.net',  10      inclusion_rule          => true,  11      queue_to_queue          => true);  12  END;  13  / PL/SQL 过程已成功完成。 SQL> BEGIN   2    DBMS_STREAMS_ADM.ADD_TABLE_RULES(   3      table_name     => 'rep.wen',   4      streams_type   => 'capture',   5      streams_name   => 'capture_simp',   6      queue_name     => 'orcl.streams_queue',   7      include_dml    => true,   8      include_ddl    => true,   9      inclusion_rule => true);  10  END;  11  / PL/SQL 过程已成功完成。 SQL> BEGIN   2    DBMS_CAPTURE_ADM.START_CAPTURE(   3      capture_name  => 'capture_simp');   4  END;   5  / PL/SQL 过程已成功完成。 在备库： SQL> conn / as sysdba Connected. SQL> create user centledb identified by centledb; g User created. SQL> rant dba to centledb; Grant succeeded. SQL> conn centledb/centledb Connected. SQL> create database link orcl.sun.net connect to orcl identified by orcl using 'orcl.sun.net'; Database link created. SQL> select * from dual@orcl.sun.net; D - X SQL> EXEC  DBMS_STREAMS_ADM.SET_UP_QUEUE(); PL/SQL procedure successfully completed. SQL> DECLARE   2    iscn  NUMBER;         -- Variable to hold instantiation SCN value   3  BEGIN   4    iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();   5    DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@centledb.sun.net(   6      source_object_name    => 'rep.wen',   7      source_database_name  => 'orcl.sun.net',   8      instantiation_scn     => iscn);   9  END;  10  / PL/SQL procedure successfully completed. SQL> BEGIN   2    DBMS_STREAMS_ADM.ADD_TABLE_RULES(   3      table_name      => 'rep.wen',   4      streams_type    => 'apply',   5      streams_name    => 'apply_simp',   6      queue_name      => 'centledb.streams_queue',   7      include_dml     => true,   8      include_ddl     => true,   9      source_database => 'orcl.sun.net',  10      inclusion_rule  => true);  11  END;  12  / PL/SQL procedure successfully completed. SQL> BEGIN   2    DBMS_APPLY_ADM.SET_PARAMETER(   3      apply_name  => 'apply_simp',   4      parameter   => 'disable_on_error',   5      value       => 'n');   6  END;   7  /   BEGIN   DBMS_APPLY_ADM.START_APPLY(     apply_name  => 'apply_simp'); END; / PL/SQL procedure successfully completed. SQL> SQL>   2    3    4    5  PL/SQL procedure successfully completed. SQL> SQL> conn rep/rep Connected. SQL> select * from wen; select * from wen               * ERROR at line 1: ORA-00942: table or view does not exist SQL> / select * from wen               * ERROR at line 1: ORA-00942: table or view does not exist SQL> conn centledb/centledb Connected. SQL> DECLARE   2    iscn  NUMBER;         -- Variable to hold instantiation SCN value   3  BEGIN   4    iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();   5    DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@centledb.sun.net(   6      source_object_name    => 'rep.wen',   7      source_database_name  => 'orcl.sun.net',   8      instantiation_scn     => iscn);   9  END;  10  / PL/SQL procedure successfully completed. SQL> conn centledb/centledb Connected. SQL> create table rep.wen as select * from rep.wen@orcl.sun.net; Table created. SQL> select * from rep.wen;        WEN ----------        250 SQL> /        WEN ----------        250        123