流复制实验之：Streams_Single_to_single_schames

环境介绍：

这个例子是如何配置单实例到单实例的单向复制.

环境如下表：

	Source (single instance)	Target (single instance)
IP	172.28.7.244	172.28.7.11
OS	Oracle 10.2.0.1 for AIX5.3	Oracle 10.2.0.1 for windows XP
DB_NAME	orcl	orcl
Host name	Inode2	Ch-f
global_name	orcl.test.lcz.com.cn	testdb.test.lcz.com.cn
service_names	orcl.test.lcz.com.cn	testdb.test.lcz.com.cn

 

配置步骤：

(1).配置tnsnames.ora

初始single\rac(orcl1,orcl2)两个数据库的tnsnames.ora内容相同。

 

orcl.test.lcz.com.cn =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.28.7.244)(PORT = 1522))

    (LOAD_BALANCE = yes)

    (CONNECT_DATA =

      (SERVER = DEDICATED)

       (sid = orcl)

    )

  )

 

testdb.test.lcz.com.cn =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.28.7.11)(PORT = 1521))

    (LOAD_BALANCE = YES)

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (sid = orcl)

    )

  )

 

(2).修改global_name.

修改源库的globle_name如下

 

SQL> select * from global_name;

 

GLOBAL_NAME

--------------------------------------------------------------------------------

INFRADB.TEST.LCZ.COM.CN

 

SQL> alter database rename global_name to orcl.test.lcz.com.cn;

 

Database altered

 

SQL> select * from global_name;

 

GLOBAL_NAME

--------------------------------------------------------------------------------

ORCL.TEST.LCZ.COM.CN

 

修改目标库的globle_name如下:

SQL> select * from global_name;

 

GLOBAL_NAME

--------------------------------------------------------------------------------

ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM

 

SQL> alter database rename global_name to testdb.test.lcz.com.cn;

 

Database altered

 

SQL> select * from global_name;

 

GLOBAL_NAME

--------------------------------------------------------------------------------

TESTDB.TEST.LCZ.COM.CN

 

(3).修改其它参数

修改源库部分参数：

alter system set global_names=true;

alter system set aq_tm_processes=2;

alter system set aq_tm_processes=10;

alter system set logmnr_max_persistent_sessions=1 scope=spfile;

alter system set open_links=4 scope=spfile sid='*';

alter system set db_domain='test.lcz.com.cn' scope=spfile;

alter system set service_names='orcl.test.lcz.com.cn';

 

 

修改目标库部分参数：

alter system set global_names=true sid='*';

alter system set aq_tm_processes=2 sid='*';

alter system set aq_tm_processes=10 sid='*';

alter system set logmnr_max_persistent_sessions=1 scope=spfile sid='*';

alter system set open_links=4 scope=spfile sid='*';

alter system set db_domain='test.lcz.com.cn' scope=spfile sid='*';

alter system set service_names='testdb.test.lcz.com.cn' sid='*';

 

注：有些参数需要重启数据库生效

 

(4).在源数据库创建到目标数据库的连接

 

SQL> Create User strmdba Identified By oracle;

 

User created

 

SQL> Grant Connect,resource,Dba To strmdba;

 

Grant succeeded

 

SQL> conn strmdba/oracle@orcl.test.lcz.com.cn

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as strmdba

 

SQL> create database link testdb.test.lcz.com.cn connect to strmdba identified by oracle using 'testdb.test.lcz.com.cn';

 

Database link created

注意：

dblink名字使用的global_name,不是service_name

 

(5).在源数据库建source队列

 

SQL> conn strmdba/oracle@orcl.test.lcz.com.cn

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

Connected as strmdba

 

Begin

  dbms_streams_adm.set_up_queue(

   queue_table => 'source_queue_table',

   queue_name => 'source_queue');

End;

/

 

(6).在源数据库上创建capture进程

 

Begin

  dbms_streams_adm.add_schema_rules(

    schema_name => 'bjcnsdba',

    streams_type => 'capture',

    streams_name => 'source_capture_stream',

    queue_name => 'source_queue',

    include_dml => True,

    include_ddl => True,

    include_tagged_lcr => False,

    source_database => Null,

    inclusion_rule => True);

End;

/

 

(7).在源数据库上创建传播进程

 

Begin

  dbms_streams_adm.add_schema_propagation_rules(

    schema_name => 'bjcnsdba',

    streams_name => 'propagation_bjcnsdba',

    source_queue_name => 'strmdba.source_queue',

    destination_queue_name => 'strmdba.target_queue@testdb.test.lcz.com.cn',

    include_dml => True,

    include_ddl => True,

    include_tagged_lcr => False,

    source_database => 'orcl.test.lcz.com.cn',

    inclusion_rule => True);

End;

/

 

(8).在目标库的orcl上创建接收队列。

SQL> Create User strmdba Identified By oracle;

 

User created

 

SQL> Grant Connect,resource,Dba To strmdba;

 

Grant succeeded

 

SQL> conn strmdba/oracle@testdb.test.lcz.com.cn

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as strmdba

 

