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