当前位置：Gxlcms > mysql > oracle11gstreams各种类型搭建主要步骤

oracle11gstreams各种类型搭建主要步骤

时间：2021-07-01 10:21:17 帮助过：46人阅读

接前面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复制

  1. --完整参数示例
  2. connect strmadmin/strmadmin@DBXA.WORLD
  3. Connected.
  4. begin
  5.     dbms_streams_adm.maintain_global (
  6.         source_database => 'DBXA.WORLD',
  7.         source_directory_object => 'STREAMS_DP_DIR',
  8.         destination_database => 'DBXB.WORLD',
  9.         destination_directory_object => 'STREAMS_DP_DIR',
  10.         capture_name => 'DBXA_CAP',
  11.         capture_queue_name => 'DBXA_CAP_Q',
  12.         capture_queue_table => 'DBXA_CAP_Q_T',
  13.         capture_queue_user => 'STRMADMIN',
  14.         propagation_name => 'DBXA_TO_DBXB_PROP',
  15.         apply_name => 'DBXA_APP',
  16.         apply_queue_name => 'DBXA_APP_Q',
  17.         apply_queue_table => 'DBXA_APP_Q_T',
  18.         apply_queue_user => 'STRMADMIN',
  19.         script_name => 'cr_streams_global.sql',
  20.         script_directory_object => 'STREAMS_DP_DIR',
  21.         dump_file_name => NULL,
  22.         log_file => NULL,
  23.         bi_directional => FALSE,
  24.         include_ddl => TRUE,
  25.         perform_actions => TRUE,
  26.         instantiation => DBMS_STREAMS_ADM.INSTANTIATION_FULL
  27.     );
  28. end;
  29. /
  30. job finished
  31. PL/SQL procedure successfully completed.
  32. --如果不需要用户为streams组件定义有意义的名称
  33. connect strmadmin/strmadmin@DBXA.WORLD
  34. Connected.
  35. begin
  36.     dbms_streams_adm.maintain_global (
  37.         source_database => 'DBXA.WORLD',
  38.         source_directory_object => 'STREAMS_DP_DIR',
  39.         destination_database => 'DBXB.WORLD',
  40.         destination_directory_object => 'STREAMS_DP_DIR',
  41.         include_ddl => TRUE
  42.     );
  43. end;
  44. /
  45. job finished
  46. PL/SQL procedure successfully completed.
2、模式级别streams复制
  1. --完整参数
  2. connect strmadmin/strmadmin@DBXA.WORLD
  3. Connected.
  4. declare
  5.     schemas dbms_utility.uncl_array;
  6. begin
  7.     schemas(1) := 'SCOTT';
  8.     schemas(2) := 'HR';
  9.     dbms_streams_adm.maintain_schemas (
  10.         schema_names => schemas,
  11.         source_database => 'DBXA.WORLD',
  12.         source_directory_object => 'STREAMS_DP_DIR',
  13.         destination_database => 'DBXB.WORLD',
  14.         destination_directory_object => 'STREAMS_DP_DIR',
  15.         capture_name => 'DBXA_CAP',
  16.         capture_queue_name => 'DBXA_CAP_Q',
  17.         capture_queue_table => 'DBXA_CAP_Q_T',
  18.         propagation_name => 'DBXA_TO_DBXB_PROP',
  19.         apply_name => 'DBXA_APP',
  20.         apply_queue_name => 'DBXA_APP_Q',
  21.         apply_queue_table => 'DBXA_APP_Q_T',
  22.         dump_file_name => 'schemas_expimp.dmp',
  23.         log_file => 'schemas_expimp.log',
  24.         bi_directional => FALSE,
  25.         include_ddl => TRUE,
  26.         perform_actions => TRUE
  27.     );
  28. end;
  29. /
  30. job finished
  31. PL/SQL procedure successfully completed.
  32. --如果不需要用户为streams组件定义有意义的名称
  33. connect strmadmin/strmadmin@DBXA.WORLD
  34. Connected.
  35. declare
  36.     schemas dbms_utility.uncl_array;
  37. begin
  38.     schemas(1) := 'SCOTT';
  39.     schemas(2) := 'HR';
  40.     dbms_streams_adm.maintain_schemas (
  41.         schema_names => schemas,
  42.         source_database => 'DBXA.WORLD',
  43.         source_directory_object => 'STREAMS_DP_DIR',
  44.         destination_database => 'DBXB.WORLD',
  45.         destination_directory_object => 'STREAMS_DP_DIR',
  46.         include_ddl => TRUE,
  47.         instantiation => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA_NETWORK
  48.     );
  49. end;
  50. /
  51. PL/SQL procedure successfully completed.
