oracle 配透明网关连接与 ms sql 2008的连接

一、环境
公司网站系统使用的是IIS + Oracle
但公司某系统使用的是Sqlserver 2000, 但其数据需要做成报表放到网站上，为简化编程，使用
Oracle做透明网关，定期从Sqlserver 抓取数据的方式
A: Web Server
OS: Windows Server 2008
IP: 10.108.3.10
安装软体：IIS, Oracle Client
B: Oralce DB Server
OS: Windows Server 2008
IP: 10.108.3.20
安装软体： Oracle DB, Oracle Gateway
C: Sqlserver 2000 DB Server
OS: Windows Server 2003
IP: 10.108.5.100
安装软体: Sqlserver 2000
这里面A和C的安装配置都很简单，主要说下B机器 Oracle透明网关的配置
二、Oracle透明网关配置
1. gateway安装
当然是要先安装好我们的Oracle DB,做好其它业务。

然后再来安装Gateway
2. 配置 E:\Oracle\product\11.2.0\tg_1\dg4msql\admin\initdg4msql.ora 配连接条件
采用计算机名
HS_FDS_CONNECT_INFO=[PY-PC]// YX_YC
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
采用ip
HS_FDS_CONNECT_INFO=[10.22.1.165]//YX_YC
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
或
HS_FDS_CONNECT_INFO=10.22.1.165:1433//YX_YC
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
如果没配正确则报ORA-00942: 表或视图不存在
[MySQL][ODBC 5.1 Driver][mysqld-5.1.61]Table ‘test’ doesn't exist {42S02,NativeErr = 1146}
配监听
(SID_DESC =
(SID_NAME = dg4msql)
(ORACLE_HOME = D:\Oracle\product\11.2.0\dbhome_1)
(PROGRAM = dg4msql)
)
如果没配正确则报ora 28545错误
配tns
dg4msql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.22.1.165)(PORT = 1521) )
)
(CONNECT_DATA =
(SID = dg4msql)
)
(HS = OK)
)
如果配与多个sql的连接。

先配透明网关中，与ms sql 2008的连接。

D:\Oracle\product\11.2.0\dbhome_1\dg4msql\admin
默认的dg4msql中的配置不需要更改。

如：以“ptwms”的连接为例。

dg4msql中的配置：
HS_FDS_CONNECT_INFO=[localhost]//master
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
建名字为“initptwms”的ora文件。

initptwms中的配置：
HS_FDS_CONNECT_INFO=[10.22.1.165]//YX_YC
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
如果没配正确则报ORA-00942: 表或视图不存在
[MySQL][ODBC 5.1 Driver][mysqld-5.1.61]Table ‘test’doesn't exist {42S02,NativeErr = 1146} 。

后配oracle 11g与ms sql 2008的连接。

D:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN
1.配LISTENER
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\Oracle\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\Oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
--如果不配这个，远程访问时，会报错：PL/SQL连接虚拟机中的Oracle数据库，发现又报了“ORA-12514 TNS 监听程序当前无法识别连接描述符中请求服务”错误。

(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = D:\Oracle\product\11.2.0\dbhome_1)
(SID_NAME = ORCL)
)
--如果不配这个，会会报错：ora 28545错误
(SID_DESC =
(SID_NAME = ptwms)
(ORACLE_HOME = D:\Oracle\product\11.2.0\dbhome_1)
(PROGRAM = dg4msql)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.22.1.165)(PORT = 1521))
)
)
ADR_BASE_LISTENER = D:\Oracle
2.配tnsnames
dg4msql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.22.1.165)(PORT = 1521) )
)
(CONNECT_DATA =
(SID = ptwms)
)
(HS = OK)
)
注：”dg4msql”可以任意名称。

3.配Database links
注：oracle 11g对大小写有强校验，所在用户名与密码均需用””。

如下：用户名：”sa”
密码：”sa”。