配置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 USERS
TEMPORARY TABLESPACE TEMP
QUOTA 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@hrdbprim

BEGIN
   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 DB
conn sys/df as sysdba
create public database link HRDB using 'HRDB';

--CREATE DATABASE LINK AT SOURCE as STRMADMIN
conn strmadmin/STRM#123
create 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#123

BEGIN
DBMS_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#123

BEGIN
   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#123

BEGIN
  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#123
BEGIN
   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 = NONE

imp 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 Export
Specify IGNORE=Y during Import along with above import parameters.

2. By Manaually instantiating the objects

Get the Instantiation SCN at the source database:

connect STRMADMIN/[e-mail]
set serveroutput on
DECLARE
    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]
BEGIN
   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_SCN
is used for instantiation. If using the parameter recursive true with SET_SCHEMA_INSTANTIATION_SCN
then you need a dblink on the destination database to the source database
with the same name as the source database.

Refer to the following documentation
Oracle?ˉ???? Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)
B14258-01

page 15-46 - SET_SCHEMA_INSTANTIATION_SCN Procedure

otherwise 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 db
SQL> select count(*) from wj.www;

  COUNT(*)
----------
      1211

SQL> truncate table wj.www;

Table truncated.


@target db
strmadmin@HRDB1(dtydb3)>select count(*) from wj.www;

  COUNT(*)
----------
      1211

strmadmin@HRDB1(dtydb3)>select count(*) from wj.www;

  COUNT(*)
----------
         0

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