oracle11gstreams各种类型搭建主要步骤

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了oracle11gstreams各种类型搭建主要步骤，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含24707字，纯文字阅读大概需要36分钟。

内容图文

接前面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 completed

5、使用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 off

7、从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)点对点复制

内容总结

以上是互联网集市为您收集整理的oracle11gstreams各种类型搭建主要步骤全部内容，希望文章能够帮你解决oracle11gstreams各种类型搭建主要步骤所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---