3、表级别streams复制
  1. --完整参数
  2. connect strmadmin/strmadmin@DBXA.WORLD
  3. Connected.
  4. declare
  5.     tables dbms_utility.uncl_array;
  6. begin
  7.     tables(1) := 'SCOTT.DEPT';
  8.     tables(2) := 'SCOTT.EMP';
  9.     tables(3) := 'HR.SALGRADE';
  10.     tables(4) := 'HR.BONUS';
  11.     dbms_streams_adm.maintain_tables (
  12.         table_names => tables,
  13.         source_database => 'DBXA.WORLD',
  14.         source_directory_object => 'STREAMS_DP_DIR',
  15.         destination_database => 'DBXB.WORLD',
  16.         destination_directory_object => 'STREAMS_DP_DIR',
  17.         capture_name => 'DBXA_CAP',
  18.         capture_queue_name => 'DBXA_CAP_Q',
  19.         capture_queue_table => 'DBXA_CAP_Q_T',
  20.         propagation_name => 'DBXA_TO_DBXB_PROP',
  21.         apply_name => 'DBXA_APP',
  22.         apply_queue_name => 'DBXA_APP_Q',
  23.         apply_queue_table => 'DBXA_APP_Q_T',
  24.         dump_file_name => NULL,
  25.         log_file => NULL,
  26.         bi_directional => FALSE,
  27.         include_ddl => TRUE,
  28.         perform_actions => TRUE,
  29.         instantiation => DBMS_STREAMS_ADM.INSTANTIATION_TABLE
  30.     );
  31. end;
  32. /
  33. job finished
  34. PL/SQL procedure successfully completed.
4、表空间级别streams复制

  1. --在源数据库中创建 目录对象
  2. create directory example_ts_dir as '/u01/oradata/DBXA';
  3. Directory created.
  4. grant read on directory example_ts_dir to strmadmin;
  5. Grant succeeded
  6. --在目标数据库中创建目录对象
  7. create directory example_ts_dir as '/u02/app/oradata/DBXB';
  8. Directory created.
  9. --使用maintain_simple_tts过程创建表空间streams复制
  10. connect strmadmin/strmadmin@DBXA.WORLD
  11. Connected.
  12. begin
  13.     dbms_streams_adm.maintain_simple_tts(
  14.         tablespace_name => 'EXAMPLE_TS',
  15.         source_directory_object => 'STREAMS_DP_DIR',
  16.         source_database => 'DBXA.WORLD',
  17.         destination_directory_object => 'EXAMPLE_TS_DIR',
  18.         destination_database => 'DBXB.WORLD',
  19.         perform_actions => TRUE,
  20.         script_name => 'cr_maintain_simple_tts_uni.sql',
  21.         script_directory_object => 'STREAMS_DP_DIR',
  22.         bi_directional => FALSE
  23.     );
  24. end;
  25. /
  26. PL/SQL procedure successfully completed.
  27. --使用MAINTAIN_TTS过程创建表空间streams复制
  28. connect strmadmin/strmadmin@DBXA.WORLD
  29. Connected.
  30. declare
  31.     ts_names dbms_streams_tablespace_adm.tablespace_set;
  32. begin
  33.     ts_names(1) := 'DATA_TS';
  34.     ts_names(2) := 'INDEX_TS';
  35.     ts_names(3) := 'EXAMPLE_TS';
  36.     dbms_streams_adm.maintain_tts(
  37.         tablespace_names => ts_names,
  38.         source_directory_object => 'STREAMS_DP_DIR',
  39.         destination_directory_object => 'EXAMPLE_TS_DIR',
  40.         source_database => 'DBXA.WORLD',
  41.         destination_database => 'DBXB.WORLD',
  42.         perform_actions => TRUE,
  43.         script_name => 'cr_streams_maintain_tts_uni.sql',
  44.         script_directory_object => 'STREAMS_DP_DIR',
  45.         dump_file_name => 'maint_tts.dmp',
  46.         capture_name => 'DBXA_CAP',
  47.         capture_queue_table => 'DBXA_CAP_Q_T',
  48.         capture_queue_name => 'DBXA_CAP_Q',
  49.         capture_queue_user => 'STRMADMIN',
  50.         propagation_name => 'DBXA_TO_DBXB_PROP',
  51.         apply_name => 'DBXA_APP',
  52.         apply_queue_table => 'DBXA_APP_Q_T',
  53.         apply_queue_name => 'DBXA_APP_Q',
  54.         apply_queue_user => 'STRMADMIN',
  55.         log_file => 'maintain_tts.log',
  56.         bi_directional => FALSE,
  57.         include_ddl => TRUE
  58.     );
  59. end;
  60. /
  61. PL/SQL procedure successfully completed
