Home > Article > Database > oracle 11g streams各种类型搭建主要步骤

oracle 11g streams各种类型搭建主要步骤

WBOYOriginal: 2016-06-07 15:37:241408browse

接前面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()  '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()  '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()  '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)点对点复制

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：oracle常用函数整理Next article：H2数据库攻略

See more

oracle 11g streams各种类型搭建主要步骤

Related articles