oracle 11g streams搭建

环境：
主数据库
操作系统：oracle linux 6.3
IP地址：192.168.137.81
数据库：Oracle 11.2.0.3.0
ORACLE_SID：sm1
Global_name：sm1

从数据库
操作系统：oracle linux 6.3
IP地址：192.168.137.82
数据库：Oracle 11.2.0.3.0
ORACLE_SID：sm2

Global_name：sm2

搭建步骤：

1、sm1、sm2 开启归档

SQL> startup mount;SQL> ALTER DATABASE ARCHIVELOG;

2、sm1、sm2 配置数据库相关参数

SQL> alter system set aq_tm_processes=2 scope=both;System altered.SQL> alter system set global_names=true scope=both; System altered.SQL> alter system set job_queue_processes=10 scope=both;System altered.SQL> alter system set parallel_max_servers=20 scope=both; System altered.SQL> alter system set undo_retention=3600 scope=both;System altered.SQL> alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile; System altered.SQL> alter system set streams_pool_size=25M scope=spfile;System altered.SQL> alter system set utl_file_dir='*' scope=spfile; System altered.SQL> alter system set open_links=4 scope=spfile; System altered.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area  822579200 bytesFixed Size                  2232840 bytesVariable Size             662703608 bytesDatabase Buffers          155189248 bytesRedo Buffers                2453504 bytesDatabase mounted.Database opened.

3、sm1、sm2 配置streams用户（注意：sm1、和sm2数据文件的路径要相应的修改）

SQL> create tablespace streams_tbs datafile '/u01/app/oracle/oradata/sm1/streams_tbs01.dbf' size 100m autoextend on next 1m maxsize unlimited;Tablespace created.SQL> create user stradmin identified by oracle default tablespace streams_tbs;User created.SQL> grant connect, resource, dba, aq_administrator_role to stradmin;Grant succeeded.SQL> begindbms_streams_auth.grant_admin_privilege(grantee => 'stradmin',grant_privileges => true);end;PL/SQL procedure successfully completed.

4、配置监听

-- sm1库上的listener.oraLISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.81)(PORT = 1521))(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))    ))SID_LIST_LISTENER = (SID_LIST = (SID_DESC =(GLOBAL_DBNAME = sm1)(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)(SID_NAME=sm1)))ADR_BASE_LISTENER = /u01/app/oracle-- sm1库上的tnsname.orasm1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.81)(PORT = 1521))    (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = sm1)))sm2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.82)(PORT = 1521))    (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = sm2)))-- sm2库上的listener.oraLISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.82)(PORT = 1521))(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))    ))SID_LIST_LISTENER = (SID_LIST = (SID_DESC =(GLOBAL_DBNAME = sm2)(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)(SID_NAME=sm2)))ADR_BASE_LISTENER = /u01/app/oracle-- sm2库上的tnsname.orasm1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.81)(PORT = 1521))    (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = sm1)))sm2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.82)(PORT = 1521))    (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = sm2)))

5、  在主库（sm1）上面启用 supplemental log

SQL> ALTER DATABASE FORCE LOGGING;Database altered.SQL> alter database add supplemental log data; Database altered.SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;SUPPLEME SUP SUP SUP-------- --- --- ---YES      NO  NO  NO

6、创建db link

-- sm1SQL> conn stradmin/oracleConnected.SQL> create database link sm2 connect to stradmin identified by oracle using 'sm2';Database link created.-- sm2SQL> conn stradmin/oracleConnected.SQL> create database link sm1 connect to stradmin identified by oracle using 'sm1';Database link created.

7、创建队列流

-- 创建主库队列流（sm1）SQL> conn stradmin/oracleConnected.SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'sm1_queue_table', queue_name => 'sm1_queue')PL/SQL procedure successfully completed.-- 创建从库队列流（sm2）SQL> conn stradmin/oracleConnected.SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'sm2_queue_table', queue_name => 'sm2_queue')PL/SQL procedure successfully completed.

8、创建示例用户用于测试

-- 主库上操作--在sm1上创建表空间SQL> CREATE TABLESPACE chenhaoDATAFILE '/u01/app/oracle/oradata/sm1/chenhao01.dbf' SIZE 300M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITEDLOGGINGEXTENT MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT AUTO;Tablespace created.--在sm1上创建用户SQL> create user chenhao identified by oracledefault tablespace chenhaotemporary tablespace tempts1profile DEFAULT;User created.SQL> grant connect, resource to chenhao;Grant succeeded.-- 从库上操作--在sm2上创建表空间SQL> CREATE TABLESPACE chenhaoDATAFILE '/u01/app/oracle/oradata/sm2/chenhao01.dbf' SIZE 300M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITEDLOGGINGEXTENT MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT AUTO;Tablespace created.--在sm2上创建用户SQL> create user chenhao identified by oracledefault tablespace chenhaotemporary tablespace tempts1profile DEFAULT;User created.SQL> grant connect, resource to chenhao;Grant succeeded.

9、在主库上（sm1）创建捕获进程

QL> connect stradmin/oracle Connected.begindbms_streams_adm.add_schema_rules(schema_name => 'chenhao',streams_type => 'capture',streams_name => 'capture_sm1',queue_name => 'stradmin.sm1_queue',include_dml => true,include_ddl => true, include_tagged_lcr => false,source_database => null,inclusion_rule => true);end;/PL/SQL procedure successfully completed.