5、使用MAINTAIN存储过程配置下游捕获进程

  1. --存储过程运行在目标数据库DBXB.WORKD中
  2. --不需要传播进程，如果指定了传播进程也不会创建
  3. --捕获进程和应用进程的队列名和队列表名一样
  4. connect strmadmin/strmadmin@DBXB.WORLD
  5. Connected.
  6. declare
  7.     schemas dbms_utility.uncl_array;
  8. begin
  9.     schemas(1) := 'SCOTT';
  10.     schemas(2) := 'HR';
  11.     dbms_streams_adm.maintain_schemas (
  12.         schema_names => schemas,
  13.         source_database => 'DBXA.WORLD',
  14.         source_directory_object => 'STREAMS_DP_DIR',
  15.         destination_database => 'DBXB.WORLD',
  16.         destination_directory_object => 'STREAMS_DP_DIR',
  17.         capture_name => 'DBXA_CAP',
  18.         capture_queue_name => 'DBXA_CAP_Q',
  19.         capture_queue_table => 'DBXA_CAP_Q_T',
  20.         propagation_name => 'DBXA_TO_DBXB_PROP',
  21.         apply_name => 'DBXA_APP',
  22.         apply_queue_name => 'DBXA_CAP_Q',
  23.         apply_queue_table => 'DBXA_CAP_Q_T',
  24.         dump_file_name => NULL,
  25.         log_file => NULL,
  26.         bi_directional => FALSE,
  27.         include_ddl => TRUE,
  28.         perform_actions => TRUE
  29.     );
  30. end;
  31. /
  32. job finished
  33. PL/SQL procedure successfully completed.
  34. --配置从数据库DBXA.WORLD到DBXB.WORLD的streams复制的方法
  35. --存储过程运行在DBXB.WORLD数据库中
  36. --传播定义从DBXB.WORLD到DBXC.WORLD
  37. --捕获进程和应用进程的队列名和队列表名不同
  38. connect strmadmin/strmadmin@DBXB.WORLD
  39. Connected.
  40. declare
  41.     schemas dbms_utility.uncl_array;
  42. begin
  43.     schemas(1) := 'SCOTT';
  44.     schemas(2) := 'HR';
  45.     dbms_streams_adm.maintain_schemas (
  46.         schema_names => schemas,
  47.         source_database => 'DBXA.WORLD',
  48.         source_directory_object => 'STREAMS_DP_DIR',
  49.         destination_database => 'DBXC.WORLD',
  50.         destination_directory_object => 'STREAMS_DP_DIR',
  51.         capture_name => 'DBXA_CAP',
  52.         capture_queue_name => 'DBXA_CAP_Q',
  53.         capture_queue_table => 'DBXA_CAP_Q_T',
  54.         propagation_name => 'DBXB_TO_DBXC_PROP',
  55.         apply_name => 'DBXA_APP',
  56.         apply_queue_name => 'DBXA_APP_Q',
  57.         apply_queue_table => 'DBXA_APP_Q_T',
  58.         dump_file_name => NULL,
  59.         log_file => NULL,
  60.         bi_directional => FALSE,
  61.         include_ddl => TRUE,
  62.         perform_actions => TRUE
  63.     );
  64. end;
  65. /
  66. PL/SQL procedure successfully completed.
6、创建视图来查看streams 存储过程的状态和进度
  1. --主要是基于DBA_RECOVERABLE_SCRIPT视图和DBA_RECOVERABLE_SCRIPT_BLOCKS视图创建
  2. connect sys as sysdba
  3. set long 100000000
  4. create or replace view streams_build_status
  5. as
  6.     select to_char(rs.creation_time,'HH24:Mi:SS MM/DD/YY') CREATE_DATE,
  7.         rs.status,
  8.         rs.done_block_num||' of ' ||rs.total_blocks ||' Steps Completed' PROGRESS,
  9.         to_char(to_number(sysdate-rs.creation_time)*86400,9999.99) ELAPSED_SECONDS,
  10.         substr(rsb.forward_block,1,5000) CURRENT_STEP,
  11.         rs.invoking_package||'.'||rs.invoking_procedure PROCEDURE,
  12.         rs.script_id
  13.     from dba_recoverable_script rs,
  14.             dba_recoverable_script_blocks rsb
  15.     where rs.script_id = rsb.script_id
  16.     and rsb.block_num = rs.done_block_num + 1;
  17.     
  18. create public synonym streams_build_status for streams_build_status;
  19. grant select on streams_build_status to public;
  20. --查询进度信息（存储过程当前正在执行、一共有14个过程块已经完成13个、已经运行了276秒）
  21. select status,
  22.     progress,
  23.     elapsed_seconds elapsed,
  24.     script_id
  25. from streams_build_status;
  26. STATUS    PROGRESS                  ELAPSED SCRIPT_ID
  27. --------- ------------------------- ------- --------------------------------
  28. EXECUTING 13 of 14 Steps Completed   276.00 7CC97F3B9169704BE040A8C014006E63
  29. --查看正在执行的过程块
  30. SQL> select current_step
  31. 2 from streams_build_status;
  32. CURRENT_STEP
  33. -----------------------------------------------------------------------
  34. -- Start capture process DBXA$CAP
  35. --
  36. BEGIN
  37.     dbms_capture_adm.start_capture(
  38.         capture_name => '"DBXA$CAP"'
  39.     );
  40. EXCEPTION WHEN OTHERS THEN
  41.     IF sqlcode = -26666 THEN NULL; -- CAPTURE process already running
  42.     ELSE RAISE;
  43.     END IF;
  44. END;
  45. --提取脚本及过程块
  46. set long 10000000
  47. set pages 1000
  48. spool maintain_script.sql
  49.     select '-- Block: ' || block_num,
  50.         forward_block
  51.     from dba_recoverable_script_blocks
  52.     where script_id = '7CC97F3B9169704BE040A8C014006E63'
  53.     order by block_num;
  54. spool off
