通过透明网关使Linux上Oralcle链接SQL

方法一：注意：确保外部可以通过命令telnet IP Port连通.1.准备:一台SQLServer主机 OS : windows2003 + SqlServer2000 ip: 10.86.16.27一台透明网关主机 OS : windows2003 ip: 10.86.184.250一台Oracle10g数据库 OS: Linux + Oracle 10g ip:10.86.17.1002.在透明网关主机上安装10201_gateways_win32.zip :运行Setup进行安装, 选择oracle transparent gateway for microsoft sql server .并完成安装.在安装目录下D:\oracle\product\10.2.0\tg_1\tg4msql\admin 新建名称为init<SID>.ora的文件 , <SID>可以随便起名.这里我起名为:inithowey.ora 内容如下:10.86.16.27为SQlServer主机IP .Smartit为SQLServer数据库名称:HS_FDS_CONNECT_INFO="SERVER=SERVER=10.86.16.27;DATABASE=SmartIT"HS_FDS_TRACE_LEVEL=OFFHS_FDS_RECOVERY_ACCOUNT=RECOVERHS_FDS_RECOVERY_PWD=RECOVER3.在透明网关主机上配置listener.ora , 所在位置如下:D:\oracle\product\10.2.0\tg_1\NETWORK\ADMIN 内容如下:SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = D:\oracle\product\10.2.0\tg_1)(PROGRAM = extproc))(SID_DESC=(SID_NAME=howey)(ORACLE_HOME=D:\oracle\product\10.2.0\tg_1)(PROGRAM=tg4msql)))LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.184.250)(PORT = 1521))(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))))4. 启动windows透明网关监听D:\oracle\product\10.2.0\tg_1\BIN>lsnrctl startLSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 31-3月 -2009 15:50:14 Copyright (c) 1991, 2005, Oracle. All rights reserved.正在启动 tnslsnr: 请稍后...TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production系统参数档案是 D:\oracle\product\10.2.0\tg_1\network\admin\listener.ora日志信息写入 D:\oracle\product\10.2.0\tg_1\network\log\listener.log监听之处: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.86.184.250)(PORT=1521)))监听之处: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))连线至 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.86.184.250)(PORT=1521)))监听器的状态------------------------别名 LISTENER版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production启动日期 31-10月 -2011 15:50:15正常运作时间 0 days 0 hr. 0 min. 1 sec追踪层次 off安管 ON: Local OS AuthenticationSNMP OFF监听器参数档案 D:\oracle\product\10.2.0\tg_1\network\admin\listener.ora监听器日志文件 D:\oracle\product\10.2.0\tg_1\network\log\listener.log监听终止点摘要...(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.86.184.250)(PORT=1521)))(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))服务摘要...服务 "PLSExtProc" 有 1 个执行处理.执行处理 "PLSExtProc", 状态 UNKNOWN, 有 1 个此服务的处理程序...服务 "howey" 有 1 个执行处理.执行处理 "howey", 状态 UNKNOWN, 有 1 个此服务的处理程序...命令顺利完成5在Oracle数据库上配置tnsname.ora文件:HOWEY =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.184.250)(PORT = 1521))(CONNECT_DATA = (SID = howey))(HS = OK)))HOWEY =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.184.250)(PORT = 1521))(CONNECT_DATA = (SID = howey))(HS = OK))测试连通性:[oracle@qhdtest admin]$ tnsping howeyTNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 31-OCT-2011 16:54:20 Copyright (c) 1997, 2005, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.184.250)(PORT = 1521)) (CONNECT_DATA = (SID = howey)) (HS = OK))OK (0 msec)6.创建数据库连接SYS@qhdtest:SQL> create database link smart connect to sa identified by sasa using 'howey';7. 在Oracle上测试数据库连接:SYS@qhdtest:SQL> select count(*) from dbo.sysobjects@smart;COUNT(*)----------624测试成功.附过程中出现的一些错误总结:注意点1：1、SYS@qhdtest:SQL> select count(*) from dbo.sysobjects@smart_link;select count(*) from dbo.sysobjects@smart_linkERROR at line 1:ORA-28545: error diagnosed by Net8 when connecting to an agentUnable to retrieve text of NETWORK/NCR message 65535ORA-02063: preceding 2 lines from SMART_LINK答案：其实此错误是由于Oracle和透明网关通信出现错误.可打开透明网关1521端口解决.并使用tnsping命令验证.如果三者都装在一台机器上,也一定要保证网络连通,因为这样的访问必须要通过网关.网上很多关于此错误的解决方式:用如下语句建立DBlink, 注:用户名和密码要用””双引号SYS@qhdtest:SQL> create database link smart_link connect to "sa" identified by2、"sasa" using 'howey'; 其实只有访问SQLServer2005时,密码必须要用双引号.用户名不用在访问SQLServer2000中用户名,密码加不加双引号没有任何关系.注意点2:SYS@qhdtest:SQL> select count(*) from dbo.sysobjects@smart_link;ERROR at line 1:ORA-02068: following severe error from SMART_LINKORA-03135: connection lost contact最终解决:数据库的tnsname.ora修改为:HOWEY =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.184.250)(PORT = 1521))(CONNECT_DATA = (SID = howey))(HS = OK)))由于以上tnsname.ora文件是手工编写,括号错误，导致出现ora-02068 和 03135这2个找不到原因的ora 错误.注意点3:在访问SQLServer2005的时候出现了一个问题,就是在oracle执行查询SQLServer过程中没有结果返回,就是错误信息也没有.解决方法：运行$ORACLE_HOME/rdbms/admin/caths.sql 脚本. 此脚本为创建HS所需表及视图表方法二：下载对应版本的Database Gateway，ora 11g linux_x86 32位版的大概440M。