Oracle中使用透明网关链接到Sqlserver ——转自《wangqi0079百度空间》

2012-02-08补充

最近被透明网关弄得很蛋疼，总是抱"用户名"登录失败，最后发现问题在建立数据链接的时候如果我使用“create database link 数据库  connect to "用户名" identified by "密码" using ‘名称’ 这个sql语句时，链接就没问题。如果使用pl/sql中



这个快捷界面的话，仍会报这个错误。

最后发现pl/sql拼写出的sql为“create database link 服务器
  connect to 用户名 identified by 密码
  using '名称”

这里的密码处没有加入“双引号”致使密码变为大写，哎！以后还是学好命令行吧

2012-02-06补充

透明网关连接到多个 sqlserver 上，且端口非默认端口 连接非默认端口的 sqlserver 比较麻烦，HS\_FDS\_CONNECT\_INFO 的设置要借助 sqlserver 别名来解析。（如 果是 11g 的话就比较简单， HS\_FDS\_CONNECT\_INFO=<hostname>:<port>/<server alias>/<database>语法 用 即可） 
　　1、生成 sqlserver 别名 生成 sqlserver 别名的方式有三种： 
　　1）借助 sqlserver 的网络客户端配置。

　　2）设置 sqlserver 别名 由于透明网关中可以读取 HKEY\_LOCAL\_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo 下的值作为 sqlserver 的别 名，所以可以通过更新注册表的方式为 sqlserver 设置别名。 在注册表的 HKEY\_LOCAL\_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client 下新建项 ConnectTo， 然后在新建的 ConnectTo 下新建字符串值：
在数值名称中输入你的 sqlserver 别名，在数值数据中输入：DBMSSOCN,4,1422 其中：DBMSSOCN 是固定字符串，不用修改；4 是 sqlserver 服务器的 IP；1422 为 sqlserver 的端口。最简单的方法是，生成一个.reg 文件，如 hc.reg，包含如下内容：

 Windows Registry Editor Version 5.00 [HKEY\_LOCAL\_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo] "hc"="DBMSSOCN,.4,1422" 直接双击这个 reg 文件就可以把信息写入注册表中。

　　3）odbc 用 odbc 的方式其实也是通过配置 odbc 来为 sqlserver 定义别名。具体步骤是： 开始菜单->设置->控制面板->ODBC 数据源，点击添加，在弹出的界面上选择[SQL Server],点击[完成]，弹 出在弹出界面中设定数据源名称，如 hc； 点击下一步，在弹出界面中，点击[客户端配置]，在弹出界面中输入服务器别名，如 hc，选择 TCP/IP 协议， 输入服务器名称：

　　4）取消选择[动态决定端口]，然后输入非缺省的端口号，如 1422，点击确定返回到 创建数据源窗口中； 选择[使用用户输入登录 ID 和密码的 SQL Server 验证]，在界面的下方输入登录 ID 和密码； 测试数据源，如果测试成功，odbc 配置就完成了。 之后一路下一步，最后点击完成，并测试 测试 此时，在注册表的 HKEY\_LOCAL\_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo 就 有了 sqlserver 别名相关信息了。 

init<sid_name>.ora中  HS_FDS_CONNECT_INFO=服务器别名.数据库名。例如：HS_FDS_CONNECT_INFO=zjgl.house_zong

 

在最近项目中需要从Oracle中访问SQL Server数据库, 自然想到了透明网关. 因为Oracle数据库是Linux上的, 而Linux上的Oracle9i不包括连接到SQL Server的透明网关.就在一台单独的Windows服务器上安装了透明网关用做Oracle访问SQL Server的桥梁.

环境如下：

Oracle Database Server：Linux + oracle 9.2.0.4 IP:10.194.129.197

Transparent Gateway：Windows 2003 server IP: 10.194.129.225

MS SQL Server：Windows 2003 server + SQL Server 2005 IP: 10.194.129.225

1、 Transparent Gateway for SQL Server安装

从Oracle 9i数据库安装光盘setup.exe安装,选择安装客户端

 

安装类型选择：管理员

安装完客户端后，重新运行setup.exe，安装产品选择Oracle 9i Database 9.2.0.1.0, 安装类型选择"自定义",安装组件选择Oracle Net Services和Oracle Transparent Gateways, 并在此项下选择Oracle Transparent Gateway for Microsoft SQL Server, 安装过程中可以不设置连接到SQL Server的信息.如下图所示：