7、从MAINTAIN存储过程的错误中恢复
  1. --下面展示配置模式级别的复制时，对于一个造成MAINTAIN存储过程失败的简单错误处理
  2. connect strmadmin/strmadmin@DBXA.WORLD
  3. Connected.
  4. declare
  5.     schemas dbms_utility.uncl_array;
  6. begin
  7.     schemas(1) := 'SCOTT';
  8.     schemas(2) := 'HR';
  9.     dbms_streams_adm.maintain_schemas (
  10.         schema_names => schemas,
  11.         source_database => 'DBXA.WORLD',
  12.         source_directory_object => 'STREAMS_DP_DIR',
  13.         destination_database => 'DBXB.WORLD',
  14.         destination_directory_object => 'STREAMS_DP_DIR',
  15.         include_ddl => TRUE,
  16.         instantiation => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA_NETWORK
  17.     );
  18. end;
  19. /
  20. declare
  21. *
  22. ERROR at line 1:
  23. ORA-23616: Failure in executing block 7 for script
  24. 7CD4E8B08BD40E08E040A8C014007723 with
  25. ORA-39001: invalid argument value
  26. ORA-06512: at "SYS.DBMS_RECO_SCRIPT_INVOK", line 139
  27. ORA-06512: at "SYS.DBMS_STREAMS_RPC", line 465
  28. ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 659
  29. ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 682
  30. ORA-06512: at "SYS.DBMS_STREAMS_MT", line 7972
  31. ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 2674
  32. ORA-06512: at line 7
  33. --通过错误提示的script_id查看错误的具体信息
  34. select forward_block
  35. from dba_recoverable_script_blocks
  36. where script_id = '7CD4E8B08BD40E08E040A8C014007723'
  37. and block_num = 7;
  38. FORWARD_BLOCK
  39. -----------------------------------------------------------
  40. --
  41. -- Datapump SCHEMA MODE IMPORT (NETWORK)
  42. --
  43. DECLARE
  44. h1 NUM
  45. ......
  46. ......
  47. --由上可以看出是数据库连接丢失造成的错误
  48. --在创建数据库连接时候作业在恢复之后便可以成功完成
  49. begin
  50.     dbms_streams_adm.recover_operation(
  51.         script_id => '7CD4E8B08BD40E08E040A8C014007723',
  52.         operation_mode => 'FORWARD'
  53.     );
  54. end;
  55. /
  56. PL/SQL procedure successfully completed.
