表级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