oracle 12 getway,配置Oracle Gateway 12连接到SQL Server 2014

最新推荐文章于 2024-06-10 20:16:00 发布

请你吃草莓蛋挞

最新推荐文章于 2024-06-10 20:16:00 发布

阅读量555

点赞数

文章标签： oracle 12 getway

最近的工作中需要基于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

# 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

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

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;

----------

四、简化管理

由于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)

六、连接过程图(参考其他大湿)