以下的IP地址及数据库以实际环境为准

2、 编辑%ORACLE_HOME%/tg4msql/admin/init%ORACLE_SID%.ora, 该文件包含了TG for SQL Server的配置信息, 其中%ORACLE_SID%是给TG的"SID", 默认为tg4msql. 修改文件中的行

HS_FDS_CONNECT_INFO="SERVER=10.194.129.225;DATABASE=185life"

其中SERVER后为SQL Server所在的服务器名称或ip地址,Database为连接到的数据库名称.

当然，如果在SQL Server服务器上，有两个sql server实例，则使用‘服务器名"实例名’的方式设置上面的SERVER值，如：HS_FDS_CONNECT_INFO="SERVER=ZZL/MSSQLSERVER;DATABASE=MSDB"

3、 编辑%ORACLE_HOME%"network"admin"listener.ora, 编辑对应listener的SID_LIST

SID_LIST_LISTENER=

   (SID_LIST=

      (SID_DESC=

         (SID_NAME=%ORACLE_SID%)        

         (ORACLE_HOME=oracle_home_directory)        

         (PROGRAM=tg4msql)

      )

   )

例如：

SID_LIST_LISTENER =

(SID_LIST =

    (SID_DESC =

      (SID_NAME = tg4msql)

      (ORACLE_HOME = d:/oracle/ora92) #oracle的主目录

      (PROGRAM = tg4msql)

    )

)

其中%ORACLE_SID%必须为第二布中设置的SID, 默认值为tg4msql. 修改listener.ora文件后需重启listener使修改生效.如果没有listener.ora文件，请用Net Configuration Assistant或Net Manager新建一个LISTENER.

以上操作都是在Transparent Gateway所在机器上。

4、 在Oracle服务器上：编辑需要连接到透明网关的Oracle Server的$ORACLE_HOME/network/admin/tnsnames.ora, 例如:

TG4MSQL =

(DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = tg4msql)

    )

    (HS = OK)

)

HOST指向Transparent Gateway所在的机器的IP，Service_name为第3步中的SID_NAME

使用tnsping测试服务名是否连通，命令：tnsping tg4msql。如果不通，请检查防火墙及网络配置。

5、 在Oracle数据库中建立连接, 指向TG for SQL Server.

CREATE DATABASE LINK tg4msql CONNECT TO "user" IDENTIFIED BY "password" USING 'tg4msql';

其中tg4msql是tnsnames中建立的连接字符串.

ms sql server的用户名和密码必须小写，而且要加双引号

否则会报如下错误：

                错误信息：

                      ORA-28500: connection from ORACLE to a non-Oracle system returned this messsage:

                      [Transparent gateway from MSSQL][Microsoft][ODBC SQL Server Driver][SQL Server]??

                      'sa'????? (SQL State:28000; SQL Code: 18456)

                      ORA-02063: preceding 2 lines from LINK_ZZL

                错误原因：

                      在CREATE    DATABASE LINK LINK_ZZL的连接字符串中，ms sql server的用户名和密码必须小写，而且要加双引号

6、确保CATHS.sql已被运行。

以SYS用户连接上去，查看有没有SYS.HS_FDS_CLASS，如果没有，运行$ORACLE_HOME/RDBMS"ADMIN"CATHS.SQL

6、 测试, 如运行select * from emp@tg4msql等.
访问dblink时说明：
              这是基于网关的方式，因此不能在断开网络的情况下本地的oracle连接本地的ms sql server               ORA-28545: error diagnosed by Net8 when connecting to an agent
              NCRO: Failed to make RSLV connection
              ORA-02063: preceding 2 lines from LINK_ZZL

7、BUG解决。

如果在第6步出现以下错误提示，则以SYS运行

exec dbms_hs.replace_base_caps(531, 531, 'First/Last function');

ERROR at line 1:

           ORA-28522: error initializing heterogeneous capabilities

           ORA-28522: error initializing heterogeneous capabilities

           ORA-28559: FDS_CLASS_NAME is <GTW>9.2.0.5.0_128, FDS_INST_NAME is <link>

           ORA-02063: preceding 3 lines from %s

           ORA-00604: error occurred at recursive SQL level 1

           ORA-24274: no row exists in the HS$_BASE_CAPS table for these parameters

           ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

           ORA-06512: at "SYS.DBMS_HS_UTL", line 431

           ORA-06512: at "SYS.DBMS_HS_CHK", line 51

           ORA-06512: at "SYS.DBMS_HS_UTL", line 48

           ORA-06512: at "SYS.DBMS_HS", line 38

           ORA-06512: at line 1

