接前面oracle 11g streams 逻辑修改记录(LCR)示例 想要搭建一个完整的streams环境请看oracle 11g streams搭建 1 、数据库级别 streams 复制 --完整参数示例connect strmadmin/strmadmin@DBXA.WORLDConnected.begin dbms_streams_adm.maintain_global ( sou
接前面"oracle 11g streams 逻辑修改记录(LCR)示例"
想要搭建一个完整的streams环境请看"oracle 11g streams搭建"
1、数据库级别streams复制
--完整参数示例 connect strmadmin/strmadmin@DBXA.WORLD Connected. begin dbms_streams_adm.maintain_global ( source_database => 'DBXA.WORLD', source_directory_object => 'STREAMS_DP_DIR', destination_database => 'DBXB.WORLD', destination_directory_object => 'STREAMS_DP_DIR', capture_name => 'DBXA_CAP', capture_queue_name => 'DBXA_CAP_Q', capture_queue_table => 'DBXA_CAP_Q_T', capture_queue_user => 'STRMADMIN', propagation_name => 'DBXA_TO_DBXB_PROP', apply_name => 'DBXA_APP', apply_queue_name => 'DBXA_APP_Q', apply_queue_table => 'DBXA_APP_Q_T', apply_queue_user => 'STRMADMIN', script_name => 'cr_streams_global.sql', script_directory_object => 'STREAMS_DP_DIR', dump_file_name => NULL, log_file => NULL, bi_directional => FALSE, include_ddl => TRUE, perform_actions => TRUE, instantiation => DBMS_STREAMS_ADM.INSTANTIATION_FULL ); end; / job finished PL/SQL procedure successfully completed. --如果不需要用户为streams组件定义有意义的名称 connect strmadmin/strmadmin@DBXA.WORLD Connected. begin dbms_streams_adm.maintain_global ( source_database => 'DBXA.WORLD', source_directory_object => 'STREAMS_DP_DIR', destination_database => 'DBXB.WORLD', destination_directory_object => 'STREAMS_DP_DIR', include_ddl => TRUE ); end; / job finished PL/SQL procedure successfully completed.2、模式级别streams复制
--完整参数 connect strmadmin/strmadmin@DBXA.WORLD Connected. declare schemas dbms_utility.uncl_array; begin schemas(1) := 'SCOTT'; schemas(2) := 'HR'; dbms_streams_adm.maintain_schemas ( schema_names => schemas, source_database => 'DBXA.WORLD', source_directory_object => 'STREAMS_DP_DIR', destination_database => 'DBXB.WORLD', destination_directory_object => 'STREAMS_DP_DIR', capture_name => 'DBXA_CAP', capture_queue_name => 'DBXA_CAP_Q', capture_queue_table => 'DBXA_CAP_Q_T', propagation_name => 'DBXA_TO_DBXB_PROP', apply_name => 'DBXA_APP', apply_queue_name => 'DBXA_APP_Q', apply_queue_table => 'DBXA_APP_Q_T', dump_file_name => 'schemas_expimp.dmp', log_file => 'schemas_expimp.log', bi_directional => FALSE, include_ddl => TRUE, perform_actions => TRUE ); end; / job finished PL/SQL procedure successfully completed. --如果不需要用户为streams组件定义有意义的名称 connect strmadmin/strmadmin@DBXA.WORLD Connected. declare schemas dbms_utility.uncl_array; begin schemas(1) := 'SCOTT'; schemas(2) := 'HR'; dbms_streams_adm.maintain_schemas ( schema_names => schemas, source_database => 'DBXA.WORLD', source_directory_object => 'STREAMS_DP_DIR', destination_database => 'DBXB.WORLD', destination_directory_object => 'STREAMS_DP_DIR', include_ddl => TRUE, instantiation => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA_NETWORK ); end; / PL/SQL procedure successfully completed.3、表级别streams复制
--完整参数 connect strmadmin/strmadmin@DBXA.WORLD Connected. declare tables dbms_utility.uncl_array; begin tables(1) := 'SCOTT.DEPT'; tables(2) := 'SCOTT.EMP'; tables(3) := 'HR.SALGRADE'; tables(4) := 'HR.BONUS'; dbms_streams_adm.maintain_tables ( table_names => tables, source_database => 'DBXA.WORLD', source_directory_object => 'STREAMS_DP_DIR', destination_database => 'DBXB.WORLD', destination_directory_object => 'STREAMS_DP_DIR', capture_name => 'DBXA_CAP', capture_queue_name => 'DBXA_CAP_Q', capture_queue_table => 'DBXA_CAP_Q_T', propagation_name => 'DBXA_TO_DBXB_PROP', apply_name => 'DBXA_APP', apply_queue_name => 'DBXA_APP_Q', apply_queue_table => 'DBXA_APP_Q_T', dump_file_name => NULL, log_file => NULL, bi_directional => FALSE, include_ddl => TRUE, perform_actions => TRUE, instantiation => DBMS_STREAMS_ADM.INSTANTIATION_TABLE ); end; / job finished PL/SQL procedure successfully completed.4、表空间级别streams复制
--在源数据库中创建 目录对象 create directory example_ts_dir as '/u01/oradata/DBXA'; Directory created. grant read on directory example_ts_dir to strmadmin; Grant succeeded --在目标数据库中创建目录对象 create directory example_ts_dir as '/u02/app/oradata/DBXB'; Directory created. --使用maintain_simple_tts过程创建表空间streams复制 connect strmadmin/strmadmin@DBXA.WORLD Connected. begin dbms_streams_adm.maintain_simple_tts( tablespace_name => 'EXAMPLE_TS', source_directory_object => 'STREAMS_DP_DIR', source_database => 'DBXA.WORLD', destination_directory_object => 'EXAMPLE_TS_DIR', destination_database => 'DBXB.WORLD', perform_actions => TRUE, script_name => 'cr_maintain_simple_tts_uni.sql', script_directory_object => 'STREAMS_DP_DIR', bi_directional => FALSE ); end; / PL/SQL procedure successfully completed. --使用MAINTAIN_TTS过程创建表空间streams复制 connect strmadmin/strmadmin@DBXA.WORLD Connected. declare ts_names dbms_streams_tablespace_adm.tablespace_set; begin ts_names(1) := 'DATA_TS'; ts_names(2) := 'INDEX_TS'; ts_names(3) := 'EXAMPLE_TS'; dbms_streams_adm.maintain_tts( tablespace_names => ts_names, source_directory_object => 'STREAMS_DP_DIR', destination_directory_object => 'EXAMPLE_TS_DIR', source_database => 'DBXA.WORLD', destination_database => 'DBXB.WORLD', perform_actions => TRUE, script_name => 'cr_streams_maintain_tts_uni.sql', script_directory_object => 'STREAMS_DP_DIR', dump_file_name => 'maint_tts.dmp', capture_name => 'DBXA_CAP', capture_queue_table => 'DBXA_CAP_Q_T', capture_queue_name => 'DBXA_CAP_Q', capture_queue_user => 'STRMADMIN', propagation_name => 'DBXA_TO_DBXB_PROP', apply_name => 'DBXA_APP', apply_queue_table => 'DBXA_APP_Q_T', apply_queue_name => 'DBXA_APP_Q', apply_queue_user => 'STRMADMIN', log_file => 'maintain_tts.log', bi_directional => FALSE, include_ddl => TRUE ); end; / PL/SQL procedure successfully completed5、使用MAINTAIN存储过程配置下游捕获进程
--存储过程运行在目标数据库DBXB.WORKD中 --不需要传播进程,如果指定了传播进程也不会创建 --捕获进程和应用进程的队列名和队列表名一样 connect strmadmin/strmadmin@DBXB.WORLD Connected. declare schemas dbms_utility.uncl_array; begin schemas(1) := 'SCOTT'; schemas(2) := 'HR'; dbms_streams_adm.maintain_schemas ( schema_names => schemas, source_database => 'DBXA.WORLD', source_directory_object => 'STREAMS_DP_DIR', destination_database => 'DBXB.WORLD', destination_directory_object => 'STREAMS_DP_DIR', capture_name => 'DBXA_CAP', capture_queue_name => 'DBXA_CAP_Q', capture_queue_table => 'DBXA_CAP_Q_T', propagation_name => 'DBXA_TO_DBXB_PROP', apply_name => 'DBXA_APP', apply_queue_name => 'DBXA_CAP_Q', apply_queue_table => 'DBXA_CAP_Q_T', dump_file_name => NULL, log_file => NULL, bi_directional => FALSE, include_ddl => TRUE, perform_actions => TRUE ); end; / job finished PL/SQL procedure successfully completed. --配置从数据库DBXA.WORLD到DBXB.WORLD的streams复制的方法 --存储过程运行在DBXB.WORLD数据库中 --传播定义从DBXB.WORLD到DBXC.WORLD --捕获进程和应用进程的队列名和队列表名不同 connect strmadmin/strmadmin@DBXB.WORLD Connected. declare schemas dbms_utility.uncl_array; begin schemas(1) := 'SCOTT'; schemas(2) := 'HR'; dbms_streams_adm.maintain_schemas ( schema_names => schemas, source_database => 'DBXA.WORLD', source_directory_object => 'STREAMS_DP_DIR', destination_database => 'DBXC.WORLD', destination_directory_object => 'STREAMS_DP_DIR', capture_name => 'DBXA_CAP', capture_queue_name => 'DBXA_CAP_Q', capture_queue_table => 'DBXA_CAP_Q_T', propagation_name => 'DBXB_TO_DBXC_PROP', apply_name => 'DBXA_APP', apply_queue_name => 'DBXA_APP_Q', apply_queue_table => 'DBXA_APP_Q_T', dump_file_name => NULL, log_file => NULL, bi_directional => FALSE, include_ddl => TRUE, perform_actions => TRUE ); end; / PL/SQL procedure successfully completed.6、创建视图来查看streams 存储过程的状态和进度
--主要是基于DBA_RECOVERABLE_SCRIPT视图和DBA_RECOVERABLE_SCRIPT_BLOCKS视图创建 connect sys as sysdba set long 100000000 create or replace view streams_build_status as select to_char(rs.creation_time,'HH24:Mi:SS MM/DD/YY') CREATE_DATE, rs.status, rs.done_block_num||' of ' ||rs.total_blocks ||' Steps Completed' PROGRESS, to_char(to_number(sysdate-rs.creation_time)*86400,9999.99) ELAPSED_SECONDS, substr(rsb.forward_block,1,5000) CURRENT_STEP, rs.invoking_package||'.'||rs.invoking_procedure PROCEDURE, rs.script_id from dba_recoverable_script rs, dba_recoverable_script_blocks rsb where rs.script_id = rsb.script_id and rsb.block_num = rs.done_block_num + 1; create public synonym streams_build_status for streams_build_status; grant select on streams_build_status to public; --查询进度信息(存储过程当前正在执行、一共有14个过程块已经完成13个、已经运行了276秒) select status, progress, elapsed_seconds elapsed, script_id from streams_build_status; STATUS PROGRESS ELAPSED SCRIPT_ID --------- ------------------------- ------- -------------------------------- EXECUTING 13 of 14 Steps Completed 276.00 7CC97F3B9169704BE040A8C014006E63 --查看正在执行的过程块 SQL> select current_step 2 from streams_build_status; CURRENT_STEP ----------------------------------------------------------------------- -- Start capture process DBXA$CAP -- BEGIN dbms_capture_adm.start_capture( capture_name => '"DBXA$CAP"' ); EXCEPTION WHEN OTHERS THEN IF sqlcode = -26666 THEN NULL; -- CAPTURE process already running ELSE RAISE; END IF; END; --提取脚本及过程块 set long 10000000 set pages 1000 spool maintain_script.sql select '-- Block: ' || block_num, forward_block from dba_recoverable_script_blocks where script_id = '7CC97F3B9169704BE040A8C014006E63' order by block_num; spool off7、从MAINTAIN存储过程的错误中恢复
--下面展示配置模式级别的复制时,对于一个造成MAINTAIN存储过程失败的简单错误处理 connect strmadmin/strmadmin@DBXA.WORLD Connected. declare schemas dbms_utility.uncl_array; begin schemas(1) := 'SCOTT'; schemas(2) := 'HR'; dbms_streams_adm.maintain_schemas ( schema_names => schemas, source_database => 'DBXA.WORLD', source_directory_object => 'STREAMS_DP_DIR', destination_database => 'DBXB.WORLD', destination_directory_object => 'STREAMS_DP_DIR', include_ddl => TRUE, instantiation => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA_NETWORK ); end; / declare * ERROR at line 1: ORA-23616: Failure in executing block 7 for script 7CD4E8B08BD40E08E040A8C014007723 with ORA-39001: invalid argument value ORA-06512: at "SYS.DBMS_RECO_SCRIPT_INVOK", line 139 ORA-06512: at "SYS.DBMS_STREAMS_RPC", line 465 ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 659 ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 682 ORA-06512: at "SYS.DBMS_STREAMS_MT", line 7972 ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 2674 ORA-06512: at line 7 --通过错误提示的script_id查看错误的具体信息 select forward_block from dba_recoverable_script_blocks where script_id = '7CD4E8B08BD40E08E040A8C014007723' and block_num = 7; FORWARD_BLOCK ----------------------------------------------------------- -- -- Datapump SCHEMA MODE IMPORT (NETWORK) -- DECLARE h1 NUM ...... ...... --由上可以看出是数据库连接丢失造成的错误 --在创建数据库连接时候作业在恢复之后便可以成功完成 begin dbms_streams_adm.recover_operation( script_id => '7CD4E8B08BD40E08E040A8C014007723', operation_mode => 'FORWARD' ); end; / PL/SQL procedure successfully completed.8、含有本地进程的单向复制
--配置schema级别的streams,source:DBXA.WORLD、target:DBXB.WORLD --连接到目标数据库并且创建streams队列和表 conn strmadmin/strmadmin@DBXB.WORLD Connected. begin dbms_streams_adm.set_up_queue( queue_name => 'DBXA_APP_Q', queue_table => 'DBXA_APP_Q_T', queue_user => 'STRMADMIN' ); end; / --在target数据库中创建应用进程和规则 begin dbms_streams_adm.add_schema_rules ( schema_name => 'SCOTT', streams_type => 'APPLY', streams_name => 'DBXA_APP', queue_name => 'DBXA_APP_Q', include_dml => true, include_ddl => true, inclusion_rule => true, include_tagged_lcr => false, source_database => 'DBXA.WORLD', and_condition => ' :lcr.get_compatible() < dbms_streams.max_compatible()' ); end; / PL/SQL procedure successfully completed. --连接source数据库并创建Streams队列 conn strmadmin/strmadmin@DBXA.WORLD Connected. set serveroutput on size unlimited set echo on begin dbms_streams_adm.set_up_queue( queue_name => 'DBXA_CAP_Q', queue_table => 'DBXA_CAP_Q_T', queue_user => 'STRMADMIN' ); end; / PL/SQL procedure successfully completed. --在source数据库创建传播进程和传播规则 begin dbms_streams_adm.add_schema_propagation_rules ( schema_name => 'SCOTT', streams_name => 'DBXA_TO_DBXB_PROP', source_queue_name => 'DBXA_CAP_Q', destination_queue_name => 'DBXA_APP_Q@DBXB.WORLD', include_dml => true, include_ddl => true, inclusion_rule => true, include_tagged_lcr => false, queue_to_queue => true, source_database => 'DBXA.WORLD', and_condition => ' :lcr.get_compatible() < dbms_streams.max_compatible()' ); end; / PL/SQL procedure successfully completed. --在source数据库中创建捕获进程和捕获规则 begin dbms_streams_adm.add_schema_rules ( schema_name => 'SCOTT', streams_type => 'CAPTURE', streams_name => 'DBXA_CAP', queue_name => 'DBXA_CAP_Q', include_dml => true, include_ddl => true, include_tagged_lcr => false, inclusion_rule => true, source_database => 'DBXA.WORLD', and_condition => ' :lcr.get_compatible() < dbms_streams.max_compatible()' ); end; / PL/SQL procedure successfully completed. --在target数据库中实例化对象 --使用data pump导出,参数文件内容示例 directory=data_pump_dir schemas=SCOTT parallel=4 dumpfile=schemas_%u.dmp logfile=schemas_expdp.log --使用data pump导入,参数文件内容示例 directory=data_pump_dir full=y parallel=4 table_exists_action=truncate dumpfile=schemas_%u.dmp logfile=schemas_expdp.log --在target数据库中启动APPLY进程 conn strmadmin/strmadmin@DBXB.WORLD Connected. begin dbms_apply_adm.start_apply('DBXA_APP'); end; / PL/SQL procedure successfully completed. --在source数据库中启动CAPTURE进程 conn strmadmin/strmadmin@DBXA.WORLD Connected. begin dbms_capture_adm.start_capture('DBXA_CAP'); end; / PL/SQL procedure successfully completed. --检查 alter 警告日志文件 --看见如下消息就说明capture进程正在挖掘日志 LOGMINER: Begin mining logfile for session 23 thread 1 sequence 230, /u01/oradata/DBXA/redo01.log LOGMINER: End mining logfile for session 23 thread 1 sequence 230, /u01/oradata/DBXA/redo01.log LOGMINER: Begin mining logfile for session 23 thread 1 sequence 231, /u01/oradata/DBXA/redo01.log 9、包含下游捕获进程的单向复制(归档日志下游捕获) --DBXB.WORLD作为下游数据库和目标数据库使用 --通过log_archive_dest_2来建立重做传输 connect sys/manager@DBXA.WORLD as sysdba Connected. alter system set log_archive_dest_2= 'service=DBXB.WORLD ASYNC NOREGISTER VALID_FOR=(online_logfiles, all_roles) 3 TEMPLATE=DBXA_arch_%t_%s_%r.arc' scope=both; System altered. alter system set log_archive_dest_state_2=ENABLE scope=both; System altered. connect sys/manager@DBXB.WORLD as sysdba Connected. alter system set log_archive_dest_2= 'location=/u01/oradata/DBXA_logs VALID_FOR=(standby_logfiles, primary_role)' scope=both; System altered. alter system set log_archive_dest_state_2=ENABLE scope=both; System altered. --由于目标数据库也是下游数据库,因此不需要配置传播进程。捕获进程和应用进程将共享streams队列和队列表 connect strmadmin/strmadmin@DBXA.WORLD Connected. -- Create Streams Queue. begin dbms_streams_adm.set_up_queue( queue_name => 'DBXA_CAP_APP_Q', queue_table => 'DBXA_CAP_APP_Q_T', queue_user => 'STRMADMIN' ); end; / PL/SQL procedure successfully completed. -- Create apply process and rules. begin dbms_streams_adm.add_schema_rules ( schema_name => 'SCOTT', streams_type => 'APPLY', streams_name => 'DBXA_APP', queue_name => 'DBXA_CAP_APP_Q', include_dml => true, include_ddl => true, inclusion_rule => true, include_tagged_lcr => false, source_database => 'DBXA.WORLD' ); end; / PL/SQL procedure successfully completed. -- No need to create propagation, since apply -- and capture run in the same database. -- Explicitly create the capture process. -- Note that use_database_link is set to TRUE. begin dbms_capture_adm.create_capture( capture_name => 'DBXA_CAP', queue_name => 'DBXA_CAP_APP_Q', use_database_link => TRUE, source_database => 'DBXA.WORLD' ); end; / PL/SQL procedure successfully completed. -- Add capture rules. begin dbms_streams_adm.add_schema_rules ( schema_name => 'SCOTT', streams_type => 'CAPTURE', streams_name => 'DBXA_CAP', queue_name => 'DBXA_CAP_APP_Q', include_dml => true, include_ddl => true, include_tagged_lcr => false, inclusion_rule => true, source_database => 'DBXA.WORLD' ); end; / PL/SQL procedure successfully completed. --之后就和前面一样实例化对象和启动应用进程和捕获进程9、包含下游捕获进程的单向复制(实时下游捕获)
--source数据库DBXA.WORLD、target数据库DBXB.WORLD --在source数据库中找到重做日志文件大小和重做日志文件数目 conn sys/manager@DBXA.WORLD as sysdba Connected. select group#, bytes/1048576 MB from v$log; GROUP# MB ---------- ---------- 1 50 2 50 3 50 --在下游数据库中创建备份重做日志,并且比source数据库的重做日志多一组(和配置dataguard一样) conn sys/manager@DBXB.WORLD as sysdba Connected. alter database add standby logfile group 4 ('/u01/oradata/DBXA/standby_logs/standby_redo04.log') size 50M; Database altered. alter database add standby logfile group 5 ('/u01/oradata/DBXA/standby_logs/standby_redo05.log') size 50M; Database altered. alter database add standby logfile group 6 ('/u01/oradata/DBXA/standby_logs/standby_redo06.log') size 50M; Database altered. alter database add standby logfile group 7 ('/u01/oradata/DBXA/standby_logs/standby_redo07.log') size 50M; Database altered. -- Check created Standby logs select thread#, group#, sequence#, status, archived from v$standby_log; THREAD# GROUP# SEQUENCE# STATUS ARC ---------- ---------- ---------- ---------- --- 0 4 0 UNASSIGNED YES 0 5 0 UNASSIGNED YES 0 6 0 UNASSIGNED YES 0 7 0 UNASSIGNED YES --这步和使用归档日志一样,为source和target数据库配置传输初始化参数 --和使用归档日志一样,创建捕获进程和应用进程 --注意:需要修改捕获进程参数 conn strmadmin/strmadmin@DBXB.WORLD Connected. -- Modify capture Parameter begin dbms_capture_adm.set_parameter( capture_name => 'DBXA_CAP', parameter => 'downstream_real_time_mine', value => 'Y' ); end; / PL/SQL procedure successfully completed. conn sys/manager@DBXA.WORLD as sysdba Connected. alter system archive log current; System altered. conn sys/manager@DBXB.WORLD as sysdba Connected. select thread#, group#, sequence#, archived, status from v$standby_log; THREAD# GROUP# SEQUENCE# ARC STATUS ---------- ---------- ---------- --- ---------- 1 4 289 YES ACTIVE 0 5 0 YES UNASSIGNED 0 6 0 YES UNASSIGNED 0 7 0 YES UNASSIGNED --之后就和前面一样实例化对象和启动应用进程和捕获进程10、双向复制
-- Connect to DBXB Database. conn strmadmin/strmadmin@DBXB.WORLD Connected. -- Create Queue for apply in DBXB. begin dbms_streams_adm.set_up_queue( queue_name => 'DBXA_APP_Q', queue_table => 'DBXA_APP_Q_T', queue_user => 'STRMADMIN' ); end; / PL/SQL procedure successfully completed. -- Create Queue for capture in DBXB. begin dbms_streams_adm.set_up_queue( queue_name => 'DBXB_CAP_Q', queue_table => 'DBXB_CAP_Q_T', queue_user => 'STRMADMIN' ); end; / PL/SQL procedure successfully completed. -- Create apply process and rules in DBXB. begin dbms_streams_adm.add_schema_rules ( schema_name => 'SCOTT', streams_type => 'APPLY', streams_name => 'DBXA_APP', queue_name => 'DBXA_APP_Q', include_dml => true, include_ddl => true, inclusion_rule => true, include_tagged_lcr => false, source_database => 'DBXA.WORLD' ); end; / PL/SQL procedure successfully completed. -- Create capture process and rules in DBXB. begin dbms_streams_adm.add_schema_rules ( schema_name => 'SCOTT', streams_type => 'CAPTURE', streams_name => 'DBXB_CAP', queue_name => 'DBXB_CAP_Q', include_dml => true, include_ddl => true, include_tagged_lcr => false, inclusion_rule => true, source_database => 'DBXB.WORLD' ); end; / PL/SQL procedure successfully completed. -- Create propagation in DBXB to send changes to DBXA. begin dbms_streams_adm.add_schema_propagation_rules ( schema_name => 'SCOTT', streams_name => 'DBXB_TO_DBXB_PROP', source_queue_name => 'DBXB_CAP_Q', destination_queue_name => 'DBXB_APP_Q@DBXA.WORLD', include_dml => true, include_ddl => true, inclusion_rule => true, include_tagged_lcr => false, queue_to_queue => true, source_database => 'DBXB.WORLD' ); end; / PL/SQL procedure successfully completed. -- Connect to DBXA Database conn strmadmin/strmadmin@DBXA.WORLD Connected. -- Create Queue for apply in DBXA. begin dbms_streams_adm.set_up_queue( queue_name => 'DBXB_APP_Q', queue_table => 'DBXB_APP_Q_T', queue_user => 'STRMADMIN' ); end; / PL/SQL procedure successfully completed. -- Create Queue for capture in DBXA. begin dbms_streams_adm.set_up_queue( queue_name => 'DBXA_CAP_Q', queue_table => 'DBXA_CAP_Q_T', queue_user => 'STRMADMIN' ); end; / PL/SQL procedure successfully completed. -- Create apply Process and Rules in DBXA. begin dbms_streams_adm.add_schema_rules ( schema_name => 'SCOTT', streams_type => 'APPLY', streams_name => 'DBXB_APP', queue_name => 'DBXB_APP_Q', include_dml => true, include_ddl => true, inclusion_rule => true, include_tagged_lcr => false, source_database => 'DBXB.WORLD' ); end; / PL/SQL procedure successfully completed. -- Create capture process and rules in DBXA. begin dbms_streams_adm.add_schema_rules ( schema_name => 'SCOTT', streams_type => 'CAPTURE', streams_name => 'DBXA_CAP', queue_name => 'DBXA_CAP_Q', include_dml => true, include_ddl => true, include_tagged_lcr => false, inclusion_rule => true, source_database => 'DBXA.WORLD' ); end; / PL/SQL procedure successfully completed. -- Create propagation in DBXA to send changes to DBXB. begin dbms_streams_adm.add_schema_propagation_rules ( schema_name => 'SCOTT', streams_name => 'DBXA_TO_DBXB_PROP', source_queue_name => 'DBXA_CAP_Q', destination_queue_name => 'DBXA_APP_Q@DBXB.WORLD', include_dml => true, include_ddl => true, inclusion_rule => true, include_tagged_lcr => false, queue_to_queue => true, source_database => 'DBXA.WORLD' ); end; / PL/SQL procedure successfully completed. --接下来将source数据库的对象导入到target数据库中,并设立实例化scn(data pump导入就不演示了) --在source数据库中也实例化scn(对需要的表)。如下手动设置 -- Connect to DBXB database. conn strmadmin/strmadmin@DBXB.WORLD Connected. declare v_scn number; begin v_scn := dbms_flashback.get_system_change_number(); dbms_apply_adm.set_schema_instantiation_scn@DBXA.WORLD( source_schema_name => 'SCOTT', source_database_name => 'DBXB.WORLD', instantiation_scn => v_scn, recursive => true ); end; / PL/SQL procedure successfully completed. --以上都操作完之后可以在两个数据库都启动capture和apply进程(每个数据库都要启动这两个进程)11、使用同步捕获进程的复制
-- Connect to Destination Database. conn strmadmin/strmadmin@DBXB.WORLD Connected. -- Create Streams Queue. begin dbms_streams_adm.set_up_queue( queue_name => 'DBXA_APP_Q', queue_table => 'DBXA_APP_Q_T', queue_user => 'STRMADMIN' ); end; / PL/SQL procedure successfully completed. -- Create the Apply Process. -- The APPLY_CAPTURED is set to FALSE by default. -- But, it is included for documentation purpose. -- begin dbms_apply_adm.create_apply( apply_name => 'DBXA_APP', queue_name => 'DBXA_APP_Q', apply_captured => FALSE ); end; / PL/SQL procedure successfully completed. begin dbms_streams_adm.add_schema_rules ( schema_name => 'SCOTT', streams_type => 'APPLY', streams_name => 'DBXA_APP', queue_name => 'DBXA_APP_Q', include_dml => true, source_database => 'DBXA.WORLD' ); end; / PL/SQL procedure successfully completed. -- Connect to the Source Database. conn strmadmin/strmadmin@DBXA.WORLD Connected. -- Create Streams Queue for synchronous capture. begin dbms_streams_adm.set_up_queue( queue_name => 'DBXA_SYNC_CAP_Q', queue_table => 'DBXA_SYNC_CAP_Q_T', queue_user => 'STRMADMIN' ); end; / PL/SQL procedure successfully completed. -- Create Synchronous capture process -- and add capture rules for replicated tables. begin dbms_streams_adm.add_table_rules ( table_name => 'SCOTT.DEPT', streams_type => 'SYNC_CAPTURE', streams_name => 'DBXA_SYNC_CAP', queue_name => 'DBXA_SYNC_CAP_Q', include_dml => true, inclusion_rule => true, source_database => 'DBXA.WORLD' ); dbms_streams_adm.add_table_rules ( table_name => 'SCOTT.EMP', streams_type => 'SYNC_CAPTURE', streams_name => 'DBXA_SYNC_CAP', queue_name => 'DBXA_SYNC_CAP_Q', include_dml => true, inclusion_rule => true, source_database => 'DBXA.WORLD' ); end; / PL/SQL procedure successfully completed. -- Create propagation Process and add rules. -- We can add rules at Schema or Global level. begin dbms_streams_adm.add_schema_propagation_rules ( schema_name => 'SCOTT', streams_name => 'DBXA_TO_DBXB_PROP', source_queue_name => 'DBXA_SYNC_CAP_Q', destination_queue_name => 'DBXA_APP_Q@DBXB.WORLD', include_dml => true, queue_to_queue => true, source_database => 'DBXA.WORLD' ); end; / PL/SQL procedure successfully completed. -- Since the Source and Destination tables are -- already in sync, no need to export/import data. -- We perform the instantiation manually. declare v_scn number; begin v_scn := dbms_flashback.get_system_change_number(); dbms_apply_adm.set_table_instantiation_scn@DBXB.WORLD( source_object_name => 'SCOTT.DEPT', source_database_name => 'DBXA.WORLD', instantiation_scn => v_scn); dbms_apply_adm.set_table_instantiation_scn@DBXB.WORLD( source_object_name => 'SCOTT.EMP', source_database_name => 'DBXA.WORLD', instantiation_scn => v_scn ); end; / PL/SQL procedure successfully completed.12、还有以下几种配置策略(P228)
1)从单个源到多个目标数据库
2)使用队列转发的复制
3)使用应用转发的复制
4)辐射类型复制
5)点对点复制