通过streams replication实现Oracle和SQL Server之间异构数据同步(1)

日期： 2006-11-07 14:50:57

分享到

关于streams replication的原理本文就不做介绍了，相关文章可以在OTN和metalink上找到。本文主要介绍通过streams replication和transparent gateway实现

Oracle向SQL Server的数据同步的配置过程。主要参考了metalink上的文章，并在期间开了4个SR解决发现的问题（并发现新bug一个）。

一、测试环境：

Oracle DB Server: 9205, SID: ORA92, Host: ORATEST(192.168.111.22), schema: orarep

SQL Server: 2000, DB: pubs, Host: SQLTEST,(192.168.111.11) schema: sqlrep

Oracle 透明网关：10gR2

二、透明网关的配置

将Oracle transparent gateway 10gR2 for MS SQL Server安装装在SQL Server服务器上。

1.    配置服务器Yangcl上的$ORACLE_HOME\network\admin下的listener.ora如下：

# listener.ora Network Configuration File: C:\oracle\product\10.2.0\tg_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = C:\oracle\product\10.2.0\tg_1)

      (PROGRAM = extproc)

    (SID_DESC =

      (SID_NAME = mssql)

      (ORACLE_HOME = C:\oracle\product\10.2.0\tg_1)

      (PROGRAM = tg4msql)

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

其中黑体部分尤为重要。SID_NAME自己取一个，但要和后面保持一致！

2.    配置服务器Yangcl上的$ORACLE_HOME\tg4msql\admin下的init<SID_NAME>.ora如下：

HS_FDS_CONNECT_INFO=SQLTEST.pubs

HS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

其中，HS_FDS_CONNECT_INFO的格式为<MS SQL Server Name>.<DB Name>

3.    配置服务器Huanged上的$ORACLE_HOME\network\admin下的tnsnames.ora，添加如下条目：

MSSQL =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    (CONNECT_DATA =

      (SID = mssql)

    (HS = OK)

Host为MS SQL Server的host名字，SID和上面保持一致。

4.    在服务器Yangcl上启动listener：

Lsnrctl start

5.    在Oracle上创建到SQL Server的DB Link

conn orarep/orarep

create database link mssql connect to sqlrep identified by sqlrep using ‘mssql';

6.    测试连接

Select * from employee@mssql;

有结果则表示网关连接正确，否则根据错误提示调整设置。

三、Steams Replication的配置

1.    确保Oracle运行在归档模式下

Conn / as sysdba

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     192

Next log sequence to archive   194

Current log sequence           194

--否则修改为归档模式

1)SHUTDOWN IMMEDIATE;



2)STARTUP MOUNT;



3)ALTER DATABASE ARCHIVELOG;



4)ALTER DATABASE OPEN;

2.    在Oracle上创建测试表

conn orarep/orarep

create table rep_test1 (a number primary key, b char(20));

create table rep_test2 (a number(9,2) primary key, b varchar2(20), c date);

为表增加补充日志组：

ALTER TABLE rep_test1 ADD SUPPLEMENTAL LOG GROUP lg_test1_pk (a) ALWAYS;

ALTER TABLE rep_test2 ADD SUPPLEMENTAL LOG GROUP lg_test2_pk (a) ALWAYS;

3.    在SQL Server上创建测试表

注意：SQL Server对应的数据库上创建与Oracle上用户名相同的用户，并且一定要大写，与login id对应。创建表和字段也一定要大写！

Use pubs

create table REP_TEST1 (A int primary key not null, B char(20))

create table REP_TEST2 (A DECIMAL(9,2) primary key not null, B varchar(20), C datetime)

go

创建完毕后，要检查表和owner：

在oracle上执行

SQL> select owner, table_name, column_name from all_tab_columns@mssql where owner=’ORAREP’;

OWNER           TABLE_NAME         COLUMN_NAME

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

ORAREP          ORA_TEST1          A

ORAREP          ORA_TEST1          B

ORAREP          ORA_TEST2          A

