Oracle Stream 同步数据

1 引言

    Oracle官方网：

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10727/strmover.htm

    Oracle Stream功能是为提高数据库的高可用性而设计的，在Oracle 9i及之前的版本这个功能被称为Advance Replication。Oracle Stream利用高级队列技术，通过解析归档日志，将归档日志解析成DDL及DML语句，从而实现数据库之间的同步。这种技术可以将整个数据库、数据库中的对象复制到另一数据库中，通过使用Stream的技术，对归档日志的挖掘，可以在对主系统没有任何压力的情况下，实现对数据库对象级甚至整个数据库的同步。
    解析归档日志这种技术现在应用的比较广泛，Quest公司的shareplex软件及DSG公司的realsync都是这样的产品，一些公司利用这样的产品做应用级的容灾。但shareplex或是realsync都是十分昂贵的，因此你可以尝试用Stream这个Oracle提供的不用额外花钱的功能。 Oracle Stream对生产库的影响是非常小的，从库可以是与主库不同的操作系统平台，你可以利用Oracle Stream复制几个从库，从库可用于查询、报表、容灾等不同的功能。本文不谈技术细节，只是以手把手的方式一步一步的带你把Stream的环境搭建起来，细节内容可以查联机文档。
2 概述
主数据库：
操作系统：Solaris 9
IP地址：192.168.10.35
数据库：Oracle 10.2.0.2
ORACLE_SID：prod
Global_name：prod
从数据库：
操作系统：AIX 5.2
IP地址：192.168.10.43
数据库：Oracle 10.2.0.3
ORACLE_SID：h10g
Global_name：h10g

3 环境准备
3.1 设定初始化参数
使用pfile的修改init.ora文件，使用spfile的通过alter system命令修改spile文件。主、从数据库分别执行如下的语句：

Sqlplus ‘/ as sysdba’ 
alter system set aq_tm_processes=2 scope=both; 
alter system set global_names=true scope=both; 
alter system set job_queue_processes=10 scope=both; 
alter system set parallel_max_servers=20 scope=both; 
alter system set undo_retention=3600 scope=both; 
alter system set nls_date_format=‘YYYY-MM-DD HH24:MI:SS‘ scope=spfile; 
alter system set streams_pool_size=25M scope=spfile; 
alter system set utl_file_dir=‘*‘ scope=spfile; 
alter system set open_links=4 scope=spfile; 

sqlplus ‘/ as sysdba’ 
alter system set log_archive_dest_1=’location=/yang/arch’ scope=spfile; 
alter system set log_archive_start=TRUE scope=spfile; 
alter system set log_archive_format=’ arch%t_%s_%r.arc’ scope=spfile; 
shutdown immediate; 
startup mount; 
alter database archivelog; 
alter database open; 

SQL> archive log list 
Database log mode Archive Mode 
Automatic archival Enabled 
Archive destination /yang/arch 
Oldest online log sequence 534 
Next log sequence to archive 536 
Current log sequence 536 

create tablespace tbs_stream datafile ‘/yang/oradata/prod/tbs_stream01.dbf‘ 
size 100m autoextend on maxsize unlimited segment space management auto; 

execute dbms_logmnr_d.set_tablespace(‘tbs_stream‘); 

create user strmadmin identified by strmadmin 
default tablespace tbs_stream temporary tablespace temp; 

grant connect,resource,dba,aq_administrator_role to strmadmin; 
begin 
dbms_streams_auth.grant_admin_privilege( 
grantee => ‘strmadmin‘, 
grant_privileges => true); 
end; 
/ 

create tablespace tbs_stream datafile ‘+VGDATA/h10g/datafile/tbs_stream01.dbf‘ 
size 100m autoextend on maxsize unlimited segment space management auto; 

execute dbms_logmnr_d.set_tablespace(‘tbs_stream‘); 

create user strmadmin identified by strmadmin 
default tablespace tbs_stream temporary tablespace temp; 

grant connect,resource,dba,aq_administrator_role to strmadmin; 
begin 
dbms_streams_auth.grant_admin_privilege( 
grantee => ‘strmadmin‘, 
grant_privileges => true); 
end; 
/ 

