oracle利用透明网关访问mssql
遇到一个客户,有个需求,想将mssql中的数据抽取到oracle中。经过上网查找,感觉gateway这个工具可以实现,因此就搭建实验环境进行测试。首先在oracle delivery上面下载对应的安装包。
实验环境如下:
oracle数据库:
IP地址:192.168.56.100
数据库名称:orcl
数据库oracle_home:/u01/app/oracle/product/11.2.0/db_1
监听端口号:1521
gateway软件:
IP地址:192.168.56.100
gateway的home目录:/u01/app/gateway
SID名称:dg4msql
监听端口号:1523
mssql数据库:
IP地址:192.168.56.110
数据库名:MSDB
登录名:sa
密码:1qaz@WSX
上传刚才下载的软件包,将其随便解压在某个路径,在这里需要注意,我是把gateway和oracle安装在同一台服务器上,进入gateway,runInstaller开始安装:
[oracle@Abbott soft]$ cd gateways/
[oracle@Abbott gateways]$ ls
doc install legacy response runInstaller stage welcome.html
[oracle@Abbott gateways]$ export DISPLAY=192.168.56.1:0.0
[oracle@Abbott gateways]$ ./runInstaller
这里需要指定gateway的home名称和路径
安装for MSSQLSERVER的软件
指定要访问的数据库服务器IP地址,端口号,实例名和数据库名
在这里点击安装即可,
等安装结束后,运行如下脚本:
但是运行脚本时会有如下提示,这是因为我把gateway和oracle安装在了同一台服务器上,有些环境变量发生了冲突,直接回车,忽略:
[root@Abbott /]# /u02/app/gateway/root.sh
Running Oracle 11g root.sh script... The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u02/app/gateway Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
点击OK之后会自动弹出配置监听的窗口,选择配置典型监听,然后下一步,安装完成之后推出即可:
接着查看配置文件情况,因为在安装软件的时候就指定了MSSQLSERVER的ip地址,端口号,数据库名等参数,所以这里不需要做什么修改。配置文件目录为gateway的home目录下面的$ORACLE_HOME/dg4msql/admin/initdg4msql.ora:
[oracle@Abbott admin]$ vi 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.56.110]:1433//MSDB
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
接着查看监听是否正常,进去之后看到没有配置静态监听,因此需要在这里添加一条静态静听(以下加黑标红部分请注意,这里的SID_NAME与配置文件的init+SID.ora中的SID要相同)。监听文件的路径为gateway_home目录下的/network/admin/listener.ora:
[oracle@Abbott admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/gateway/network/admin/listener.ora
# Generated by Oracle configuration tools. LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1523))
)
) ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
# (GLOBAL_DBNAME = MSDB)
(PROGRAM = dg4msql)
(ORACLE_HOME = /u01/app/gateway)
(SID_NAME = dg4msql)
)
)
这里如果直接使用lsnrctl status查看监听状态,查看到的是oracle数据库的监听,因此需要export ORACLE_HOME=/u01/app/gateway,然后再使用此命令:
[oracle@Abbott admin]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 04-JUL-2017 09:17:09 Copyright (c) 1991, 2013, Oracle. All rights reserved. Starting /u01/app/gateway//bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/gateway/network/admin/listener.ora
Log messages written to /u01/app/gateway/diag/tnslsnr/Abbott/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.100)(PORT=1523))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1523)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 04-JUL-2017 09:17:09
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/gateway/network/admin/listener.ora
Listener Log File /u01/app/gateway/diag/tnslsnr/Abbott/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.100)(PORT=1523)))
Services Summary...
Service "dg4msql" has 1 instance(s).
Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
监听配置成功,接着就是配置tns,注意:这里的tns需要在oracle的home/network/admin/tnsnames.ora文件中配置:
[oracle@Abbott admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools. MSDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.100)(PORT = 1523))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)
配置完成后尝试tnsping:
[oracle@Abbott admin]$ tnsping MSDB TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 04-JUL-2017 11:30:13 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.56.100)(PORT = 1523)) (CONNECT_DATA=(SID=dg4msql)) (HS=OK))
OK (0 msec)
如果tnsping没有问题,接着就可以进入数据库,创建dblink,然后查询mssqlserver的数据:
SQL> create public database link aaaa connect to "sa" identified by "1qaz@WSX" using 'MSDB'; Database link created. SQL> select * from dbo.t1@AAAA; id
----------
1 SQL> / id
----------
1 在表t1中再插入一行数据: SQL> / id
----------
1
2
遇到问题:
SQL> select 1 from dual@aaaa;
select 1 from dual@aaaa
*
ERROR at line 1:
ORA-28513: internal error in heterogeneous remote agent
ORA-02063: preceding line from AAAA
最后检查,时因为监听中的SID_NAME错误的指定成了MSSQLSERVER的数据库名称MSDB,而不是dg4msql,这里为什么要指定为dg4msql呢?因为gateway的参数文件格式为initSID.ora,而默认的SID为dg4msql,我们也没有人为改动,指定SID表明gateway具体监听的哪个数据库,而当指定了dg4msql时,它自动会寻找initdg4msql.ora这个文件,从而根据配置文件找到对应的MSSQLSERVER。
最新文章
- [LeetCode] All O`one Data Structure 全O(1)的数据结构
- 处理 pcap 中的 mac 二进制字节流为可读格式
- 关于用photoshop制作海报
- 浅谈WebSocket
- Spring Boot 实践折腾记(三):三板斧,Spring Boot下使用Mybatis
- UML 序列图一点理解
- python(3)-函数动态参数
- Linux中Curl命令couldn't connect to host解决方案 php操作Curl(http,https)无法获取远程数据解决方案
- “采用VS2010至MFC4.2发育”最后溶液
- Hadoop集群搭建(非HA)
- 通过npm写一个cli命令行工具
- ios GCD将异步转换为同步
- 前端框架本质之探究——以Vue.js为例
- 论文笔记:ReNet: A Recurrent Neural Network Based Alternative to Convolutional Networks
- VHDL基础1
- Kali Linux没有声音的解决方法
- POJ - 2115C Looooops 扩展欧几里得(做的少了无法一眼看出)
- [pip]安装和管理python第三方包
- 超频真的不难!G3258超频4.5GHz全攻略
- C++类的静态成员变量初始化 Win32 API 定时器使用
热门文章
- 【u240】棋子放置
- 并发编程--CAS自旋锁
- 记录一次对接XX支付SDK过程中报错问题
- 利用WPF建立自己的3d gis软件(非axhost方式)(二)基础状态切换
- Linux核心设计依据(六)该块I/O一层
- Method for training dynamic random access memory (DRAM) controller timing delays
- 常用软件的安装(windows/linux)
- atitit.提高开发效率---mda 革命性的软件开发方法
- either you have JavaScript disabled or your browser does not support JavaScript
- c语言学习笔记(2)——预备知识