Oracle Streams 实现-CSDN博客

3、设置STREAM

3.1 创建流队列

connect strmadmin/strmadmin
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

# 主从库tczf,tccz都要创建流队列。上面命令会创建一个队列缺省名：streams_queue，队列表缺省是：STREAMS_QUEUE_TABLE，队列存储的object类型是anaydata。

[验证]

select owner,queue_table,name from dba_queues where wner='STRMADMIN';

OWNER           QUEUE_TABLE                     NAME
----------------   ----------------------------------   -------------
STRMADMIN    STREAMS_QUEUE_TABLE    STREAMS_QUEUE
STRMADMIN    STREAMS_QUEUE_TABLE    AQ$_STREAMS_QUEUE_TABLE_E

select owner,queue_table,object_type from dba_queue_tables where wner='STRMADMIN';

OWNER            QUEUE_TABLE                        OBJECT_TYPE
-----------------   ---------------------------------      ----------
STRMADMIN    STREAMS_QUEUE_TABLE       SYS.ANYDATA

3.2 在源库上创建创建传播和创建捕获进程

3.2.1 传播进程

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name                    => 'tccs',
streams_name                   => 'tczf_to_tccz',
source_queue_name         => 'strmadmin.streams_queue',
destination_queue_name => 'strmadmin.streams_queue@tccz.com',
include_dml                        => true,
include_ddl                       => true,
source_database              => 'tczf.com',
inclusion_rule                    => true,
queue_to_queue              => true);
END;
/

[验证]

select PROPAGATION_NAME,SOURCE_QUEUE_NAME,DESTINATION_QUEUE_NAME,DESTINATION_DBLINK,STATUS from dba_propagation;

PROPAGATION_NAME SOURCE_QUEUE_NAME DESTINATION_QUEUE_NAME DESTINATION_DBL STATUS
-------------------------------- ------------------------------ -------------------

TCZF_TO_TCCZ STREAMS_QUEUE STREAMS_QUEUE TCCZ.COM ENABLED

3.2.2 捕获进程

connect strmadmin/strmadmin

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name     => 'tccs',
streams_type      => 'capture',
streams_name     => 'capture_tczf',
queue_name       => 'strmadmin.streams_queue',
include_dml         => true,
include_ddl         => true,
inclusion_rule     => true);
END;
/

[验证]

SQL> select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture;

CAPTURE_NAME            QUEUE_NAME            START_SCN   STATUS   CAPTURE_TY
----------------------------   -------------------------- --------------- ------------
CAPTURE_TCZF     STREAMS_QUEUE      504733         DISABLED   LOCAL

SQL> select * from ALL_CAPTURE_PREPARED_SCHEMAS;

SCHEMA_NAME   TIMESTAMP SUPPLEME SUPPLEME SUPPLEME SUPPLEME
-------------------- ---------------    -------------- --------------
TCCS                   12-JUN-08      IMPLICIT       IMPLICIT     IMPLICIT     NO

3.3 实例化复制数据库

#在tczf上，执行如下Shell语句。如果从库的tccs用户不存在，建立一个tccs的空用户。

exp userid=tccs/tccs@tczf file='d:\tccs.dmp' object_consistent=y rows=y

imp userid=system/tccz@tccz file='d:\tccs.dmp' ignore=y commit=y log=''d:\tccs.log' streams_instantiation=y fromuser=tccs touser=tccs

#或者用其它的方法，如链路数据泵impdp。但存在long类型不能传输的问题。操作如下：

在从库tccz上操作导入数据

impdp system/system network_link=tczf.com schemas=tccs

#在主库tczf上设置从库tccz上tccs schema（用户）的Instantiation SCN

sqlplus strmadmin/strmadmin@tczf

DECLARE
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@tccz.com(
source_schema_name    => 'tccs',
source_database_name => 'tczf.com',
instantiation_scn     => iscn,
recursive             => true);
END;
/

3.4 在目标库上创建应用进程

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name      => 'tccs',
streams_type    => 'apply',
streams_name    => 'apply_tccz',
queue_name      => 'strmadmin.streams_queue',
include_dml     => true,
include_ddl     => true,
source_database => 'tczf. com',
inclusion_rule => true);
END;
/

[验证]

可以通过下面几个视图查看状态
dba_apply
v$streams_apply_reader
v$streams_apply_coordinator
v$streams_apply_server

select apply_name,queue_name,status from dba_apply;

APPLY_NAME          QUEUE_NAME            STATUS
---------------------      ------------------------   --------
APPLY_TCCZ    STREAMS_QUEUE       DISABLED

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10804699/viewspace-563093/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10804699/viewspace-563093/