配置oracle stream(step by step setup schema level Streams Replication)

过程和database level基本一样，本篇只简单介绍下过程

说明：

source 为hrdbprim，target db为hrdb，此处的数据库名称必须和global_name一致

hrdbprim =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.124.239)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hrdbprim)
    )
  )
hrdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.124.233)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hrdb)
    )
  )

STRMADMIN = stream管理用户
WJ = 需要复制同步的用户

一、环境准备
1,删除原有的stream配置
execute DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
2，配置数据库参数，一般需要设置global_names, _job_queue_interval, sga_target, streams_pool_size四个参数
3，创建管理用户STRMADMIN

create user STRMADMIN identified by STRM#123;ALTER USER STRMADMIN DEFAULT TABLESPACE USERSTEMPORARY TABLESPACE TEMPQUOTA UNLIMITED ON USERS;GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');

二、开始stream同步
1，@source DB 建立用户传播的队列和db link

connect STRMADMIN/STRM#123@hrdbprimBEGIN   DBMS_STREAMS_ADM.SET_UP_QUEUE(     queue_table => 'STREAMS_QUEUE_TABLE',     queue_name  => 'STREAMS_QUEUE',     queue_user  => 'STRMADMIN');END;/--CREATE DATABASE LINK AT SOURCE as SYS@BOTH DBconn sys/df as sysdbacreate public database link HRDB using 'HRDB';--CREATE DATABASE LINK AT SOURCE as STRMADMINconn strmadmin/STRM#123create database link HRDB connect to strmadmin identified by STRM#123;

以下均使用STRMADMIN用户

b, @target hrdb

/* Step 2 - Connect as the Streams Administrator in the target site TARGET and create the streams queue */conn strmadmin/STRM#123BEGINDBMS_STREAMS_ADM.SET_UP_QUEUE(queue_name => 'STREAMS_QUEUE',queue_table =>'STREAMS_QUEUE_TABLE',queue_user => 'STRMADMIN');END;/

c， @target DB 建立对单用户同步规则

/* STEP 4.- Add apply rules for the Schema at the destination database  */conn strmadmin/STRM#123BEGIN   DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(     schema_name     => 'WJ',     streams_type    => 'APPLY ',     streams_name    => 'STREAM_APPLY',     queue_name      => 'STRMADMIN.STREAMS_QUEUE',     include_dml     => true,     include_ddl     => true,     source_database => 'hrdbprim');END;/

d, @source DB

conn strmadmin/STRM#123BEGIN  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(    schema_name     => 'WJ',    streams_type    => 'CAPTURE',    streams_name    => 'STREAM_CAPTURE',    queue_name      => 'STRMADMIN.STREAMS_QUEUE',    include_dml     => true,    include_ddl     => true,    source_database => 'hrdbprim');END;/

e, @source DB 创建传播job

conn strmadmin/STRM#123BEGIN   DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(     schema_name            => 'WJ',     streams_name           => 'STREAM_PROPAGATE',     source_queue_name      => 'STRMADMIN.STREAMS_QUEUE',     destination_queue_name => 'STRMADMIN.STREAMS_QUEUE@HRDB',     include_dml            => true,     include_ddl            => true,     source_database        => 'HRDBPRIM');END;/

F,导入导出用户、注意参数

exp USERID=SYSTEM/123@hrdbprim OWNER=WJ FILE=wj.dmp LOG=wj.log OBJECT_CONSISTENT=Y STATISTICS = NONEimp USERID=SYSTEM/123@hrdb CONSTRAINTS=Y FULL=Y FILE=wj.dmp IGNORE=Y COMMIT=Y LOG=wj_imp.log STREAMS_INSTANTIATION=Y

备注：如果导入数据的target db中已有数据库，就比较麻烦可以使用如下两种方法

1. By means of Metadata-only export/import :Specify ROWS=N during ExportSpecify IGNORE=Y during Import along with above import parameters.2. By Manaually instantiating the objectsGet the Instantiation SCN at the source database:connect STRMADMIN/[e-mail] serveroutput onDECLARE    iscn NUMBER; -- Variable to hold instantiation SCN value    BEGIN       iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();       DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);END;/Instantiate the objects at the destination database with this SCN value. The SET_TABLE_INSTANTIATION_SCN procedure controls which LCRs for a table are to be applied by the apply process. If the commit SCN of an LCR from the source database is less than or equal to this instantiation SCN, then the apply process discards the LCR. Else, the apply process applies the LCR.connect STRMADMIN/[e-mail]   DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(     SOURCE_SCHEMA_NAME   => 'HR',     SOURCE_DATABASE_NAME => 'STRM1.NET',     RECURSIVE            => TRUE,     INSTANTIATION_SCN    => &iscn );END;Enter value for iscn:<Provide the value of SCN that you got from the source database above>In 10g recursive=true parameter of DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCNis used for instantiation. If using the parameter recursive true with SET_SCHEMA_INSTANTIATION_SCNthen you need a dblink on the destination database to the source databasewith the same name as the source database.Refer to the following documentationOracle?ˉ???? Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)B14258-01page 15-46 - SET_SCHEMA_INSTANTIATION_SCN Procedureotherwise apply aborts with following error example -ORA-26687: no instantiation SCN provided for "HR"."DEPARTMENTS" in  source database "STRM1.NET"  */

G, @target db 开启apply

BEGIN  DBMS_APPLY_ADM.ALTER_APPLY(    apply_name => 'STREAM_APPLY',    apply_user => 'Wj');END;/

H，@target DB 错误处理机制

BEGIN  DBMS_APPLY_ADM.SET_PARAMETER(    apply_name => 'STREAM_APPLY',    parameter  => 'disable_on_error',    value      => 'n');END;/DECLARE   v_started number;BEGIN   SELECT decode(status, 'ENABLED', 1, 0) INTO v_started   FROM DBA_APPLY WHERE APPLY_NAME = 'STREAM_APPLY';   if (v_started = 0) then      DBMS_APPLY_ADM.START_APPLY(apply_name => 'STREAM_APPLY');   end if;END;/

I，@source DB 打开capture

BEGIN  DBMS_CAPTURE_ADM.ALTER_CAPTURE(    capture_name              => 'STREAM_CAPTURE',    checkpoint_retention_time => 7);END;/begin  DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'STREAM_CAPTURE');end;/

结果测试：

@source dbSQL> select count(*) from wj.www;  COUNT(*)----------      1211SQL> truncate table wj.www;Table truncated.@target dbstrmadmin@HRDB1(dtydb3)>select count(*) from wj.www;  COUNT(*)----------      1211strmadmin@HRDB1(dtydb3)>select count(*) from wj.www;  COUNT(*)----------         0

参考文档
How To Setup One-Way SCHEMA Level Streams Replication [ID 301431.1]