oracle11gstreams各种类型搭建主要步骤

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

--完整参数
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.

--完整参数
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.

--在源数据库中创建 目录对象
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

--存储过程运行在目标数据库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.

--主要是基于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

--下面展示配置模式级别的复制时，对于一个造成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.

--配置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.

--之后就和前面一样实例化对象和启动应用进程和捕获进程

--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 
 
--之后就和前面一样实例化对象和启动应用进程和捕获进程 

-- 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进程（每个数据库都要启动这两个进程）

-- 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.

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

2)使用队列转发的复制

3)使用应用转发的复制

4)辐射类型复制

5)点对点复制