关于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
etworkadmin下的listener.ora如下：

# listener.ora Network Configuration File: C:oracleproduct10.2.0	g_1
etworkadminlistener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = C:oracleproduct10.2.0	g_1)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (SID_NAME = mssql)

      (ORACLE_HOME = C:oracleproduct10.2.0	g_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	g4msqladmin下的init.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的格式为.

3.    配置服务器Huanged上的$ORACLE_HOME
etworkadmin下的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(

  privile