8、含有本地进程的单向复制
  1. --配置schema级别的streams，source：DBXA.WORLD、target：DBXB.WORLD
  2. --连接到目标数据库并且创建streams队列和表
  3. conn strmadmin/strmadmin@DBXB.WORLD
  4. Connected.
  5. begin
  6.     dbms_streams_adm.set_up_queue(
  7.         queue_name => 'DBXA_APP_Q',
  8.         queue_table => 'DBXA_APP_Q_T',
  9.         queue_user => 'STRMADMIN'
  10.     );
  11. end;
  12. /
  13. --在target数据库中创建应用进程和规则
  14. begin
  15.     dbms_streams_adm.add_schema_rules (
  16.         schema_name => 'SCOTT',
  17.         streams_type => 'APPLY',
  18.         streams_name => 'DBXA_APP',
  19.         queue_name => 'DBXA_APP_Q',
  20.         include_dml => true,
  21.         include_ddl => true,
  22.         inclusion_rule => true,
  23.         include_tagged_lcr => false,
  24.         source_database => 'DBXA.WORLD',
  25.         and_condition => ' :lcr.get_compatible() < dbms_streams.max_compatible()'
  26.     );
  27. end;
  28. /
  29. PL/SQL procedure successfully completed.
  30. --连接source数据库并创建Streams队列
  31. conn strmadmin/strmadmin@DBXA.WORLD
  32. Connected.
  33. set serveroutput on size unlimited
  34. set echo on
  35. begin
  36.     dbms_streams_adm.set_up_queue(
  37.         queue_name => 'DBXA_CAP_Q',
  38.         queue_table => 'DBXA_CAP_Q_T',
  39.         queue_user => 'STRMADMIN'
  40.     );
  41. end;
  42. /
  43. PL/SQL procedure successfully completed.
  44. --在source数据库创建传播进程和传播规则
  45. begin
  46.     dbms_streams_adm.add_schema_propagation_rules (
  47.         schema_name => 'SCOTT',
  48.         streams_name => 'DBXA_TO_DBXB_PROP',
  49.         source_queue_name => 'DBXA_CAP_Q',
  50.         destination_queue_name => 'DBXA_APP_Q@DBXB.WORLD',
  51.         include_dml => true,
  52.         include_ddl => true,
  53.         inclusion_rule => true,
  54.         include_tagged_lcr => false,
  55.         queue_to_queue => true,
  56.         source_database => 'DBXA.WORLD',
  57.         and_condition => ' :lcr.get_compatible() < dbms_streams.max_compatible()'
  58.     );
  59. end;
  60. /
  61. PL/SQL procedure successfully completed.
  62. --在source数据库中创建捕获进程和捕获规则
  63. begin
  64.     dbms_streams_adm.add_schema_rules (
  65.         schema_name => 'SCOTT',
  66.         streams_type => 'CAPTURE',
  67.         streams_name => 'DBXA_CAP',
  68.         queue_name => 'DBXA_CAP_Q',
  69.         include_dml => true,
  70.         include_ddl => true,
  71.         include_tagged_lcr => false,
  72.         inclusion_rule => true,
  73.         source_database => 'DBXA.WORLD',
  74.         and_condition => ' :lcr.get_compatible() < dbms_streams.max_compatible()'
  75.     );
  76. end;
  77. /
  78. PL/SQL procedure successfully completed.
  79. --在target数据库中实例化对象
  80. --使用data pump导出，参数文件内容示例
  81. directory=data_pump_dir
  82. schemas=SCOTT
  83. parallel=4
  84. dumpfile=schemas_%u.dmp
  85. logfile=schemas_expdp.log
  86. --使用data pump导入，参数文件内容示例
  87. directory=data_pump_dir
  88. full=y
  89. parallel=4
  90. table_exists_action=truncate
  91. dumpfile=schemas_%u.dmp
  92. logfile=schemas_expdp.log
  93. --在target数据库中启动APPLY进程
  94. conn strmadmin/strmadmin@DBXB.WORLD
  95. Connected.
  96. begin
  97.     dbms_apply_adm.start_apply('DBXA_APP');
  98. end;
  99. /
  100. PL/SQL procedure successfully completed.
  101. --在source数据库中启动CAPTURE进程
  102. conn strmadmin/strmadmin@DBXA.WORLD
  103. Connected.
  104. begin
  105.     dbms_capture_adm.start_capture('DBXA_CAP');
  106. end;
  107. /
  108. PL/SQL procedure successfully completed.
  109. --检查 alter 警告日志文件
  110. --看见如下消息就说明capture进程正在挖掘日志
  111. LOGMINER: Begin mining logfile for session 23 thread 1 sequence 230,
  112. /u01/oradata/DBXA/redo01.log
  113. LOGMINER: End mining logfile for session 23 thread 1 sequence 230,
  114. /u01/oradata/DBXA/redo01.log
  115. LOGMINER: Begin mining logfile for session 23 thread 1 sequence 231,
  116. /u01/oradata/DBXA/redo01.log
  117. 9、包含下游捕获进程的单向复制（归档日志下游捕获）
  118. --DBXB.WORLD作为下游数据库和目标数据库使用
  119. --通过log_archive_dest_2来建立重做传输
  120. connect sys/manager@DBXA.WORLD as sysdba
  121. Connected.
  122. alter system set log_archive_dest_2=
  123.         'service=DBXB.WORLD ASYNC NOREGISTER VALID_FOR=(online_logfiles, all_roles)
  124.         3 TEMPLATE=DBXA_arch_%t_%s_%r.arc' scope=both;
  125. System altered.
  126. alter system set log_archive_dest_state_2=ENABLE scope=both;
  127. System altered.
  128. connect sys/manager@DBXB.WORLD as sysdba
  129. Connected.
  130. alter system set log_archive_dest_2=
  131.         'location=/u01/oradata/DBXA_logs VALID_FOR=(standby_logfiles, primary_role)'
  132.         scope=both;
  133. System altered.
  134. alter system set log_archive_dest_state_2=ENABLE scope=both;
  135. System altered.
  136. --由于目标数据库也是下游数据库，因此不需要配置传播进程。捕获进程和应用进程将共享streams队列和队列表
  137. connect strmadmin/strmadmin@DBXA.WORLD
  138. Connected.
  139. -- Create Streams Queue.
  140. begin
  141.     dbms_streams_adm.set_up_queue(
  142.         queue_name => 'DBXA_CAP_APP_Q',
  143.         queue_table => 'DBXA_CAP_APP_Q_T',
  144.         queue_user => 'STRMADMIN'
  145.     );
  146. end;
  147. /
  148. PL/SQL procedure successfully completed.
  149. -- Create apply process and rules.
  150. begin
  151.     dbms_streams_adm.add_schema_rules (
  152.         schema_name => 'SCOTT',
  153.         streams_type => 'APPLY',
  154.         streams_name => 'DBXA_APP',
  155.         queue_name => 'DBXA_CAP_APP_Q',
  156.         include_dml => true,
  157.         include_ddl => true,
  158.         inclusion_rule => true,
  159.         include_tagged_lcr => false,
  160.         source_database => 'DBXA.WORLD'
  161.     );
  162. end;
  163. /
  164. PL/SQL procedure successfully completed.
  165. -- No need to create propagation, since apply
  166. -- and capture run in the same database.
  167. -- Explicitly create the capture process.
  168. -- Note that use_database_link is set to TRUE.
  169. begin
  170.     dbms_capture_adm.create_capture(
  171.         capture_name => 'DBXA_CAP',
  172.         queue_name => 'DBXA_CAP_APP_Q',
  173.         use_database_link => TRUE,
  174.         source_database => 'DBXA.WORLD'
  175.     );
  176. end;
  177. /
  178. PL/SQL procedure successfully completed.
  179. -- Add capture rules.
  180. begin
  181.     dbms_streams_adm.add_schema_rules (
  182.         schema_name => 'SCOTT',
  183.         streams_type => 'CAPTURE',
  184.         streams_name => 'DBXA_CAP',
  185.         queue_name => 'DBXA_CAP_APP_Q',
  186.         include_dml => true,
  187.         include_ddl => true,
  188.         include_tagged_lcr => false,
  189.         inclusion_rule => true,
  190.         source_database => 'DBXA.WORLD'
  191.     );
  192. end;
  193. /
  194. PL/SQL procedure successfully completed.
  195. --之后就和前面一样实例化对象和启动应用进程和捕获进程