10、实例化复制数据库

--导出主库（sm1）chenhao schema数据[oracle@sm1 ~]$ exp userid=chenhao/oracle@sm1 file='/home/oracle/chenhao.dmp' object_consistent=y rows=yExport: Release 11.2.0.3.0 - Production on Tue Jul 29 15:51:36 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in US7ASCII character set and AL16UTF16 NCHAR character set. exporting pre-schema procedural objects and actions. exporting foreign function library names for user CHENHAO . exporting PUBLIC type synonyms. exporting private type synonyms. exporting object type definitions for user CHENHAO About to export CHENHAO's objects .... exporting database links. exporting sequence numbers. exporting cluster definitions. about to export CHENHAO's tables via Conventional Path .... exporting synonyms. exporting views. exporting stored procedures. exporting operators. exporting referential integrity constraints. exporting triggers. exporting indextypes. exporting bitmap, functional and extensible indexes. exporting posttables actions. exporting materialized views. exporting snapshot logs. exporting job queues. exporting refresh groups and children. exporting dimensions. exporting post-schema procedural objects and actions. exporting statisticsExport terminated successfully without warnings.-- 导入刚刚导出的数据到从库（sm2）的chenhao schema中[oracle@sm1 ~]$ imp userid=chenhao/oracle@sm2 file='/home/oracle/chenhao.dmp' ignore=y commit=y log='/home/oracle/chenhao.log' streams_instantiation=y fromuser=chenhao touser=chenhaoImport: Release 11.2.0.3.0 - Production on Tue Jul 29 15:53:06 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V11.02.00 via conventional pathimport done in US7ASCII character set and AL16UTF16 NCHAR character setImport terminated successfully without warnings.

11、创建传播进程

--在主库的操作（sm1）SQL> connect stradmin/oracle Connected.SQL> begin dbms_streams_adm.add_schema_propagation_rules( schema_name => 'chenhao', streams_name => 'sm1_to_sm2', source_queue_name => 'stradmin.sm1_queue', destination_queue_name => 'stradmin.sm2_queue@sm2', include_dml => true, include_ddl => true, include_tagged_lcr => false, source_database => 'sm1', inclusion_rule => true); end;/PL/SQL procedure successfully completed.--修改propagation休眠时间为5，表示实时传播LCR(logical change recoder)。SQL> begindbms_aqadm.alter_propagation_schedule(queue_name => 'sm1_queue',destination => 'sm2',destination_queue => 'sm2_queue',latency => 5);end;/PL/SQL procedure successfully completed.

12、创建应用进程 

--从库（sm2）上操作SQL> conn stradmin/oracleConnected.SQL> begin dbms_streams_adm.add_schema_rules( schema_name => 'chenhao', streams_type => 'apply', streams_name => 'apply_sm2', queue_name => 'stradmin.sm2_queue', include_dml => true, include_ddl => true, include_tagged_lcr => false, source_database => 'sm1', inclusion_rule => true); end; /PL/SQL procedure successfully completed.

13、启动STREAM

--以stradmin身份，登录从数据库(sm2)。 SQL> conn stradmin/oracleConnected.--启动Apply进程SQL> exec dbms_apply_adm.start_apply(apply_name => 'apply_sm2')PL/SQL procedure successfully completed.--以stradmin身份，登录主数据库(sm1)。 SQL> conn stradmin/oracleConnected.--启动Capture进程SQL> exec dbms_capture_adm.start_capture(capture_name => 'capture_sm1');PL/SQL procedure successfully completed.

14、测试

--在主库上操作(sm1)SQL> conn chenhao/oracleConnected.SQL> create table t1(id number, name varchar2(10));Table created.SQL> insert into t1 values(1, 'AAA');1 row created.SQL> insert into t1 values(2, 'BBB');1 row created.SQL> insert into t1 values(3, 'CCC');1 row created.SQL> commit;Commit complete.--从库上操作(sm2)SQL> desc t1; Name     Null?    Type -------- -------- -------------- ID                NUMBER NAME              VARCHAR2(10)SQL> select * from t1;        ID NAME---------- ----------         1 AAA         2 BBB         3 CCC

15、停止stream

--以stradmin, 登陆主库(sm1)SQL> conn stradmin/oracleConnected.SQL> exec dbms_capture_adm.stop_capture(capture_name => 'capture_sm1')PL/SQL procedure successfully completed.--以stradmin, 登陆从库(sm2)SQL> conn stradmin/oracleConnected.SQL> exec dbms_apply_adm.stop_apply(apply_name => 'apply_sm2')PL/SQL procedure successfully completed.

16、清除所有配置（注意在清除钱要先停止stream）

--以stradmin身份，登录主数据库（sm1）。 SQL> conn stradmin/oracleConnected.SQL> exec DBMS_STREAMS_ADM.remove_streams_configuration(); PL/SQL procedure successfully completed.--以stradmin身份，登录从数据库（sm2）。 SQL> conn stradmin/oracleConnected.SQL> exec DBMS_STREAMS_ADM.remove_streams_configuration();PL/SQL procedure successfully completed.

大功告成！~