Begin

  dbms_streams_adm.set_up_queue(

   queue_table => 'target_queue_table',

   queue_name => 'target_queue');

End;

/

 

Select q.Owner, q.Name, t.Queue_Table, t.Owner_Instance

         From Dba_Queues q, Dba_Queue_Tables t

 Where t.Object_Type = 'SYS.ANYDATA'

          And q.Queue_Table = t.Queue_Table

          And q.Owner = t.Owner

 Order By q.Owner;

 

OWNER      NAME                           QUEUE_TABLE             OWNER_INSTANCE

---------- ------------------------------ ----------------------- --------------

IX         STREAMS_QUEUE                  STREAMS_QUEUE_TABLE                  1

IX         AQ$_STREAMS_QUEUE_TABLE_E      STREAMS_QUEUE_TABLE                  1

STRMDBA    TARGET_QUEUE                 TARGET_QUEUE_TABLE                   1

STRMDBA  AQ$_TARGET_QUEUE_TABLE_E       TARGET_QUEUE_TABLE                   1

SYS        SCHEDULER$_JOBQ                SCHEDULER$_JOBQTAB                   1

SYS        AQ$_SCHEDULER$_JOBQTAB_E       SCHEDULER$_JOBQTAB                   1

 

(9).在目标库上建apply进程。

 

SQL> conn strmdba/oracle@testdb.test.lcz.com.cn

 

Begin

  dbms_streams_adm.add_schema_rules(

    schema_name => 'bjcnsdba',

    streams_type => 'apply',

    streams_name => 'target_apply_stream',

    queue_name => 'target_queue',

    include_dml => True,

    include_ddl => True,

    include_tagged_lcr => False,

    source_database => 'orcl.test.lcz.com.cn',

    inclusion_rule => True);

End;

/

 

(10).在目标库上启动apply进程

Begin

  dbms_apply_adm.start_apply(apply_name => 'target_apply_stream');

End;

/

 

(11).从源库上导出数据

 

exp strmdba/oracle@orcl_single owner=bjcnsdba object_consistent=y file=bjcnsdba.dmp grants=y rows=y indexes=y statistics=none

 

(12).在目标库导入数据

 

imp system/oracle fromuser=bjcnsdba touser=bjcnsdba file=d:\bjcnsdba.dmp constraints=y ignore=y grants=y streams_instantiation=y

 

(13).在源库上启动capture:

Begin

  dbms_capture_adm.start_capture(capture_name => 'source_capture_stream');

End;

/

 

(14).验证复制

 

在源上的bjcnsdba用户下执行DDL和DML操作，在目标库上检查是否同步。

 

Create Table test3 (Id Int,Name varchar(20));

Insert Into test3

Select 1,'aa' From dual;

Commit;

 

文章参考：《大话oracle RAC》

 

遇到的问题：

 

1.在源数据库上创建capture进程时，报下面的错

 

Begin

  dbms_streams_adm.add_schema_rules(

    schema_name => 'bjcnsdba',

    streams_type => 'capture',

    streams_name => 'source_capture_stream',

    queue_name => 'source_queue',

    include_dml => True,

    include_ddl => True,

    include_tagged_lcr => False,

    source_database => Null,

    inclusion_rule => True);

End;

/

 

ORA-26665: STREAMS process SOURCE_CAPTURE_STREAM already exists

ORA-06512: at "SYS.DBMS_STREAMS_ADM_UTL_INVOK", line 289

ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 439

ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 397

ORA-06512: at line 2

 

原因：

capture process SOURCE_CAPTURE_STREAM

已经有匹配的queue_name，不能再用source_queue建source_capture_stream进程

 

SELECT capture_name, queue_owner, queue_name

FROM dba_capture;

 

 

CAPTURE_NAME                   QUEUE_OWNER                    QUEUE_NAME

------------------------------ ------------------------------ ------------------------------

SOURCE_CAPTURE_STREAM          STRMDBA                        SOURCE_QUEUE

 

SELECT owner, name FROM dba_queues

WHERE name = 'SOURCE_QUEUE';

 

OWNER                          NAME

------------------------------ ------------------------------

STRMDBA                        SOURCE_QUEUE

 

解决：

1.在源数据库新建名称为其它的queue_name,(即不为source_queue)，并改变streams_name（即不为source_capture_stream），避免冲突。

如下：

 

--在源数据库创建队列source_queue1：

Begin

  dbms_streams_adm.set_up_queue(

   queue_table => 'source_queue_table1',

   queue_name => 'source_queue1');

End;

/

 

--在源数据库创建capture进程

Begin

  dbms_streams_adm.add_schema_rules(

    schema_name => 'bjcnsdba',

    streams_type => 'capture',

    streams_name => 'source_capture_stream1',

    queue_name => 'source_queue1',

    include_dml => True,

    include_ddl => True,

    include_tagged_lcr => False,

    source_database => Null,

    inclusion_rule => True);

End;

/

 

2.还有就是还可以把STRMDBA用户删除重建，这新这个用户下以前所有创建的进程就都没了。

 

参考：

ORA-26665 When Creating A Capture Process [ID 279666.1]