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 immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.

Total System Global Area  822579200 bytes
Fixed Size                  2232840 bytes
Variable Size             662703608 bytes
Database Buffers          155189248 bytes
Redo Buffers                2453504 bytes
Database 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> begin
	dbms_streams_auth.grant_admin_privilege(
		grantee => 'stradmin',
		grant_privileges => true
	);
end;

PL/SQL procedure successfully completed.

4、配置监听

-- sm1库上的listener.ora
LISTENER = (
	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.ora
sm1 = (
	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.ora
LISTENER = (
	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.ora
sm1 = (
	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

-- sm1
SQL> conn stradmin/oracle
Connected.

SQL> create database link sm2 connect to stradmin identified by oracle using 'sm2';

Database link created.

-- sm2
SQL> conn stradmin/oracle
Connected.

SQL> create database link sm1 connect to stradmin identified by oracle using 'sm1';

Database link created.

7、创建队列流

-- 创建主库队列流（sm1）
SQL> conn stradmin/oracle
Connected.

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/oracle
Connected.

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 chenhao
DATAFILE '/u01/app/oracle/oradata/sm1/chenhao01.dbf' SIZE 300M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.
--在sm1上创建用户
SQL> create user chenhao identified by oracle
default tablespace chenhao
temporary tablespace tempts1
profile DEFAULT;

User created.

SQL> grant connect, resource to chenhao;

Grant succeeded.

-- 从库上操作
--在sm2上创建表空间
SQL> CREATE TABLESPACE chenhao
DATAFILE '/u01/app/oracle/oradata/sm2/chenhao01.dbf' SIZE 300M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.
--在sm2上创建用户
SQL> create user chenhao identified by oracle
default tablespace chenhao
temporary tablespace tempts1
profile DEFAULT;

User created.

SQL> grant connect, resource to chenhao;

Grant succeeded.

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

QL> connect stradmin/oracle 
Connected.

begin
	dbms_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=y

Export: Release 11.2.0.3.0 - Production on Tue Jul 29 15:51:36 2014

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export 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 statistics
Export 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=chenhao

Import: Release 11.2.0.3.0 - Production on Tue Jul 29 15:53:06 2014

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
Import 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> begin
	dbms_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/oracle
Connected.

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/oracle
Connected.

--启动Apply进程
SQL> exec dbms_apply_adm.start_apply(apply_name => 'apply_sm2')

PL/SQL procedure successfully completed.

--以stradmin身份，登录主数据库(sm1)。 
SQL> conn stradmin/oracle
Connected.

--启动Capture进程
SQL> exec dbms_capture_adm.start_capture(capture_name => 'capture_sm1');

PL/SQL procedure successfully completed.

14、测试

--在主库上操作(sm1)
SQL> conn chenhao/oracle
Connected.
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/oracle
Connected.

SQL> exec dbms_capture_adm.stop_capture(capture_name => 'capture_sm1')

PL/SQL procedure successfully completed.

--以stradmin, 登陆从库(sm2)
SQL> conn stradmin/oracle
Connected.

SQL> exec dbms_apply_adm.stop_apply(apply_name => 'apply_sm2')

PL/SQL procedure successfully completed.

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

--以stradmin身份，登录主数据库（sm1）。 
SQL> conn stradmin/oracle
Connected.

SQL> exec DBMS_STREAMS_ADM.remove_streams_configuration(); 

PL/SQL procedure successfully completed.

--以stradmin身份，登录从数据库（sm2）。 
SQL> conn stradmin/oracle
Connected.

SQL> exec DBMS_STREAMS_ADM.remove_streams_configuration();

PL/SQL procedure successfully completed.

大功告成！~