最近的工作中需要基於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 gateway1、准備環境
$ 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)
六、連接過程圖(參考其他大濕)
