Oracle下配置透明网关访问MS SQL SERVER
配置环境：(configuration setting)
|
服务器 |
操作系统 |
IP地址 |
|
SQL SERVER |
Windows 2003 |
10.9.5.47 |
|
Oracle DB |
Linux 5.8 |
10.9.5.90 |
|
Oracle Gateways |
1. 安装透明网关
Oracle 11G之后,才出现linux下的Gateways,以前只有windows版本!
1.1 解压gateway软件
unzip p13390677_112040_Linux-x86-64_5of7.zip 1.2 安装gateway软件
./runInstaller 注释：Instance name，database name可以不写，就算写完，也需要修改配置文件
1.3 运行root.sh文件
1.4 创建监听
注释：监听名字不要修改，否则静态监听无法出现service
注释：端口只要不是1521就行
2. 配置参数文件
2.1 修改SID参数文件
[[email protected] gateways]$ cd /home/oracle/app/oracle/product/11.2.0/gw_1/dg4msql/admin/
[[email protected] admin]$ ll
total 28
-rw-r--r-- 1 oracle oinstall 10976 Nov 30 dg4msql_cvw.sql
-rw-r--r-- 1 oracle oinstall 746 Jun 8 dg4msql_tx.sql
-rw-r--r-- 1 oracle oinstall 355 Jul 23 11:08 initdg4msql.ora
-rw-r--r-- 1 oracle oinstall 415 Jul 23 11:08 listener.ora.sample
-rw-r--r-- 1 oracle oinstall 244 Jul 23 11:08 tnsnames.ora.sample
[[email protected] admin]$ cp initdg4msql.ora initMSDB.ora
[[email protected] admin]$ vi initMSDB.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=[10.9.5.47]:1433//MSSQLSERVER/test
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
注释：MSSQLSERVER
注释：test
在安装Gateway时添加这些信息，在配置文件中显示是错误的，windows下不知道会不会有这样的报错
在这里添加了instance name，database name，文件里显示是
HS_FDS_CONNECT_INFO=[10.9.5.47]:1433//MSSQLSERVER
注释：这里的MSSQLSERVER是指database name
注意：
根据提示# alternate connect format is hostname/serverinstance/databasename
网上说不写databasename没有问题，但经测试会报错
ORA-28500
2.2 修改gw的listener.ora
[[email protected] admin]$ pwd
/home/oracle/app/oracle/product/11.2.0/gw_1/dg4msql/admin
[[email protected] admin]$ cat listener.ora.sample
# This is a sample listener.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4msql)
(ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/gw_1)
(PROGRAM=dg4msql)
)
)
#CONNECT_TIMEOUT_LISTENER = 0
--将红色字体文件复制到listener.ora
[[email protected] admin]$ vi ../../network/admin/listener.ora
# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/gw_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.9.5.90)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=MSDB)
(ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/gw_1)
(PROGRAM=dg4msql)
)
)
ADR_BASE_LISTENER = /home/oracle/app/oracle
--MSDB指的是/home/oracle/app/oracle/product/11.2.0/gw_1/dg4msql/admin/initMSDB.ora
2.3 重启监听
[[email protected] admin]$ export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/gw_1
[[email protected] admin]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 23-JUL-2014 14:35:01
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.9.5.90)(PORT=1522)))
The command completed successfully
[[email protected] admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 23-JUL-2014 14:35:07
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /home/oracle/app/oracle/product/11.2.0/gw_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /home/oracle/app/oracle/product/11.2.0/gw_1/network/admin/listener.ora
Log messages written to /home/oracle/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.9.5.90)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.9.5.90)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 23-JUL-2014 14:35:08
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/app/oracle/product/11.2.0/gw_1/network/admin/listener.ora
Listener Log File /home/oracle/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.9.5.90)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "MSDB" has 1 instance(s).
Instance "MSDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
--如果更改listener名字，service无法显示，创建DBLINK之后，连接会报：
ORA-28545
2.4 修改db的tnsnames.ora
[[email protected] admin]$ pwd
/home/oracle/app/oracle/product/11.2.0/gw_1/dg4msql/admin
[[email protected] admin]$ cat tnsnames.ora.sample
# This is a sample tnsnames.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent
dg4msql =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)
[[email protected] admin]$
vi /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
msdb =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=10.9.5.90)(PORT=1522))
(CONNECT_DATA=(SID=MSDB))
(HS=OK)
)
--红色字符复制到oracle目录下的tnsnames.ora
--端口号与gateway的端口改为一致
--SID与gateway中initMSDB.ora一致
2.5 验证配置
[[email protected] ~]$ export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
[[email protected] ~]$ tnsping msdb
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 23-JUL-2014 14:44:04
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=10.9.5.90)(PORT=1522)) (CONNECT_DATA=(SID=MSDB)) (HS=OK))
OK (0 msec)
2.6 创建dblink
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 23 14:44:59 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create database link mslink connect to oracle identified by oracle using 'msdb';
Database link created.
SQL> select * from [email protected];
id
----------
1
T1的表必须在MSSQL中的test数据库中，否则会出现：
ORA-00942: table or view does not exist
[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]??? 'T2'
??? {42S02,NativeErr = 208}[Oracle][ODBC SQL Server Wire Protocol
driver][Microsoft SQL Server]??????? {HY000,NativeErr = 8180}
ORA-02063: preceding 2 lines from MSLINK
3. 附录
对远程数据库不允许进行ddl操作
如果需要配置对多个sql server数据库进行访问，可以在目录tg4msql中配置多个init.ora文件，里面指定对应的数据库同时在listener.ora和tnsnames.ora进行配置，再建立相应的db links即可。