ORACLE透明网关访问SQL Server配置总结
2018-03-01 11:42 潇湘隐者 阅读(3493) 评论(0) 编辑 收藏 举报
透明网关概念
ORACLE透明网关（Oracle Transparent Gateway）可以解决ORACLE数据库和非ORACLE数据库交互数据的需求。在一个异构的分布式环境中，通过ORACLE透明网关可以访问其他类型数据库，例如DB2，SQL Server、Sybase....。这个类似于SQL Server里面的LINKED SERVER可以直接访问异构的数据库环境，由于Windows本身提供了很多驱动，所以有时候不需要安装额外的驱动程序，有时候也需要安装相关驱动才能访问（例如，SQL SERVER 通过LINKED SERVER访问ORACLE数据库，就必须安装Oracle Client客户端相关组件），而ORACLE则必须通过透明网关才能访问其它类型数据库，官方文档关于透明网关的介绍如下：
Oracle Database Gateways provide the ability to transparently integrate with non-Oracle systems from an Oracle environment. This transparency eliminates the need for application developers to customize their applications to access data from different non-Oracle systems, thus decreasing development efforts and increasing the mobility of the application. Applications can be developed using a consistent Oracle interface for both Oracle and non-Oracle systems.
For smooth interoperability between disparate systems, SQL translations, data dictionary translations and data type translations are required, even if the non-Oracle systems are based on SQL standards. Gateways have the ability to translate one system's dialect to another.
Oracle has tailored Gateways to many systems, DB2, Sybase, Informix, SQL Server, IMS, VSAM, Adabas, to name a few. These are specifically coded for the target non-Oracle system. They provide an optimized solution and are also end-to-end certified.
ORACLE官方关于透明网关的文档资料，请见链接http://www.oracle.com/technetwork/database/gateways/index.html，主要有Database Gateways Features Overview 与Database Gateways Technical Whitepaper等相关资料。
透明网关安装
关于透明网关的安装，透明网关可以安装在Windows平台或Linux平台；它既可以和ORACLE数据库实例安装在一起（Oracle Server端），也可以安装在其它数据库服务器上（例如，安装在SQL Server数据库服务器上），也可以安装在一台跟数据库无任何关系的机器上。也就是说Oracle Gateway架构设计的耦合性非常低。刚开始接触透明网关时，透明网关需要安装在哪里这个问题一直困扰我，直到我一一验证测试。通俗的来说，透明网关可以装在任意的一台机器上，如果透明网关安装在Oracle Server端，这时候Server自身担当两种角色：服务器和透明网关代理，如果安装在非Oracle Server端，那么其他服务器就充当透明网关代理的角色。
Windows平台安装
从官方网址下载win32_11gR2_gateways.zip http://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/112010-win32soft-098630-zhs.html安装介质后，解压安装，安装步骤非常简单，本身安装文件里面也有详细安装文档，本不需在这里过多介绍，不过，对于一些初次初学者，也有一些细节地方需要注意。如下所示，对一些步骤做了截图处理。
选择安装路径，这个路径可以和ORACLE数据库路径一致（一般很少有ORACLE实例安装在Windows平台，如果是安装在Linux平台，可以和ORACLE实例的安装路径一致，也可以任意选择一个路径），也可以单独选择一个安装路径。
选择需要安装的组件，例如我这里只需要通过透明网关访问SQL Server 数据库。那么我只勾选了下面一个组件。
在这里需要提供SQL Server数据库服务器名或IP、实例名称、数据库名称等等。
安装完成后，你会在D:\product\11.2.0\tg_1\dg4msql\admin这个路径（这个与安装目录有关系，都是位于$ORACLE_HOME\dg4msql\admin）下看到initdg4msql.ora 文件。
如下所示，如果前面UI界面填写了相关信息。那么在透明网关初始化参数文件initdg4msql.ora下就会有相关信息。如下所示。注意，默认使用的分隔符为“/”，ORACLE透明网关安装在一个Windows 2012应用服务器上。实际上,使用分隔符"/"是会遇到一些问题的（应该使用//），Windows平台的正确分隔符为“.”。后续会介绍这个案例，当然如果是Linux平台使用分隔符“/”则没有任何问题。
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[192.168.27.57]/MSSQLSERVER/TEST
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
Oracle Net 配置，可以通过图形界面配置，也可以忽略这些步骤，后续通过手工编辑配置Oracle Net
如下所示，在D:\product\11.2.0\tg_1\network\admin\listener.ora下面，你会看到下面配置信息：
# listener.ora Network Configuration File: D:\product\11.2.0\tg_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
ADR_BASE_GW_LISTENER = D:\product\11.2.0\tg_1
GW_LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.27.132)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
当然这个配置信息不全，正确的配置信息如下所示（注意：这里监听器名称为GEW_LISTENER,这个与默认监听器名称LISTENER有细微不同）：
ADR_BASE_GEW_LISTENER = D:\product\11.2.0\tg_1
GEW_LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.27.132)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
SID_LIST_GEW_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg4msql)
(ORACLE_HOME = D:\product\11.2.0\tg_1)
(PROGRAM = dg4msql)
)
)
Linux平台安装
以ORACLE 11g为例，首先从官网http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linx8664soft-100572.html下载linux.x64_11gR2_gateways.zip，我将ORACLE透明网关和ORACLE实例安装在同一台服务器上，并且安装目录与ORACLE数据库实例的安装路径一致，安装步骤简略。 关于ORACLE透明网关的安装路径，其实可以选择一个跟ORACLE实例主目录HOME不同的目录，或者跟ORACLE实例主目录相同的目录，官方文档介绍如下所示：
· On the same computer as an existing Oracle database but in a different Oracle home.
· On a system with no Oracle database.
· On the same computer as the Oracle database and in the same Oracle home directory. Note that in this case, the Oracle database and the gateway must be at the same release level.
如果透明网关的安装目录跟ORACLE实例的$ORACLE_HOME一致的话，那么关于透明网关的监听配置和本地服务名配置(Tnsnames)配置就位于数据库的监听文件，tnsnames.ora下，如果透明网关安装一个单独路径下，那么它会有单独的监听文件和TNS文件。
透明网关配置
Gateway初始化参数文件配置
透明网关初始化参数文件一般位于$ORACLE_HOME/dg4msql/admin/下， $ORACLE_HOME为透明网关安装目录。透明网关初始化参数文件默为initdg4msql.ora， 这意味着gateway system identifier(SID)为dg4msql, 透明网关的SID用来标识一个透明网关，如果你定义了透明网关SID，那么参数文件名也必须随之修改，这个跟ORACLE实例init{SID}.ora类似。当你需要访问多个SQL Server数据库或多个其它数据库时，你就必须配置多个透明网关SID。
官方文档关于透明网关初始化参数文件下参数HS_FDS_CONNECT_INFO的而配置形式为：
HS_FDS_CONNECT_INFO=host_name[[:port_number]|/[instance_name]][/database_name]
#要注意端口号、命令实例/默认实例,以及数据库名称。另外，需要注意的是平台不同，分隔符也不通，Windows平台如果使用分隔符"/"的话。则会遇到错误，具体参考案例问题3
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[192.168.27.57].MSSQLSERVER.TEST
HS_FDS_TRACE_LEVEL=OFF #默认，一般不需开启
HS_FDS_RECOVERY_ACCOUNT=RECOVER #访问其他数据库的账号，这个参数似乎没有用途，官方文档也不见细节介绍
HS_FDS_RECOVERY_PWD=RECOVER #访问其他数据库的账号密码，这个参数似乎没有用途，官方文档也不见细节介绍
官方文档Database Gateway Installation and Configuration Guide关于ORACLE透明网关初始参数文件的介绍如下：
9.1.1 Choose a System Identifier for the Gateway
The gateway system identifier (SID) is an alphanumeric character string that identifies a gateway instance. You need one gateway instance, and therefore one gateway SID, for each SQL Server database you are accessing. The SID is used as part of the file name for the initialization parameter file. The default SID is dg4msql.
You can define a gateway SID, but using the default of dg4msql is easier because you do not need to change the initialization parameter file name. However, if you want to access two SQL Server databases, you need two gateway SIDs, one for each instance of the gateway. If you have only one SQL Server database and want to access it sometimes with one set of gateway parameter settings, and other times with different gateway parameter settings, then you will need multiple gateway SIDs for the single SQL Server database.
9.1.2 Customize the Initialization Parameter File
The initialization parameter file must be available when the gateway is started. During installation, the following default initialization parameter file is created:
$ORACLE_HOME/dg4msql/admin/initdg4msql.ora
Where $ORACLE_HOME is the directory under which the gateway is installed.
This initialization file is for the default gateway SID. If you are not using dg4msql as the gateway SID, you must rename the initialization parameter file using the SID you chose in the preceding step "Choose a System Identifier for the Gateway". This default initialization parameter file is sufficient for starting the gateway, verifying a successful installation, and running the demonstration scripts.
A number of initialization parameters can be used to modify the gateway behavior. Refer to Appendix C, "Initialization Parameters" for the complete list of initialization parameters that can be set. Changes made to the initialization parameters only take effect in the next gateway session. The most important parameter is the HS_FDS_CONNECT_INFO, which describes the connection to the non-Oracle system.
The default initialization parameter file already has an entry for this parameter. The syntax for HS_FDS_CONNECT_INFO is as follows:
HS_FDS_CONNECT_INFO=host_name[[:port_number]|/[instance_name]][/database_name]
Where:
|
Variable |
Description |
|
host_name |
is the host name or IP address of the machine hosting the SQL Server database. |
|
port_number |
is the port number of the SQL Server database. |
|
instance_name |
is the instance of SQL Server running on the machine. |
|
database_name |
is the SQL Server Database database name. |
Either of the variables port_number or instance_name can be used, but not both together. Optionally, they both can be omitted. The variable database_name is always optional. The slash (/) is required when a particular value is omitted. For example, all of the following entries are valid:
HS_FDS_CONNECT_INFO=host_name/instance_name/database_name
HS_FDS_CONNECT_INFO=host_name//database_name
HS_FDS_CONNECT_INFO=host_name:port_name//database_name
HS_FDS_CONNECT_INFO=host_name/instance_name
HS_FDS_CONNECT_INFO=host_name
This release of gateway can support IPv6. If IPv6 address format is to be specified, you have to wrap it with square brackets to indicate the separation from the port number. For example,
HS_FDS_CONNECT_INFO=[2001:0db8:20C:F1FF:FEC6:38AF]:1300//SQL_DB1
配置透明网关监听
ORACLE透明网关需要Oracle Net与ORACLE数据库进行通信，所以在ORACLE透明网关安装后，必须为ORACLE透明网关配置监听。Oracle Net 侦听器侦听来自 Oracle 数据库的传入请求。为了让Oracle Net Listener为透明网关侦听, 必须将有关透明网关的信息添加到Oracle Net Listener配置文件listener.ora中。该文件默认位于$ORACLE_HOME/network/admin下，其中$ORACLE_HOME是安装透明网关的目录。如果透明网关的安装目录和ORACLE实例一致的话，那么它会和数据库共用监听文件。
透明网关监听的配置跟数据库的监听配置基本一致，除了需要注意配置合适的端口号。
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MyTestNT10)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg4msql) #此处为gateway system identifier(SID)的SID，要与initdg4msql.ora中的名字对应 (ORACLE_HOME = D:\product\11.2.0\tg_1) #透明网关安装的主目录。 (PROGRAM = dg4msql) #此处为固定格式)
)
ADR_BASE_LISTENER = D:\product\11.2.0\tg_1
另外，如下所示，数据库实例和透明网关安装在一起，红色部分是为透明网关配置监听而增加的部分。
[oracle@DB-Server admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = DB-Server.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.57.24)(PORT=1522)) #透明网关的监听配置
)
)
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(SID_NAME = dg4msql)
(ORACLE_HOME =/u01/app/oracle/product/11.1.0/dbhome_1)
(PROGRAM = dg4msql) #此处是固定的
)
)
ADR_BASE_LISTENER = /u01/app/oracle
关于透明网关的监听，这里我将透明网关和ORACLE实例安装在相同的Linux服务器下，我们可以对比监听重启前后的不同
[oracle@DB-Server admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-FEB-2018 09:37:58Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionStart Date 09-JAN-2018 10:51:43
Uptime 29 days 22 hr. 46 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/DB-Server/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DB-Server.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
重启后
[oracle@DB-Server admin]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-FEB-2018 09:43:37
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[oracle@DB-Server admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-FEB-2018 09:43:42
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.1.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/DB-Server/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DB-Server.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.57.24)(PORT=1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 08-FEB-2018 09:43:43
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/DB-Server/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DB-Server.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.57.24)(PORT=1522)))
Services Summary...
Service "dg4msql" has 1 instance(s).
Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
注意，配置了透明网关的监听后，需要重启一下监听服务。
配置服务器的TNS
数据库通过TNS连接到透明网关，这个跟数据库的TNS配置一些区别，例如， 透明网关的TNS配置是有HS=OK的
[oracle@DB-Server ~]$ cd $ORACLE_HOME
[oracle@DB-Server dbhome_1]$ cd network/admin/
[oracle@DB-Server admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
GSP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB-Server.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = gsp.localdomain)
)
)
MYGATEWAY=
(DESCRIPTION =
(ADDRESS = (PROTOCOL=TCP)(HOST=10.20.57.24)(PORT=1522))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)
创建Database Link
在数据库实例上创建database link，这个很简单，没有什么可说的。然后就是测试验证。
SQL> CREATE PUBLIC DATABASE LINK dblink CONNECT TO
"user" IDENTIFIED BY "password" USING 'tns_name_entry';
透明网关的疑难杂症
问题1：ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
如下测试所示，
SQL> create database link sqlgatetest connect to kkk identified by "kkk123456" using 'SQLGATEWAY';
Database link created.
SQL> select count(*) from dbo.mytest@sqlgatetest;
select count(*) from dbo.mytest@sqlgatetest
*
ERROR at line 1:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
其中tnsnames.ora下SQLGATEWAY的配置如下：
MYGATEWAY=
(DESCRIPTION =
(ADDRESS = (PROTOCOL=TCP)(HOST=10.20.57.24)(PORT=1522))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)
[oracle@DB-Server admin]$ tnsping SQLGATEWAY
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-FEB-2018 14:56:20Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.1.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST = (ADDRESS= (PROTOCOL=TCP)(HOST=192.168.27.132)(PORT=1522)) (CONNECT_DATA=(SID=dg4msql)) (HS=OK)))
OK (0 msec)
这个是因为在配置tnsnames.ora里面的配置错误导致，透明网关安装在一台IP地址为192.168.27.132的Windows Server上，如下所示
错误的配置
SQLGATEWAY=
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL=TCP)(HOST=192.168.27.132)(PORT=1522))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)
)
正确的配置：
SQLGATEWAY=
(DESCRIPTION=
(ADDRESS= (PROTOCOL=TCP)(HOST=192.168.27.132)(PORT=1522))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)
另外，如果缺失一个括号之类的，也会遇到这个问题。例如，如下这样设置，你也会遇到这个错误。
SQLGATEWAY=
(DESCRIPTION=
(ADDRESS= (PROTOCOL=TCP)(HOST=192.168.27.132)(PORT=1522) #这里少了一个括号
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)
问题2：遇到ORA-28545 & ORA-02063错误
这个问题比较特殊，引起这个错误的原因较多，我这里记录一个我测试时遇到的案例，如下所示：
SQL> select count(*) from dbo.mytest@sqlgatetest;
select count(*) from dbo.mytest@sqlgatetest
*
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from SQLGATETEST
我这里不是默认监听，而是一个命名监听，如下所示，检查监听状态，发现没有
修改前配置（注意红色部分是引起错误的原因）
ADR_BASE_GW_LISTENER = D:\product\11.2.0\tg_1
GW_LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.27.132)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg4msql)
(ORACLE_HOME = D:\product\11.2.0\tg_1)
(PROGRAM = dg4msql)
)
)
修改为正确的监听配置
ADR_BASE_GEW_LISTENER = D:\product\11.2.0\tg_1
GEW_LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.27.132)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
SID_LIST_GEW_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg4msql)
(ORACLE_HOME = D:\product\11.2.0\tg_1)
(PROGRAM = dg4msql)
)
)
当然，你也可以修改为默认监听服务，如下所示
ADR_BASE_LISTENER = D:\product\11.2.0\tg_1
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg4msql)
(ORACLE_HOME = D:\product\11.2.0\tg_1)
(PROGRAM = dg4msql)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.27.132)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
C:\>lsnrctl status
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 08-FEB-2018 15:32
:53
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.27.132)(PORT=152
2)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date 08-FEB-2018 15:32:20
Uptime 0 days 0 hr. 0 min. 36 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\product\11.2.0\tg_1\network\admin\listener.ora
Listener Log File d:\product\11.2.0\tg_1\diag\tnslsnr\GETTestNT38\listen
er\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.27.132)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
Services Summary...
Service "dg4msql" has 1 instance(s).
Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
SQL> select count(*) from dbo.mytest@sqlgatetest;
COUNT(*)
----------
339968
SQL>
2、有时候tnsnames.ora中不正确的配置也会遇到这个错误。如下所示：
SQLGATEWAY=
(DESCRIPTION=
(ADDRESS_LIST= (PROTOCOL=TCP)(HOST=192.168.27.132)(PORT=1522)) #正确配置为ADDRESS
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)
此时，你就会遇到这个错误
SQL> select count(*) from dbo.mytest@sqlgatetest;
select count(*) from dbo.mytest@sqlgatetest
*
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from SQLGATETEST
3：如果透明网关的监听服务关闭的话，也会遇到这个错误。
问题3：遇到ORA-28500 & ORA-02063错误
遇到这个错误，是因为D:\product\11.2.0\tg_1\dg4msql\admin下的initdg4msql.ora配置有问题
HS_FDS_CONNECT_INFO=[192.168.27.57]/MSSQLSERVER/TEST
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
如下所示，将分隔符“/” 改成“.” 就能避免这个问题 或者你用//都可以。
HS_FDS_CONNECT_INFO=[192.168.27.57].MSSQLSERVER.TEST
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_CONNECT_INFO=[192.168.27.57]//MSSQLSERVER//TEST
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
问题4：遇到ORA-28500 & ORA-02063错误
SQL> select count(*) from dbo.mytest@mylink;
select count(*) from dbo.mytest@mylink
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access
denied. {08001,NativeErr = 17}[Oracle][ODBC SQL Server
Driver][DBNETLIB]ConnectionOpen (Connect()). {01000,NativeErr =
53}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}
ORA-02063: preceding 2 lines from MYLINK
SQL> select count(*) from dbo.mytest@mylink;
select count(*) from dbo.mytest@mylink
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access
denied. {08001,NativeErr = 17}[Oracle][ODBC SQL Server
Driver][DBNETLIB]ConnectionOpen (Connect()). {01000,NativeErr =
53}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}
ORA-02063: preceding 2 lines from MYLINK
出现ORA-28500错误，需要关注ORA-28500后面的详细错误信息，根据具体错误信息进行判别，例如，此处提示“SQL Server does not exist or access denied",检查后发现，是因为initdg4msql.ora下，IP地址配置错误了。修改为正确的IP地址后就OK了。
HS_FDS_CONNECT_INFO=[192.168.27.57].MSSQLSERVER.TEST
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
[oracle@DB-Server admin]$ tnsping mytest;
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 06-FEB-2018 11:15:33
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.1.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.103.51)(PORT=1522)) (CONNECT_DATA=(SID=mylis)) (HS=OK))
TNS-12541: TNS:no listener
ERROR at line 1:
ORA-28513: internal error in heterogeneous remote agent
Solution:
This error can happen when the gatewayinit file is not found in the gateway/admin directory or if it has an error inthe file. When there's a config error in the file it commonly reports the linein which the error is.
其它关于透明网关访问SQL Server的一些报错或疑难杂症，可以参考官方文档How to Resolve Common Errors Encountered while using Database Gateways (DG4IFMX, Dg4MSQL, DG4SYBS), DG4ODBC or Generic Connectivity (文档 ID 234517.1)。 在此不一一测试。
参考资料：
How to Resolve Common Errors Encountered while using Database Gateways (DG4IFMX, Dg4MSQL, DG4SYBS), DG4ODBC or Generic Connectivity (文档 ID 234517.1)