H10G = 
(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.43)(PORT = 1521)) 
) 
(CONNECT_DATA = 
(SID = h10g) 
(SERVER = DEDICATED) 
) 
) 

PROD = 
(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.35)(PORT = 1521)) 
) 
(CONNECT_DATA = 
(SID = prod) 
(SERVER = DEDICATED) 
) 
) 

alter database add supplemental log data; 

alter table add supplement log group log_group_name(table_column_name) always; 

connect strmadmin/strmadmin 
begin 
dbms_streams_adm.set_up_queue( 
queue_table => ‘prod_queue_table‘, 
queue_name => ‘prod_queue‘); 
end; 
/ 

connect strmadmin/strmadmin 
begin 
dbms_streams_adm.set_up_queue( 
queue_table => ‘h10g_queue_table‘, 
queue_name => ‘h10g_queue‘); 
end; 
/ 

connect strmadmin/strmadmin 
begin 
dbms_streams_adm.add_schema_rules( 
schema_name => ‘hr‘, 
streams_type => ‘capture‘, 
streams_name => ‘capture_prod‘, 
queue_name => ‘strmadmin.prod_queue‘, 
include_dml => true, 
include_ddl => true, 
include_tagged_lcr => false, 
source_database => null, 
inclusion_rule => true); 
end; 
/ 

exp userid=hr/hr@prod file=‘/tmp/hr.dmp‘ object_consistent=y rows=y 
imp userid=system/manager@h10g file=‘/tmp/hr.dmp‘ ignore=y commit=y log=‘/tmp/hr.log‘ streams_instantiation=y fromuser=hr touser=hr 

connect strmadmin/strmadmin 
begin 
dbms_streams_adm.add_schema_propagation_rules( 
schema_name => ‘hr‘, 
streams_name => ‘prod_to_h10g‘, 
source_queue_name => ‘strmadmin.prod_queue‘, 
destination_queue_namm => ‘strmadmin.h10g_queue@h10g‘, 
include_dml => true, 
include_ddl => true, 
include_tagged_lcr => false, 
source_database => ‘prod‘, 
inclusion_rule => true); 
end; 
/ 

begin 
dbms_aqadm.alter_propagation_schedule( 
queue_name => ‘prod_queue‘, 
destination => ‘h10g‘, 
latency => 0); 
end; 
/ 

connect strmadmin/strmadmin 
begin 
dbms_streams_adm.add_schema_rules( 
schema_name => ‘hr‘, 
streams_type => ‘apply‘, 
streams_name => ‘apply_h10g‘, 
queue_name => ‘strmadmin.h10g_queue‘, 
include_dml => true, 
include_ddl => true, 
include_tagged_lcr => false, 
source_database => ‘prod‘, 
inclusion_rule => true); 
end; 
/ 

4 测试场景
    本文档建立了针对hr用户的Stream 复制环境，如果没有特别声明，以下测试场景均以hr用户身份执行。
