Oracle透明網關訪問SQLServer資料庫

針對oracle資料庫不同實例之間的數據訪問，我們可以直接通過dblink訪問，如果oracle資料庫想訪問mysql/sqlserver等資料庫的數據，我們可以通過配置oracle透明網關實現異構資料庫dblink訪問。

好久沒做透明網關的配置了，最近有業務需求，這裡將部署過程做個記錄，希望對有需要的朋友有所幫助。

一、Oracle資料庫通過透明網關訪問SQLServer資料庫環境說明

RHEL6.6  oracle 11.2.0.4

WinServer 2008R2  SQLServer 2008 R2

Oracle Gateway 11.2.0.4 For SQLServer

二、數據訪問流程

oracle client 通過dblink ——>tnsname——>listener——>dg4msql——>SQLServer資料庫

三、Oracle透明網關（SQLServer）下載

https://updates.oracle.com/Orion/Download/process_form/p13390677_112040_Linux-x86-64_5of7.zip

這裡附mos下載地址頁面

四、Oracle Gateway for SQLServer安裝

（好久沒圖形界面安裝了，一時還真不適應，這裡通過xmanager啟動圖形界面）

RHEL6.6修改/etc/gdm/custom.conf
# GDM configuration storage
[daemon]
[security]
[xdmcp]
Enable=1              ---------增加此行
[greeter]
[chooser]
[debug]

啟動Xmanager - Passive

在linux伺服器shell執行
export DISPLAY=xmanger_client_ipaddr:0.0
xhost +
執行命令xclock測試圖形界面是否生效

[root@test dbbak]# unzip linux.x64_11gR2_gateways.zip
[root@test dbbak]# chown -R oracle:oinstall gateways
[oracle@test dbbak]$ cd gateways/
[oracle@test gateways]$ ./runInstaller

[oracle@test hs]$ which dg4msql
/U01/app/oracle/product/11.2.0.4/bin/dg4msql
制定下麵命令驗證是否安裝成功
[oracle@test admin]$ dg4msql

Oracle Corporation --- SATURDAY  APR 28 2018 12:24:05.691

Heterogeneous Agent Release 11.2.0.4.0 - 64bit Production  Built with
   Oracle Database Gateway for MSSQL
五、SQLServer資料庫創建賬號、授權

/* For security reasons the login is created disabled and with a random password. */
/****** Object:  Login [dbtest]    Script Date: 04/27/2018 15:43:45 ******/
CREATE LOGIN [dbtest] WITH PASSWORD=N'abcd1234', DEFAULT_DATABASE=[mssql_test], DEFAULT_LANGUAGE=[簡體中文], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

EXEC sys.sp_addsrvrolemember @loginame = N'dbtest', @rolename = N'sysadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = N'dbtest', @rolename = N'dbcreator'
GO

ALTER LOGIN [dbtest] DISABLE
GO

六、Oracle資料庫相關配置

(1)透明網關配置

[oracle@test ~]$ cd /U01/app/oracle/product/11.2.0.4/dg4msql/admin
[oracle@test admin]$ cp -rp initdg4msql.ora  initmssql.ora
[oracle@test admin]$ vi initmssql.ora

# HS init parameters
HS_FDS_CONNECT_INFO=[10.1.1.2]:1433//mssql_test

# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

(2)監聽配置

[oracle@test admin]$ vi /U01/app/oracle/product/11.2.0.4/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
  (GLOBAL_DBNAME = dbsid)
  (SID_NAME = dbsid)
  (ORACLE_HOME=/U01/app/oracle/product/11.2.0.4)
  )
  (SID_DESC=
  (SID_NAME=mssql)
  (ORACLE_HOME=/U01/app/oracle/product/11.2.0.4)
  (PROGRAM=dg4msql)
  )
)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1521))
    )
)

重啟監聽
lsnrctl stop
lsnrctl start

(3)tnsname配置

[oracle@test admin]$ vi /U01/app/oracle/product/11.2.0.4/network/admin/tnsnames.ora
dbsid_mssql =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1 )(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = mssql)
    )
   (HS = OK)
  )
 
測試tnsname連接 

[oracle@test admin]$ tnsping dbsid_mssql

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 27-APR-2018 15:24:57

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1521))) (CONNECT_DATA = (SID = mssql)) (HS = OK))
OK (0 msec)

七、創建dblink

create  PUBLIC DATABASE LINK dblk connect to "dbtest" identified by "abcd1234" using 'dbsid_mssql';

八、通過oracle連接SQLServer資料庫執行相關操作

SQL> select * from t1@dblk;

        id
----------
        10

九、錯誤信息以及處理方法

（1）錯誤01（這個錯誤信息導致我浪費了大半天時間）

錯誤信息：

SQL> select * from t1@dblk;
select * from t1@dblk
                 *
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from DBLK

監聽日誌：
<msg time='2018-04-28T09:46:21.146+08:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='test'
 host_addr='10.1.1.1'>
 <txt>28-APR-2018 09:46:21 * (CONNECT_DATA=(SID=mssql)(CID=(PROGRAM=)(HOST=test)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.1)(PORT=10397)) * establish * mssql * 12518
 </txt>
</msg>
<msg time='2018-04-28T09:46:21.146+08:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='test'
 host_addr='10.1.1.1'>
 <txt>TNS-12518: TNS:listener could not hand off client connection
 TNS-12547: TNS:lost contact
  TNS-12560: TNS:protocol adapter error
   TNS-00517: Lost contact
    Linux Error: 32: Broken pipe
 </txt>
</msg>

alter日誌： 
HS:  Unable to establish RPC connection to HS Agent...
HS:  ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.1)(PORT=1521))(CONNECT_DATA=(SID=dgmsql))), NCR error = 65535 Unable to retrieve text of NETWORK/NCR message 65535

錯誤原因以及解決辦法：
由於資料庫版本是11.2.0.4，而gateway版本是11.2.0.1，所以導致上述listener到hs的連接失敗，更換gatway版本為11.2.0.4後問題解決。
(2)參考資料
https://blog.csdn.net/shiyu1157758655/article/details/74939952
https://blog.csdn.net/sharqueen_wu/article/details/30237371