9、包含下游捕获进程的单向复制（实时下游捕获）
  1. --source数据库DBXA.WORLD、target数据库DBXB.WORLD
  2. --在source数据库中找到重做日志文件大小和重做日志文件数目
  3. conn sys/manager@DBXA.WORLD as sysdba
  4. Connected.
  5. select group#,
  6.     bytes/1048576 MB
  7. from v$log;
  8. GROUP# MB
  9. ---------- ----------
  10.          1         50
  11.          2         50
  12.          3         50
  13. --在下游数据库中创建备份重做日志，并且比source数据库的重做日志多一组（和配置dataguard一样）
  14. conn sys/manager@DBXB.WORLD as sysdba
  15. Connected.
  16. alter database add standby logfile group 4
  17.     ('/u01/oradata/DBXA/standby_logs/standby_redo04.log') size 50M;
  18. Database altered.
  19. alter database add standby logfile group 5
  20.     ('/u01/oradata/DBXA/standby_logs/standby_redo05.log') size 50M;
  21. Database altered.
  22. alter database add standby logfile group 6
  23.     ('/u01/oradata/DBXA/standby_logs/standby_redo06.log') size 50M;
  24. Database altered.
  25. alter database add standby logfile group 7
  26.     ('/u01/oradata/DBXA/standby_logs/standby_redo07.log') size 50M;
  27. Database altered.
  28. -- Check created Standby logs
  29. select thread#,
  30.     group#,
  31.     sequence#,
  32.     status,
  33.     archived
  34. from v$standby_log;
  35. THREAD#    GROUP#     SEQUENCE#  STATUS     ARC
  36. ---------- ---------- ---------- ---------- ---
  37.          0          4          0 UNASSIGNED YES
  38.          0          5          0 UNASSIGNED YES
  39.          0          6          0 UNASSIGNED YES
  40.          0          7          0 UNASSIGNED YES
  41. --这步和使用归档日志一样，为source和target数据库配置传输初始化参数     
  42. --和使用归档日志一样，创建捕获进程和应用进程
  43.         
  44. --注意：需要修改捕获进程参数
  45. conn strmadmin/strmadmin@DBXB.WORLD
  46. Connected.
  47. -- Modify capture Parameter
  48. begin
  49.     dbms_capture_adm.set_parameter(
  50.         capture_name => 'DBXA_CAP',
  51.         parameter => 'downstream_real_time_mine',
  52.         value => 'Y'
  53.     );
  54. end;
  55. /
  56. PL/SQL procedure successfully completed.
  57. conn sys/manager@DBXA.WORLD as sysdba
  58. Connected.
  59. alter system archive log current;
  60. System altered.
  61. conn sys/manager@DBXB.WORLD as sysdba
  62. Connected.
  63. select thread#,
  64.     group#,
  65.     sequence#,
  66.     archived,
  67.     status
  68. from v$standby_log;
  69. THREAD#    GROUP#     SEQUENCE#  ARC STATUS
  70. ---------- ---------- ---------- --- ----------
  71.          1          4        289 YES ACTIVE
  72.          0          5          0 YES UNASSIGNED
  73.          0          6          0 YES UNASSIGNED
  74.          0          7          0 YES UNASSIGNED      
  75.          
  76. --之后就和前面一样实例化对象和启动应用进程和捕获进程
