配置Oracle Gateway 12连接到SQL server 2014

谁借莪１个温暖的怀抱￠ 2022-08-19 13:28 356阅读 0赞

最近的工作中需要基于Oracle连接到SQLserver2014，我们可以通过配置Gateway的方式来实现这个功能。这个Gateway的实质是透过dblink来实现的。即把SQLserver模拟成一个远端的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)

## 六、连接过程图(参考其他大湿) ##

![这里写图片描述][20160118161139516]

[20160118161139516]: /images/20220731/82e9ffe2a50442cbbc8071cd143ca213.png