http://it.chinawin.net/database/article-9457.html

2012-2-1整理

1.安装for sqlserver的透明网关:
打开oracle for windows的安装程序setup.exe,一路”下一步”,在选择安装产品时,选”oracle9i database”,再下一步时,选择”自定义安装”,单独选择transparent gateway for ms sqlserver安装.
    安装完毕后,在$Oracle_Home下生成一个tg4msql的目录.


2.配置透明网关文件:
d:\oracle\ora92\tg4msql\admin\inittg4msql.ora
内容如下:
HS_FDS_CONNECT_INFO="SERVER=SYHND;DATABASE=pubs"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER


3.配置lisenter.ora:
内容如下:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = SYHND)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = hello)
      (ORACLE_HOME = D:\oracle\ora92)
      (SID_NAME = hello)
    )

   (SID_DESC =
     (PROGRAM =tg4msql)
     (SID_NAME =tg4msql)
     (ORACLE_HOME =D:\oracle\ora92)    
   )
)
###最后一个SID_DESC是为了访问SQLSERVER而新增的.


4.配置TNSname.ora:
test.ASPIRE.ASPIRE-TECH.COM= 
  (DESCRIPTION = 
   (ADDRESS_LIST = 
     (ADDRESS = (PROTOCOL = TCP)(HOST = SYHND)(PORT = 1521)) 
   ) 
   (CONNECT_DATA = 
     (SID_NAME= tg4msql) 
   ) 
(HS=OK) 
) 

5.创建链接:
CREATE DATABASE LINK DB_SQL CONNECT TO sa IDENTIFIED BY sa USING 'test.ASPIRE.ASPIRE-TECH.COM';


6.访问测试:
SQL> select * from sales@DB_SQL;

select * from sales@DB_SQL

ORA-12154: TNS: 无法处理服务名

SQL> select * from [email]sales@DB_SQL.US.ORACLE.COM[/email];

select * from [email]sales@DB_SQL.US.ORACLE.COM[/email]

ORA-12154: TNS: 无法处理服务名

SQL>



故障排查:
使用TNSPING命令查看TNSNAME是否正常:
C:\Documents and Settings\Administrator>tnsping test.aspire.aspire-tech.com

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 11-5月 -2
006 22:56:55

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

已使用的参数文件:
D:\oracle\ora92\network\admin\sqlnet.ora


已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = SYHND)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME
= tg4msql))
^C
到了这就不动了.说明在解析服务名就不能成功.

很可能是listenr.ora或tnsname.ora或inittg4msql.ora格式有问题,重新检查:
修改了tnsname.ora:
test.ASPIRE.ASPIRE-TECH.COM= 
  (DESCRIPTION = 
   (ADDRESS_LIST = 
     (ADDRESS = (PROTOCOL = TCP)(HOST = SYHND)(PORT = 1521)) 
   ) 
   (CONNECT_DATA = 
    (SERVICE_NAME=tg4msql) 
   ) 
(HS=OK) 
)
把(SERVICE_NAME=tg4msql)修改为: (SID_NAME=tg4msql)


重启数据库:
C:\Documents and Settings\Administrator>tnsping test

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 12-5月 -2
006 11:00:49

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

已使用的参数文件:
D:\oracle\ora92\network\admin\sqlnet.ora


已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = SYHND)(PORT = 1521))) (CONNECT_DATA = (SID_NAME= tg4msql)) (HS=OK))
OK（10毫秒）

这下OK了.可以解析别名.




查看当前的哪些DBLINK:
select * from dba_db_links

删除没用的DBLINK:
SQL> drop public database link DB_SQL;

Database link dropped

SQL> drop database link sql;

Database link dropped

SQL> drop database link db_sql;

Database link dropped

SQL>

注意:DBLINK是谁创建的,就只有该用户才能删除,DBA都无法删除.
如果是public类型的DBLINK,删除时,则要加上public的参数.

再次访问远程数据库:

SQL> CREATE DATABASE LINK DB_SQL CONNECT TO sa IDENTIFIED BY sa USING 'test.ASPIRE.ASPIRE-TECH.COM';

Database link created

SQL> select * from student@db_sql;

select * from student@db_sql

ORA-28545: 连接代理时 Net8 诊断到错误
NCRO:无法执行 RSLV 连接
ORA-02063: 紧接着2 lines（源于DB_SQL）