10、双向复制
  1. -- Connect to DBXB Database.
  2. conn strmadmin/strmadmin@DBXB.WORLD
  3. Connected.
  4. -- Create Queue for apply in DBXB.
  5. begin
  6.     dbms_streams_adm.set_up_queue(
  7.         queue_name => 'DBXA_APP_Q',
  8.         queue_table => 'DBXA_APP_Q_T',
  9.         queue_user => 'STRMADMIN'
  10.     );
  11. end;
  12. /
  13. PL/SQL procedure successfully completed.
  14. -- Create Queue for capture in DBXB.
  15. begin
  16.     dbms_streams_adm.set_up_queue(
  17.         queue_name => 'DBXB_CAP_Q',
  18.         queue_table => 'DBXB_CAP_Q_T',
  19.         queue_user => 'STRMADMIN'
  20.     );
  21. end;
  22. /
  23. PL/SQL procedure successfully completed.
  24. -- Create apply process and rules in DBXB.
  25. begin
  26.     dbms_streams_adm.add_schema_rules (
  27.         schema_name => 'SCOTT',
  28.         streams_type => 'APPLY',
  29.         streams_name => 'DBXA_APP',
  30.         queue_name => 'DBXA_APP_Q',
  31.         include_dml => true,
  32.         include_ddl => true,
  33.         inclusion_rule => true,
  34.         include_tagged_lcr => false,
  35.         source_database => 'DBXA.WORLD'
  36.     );
  37. end;
  38. /
  39. PL/SQL procedure successfully completed.
  40. -- Create capture process and rules in DBXB.
  41. begin
  42.     dbms_streams_adm.add_schema_rules (
  43.         schema_name => 'SCOTT',
  44.         streams_type => 'CAPTURE',
  45.         streams_name => 'DBXB_CAP',
  46.         queue_name => 'DBXB_CAP_Q',
  47.         include_dml => true,
  48.         include_ddl => true,
  49.         include_tagged_lcr => false,
  50.         inclusion_rule => true,
  51.         source_database => 'DBXB.WORLD'
  52.     );
  53. end;
  54. /
  55. PL/SQL procedure successfully completed.
  56. -- Create propagation in DBXB to send changes to DBXA.
  57. begin
  58.     dbms_streams_adm.add_schema_propagation_rules (
  59.         schema_name => 'SCOTT',
  60.         streams_name => 'DBXB_TO_DBXB_PROP',
  61.         source_queue_name => 'DBXB_CAP_Q',
  62.         destination_queue_name => 'DBXB_APP_Q@DBXA.WORLD',
  63.         include_dml => true,
  64.         include_ddl => true,
  65.         inclusion_rule => true,
  66.         include_tagged_lcr => false,
  67.         queue_to_queue => true,
  68.         source_database => 'DBXB.WORLD'
  69.     );
  70. end;
  71. /
  72. PL/SQL procedure successfully completed.
  73. -- Connect to DBXA Database
  74. conn strmadmin/strmadmin@DBXA.WORLD
  75. Connected.
  76. -- Create Queue for apply in DBXA.
  77. begin
  78.     dbms_streams_adm.set_up_queue(
  79.         queue_name => 'DBXB_APP_Q',
  80.         queue_table => 'DBXB_APP_Q_T',
  81.         queue_user => 'STRMADMIN'
  82.     );
  83. end;
  84. /
  85. PL/SQL procedure successfully completed.
  86. -- Create Queue for capture in DBXA.
  87. begin
  88.     dbms_streams_adm.set_up_queue(
  89.         queue_name => 'DBXA_CAP_Q',
  90.         queue_table => 'DBXA_CAP_Q_T',
  91.         queue_user => 'STRMADMIN'
  92.     );
  93. end;
  94. /
  95. PL/SQL procedure successfully completed.
  96. -- Create apply Process and Rules in DBXA.
  97. begin
  98.     dbms_streams_adm.add_schema_rules (
  99.         schema_name => 'SCOTT',
  100.         streams_type => 'APPLY',
  101.         streams_name => 'DBXB_APP',
  102.         queue_name => 'DBXB_APP_Q',
  103.         include_dml => true,
  104.         include_ddl => true,
  105.         inclusion_rule => true,
  106.         include_tagged_lcr => false,
  107.         source_database => 'DBXB.WORLD'
  108.     );
  109. end;
  110. /
  111. PL/SQL procedure successfully completed.
  112. -- Create capture process and rules in DBXA.
  113. begin
  114.     dbms_streams_adm.add_schema_rules (
  115.         schema_name => 'SCOTT',
  116.         streams_type => 'CAPTURE',
  117.         streams_name => 'DBXA_CAP',
  118.         queue_name => 'DBXA_CAP_Q',
  119.         include_dml => true,
  120.         include_ddl => true,
  121.         include_tagged_lcr => false,
  122.         inclusion_rule => true,
  123.         source_database => 'DBXA.WORLD'
  124.     );
  125. end;
  126. /
  127. PL/SQL procedure successfully completed.
  128. -- Create propagation in DBXA to send changes to DBXB.
  129. begin
  130.     dbms_streams_adm.add_schema_propagation_rules (
  131.         schema_name => 'SCOTT',
  132.         streams_name => 'DBXA_TO_DBXB_PROP',
  133.         source_queue_name => 'DBXA_CAP_Q',
  134.         destination_queue_name => 'DBXA_APP_Q@DBXB.WORLD',
  135.         include_dml => true,
  136.         include_ddl => true,
  137.         inclusion_rule => true,
  138.         include_tagged_lcr => false,
  139.         queue_to_queue => true,
  140.         source_database => 'DBXA.WORLD'
  141.     );
  142. end;
  143. /
  144. PL/SQL procedure successfully completed.
  145. --接下来将source数据库的对象导入到target数据库中，并设立实例化scn（data pump导入就不演示了）
  146. --在source数据库中也实例化scn（对需要的表）。如下手动设置
  147. -- Connect to DBXB database.
  148. conn strmadmin/strmadmin@DBXB.WORLD
  149. Connected.
  150. declare
  151. v_scn number;
  152. begin
  153.     v_scn := dbms_flashback.get_system_change_number();
  154.     dbms_apply_adm.set_schema_instantiation_scn@DBXA.WORLD(
  155.         source_schema_name => 'SCOTT',
  156.         source_database_name => 'DBXB.WORLD',
  157.         instantiation_scn => v_scn,
  158.         recursive => true
  159.     );
  160. end;
  161. /
  162. PL/SQL procedure successfully completed.
  163. --以上都操作完之后可以在两个数据库都启动capture和apply进程（每个数据库都要启动这两个进程）
