配置Oracle Gateway 12连接到SQL Server 2014

494次阅读

共计 5402 个字符，预计需要花费 14 分钟才能阅读完成。

最近的工作中需要基于 Oracle 连接到 SQL Server 2014，我们可以通过配置 Gateway 的方式来实现这个功能。这个 Gateway 的实质是透过 dblink 来实现的。即把 SQL Server 模拟成一个远端的 Oracle 实例，这个实例由 Gateway 来负责进行接收，转发等等。本文简要描述其配置过程。

一、安装环境介绍

gateway: 12.1.0.2
Oracle db: 11.2.0.4 + RHEL6.3
Sqlserver: 2014 + Win2012

如果安装在已经安装 Oracle 相同的目录下，会收到如下提示，无法继续安装。[INS-32025] The chosen installation conflicts with software already
installed the given Oracle home.

二、安装 Oracle gateway

1、准备环境

$ unzip linuxamd64_12102_gateways.zip 
$ mkdir -p /u01/app/gateway
$ mkdir -p /u01/app/gateway/12.1
$ cp ~/.bash_profile ~/.bash_profile_gw
$ vim  ~/.bash_profile_gw    ### 编辑新的 bash_profile 文件

$ more ~/.bash_profile_gw    ### 编辑后如下
# .bash_profile

# Get the aliases and functions
if [-f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=wms.ycdata.net; export ORACLE_HOSTNAME
ORACLE_UNQNAME=dg4msql; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/gateway; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/12.1; export ORACLE_HOME
ORACLE_SID=dg4msql; export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

2、安装配置 gateway

$ source ~/.bash_profile_gw
$ env |grep ORACLE
ORACLE_UNQNAME=dg4msql
ORACLE_SID=dg4msql
ORACLE_BASE=/u01/app/gateway
ORACLE_HOSTNAME=wms.ycdata.net
ORACLE_HOME=/u01/app/gateway/12.1

$ export DISPLAY=192.168.21.157:0.0
$ cd gateways/
$ ./runInstaller

选择 for sql server
    Oracle Database Gateway for Microsoft SQL Server
    Oracle Database Gateway for ODBC (此项可以用于配置访问 mysql)

输入 sqlserver 连接信息，也可以后续再配置文件 initdg4msql.ora中修改    
192.168.21.157
1433
HQ1636
testdb

安装完毕后，会提示创建监听器，可以直接创建，也可以在安装完毕后再配置，本文是在安装完毕后，通过 netmgr 进行配置的。在通过 netmgr 配置时，除了配置监听器地址和端口号之外，还需要配置其他服务项：Program Name             dg4msql
SID                      dg4msql
Oracle Home Directory    /u01/app/gateway/12.1

与此同时，也可以通过 netmgr 配置 tnsnames.ora
$ cd $ORACLE_HOME/network/admin
$ more listener.ora 
# listener.ora Network Configuration File: /u01/app/gateway/12.1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER_GW =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = wms.ycdata.net)(PORT = 1531))
  )

SID_LIST_LISTENER_GW =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = dg4msql)
      (SID_NAME = dg4msql)
      (ORACLE_HOME = /u01/app/gateway/12.1)
    )
  )

ADR_BASE_LISTENER_GW = /u01/app/gateway

### 查看配置后的 tnsnames.ora
$ more tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/gateway/12.1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DG4MSQL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = wms.ycdata.net)(PORT = 1531))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dg4msql)
    )
  )

### 安装完毕，在 gateway 相应目录下也有对应的配置样例，如下  
$ cd $ORACLE_HOME/dg4msql/admin
$ ls
dg4msql_cvw.sql  dg4msql_tx.sql  initdg4msql.ora  listener.ora.sample  tnsnames.ora.sample

### 这个文件用于配置连接到 sqlserver
$ more initdg4msql.ora
HS_FDS_CONNECT_INFO=[192.168.21.157]:1433//testdb
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

三、测试 gateway

$ lsnrctl start LISTENER_GW

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-JAN-2016 18:03:03

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/gateway/12.1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/gateway/12.1/network/admin/listener.ora
Log messages written to /u01/app/gateway/diag/tnslsnr/wms/listener_gw/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wms.ycdata.net)(PORT=1531)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wms.ycdata.net)(PORT=1531)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_GW
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                08-JAN-2016 18:03:03
Uptime                    0 days 0 hr. 0 min. 0 sec    --Author : Leshami
Trace Level               off                          --Blog   : http://blog.csdn.net/leshami 
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/gateway/12.1/network/admin/listener.ora
Listener Log File         /u01/app/gateway/diag/tnslsnr/wms/listener_gw/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wms.ycdata.net)(PORT=1531)))
Services Summary...
Service "dg4msql" has 1 instance(s).
  Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

$ tnsping DG4MSQL

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 08-JAN-2016 18:29:51

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/u01/app/gateway/12.1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = wms.ycdata.net)(PORT = 1531))) 
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dg4msql)))
OK (0 msec)

$ sqlplus WMS_USER/xxx@WMSSERVER
SQL> show user;
USER is "WMS_USER"

SQL> create public database link mssql connect to robin identified by "xxx" using 'dg4msql';

SQL> select * from tt@dg4msql;
select * from tt@dg4msql
                            *
ERROR at line 1:
ORA-28546: connection initialization failed, probable Net8 admin error
ORA-02063: preceding line from DG4MSQL

调整 DG4MSQL 配置，增加 (HS=OK) 项
DG4MSQL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = wms.ycdata.net)(PORT = 1531))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = dg4msql)
    )
     (HS=OK)
  )

### 再次测试
SQL> select * from tt@dg4msql;

        id
----------
         1

四、简化管理

由于 Oracle gateway 安装时使用了不同的 Oracle Home，因此在启动 gateway 监听时，需要切换环境变量。因此可以直接将 gateway 下的监听器内容复制到 Oracle Home 下 listener.ora 文件中，同时也复制 DG4MSQL 至 Oracle Home 下的 tnsnames.ora 文件中，省去环境切换的麻烦。

五、更多参考

How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, AIX,HP-UX) post install (Doc ID 562509.1)
ORA-28500 SQLSTATE 8001 When I Select Via DG4MSQL (Doc ID 868672.1)