配备oracle stream(step by step setup database level Streams Replication)-数据库教程-爱易网页

配备oracle stream(step by step setup database level Streams Replication)

日期：2014-05-16　浏览次数：20706 次

配置oracle stream(step by step setup database level Streams Replication)
oracle stream简介
Stream 是Oracle 的消息队列(也叫Oracle Advanced Queue)技术的一种扩展应用。 Oracle 的消息队列是通过发布/订阅的方式来解决事件管理。流复制(Stream replication)只是基于它的一个数据共享技术，也可以被用作一个可灵活定制的高可用性方案。它可以实现两个数据库之间数据库级，schema级，Table级的数据同步，并且这种同步可以是双向的。 Oracle Stream也是通过数据冗余来提高可用性，这一点和Data Guard 类型。
下面是oracle stream 配置的简单过程（Database level）
1，配置数据库为归档模式
2，修改数据库相关的参数，一般需要设置global_names, _job_queue_interval, sga_target, streams_pool_size四个参数

alter system set global_names=true scope=both;
alter system set "_job_queue_interval"=1 scope=spfile;

3，stream数据库用户strmadmin及权限 @both db

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');

4,配置tnsnames.ora
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)
    )
  )

5,开始配置stream
步骤a ,source数据库建立stream队列 ,j建 db link @source

conn strmadmin/STRM#123

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_name => 'STREAMS_QUEUE',
queue_table =>'STREAMS_QUEUE_TABLE',
queue_user => 'STRMADMIN');
END;
/

conn sys/oracle as sysdba
create public database link hrdb using 'hrdb';

conn strmadmin/STRM#123

create database link hrdb connect to strmadmin identified by STRM#123;


@target db hrdb
conn sys/oracle as sysdba
create public database link HRDBPRIM using 'HRDBPRIM';

conn strmadmin/STRM#123

create database link HRDBPRIM connect to strmadmin identified by STRM#123;

b, @target hrdb 建stream队列

/* 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, @source db 建捕获(capture)和传播PROPAGATION 规则

/*Step 3 -Connected to source db, create CAPTURE and PROPAGATION rules */
conn strmadmin/STRM#123@source
BEGIN
DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES(
streams_name => 'STRMADMIN_PROP',
source_queue_name => 'STRMADMIN.STREAMS_QUEUE',
destination_queue_name => 'STRMADMIN.STREAMS_QUEUE@hrdb',
include_dml => true,
include_ddl => true,
source_database => 'HRDBPRIM');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
streams_type => 'CAPTURE',
streams_name => 'STRMADMIN_CAPTURE',
queue_name => 'STRMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'hrdbprim');
END;
/

d, @target HRDB 建apply规则

/*Step 4 - Connected as STRMADMIN at TARGET, create APPLY rules */
conn STRMADMIN/STRMADMIN@TARGET
BEGIN
DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
streams_type => 'APPLY',
streams_name => 'STRMADMIN_APPLY',
queue_name => 'STRMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'hrdbprim');
END;
/

BEGIN
DBMS_APPLY_ADM

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

配备oracle stream(step by step setup database level Streams Replication)

相关资料更多>

推荐阅读更多>