11、使用同步捕获进程的复制
  1. -- Connect to Destination Database.
  2. conn strmadmin/strmadmin@DBXB.WORLD
  3. Connected.
  4. -- Create Streams Queue.
  5. begin
  6.     dbms_streams_adm.set_up_queue(
  7.         queue_name => 'DBXA_APP_Q',
  8.         queue_table => 'DBXA_APP_Q_T',
  9.         queue_user => 'STRMADMIN'
  10.     );
  11. end;
  12. /
  13. PL/SQL procedure successfully completed.
  14. -- Create the Apply Process.
  15. -- The APPLY_CAPTURED is set to FALSE by default.
  16. -- But, it is included for documentation purpose.
  17. --
  18. begin
  19.     dbms_apply_adm.create_apply(
  20.         apply_name => 'DBXA_APP',
  21.         queue_name => 'DBXA_APP_Q',
  22.         apply_captured => FALSE
  23.     );
  24. end;
  25. /
  26. PL/SQL procedure successfully completed.
  27. begin
  28.     dbms_streams_adm.add_schema_rules (
  29.         schema_name => 'SCOTT',
  30.         streams_type => 'APPLY',
  31.         streams_name => 'DBXA_APP',
  32.         queue_name => 'DBXA_APP_Q',
  33.         include_dml => true,
  34.         source_database => 'DBXA.WORLD'
  35.     );
  36. end;
  37. /
  38. PL/SQL procedure successfully completed.
  39. -- Connect to the Source Database.
  40. conn strmadmin/strmadmin@DBXA.WORLD
  41. Connected.
  42. -- Create Streams Queue for synchronous capture.
  43. begin
  44.     dbms_streams_adm.set_up_queue(
  45.         queue_name => 'DBXA_SYNC_CAP_Q',
  46.         queue_table => 'DBXA_SYNC_CAP_Q_T',
  47.         queue_user => 'STRMADMIN'
  48.     );
  49. end;
  50. /
  51. PL/SQL procedure successfully completed.
  52. -- Create Synchronous capture process
  53. -- and add capture rules for replicated tables.
  54. begin
  55.     dbms_streams_adm.add_table_rules (
  56.         table_name => 'SCOTT.DEPT',
  57.         streams_type => 'SYNC_CAPTURE',
  58.         streams_name => 'DBXA_SYNC_CAP',
  59.         queue_name => 'DBXA_SYNC_CAP_Q',
  60.         include_dml => true,
  61.         inclusion_rule => true,
  62.         source_database => 'DBXA.WORLD'
  63.     );
  64.     dbms_streams_adm.add_table_rules (
  65.         table_name => 'SCOTT.EMP',
  66.         streams_type => 'SYNC_CAPTURE',
  67.         streams_name => 'DBXA_SYNC_CAP',
  68.         queue_name => 'DBXA_SYNC_CAP_Q',
  69.         include_dml => true,
  70.         inclusion_rule => true,
  71.         source_database => 'DBXA.WORLD'
  72.     );
  73. end;
  74. /
  75. PL/SQL procedure successfully completed.
  76. -- Create propagation Process and add rules.
  77. -- We can add rules at Schema or Global level.
  78. begin
  79.     dbms_streams_adm.add_schema_propagation_rules (
  80.         schema_name => 'SCOTT',
  81.         streams_name => 'DBXA_TO_DBXB_PROP',
  82.         source_queue_name => 'DBXA_SYNC_CAP_Q',
  83.         destination_queue_name => 'DBXA_APP_Q@DBXB.WORLD',
  84.         include_dml => true,
  85.         queue_to_queue => true,
  86.         source_database => 'DBXA.WORLD'
  87.     );
  88. end;
  89. /
  90. PL/SQL procedure successfully completed.
  91. -- Since the Source and Destination tables are
  92. -- already in sync, no need to export/import data.
  93. -- We perform the instantiation manually.
  94. declare
  95. v_scn number;
  96. begin
  97.     v_scn := dbms_flashback.get_system_change_number();
  98.     dbms_apply_adm.set_table_instantiation_scn@DBXB.WORLD(
  99.         source_object_name => 'SCOTT.DEPT',
  100.         source_database_name => 'DBXA.WORLD',
  101.         instantiation_scn => v_scn);
  102.         dbms_apply_adm.set_table_instantiation_scn@DBXB.WORLD(
  103.         source_object_name => 'SCOTT.EMP',
  104.         source_database_name => 'DBXA.WORLD',
  105.         instantiation_scn => v_scn
  106.     );
  107. end;
  108. /
  109. PL/SQL procedure successfully completed.
12、还有以下几种配置策略（P228）

1)从单个源到多个目标数据库

2)使用队列转发的复制

3)使用应用转发的复制

4)辐射类型复制

5)点对点复制

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！