Oracle透明网关访问SQLServer数据库

时间：2021-09-22 12:06:32

针对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透明网关访问SQLServer数据库

四、Oracle Gateway for SQLServer安装

（好久没图形界面安装了，一时还真不适应，这里通过xmanager启动图形界面）

RHEL6.6修改/etc/gdm/custom.conf

# GDM configuration storage

[daemon]

[security]

[xdmcp]

Enable=              ---------增加此行

[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透明网关访问SQLServer数据库

Oracle透明网关访问SQLServer数据库

Oracle透明网关访问SQLServer数据库

Oracle透明网关访问SQLServer数据库

Oracle透明网关访问SQLServer数据库

[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='' 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='' 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

相关文章