oracle双向实时同步,Oracle数据复制：单向同步和双向同步

今天有朋友问及，Oracle9i和Oracle10g之间能够构建高级复制环境。

基于实现原理，我们知道一定是可以的，但是还是动手测试了一下。

测试的简单过程如下(略去了一些基本步骤).

首先测试两个数据库的连通性

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Oct 31 10:36:31

2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights

reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit

Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

SQL> show parameter glob

NAME TYPE VALUE

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

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

global_context_pool_size string

global_names boolean TRUE

SQL> select * from global_name;

GLOBAL_NAME

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

HSBILL.HURRAY.COM.CN

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 -

64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

bash-2.03$ tnsping mars

TNS Ping Utility for Solaris: Version 9.2.0.4.0 - Production on

31-OCT-2006 10:39:41

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:

/opt/oracle/product/9.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =

(PROTOCOL = TCP)(HOST = 172.16.31.110)(PORT = 1521))) (CONNECT_DATA

= (SERVER = DEDICATED) (SERVICE_NAME = mars)))

OK (10 msec)

两个数据库分别创建db link:

9i的数据库:

bash-2.03$ sqlplus repadmin/repadmin

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Oct 31 10:57:49

2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights

reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit

Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

SQL> create public database link "MARS.HURRAY.COM.CN" connect

to repadmin identified by repadmin using 'MARS';

Database link created.

SQL> select * from dual@mars;

D

-

X

10g的数据库:

bash-2.03$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 31 10:15:20

2006Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit

Production

With the Partitioning, OLAP and Data Mining options

SQL> select username from dba_users where

username='REPADMIN';

USERNAME

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

REPADMIN

SQL> connect repadmin/repadmin

Connected.

SQL> select * from tab;

no rows selected

SQL> select * from global_name;

GLOBAL_NAME

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

MARS.HURRAY.COM.CN

SQL> create public database link "HSBILL.HURRAY.COM.CN"

connect to repadmin identified by repadmin using 'HSBILL';

Database link created.

SQL> select * from dual@hsbill;

D

-

X

两个数据库分别创建测试表：

SQL> connect eygle/eygle

Connected.

SQL> create table eygle as select * from v$session;

Table created.

SQL> alter table eygle add (constraint pk_eygle primary key

(SADDR));

Table altered.

进行复制创建:

SQL> connect repadmin/repadmin

Connected.

SQL> execute

dbms_repcat.create_master_repgroup('rep_910');

PL/SQL procedure successfully completed.

SQL> execute

dbms_repcat.create_master_repobject(sname=>'eygle',oname=>'eygle',

type=>'table',use_existing_object=>true,gname=>'rep_910',copy_rows=>false);

PL/SQL procedure successfully completed.

SQL> execute

dbms_repcat.generate_replication_support('eygle','eygle','table');

PL/SQL procedure successfully completed.

SQL> execute

dbms_repcat.add_master_database(gname=>'rep_910',master=>'MARS.HURRAY.COM.CN',use_existing_objects=>true,

copy_rows=>false, propagation_mode => 'synchronous');

PL/SQL procedure successfully completed.

SQL> execute

dbms_repcat.resume_master_activity('rep_910',true);

PL/SQL procedure successfully completed.

基本测试:

SQL> select count(*) from eygle.eygle;

COUNT(*)

----------

20

SQL> select count(*) from eygle.eygle@mars;

COUNT(*)

----------

20

SQL> delete from eygle.eygle where rownum <11;

10 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from eygle.eygle;

COUNT(*)

----------

10

SQL> select count(*) from eygle.eygle@mars;

COUNT(*)

----------

10

SQL> select * from v$version@mars;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -

64bi

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Solaris: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL> select * from v$version;

BANNER

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

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit

Production

PL/SQL Release 9.2.0.4.0 - Production

CORE 9.2.0.3.0 Production

TNS for Solaris: Version 9.2.0.4.0 - Production

NLSRTL Version 9.2.0.4.0 - Production

SQL>

本例未作全面测试，仅用于说明Oracle9iR2和Oracle10gR2之间的高级复制功能上可行。