一直比较抵触oracle 透明网关,最近有朋友说他们客户坚持要使用这个(不想修改程序),无奈之下进行了配置.
透明网关需求
Oracle透明网关链接sqlserver
Oracle：
OS：Red Hat Enterprise Linux Server release 6.8
DB：Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit
Ip：192.168.222.11
Sqlserver
OS：windows server 2003 x86
DB：sqlserver2005
IP：192.168.222.1
实例名称：MSSQLSERVER
指向的DB：xifenfei
安装 Gateway软件
使用oracle用户安装，并且使用空目录(不建议使用oracle_home目录)
配置oracle_base和oracle_home
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/wangguan
配置Gateway数据库连接信息
[oracle@ora11g admin]$ pwd
/u01/app/oracle/product/11.2.0/wangguan/dg4msql/admin
[oracle@ora11g admin]$ cat initdg4msql.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[192.168.222.1]:1433//xifenfei
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
配置Gateway监听配置
[oracle@ora11g admin]$ pwd
/u01/app/oracle/product/11.2.0/wangguan/network/admin
[oracle@ora11g admin]$ cat listener.ora
# Generated by Oracle configuration tools.
LISTENERSQL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora11g)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
SID_LIST_LISTENERSQL =
(SID_LIST =
(SID_DESC=
(SID_NAME = dg4msql)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/wangguan)
(PROGRAM = dg4msql)
)
)
ADR_BASE_LISTENERSQL = /u01/app/oracle
配置Gateway tns信息
在oracle数据库的oracle_home中配置,如果有设置tns_admin,在该目录下处理
[oracle@ora11g admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@ora11g admin]$ cat tnsnames.ora
# Generated by Oracle configuration tools.
tomssql =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.222.11)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg4msql)
)
(HS = OK)
)
[oracle@ora11g admin]$ tnsping tomssql
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 09-JAN-2020 09:10:06
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.222.11)(PORT = 1522))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dg4msql)) (HS = OK))
OK (20 msec)
创建dblink to sql
SQL> create public database link to_sql2012 connect to sa identified by "sa" using 'tomssql';
Database link created.
验证Gateway
sql server中查询
oracle 通过gateway查询
至此oracle to sql server Gateway 配置成功.如果需要配置多个$ORACLE_HOME/dg4msql/admin/init网关SID.ora文件并新增多个静态监听和tns指向网关SID即可
