配置Oracle Gateway 12串連到SQL Server 2014

配置Oracle Gateway 12串連到SQL Server 2014

最近的工作中需要基於Oracle串連到SQL Server 2014，我們可以通過配置Gateway的方式來實現這個功能。這個Gateway的實質是透過dblink來實現的。即把SQL Server類比成一個遠端的Oracle執行個體，這個執行個體由Gateway來負責進行接收，轉寄等等。本文簡要描述其配置過程。

一、安裝環境介紹

gateway: 12.1.0.2Oracle db: 11.2.0.4 + RHEL6.3Sqlserver: 2014 + Win2012如果安裝在已經安裝Oracle相同的目錄下，會收到如下提示，無法繼續安裝。[INS-32025] The chosen installation conflicts with software alreadyinstalled 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 functionsif [ -f ~/.bashrc ]; then        . ~/.bashrcfi# User specific environment and startup programsTMP=/tmp; export TMPTMPDIR=$TMP; export TMPDIRORACLE_HOSTNAME=wms.ycdata.net; export ORACLE_HOSTNAMEORACLE_UNQNAME=dg4msql; export ORACLE_UNQNAMEORACLE_BASE=/u01/app/gateway; export ORACLE_BASEORACLE_HOME=$ORACLE_BASE/12.1; export ORACLE_HOMEORACLE_SID=dg4msql; export ORACLE_SIDPATH=/usr/sbin:$PATH; export PATHPATH=$ORACLE_HOME/bin:$PATH; export PATHLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATHCLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

2、安裝配置gateway

$ source ~/.bash_profile_gw$ env |grep ORACLEORACLE_UNQNAME=dg4msqlORACLE_SID=dg4msqlORACLE_BASE=/u01/app/gatewayORACLE_HOSTNAME=wms.ycdata.netORACLE_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.1571433HQ1636testdb安裝完畢後，會提示建立監聽器，可以直接建立，也可以在安裝完畢後再配置，本文是在安裝完畢後，通過netmgr進行配置的。在通過netmgr配置時，除了配置監聽器地址和連接埠號碼之外，還需要配置其他服務項：Program Name             dg4msqlSID                      dg4msqlOracle 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$ lsdg4msql_cvw.sql  dg4msql_tx.sql  initdg4msql.ora  listener.ora.sample  tnsnames.ora.sample###這個檔案用於配置串連到sqlserver$ more initdg4msql.oraHS_FDS_CONNECT_INFO=[192.168.21.157]:1433//testdb# alternate connect format is hostname/serverinstance/databasenameHS_FDS_TRACE_LEVEL=OFFHS_FDS_RECOVERY_ACCOUNT=RECOVERHS_FDS_RECOVERY_PWD=RECOVER

三、測試gateway

$ lsnrctl start LISTENER_GWLSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-JAN-2016 18:03:03Copyright (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 - ProductionSystem parameter file is /u01/app/gateway/12.1/network/admin/listener.oraLog messages written to /u01/app/gateway/diag/tnslsnr/wms/listener_gw/alert/log.xmlListening 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_GWVersion                   TNSLSNR for Linux: Version 12.1.0.2.0 - ProductionStart Date                08-JAN-2016 18:03:03Uptime                    0 days 0 hr. 0 min. 0 sec    --Author : LeshamiTrace Level               off                          --Blog   : http://blog.csdn.net/leshami Security                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /u01/app/gateway/12.1/network/admin/listener.oraListener Log File         /u01/app/gateway/diag/tnslsnr/wms/listener_gw/alert/log.xmlListening 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 DG4MSQLTNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 08-JAN-2016 18:29:51Copyright (c) 1997, 2014, Oracle.  All rights reserved.Used parameter files:/u01/app/gateway/12.1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting 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@WMSSERVERSQL> 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 errorORA-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)

六、串連過程圖(參考其他大濕)

本文永久更新連結地址：