ORAREP          ORA_TEST2          B

ORAREP          ORA_TEST2          C

结果必须全部为大写！

附：SQL Server与Oracle的数据类型对应表

Microsoft Sql Server	Oracle	Comment
BINARY	RAW	-
BIT	NUMBER(3)	-
CHAR	CHAR	-
DATETIME	DATE	Fractional parts of a second are truncated
DECIMAL	NUMBER(p[,s])	-
FLOAT	FLOAT(49)	-
IMAGE	LONG RAW	-
INTEGER	NUMBER(10)	NUMBER range is -2,147,483,647 to 2,147,483,647
MONEY	NUMBER(19,4)	-
NCHAR	CHAR	-
NTEXT	LONG	-
NVARCHAR	VARCHAR2	-
NUMERIC	NUMBER(p[,s])	-
REAL	FLOAT(23)	-
SMALL DATETIME	DATE	The value for seconds is returned as 0
SMALL MONEY	NUMBER(10,4)	-
SMALLINT	NUMBER(5)	NUMBER range is -32,767 to 32,767
TEXT	LONG	-
TIMESTAMP	RAW	-
TINYINT	NUMBER(3)	-
VARBINARY	RAW	-
VARCHAR	VARCHAR2	-

--也可以直接在Oracle上通过以下方式创建：

declare

     nr    binary_integer;

begin

     nr := dbms_hs_passthrough.execute_immediate@mssql

        ('create table REP_TEST1 (A int primary key not null, B char(20))');

     nr := dbms_hs_passthrough.execute_immediate@mssql

        (' create table REP_TEST2 (A DECIMAL(9,2) primary key not null, B varchar(20), c datatime)');

end;

4.    在Oracle上创建streams管理帐号

Connect / as sysdba

grant connect, resource, dba, select_catalog_role

to STRMADMIN identified by STRMADMIN;

--赋予相关包的权限：

grant execute on DBMS_APPLY_ADM       to STRMADMIN;

grant execute on DBMS_AQADM           to STRMADMIN;

grant execute on DBMS_CAPTURE_ADM     to STRMADMIN;

grant execute on DBMS_FLASHBACK       to STRMADMIN;

grant execute on DBMS_PROPAGATION_ADM to STRMADMIN;

grant execute on DBMS_STREAMS_ADM     to STRMADMIN;

--赋予相关系统权限:

begin

  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(

  privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,

  grantee      => 'strmadmin',

  grant_option => FALSE);

end;

begin

  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(

  privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ,

  grantee      => 'strmadmin',

  grant_option => FALSE);

end;

--创建到SQL Server的DB Link：

Connect STRMADMIN/STRMADMIN

create database link mssql

connect to sqlrep identified by sqlrep

using 'mssql';

--测试连接是否可用：

select * from rep_test1@mssql;

--确定SQL Server的表上的主键是否可用：

select owner, constraint_name, constraint_type from all_constraints@mssql where table_name in ('REP_TEST1','REP_TEST2');

5.    创建streams队列、捕捉和表规则

Connect STRMADMIN/STRMADMIN

--创建streams队列（队列的默认名字为STREAMS_QUEUE）：

/* Create queue: following creates and starts the streams queue.  */

/* Default queue_name is ‘STREAMS_QUEUE’ */

execute DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_name => 'queue_for_reptest');

--在Oracle上创建捕捉和表规则

Connect STRMADMIN/STRMADMIN

begin

   DBMS_STREAMS_ADM.ADD_TABLE_RULES

   Table_name=>'ORAREP.REP_TEST1',

   streams_type=>'capture',

   streams_name=>'REPTEST_CAPTURE',

   queue_name=> 'strmadmin.queue_for_reptest'

);

end;

begin

   DBMS_STREAMS_ADM.ADD_TABLE_RULES

   Table_name=>'ORAREP.REP_TEST2',

   streams_type=>'capture',

   streams_name=>'REPTEST_CAPTURE',

   queue_name=> 'strmadmin.queue_for_reptest'

);

end;