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

方法一：注意：确保外部可以通过命令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:oracleproduct10.2.0tg_1tg4msqladmin 新建名称为init.ora的文件 , 可以随便起名.这里我起名为:inithowey.ora 内容如下:10.86.16.27为SQlServer主机IP .Smartit为SQLServer数据库名称:HS_FDS_CONNECT_INFO=SERVER=SERVER=10.86.16.27;DATABASE=SmartITHS_FDS_TRACE_LEVEL=OFFHS_FDS_RECOVERY_ACCOUNT=RECOVERHS_FDS_RECOVERY_PWD=RECOVER3.在透明网关主机上配置listener.ora , 所在位置如下: D:oracleproduct10.2.0tg_1NETWORKADMIN 内容如下:SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = D:oracleproduct10.2.0tg_1)(PROGRAM = extproc)(SID_DESC=(SID_NAME=howey)(ORACLE_HOME=D:oracleproduct10.2.0tg_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:oracleproduct10.2.0tg_1BINlsnrctl startLSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 31-3月 -2009 15:50:14Copyright (c) 1991, 2005, Oracle. All rights reserved.正在启动 tnslsnr: 请稍后.TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production系统参数档案是 D:oracleproduct10.2.0tg_1networkadminlistener.ora日志信息写入 D:oracleproduct10.2.0tg_1networkloglistener.log监听之处: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.86.184.250)(PORT=1521)监听之处: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=.pipeEXTPROC0ipc)连线至 (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:oracleproduct10.2.0tg_1networkadminlistener.ora监听器日志文件 D:oracleproduct10.2.0tg_1networkloglistener.log监听终止点摘要. (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.86.184.250)(PORT=1521) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=.pipeEXTPROC0ipc)服务摘要.服务 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)测试连通性:oracleqhdtest admin$ tnsping howeyTNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 31-OCT-2011 16:54:20Copyright (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.创建数据库连接SYSqhdtest:SQL create database link smart connect to sa identified by sasa using howey;7. 在Oracle上测试数据库连接:SYSqhdtest:SQL select count(*) from dbo.sysobjectssmart;COUNT(*)-624测试成功.附过程中出现的一些错误总结:注意点1：1、SYSqhdtest:SQL select count(*) from dbo.sysobjectssmart_link;select count(*) from dbo.sysobjectssmart_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, 注:用户名和密码要用”双引号SYSqhdtest:SQL create database link smart_link connect to sa identified by2、sasa using howey; 其实只有访问SQLServer2005时,密码必须要用双引号.用户名不用在访问SQLServer2000中用户名,密码加不加双引号没有任何关系.注意点2:SYSqhdtest:SQL select count(*) from dbo.sysobjectssmart_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。然后安装，一切默认设置，这就不细说了。然后开始配置，首先是如果你安装没有问题会在你的ora安装目录的有dg4msql/admin文件夹，我们需要配置一个init+SID.ora的文件，其中SID是你将要连接的数据库的实例名，自己虽便起一个只要不和现在有的重就行了。这个文件有一个模板，你可以拷一份initdg4msql.ora，然后改一下名和里面的配置就OK了。大致内容如下：# that are needed for the Database Gateway for Microsoft SQL Server # HS init parameters#HS_FDS_CONNECT_INFO=192.2.11.188# alternate connect format is hostname/serverinstance/databasenameHS_FDS_TRACE_LEVEL=OFFHS_FDS_RECOVERY_ACCOUNT=RECOVERHS_FDS_RECOVERY_PWD=RECOVERHS_FDS_TRACE_LEVEL=DEBUG其中HS_FDS_CONNECT_INFO=192.2.11.188这个比较重要要。配置这个错误会发生一个ORA-28513 internal error in heterogeneous remote agent的错误，具体这个参数的配置要求可以看下载的包里的文档，说的很细。第二步配置network/admin目录下的listener.ora tnsnames.ora两个文件，首选配置listener.ora文件，我的文件如下：# listener.ora Network Configuration File: /mnt/sda5/oracle/oracle/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(PROGRAM = tg4msql)(ORACLE_HOME = /mnt/sda5/oracle/oracle)(SID_NAME = mssql)其中，SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(PROGRAM = tg4msql)(ORACLE_HOME = /mnt/sda5/oracle/oracle)(SID_NAME = mssql)这是我们需要添加的。这两个参数：(ORACLE_HOME = /mnt/sda5/oracle/ora