Oracle11.2.04透明网关链接SQL server

1、解压gateways到本地

点击setup进入欢迎界面

2、欢迎界面

点击“已安装产品”

3、查看database是否已经安装至本地

确定database已经安装后关闭产品安装清单

4、回到了欢迎界面

点击“下一步”进入透明网关安装路径设置界面

5、透明网关安装路径设置界面

6、由于透明网关需要监听程序，本地数据库已安装监听程序，可把透明网关监听涵盖到本地数据库监听程序中（也可另行配置透明网关监听程序）。

点击图中下拉框三角

7、可见产品名称变为已安装产品名称

点击已安装产品名称

8、点击后可见路径变为已安装产品路径

继续安装，点击“下一步”

9、产品特定的先决条件检查

确认成功后点击“下一步”

10、选择需要添加的透明网关服务（这里以SQL Server为例）

点击“下一步”继续

11、设置远程数据库信息

SQL Server数据库服务器主机名：数据库所在服务器IP地址
SQL Server实例名：
打开SQL Server数据库所在服务器，进入服务界面查看如下图，括号中内容为实例名

数据库名称：如下图

点击“下一步”继续

12、查看安装概要

确认无误后点击“安装”继续

13、透明网关正在安装

14、透明网关安装完成

点击“退出”继续

15、退出安装

至此，透明网关安装完成

16、打开oracle产品安装主目录：D:\app\Administrator\product\11.2.0\dbhome_1

多出的文件夹dg4msql就是SQL Server的透明网关

17、检查透明网关tns和监听程序配置
路径：D:\app\Administrator\product\11.2.0\dbhome_1\dg4msql\admin

tnsnames.ora.sample↓

# This is a sample tnsnames.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent

dg4msql  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
    (CONNECT_DATA=(SID=dg4msql))
    (HS=OK)
  ) 

listener.ora.sample↓

# This is a sample listener.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent

LISTENER =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
 )

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4msql)
         (ORACLE_HOME=D:\app\Administrator\product\11.2.0\dbhome_1)
         (PROGRAM=dg4msql)
      )
  )

#CONNECT_TIMEOUT_LISTENER = 0

18、修改透明网关连接信息文件
路径：D:\app\Administrator\product\11.2.0\dbhome_1\dg4msql\admin

initdg4msql.ora↓ –修改前

# 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=[xxx.xxx.xxx.xxx]/实例名/数据库名
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

修改：HS_FDS_CONNECT_INFO

initdg4msql.ora↓ –修改后

# 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=机器名//数据库名
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

19、修改本地tnsnames.ora文件

添加如下服务，服务地址填写oracle数据库地址

DG4MSQL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XXX.XXX)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dg4msql)
)
(HS = OK)
)

20、修改本地监听程序配置，添加透明网关监听地址
(SID_DESC =
(SID_NAME = dg4msql)
(ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = dg4msql)
)
listener.ora↓ –修改后监听程序配置

# listener.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (SID_NAME = dg4msql)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = dg4msql)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-DGRQL0MOTO6)(PORT = 1521))
  )

ADR_BASE_LISTENER = D:\app\Administrator

21、关闭监听程序

lsnrctl stop

22、启动监听程序

lsnrctl start

23、创建dblink，除服务名需要注意外其它不变

create database link ERP
  connect to user_name identified by pass_word
  using 'dg4msql';

24、若使用dblink进行查询报错，说明以上步骤有遗漏
报错：ORA-28500

报错原因：透明网关连接信息未修改（处理方式见第18条）

报错：ORA-12154

报错原因：本地数据库未添加透明网关服务名（处理方式见第19条）