SQL>


很可能还是listener.ora和tnsname.ora还是存在问题.
修改了tnsname.ora:
test.ASPIRE.ASPIRE-TECH.COM= 
  (DESCRIPTION = 
   (ADDRESS_LIST = 
     (ADDRESS = (PROTOCOL = TCP)(HOST = SYHND)(PORT = 1521)) 
   ) 
   (CONNECT_DATA = 
    (SID_NAME=tg4msql) 
   ) 
(HS=OK) 
)
把(SID_NAME=tg4msql)修改为: (SERVICE _NAME=tg4msql)



再次访问远程数据库:

SQL> select * from student@db_sql;

name
--------------------

SQL>

SQL> select * from sales@db_sql;

stor_id ord_num              ord_date       qty payterms     title_id
------- -------------------- ----------- ------ ------------ --------
6380    6871                 1994-9-14        5 Net 60       BU1032
6380    722a                 1994-9-13        3 Net 60       PS2091
7066    A2976                1993-5-24       50 Net 30       PC8888
7066    QA7442.3             1994-9-13       75 ON invoice   PS2091
7067    D4482                1994-9-14       10 Net 60       PS2091
7067    P2121                1992-6-15       40 Net 30       TC3218
7067    P2121                1992-6-15       20 Net 30       TC4203
7067    P2121                1992-6-15       20 Net 30       TC7777
7131    N914008              1994-9-14       20 Net 30       PS2091
7131    N914014              1994-9-14       25 Net 30       MC3021
7131    P3087a               1993-5-29       20 Net 60       PS1372
7131    P3087a               1993-5-29       25 Net 60       PS2106
7131    P3087a               1993-5-29       15 Net 60       PS3333
7131    P3087a               1993-5-29       25 Net 60       PS7777
7896    QQ2299               1993-10-28      15 Net 60       BU7832
7896    TQ456                1993-12-12      10 Net 60       MC2222
7896    X999                 1993-2-21       35 ON invoice   BU2075
8042    423LL922             1994-9-14       15 ON invoice   MC3021
8042    423LL930             1994-9-14       10 ON invoice   BU1032
8042    P723                 1993-3-11       25 Net 30       BU1111

stor_id ord_num              ord_date       qty payterms     title_id
------- -------------------- ----------- ------ ------------ --------
8042    QA879.1              1993-5-22       30 Net 30       PC1035

21 rows selected

SQL>
终于配置成功了.!!!!


配置时的注意点:
关键是listener.ora和tnsname.ora两个文件.

像:(SID_NAME=tg4msql)
最好不要有空格之类的符号,以前配置出现的所有问题说不定就是因为空格的原因导致的.!!!





我的配置文件，请参考:
代码:
D:\oracle\ora92\tg4msql\admin\inittg4msql.ora

###此处中的SERVER为SQLSERVER所处的主机名或IP
HS_FDS_CONNECT_INFO="SERVER=SYHND;DATABASE=pubs"  
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

注意，如果对应的listener.ora里面的sid_name设置为其他的，例如sql2000,那么应该新增一个配置文件对应为initsql2000.ora而不是inittg4msql.ora了.

D:\oracle\ora92\network\admin\listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = SYHND)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = hello)
      (ORACLE_HOME = D:\oracle\ora92)
      (SID_NAME = hello)
    )

   (SID_DESC =
(GLOBAL_DBNAME=tg4msql) 
     (PROGRAM =tg4msql)
     (SID_NAME=tg4msql)
     (ORACLE_HOME =D:\oracle\ora92)    
   )
)



D:\oracle\ora92\network\admin\tnsnames.ora
##此处的HOST为透明网关服务所在的主机名或IP
test.ASPIRE.ASPIRE-TECH.COM= 
  (DESCRIPTION = 
   (ADDRESS_LIST = 
     (ADDRESS = (PROTOCOL = TCP)(HOST = SYHND)(PORT = 1521)) 
   ) 
   (CONNECT_DATA = 
(SERVICE_NAME=tg4msql) 
##此SERVICE_NAME应和透明网关SERVER上设定的SID_NAME相同
   ) 
(HS=OK) ##hs=ok 代表异构服务
) 


注意:
如果需要配置对多个sql server数据库进行访问，可以在目录tg4msql中配置多个init.ora文件，里面指定对应的数据库同时在listener.ora和tnsnames.ora进行配置，再建立相应的db links即可。