一直比较抵触oracle 透明网关,最近有朋友说他们客户坚持要使用这个(不想修改程序),无奈之下进行了配置.
透明网关需求
Oracle透明网关链接sqlserver

Oracle：
OS：Red Hat Enterprise Linux Server release 6.8
DB：Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit
Ip：192.168.222.11

Sqlserver
OS：windows server 2003 x86
DB：sqlserver2005
IP：192.168.222.1
实例名称：MSSQLSERVER
指向的DB：xifenfei

安装 Gateway软件
使用oracle用户安装，并且使用空目录(不建议使用oracle_home目录)
配置oracle_base和oracle_home
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/wangguan

配置Gateway数据库连接信息

[oracle@ora11g admin]$ pwd
/u01/app/oracle/product/11.2.0/wangguan/dg4msql/admin
[oracle@ora11g admin]$  cat 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=[192.168.222.1]:1433//xifenfei
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

配置Gateway监听配置

[oracle@ora11g admin]$ pwd
/u01/app/oracle/product/11.2.0/wangguan/network/admin
[oracle@ora11g admin]$ cat listener.ora
# Generated by Oracle configuration tools.
LISTENERSQL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora11g)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )
SID_LIST_LISTENERSQL =
  (SID_LIST =
    (SID_DESC=
      (SID_NAME = dg4msql)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/wangguan)
      (PROGRAM = dg4msql)
    )
  )
ADR_BASE_LISTENERSQL = /u01/app/oracle

配置Gateway tns信息
在oracle数据库的oracle_home中配置,如果有设置tns_admin,在该目录下处理

[oracle@ora11g admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@ora11g admin]$ cat tnsnames.ora
# Generated by Oracle configuration tools.
tomssql =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.222.11)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dg4msql)
    )
     (HS = OK)
  )
[oracle@ora11g admin]$ tnsping tomssql
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 09-JAN-2020 09:10:06
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.222.11)(PORT = 1522))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dg4msql)) (HS = OK))
OK (20 msec)

创建dblink to sql

SQL> create public database link to_sql2012 connect to sa identified by "sa" using 'tomssql';
Database link created.

验证Gateway
sql server中查询

oracle 通过gateway查询

至此oracle to sql server Gateway 配置成功.如果需要配置多个$ORACLE_HOME/dg4msql/admin/init网关SID.ora文件并新增多个静态监听和tns指向网关SID即可