4.1 建一张表测试
主数据库
SQL> CREATE TABLE TTT(id NUMBER PRIMARY KEY,
2 name VARCHAR2(50)
3 )
4 /
Table created.
从数据库
SQL> desc TTT
Name Null? Type
---------- -------- -------------
ID NOT NULL NUMBER
NAME VARCHAR2(50)
4.2 表中插入一行数据
主数据库
SQL> insert into ttt values (1,‘sdfsdfsdfsdf‘);
1 row created.
SQL> commit;
Commit complete.
SQL>
从数据库
SQL> select * from TTT;
ID NAME
---------- --------------------
1 sdfsdfsdfsdf
4.3 变更一下表的结构，添加一列
主数据库
SQL> ALTER TABLE TTT ADD(age NUMBER(2));
Table altered
从数据库
SQL> desc TTT
Name Null? Type
----------- -------- --------------
ID NOT NULL NUMBER
NAME VARCHAR2(50)
AGE NUMBER(2)
4.4 将表换一个表空间
主数据库
SQL> SELECT table_name,tablespace_name FROM user_tables
2 WHERE table_name=‘TTT‘;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TTT USERS
SQL> ALTER TABLE TTT MOVE TABLESPACE tbs_stream;
Table altered
SQL> SELECT table_name,tablespace_name FROM user_tables
WHERE table_name=‘TTT‘;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TTT TBS_STREAM
从数据库
SQL> SELECT table_name,tablespace_name FROM user_tables
WHERE table_name=‘TTT‘;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TTT TBS_STREAM
4.5 表上Name列建一索引
主数据库
SQL> CREATE INDEX ttt_name_idx ON TTT(name);
Index created
从数据库
SQL> SELECT table_name, index_name FROM user_indexes WHERE table_name = ‘TTT‘;
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
TTT TTT_NAME_IDX
TTT SYS_C005721
4.6 Rebuild索引测试
主数据库
SQL> ALTER INDEX ttt_name_idx REBUILD;
Index altered
从数据库
SQL> SELECT table_name, index_name FROM user_indexes WHERE table_name = ‘TTT‘;
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
TTT TTT_NAME_IDX
TTT SYS_C005721
4.7 索引换一个表空间测试
主数据库
SQL> ALTER INDEX ttt_name_idx REBUILD TABLESPACE tbs_stream;
Index altered
从数据库
SQL> SELECT table_name,index_name,tablespace_name FROM user_indexes
WHERE table_name = ‘TTT‘;
TABLE_NAME INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TTT TTT_NAME_IDX TBS_STREAM
TTT SYS_C005721 USERS
4.8 删除索引测试
主数据库
SQL> DROP INDEX ttt_name_idx;
Index dropped
从数据库
SQL> SELECT table_name,index_name,tablespace_name FROM user_indexes
WHERE table_name = ‘TTT’;
TABLE_NAME INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TTT SYS_C005721 USERS
4.9 删除表测试
主数据库
SQL> DROP TABLE ttt;
Table dropped
从数据库
SQL> DESC ttt;
Object ttt does not exist.
4.10 建一张带有LOB类型字段的表测试
主数据库
SQL> CREATE TABLE tttclob(id NUMBER PRIMARY KEY, memo CLOB);
Table created
从数据库
SQL> DESC tttclob;
Name Null? Type
----------- -------- --------------
ID NOT NULL NUMBER
MEMO CLOB
4.11 表中插入一行数据
主数据库
SQL> INSERT INTO tttclob VALUES(1,‘clob_test‘);
1 row inserted
SQL> commit;
Commit complete
从数据库
SQL> SELECT * FROM tttclob;
ID MEMO
---------- --------------------------------------------------------------------------------
1 clob_test
4.12 创建Type测试
主数据库
SQL> CREATE or REPLACE TYPE ttttype;
2 /
Type created
从数据库
SQL> SELECT * FROM user_types WHERE type_name=‘TTTTYPE‘;
TYPE_NAME TYPE_OID TYPECODE ATTRIBUTES METHODS PREDEFINED INCOMPLETE FINAL INSTANTIABLE SUPERTYPE_OWNER SUPERTYPE_NAME LOCAL_ATTRIBUTES LOCAL_METHODS TYPEID
------------------------------ -------------------------------- ------------------------------ ---------- ---------- ---------- ---------- ----- ------------ ------------------------------ ------------------------------ ---------------- ------------- --------------------------------
TTTTYPE 1B36AAF10DA8301DE040A8C0289A77B4 OBJECT 0 0 NO YES YES YES
4.13 删除Type测试
主数据库
SQL> DROP TYPE ttttype;
Type dropped
从数据库
SQL> SELECT * FROM user_types WHERE type_name=‘TTTTYPE‘;
TYPE_NAME TYPE_OID TYPECODE ATTRIBUTES METHODS PREDEFINED INCOMPLETE FINAL INSTANTIABLE SUPERTYPE_OWNER SUPERTYPE_NAME LOCAL_ATTRIBUTES LOCAL_METHODS TYPEID
------------------------------ -------------------------------- -

Oracle Stream 同步数据

原文：http://www.cnblogs.com/Kconnie/p/4687987.html