最近的工作中需要基于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 : 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 四、简化管理