二、Oracle透明网关配置
下载地址:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
解压,运行setup安装即可,这里我们将透明网关和SQLServer数据库安在了一台服务器上。
安装完后,会弹出配置监听界面,因为我们要手工配置,点击取消即可。
安装完Gateway软件后,在ORACLE_HOME目录下有一下dg4msql的目录,这就是Gateway软件的目录了。
在D:\app\Administrator\product\11.2.0\tg_1\dg4msql\admin目录下有一个initdg4msql.ora的文件。该文件是Gateway的初始参数文件,描述连接的是哪个SQL Server数据库。该文件的格式是initSID.ora,这里的SID在后面需要用到,系统默认的是dg4msql,一般情况这样就可以了。如果改名,如使用test作为SID,则文件名变成inittest.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.1.139]//test HS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER 只要修改HS_FDS_CONNECT_INFO参数就可以了。格式是:[hostname:port]/serverinstance/databasename,其中hostname是机器名称或IP,PORT是SQL Server的端口号,SQL Server默认为1433.serverinstance是SQL Server的实例名,一般空着就行。Databasename是SQL Server的数据库名。因为我们在安装过程中指定了主机名和数据库名,这里已经有信息了透明网关层的监听配置文件: D:\app\Administrator\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora
这里PROGRAM指定应用程序名称,因为实例配置文件在D:\Oracle\product\11.2.0\tg_1\dg4msql\admin\initdg4msql.ora,所以PROGRAM不能改变, SID_NAME就是前面init.ora文件名里指定的SID
# listener.ora Network Configuration File: D:\app\Administrator\product\11.2.0\tg_1\network\admin\listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.130)(PORT = 1523)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = test) (PROGRAM = dg4msql) (SID_NAME = test) (ORACLE_HOME = D:\app\Administrator\product\11.2.0\tg_1) ) ) ADR_BASE_LISTENER = D:\app\Administrator\product\11.2.0\tg_1 启动监听
C:\WINDOWS\system32>lsnrctl start LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 11-7月 -2017 11:17:45 Copyright (c) 1991, 2010, Oracle. All rights reserved. 启动tnslsnr: 请稍候... TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production 系统参数文件为D:\app\Administrator\product\11.2.0\tg_1\network\admin\listener.ora 写入d:\app\administrator\product\11.2.0\tg_1\diag\tnslsnr\shiyu\listener\alert\log.xml的日志信息 监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.130)(PORT=1523))) 监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1523ipc))) 正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.130)(PORT=1523))) LISTENER 的 STATUS ------------------------ 别名 LISTENER 版本 TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production 启动日期 11-7月 -2017 11:17:48 正常运行时间 0 天 0 小时 0 分 3 秒 跟踪级别 off 安全性 ON: Local OS Authentication SNMP OFF 监听程序参数文件 D:\app\Administrator\product\11.2.0\tg_1\network\admin\listener.ora 监听程序日志文件 d:\app\administrator\product\11.2.0\tg_1\diag\tnslsnr\shiyu\listener\alert\log.xml 监听端点概要... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.130)(PORT=1523))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1523ipc))) 服务摘要.. 服务 "test" 包含 1 个实例。 实例 "test", 状态 UNKNOWN, 包含此服务的 1 个处理程序... 命令执行成功在需要建立dblink的Oracle数据库所在服务器,配置/u01/oracle/app/product/11.2.0/db_1/network/admin/tnsnames.ora
TEST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.130)(PORT = 1523)) (CONNECT_DATA=(SID=test)) (HS=OK) )
测试tns
orcl:/u01/app/oracle/product/11.2.0/db_1/network/admin@oracle>tnsping test TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 11-JUL-2017 11:58:18 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.130)(PORT = 1523)) (CONNECT_DATA=(SID=test)) (HS=OK)) OK (0 msec) orcl:/u01/app/oracle/product/11.2.0/db_1/network/admin@oracle>
到此 oracle dblink 创建成功