怎么配置Oracle DBlink连接MySQL库
本篇内容主要讲解"怎么配置Oracle DBlink连接MySQL库"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"怎么配置Oracle DBlink连接MySQL库"吧!
一 背景描述
某客户业务需求，需要在Oracle数据库上通过网络连接获取MySQL数据库中业务数据。现针对该需求，配置Oracle连接至MySQL库的dblink。
二 配置Oracle DBlink
2.1 确认[Oracle]和[DG4ODBC]位数
SQL> select * from v$version where rownum<=1; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production |
$ file $ORACLE_HOME/bin/dg4odbc /oracle/app/product/11.2.0/db_1/bin/dg4odbc: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped |
通过检查确认，[Oracle]和[DG4ODBC]均是64位，这就要求[ODBC Driver Manager]和[ODBC Driver]也是64位组件
2.2 下载及安装ODBC Driver Manager
ODBC驱动管理器介质下载地址：www.unixodbc.org
为了方便测试，我这里直接调用操作系统自带的ODBC驱动管理器，不难看出ODBC驱动管理器rpm已经安装
# yum list|grep -i unixodbc unixODBC.x86_64 2.2.14-14.el6 @dvd unixODBC-devel.x86_64 2.2.14-14.el6 @dvd unixODBC.i686 2.2.14-14.el6 dvd unixODBC-devel.i686 2.2.14-14.el6 dvd |
ODBC驱动管理器rpm包安装后相关文件
# rpm -ql unixODBC.x86_64 /etc/odbc.ini /etc/odbcinst.ini /usr/bin/dltest /usr/bin/isql /usr/bin/iusql /usr/bin/odbc_config /usr/bin/odbcinst /usr/lib64/libboundparam.so.2 /usr/lib64/libboundparam.so.2.0.0 /usr/lib64/libesoobS.so.2 /usr/lib64/libesoobS.so.2.0.0 /usr/lib64/libgtrtst.so.2 /usr/lib64/libgtrtst.so.2.0.0 /usr/lib64/libmimerS.so.2 /usr/lib64/libmimerS.so.2.0.0 /usr/lib64/libnn.so.2 /usr/lib64/libnn.so.2.0.0 /usr/lib64/libodbc.so /usr/lib64/libodbc.so.2 /usr/lib64/libodbc.so.2.0.0 /usr/lib64/libodbccr.so.2 /usr/lib64/libodbccr.so.2.0.0 /usr/lib64/libodbcdrvcfg1S.so.2 /usr/lib64/libodbcdrvcfg1S.so.2.0.0 /usr/lib64/libodbcdrvcfg2S.so.2 /usr/lib64/libodbcdrvcfg2S.so.2.0.0 /usr/lib64/libodbcinst.so /usr/lib64/libodbcinst.so.2 /usr/lib64/libodbcinst.so.2.0.0 /usr/lib64/libodbcminiS.so.2 /usr/lib64/libodbcminiS.so.2.0.0 /usr/lib64/libodbcmyS.so /usr/lib64/libodbcmyS.so.2 /usr/lib64/libodbcmyS.so.2.0.0 /usr/lib64/libodbcnnS.so.2 /usr/lib64/libodbcnnS.so.2.0.0 /usr/lib64/libodbcpsqlS.so /usr/lib64/libodbcpsqlS.so.2 /usr/lib64/libodbcpsqlS.so.2.0.0 /usr/lib64/libodbctxtS.so.2 /usr/lib64/libodbctxtS.so.2.0.0 /usr/lib64/liboplodbcS.so.2 /usr/lib64/liboplodbcS.so.2.0.0 /usr/lib64/liboraodbcS.so.2 /usr/lib64/liboraodbcS.so.2.0.0 /usr/lib64/libsapdbS.so.2 /usr/lib64/libsapdbS.so.2.0.0 /usr/lib64/libtdsS.so.2 /usr/lib64/libtdsS.so.2.0.0 /usr/lib64/libtemplate.so.2 /usr/lib64/libtemplate.so.2.0.0 /usr/share/doc/unixODBC-2.2.14 /usr/share/doc/unixODBC-2.2.14/AUTHORS /usr/share/doc/unixODBC-2.2.14/COPYING /usr/share/doc/unixODBC-2.2.14/ChangeLog /usr/share/doc/unixODBC-2.2.14/NEWS /usr/share/doc/unixODBC-2.2.14/README /usr/share/doc/unixODBC-2.2.14/doc /usr/share/doc/unixODBC-2.2.14/doc/AdministratorManual /usr/share/doc/unixODBC-2.2.14/doc/AdministratorManual/index.html /usr/share/doc/unixODBC-2.2.14/doc/AdministratorManual/odbcinst.html /usr/share/doc/unixODBC-2.2.14/doc/AdministratorManual/php3.html /usr/share/doc/unixODBC-2.2.14/doc/AdministratorManual/unixODBC.gif /usr/share/doc/unixODBC-2.2.14/doc/AdministratorManual/unixODBCsetup.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/close.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/conne.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/dsn.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/gloss.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/index.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/intro.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/navi.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/odbc.css /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/query.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/resul.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/index.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/unixODBC.gif /usr/share/doc/unixODBC-2.2.14/doc/UserManual /usr/share/doc/unixODBC-2.2.14/doc/UserManual/Figure1.gif /usr/share/doc/unixODBC-2.2.14/doc/UserManual/Figure2.gif /usr/share/doc/unixODBC-2.2.14/doc/UserManual/Figure3.gif /usr/share/doc/unixODBC-2.2.14/doc/UserManual/Figure4.gif /usr/share/doc/unixODBC-2.2.14/doc/UserManual/Figure6.gif /usr/share/doc/unixODBC-2.2.14/doc/UserManual/My.sql /usr/share/doc/unixODBC-2.2.14/doc/UserManual/StarOfficeDataGrid.gif /usr/share/doc/unixODBC-2.2.14/doc/UserManual/index.html /usr/share/doc/unixODBC-2.2.14/doc/UserManual/unixODBC.gif /usr/share/doc/unixODBC-2.2.14/doc/index.html /usr/share/doc/unixODBC-2.2.14/doc/lst /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure.html /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure.vsd /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure2.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure2.html /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure3.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure3.html /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure4.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure4.html /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure5.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure5.html /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure6.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure6.html /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure7.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure7.html /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure8.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure8.html /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure9.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure9.html /usr/share/doc/unixODBC-2.2.14/doc/lst/back.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/next.gif /usr/share/doc/unixODBC-2.2.14/doc/smallbook.gif /usr/share/doc/unixODBC-2.2.14/doc/unixODBC.gif |
2.3 下载及安装ODBC Driver
下载地址：
https://downloads.mysql.com/archives/c-odbc/
解压介质并安装
sftp> put -r "C:\Users\xh\Desktop\mysql-connector-odbc-5.1.12-linux-glibc2.5-x86-64bit.tar.gz" # mkdir -p /soft # tar zxvf /soft/mysql-connector-odbc-5.1.12-linux-glibc2.5-x86-64bit.tar.gz # mv /soft/mysql-connector-odbc-5.1.12-linux-glibc2.5-x86-64bit/* /usr/local/mysql-connector-odbc/ |
2.4 配置ODBC data source
# vi /etc/odbc.ini [myodbc5] Driver = /usr/local/mysql-connector-odbc/lib/libmyodbc5.so Description = Connector/ODBC 5.1 Driver DSN SERVER = 192.168.210.125 PORT = 3306 USER = backup PASSWORD = mysql DATABASE = zj20_sunft OPTION = 0 TRACE = OFF |
创建libodbcinst.so.2.0.0、libodbc.so.2.0.0文件软链接
# cd /usr/lib64/ # ln -s libodbcinst.so.2.0.0 libodbcinst.so.1 # ln -s libodbc.so.2.0.0 libodbc.so.1 |
验证ODBC至MySQL Server端的连接
# isql myodbc5 -v +------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +-------------------------+ SQL> |
2.5 配置listener.ora
编辑监听配置文件，创建LISTENER2并对实例myodbc5进行静态注册
LISTENER2 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1522)) ) ) SID_LIST_LISTENER2= (SID_LIST= (SID_DESC= (SID_NAME=myodbc5) (ORACLE_HOME=/oracle/app/product/11.2.0/db_1) (PROGRAM=dg4odbc) (ENV="LD_LIBRARY_PATH=/usr/lib64:/oracle/app/product/11.2.0/db_1/lib") ) ) |
启动监听LISTENER2并查看监听状态
$ lsnrctl start LISTENER2 $ lsnrctl status LISTENER2 |
2.6 配置tnsnames.ora
$ vi tnsnames.ora myodbc5 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1522)) (CONNECT_DATA = (SID = myodbc5) ) (HS = OK) ) |
验证myodbc5连接串配置
$ tnsping myodbc5 TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 03-SEP-2018 18:54:56 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 = rac2)(PORT = 1522)) (CONNECT_DATA = (SID = myodbc5)) (HS = OK)) OK (10 msec) |
2.7 配置网关参数文件initmyodbc5.ora
$ cd $ORACLE_HOME/hs/admin $ vi initmyodbc5.ora HS_FDS_CONNECT_INFO=myodbc5 # Data source name in odbc.ini HS_FDS_TRACE_LEVEL=ON HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so HS_FDS_SUPPORT_STATISTICS=FALSE HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15 # # ODBC env variables set ODBCINI=/etc/odbc.ini |
2.8 创建Oracle DBlink
SQL> create public database link myodbc5 connect to "backup" identified by "mysql" using 'myodbc5'; |
2.9 验证Oracle DBlink
SQL> select count(*) from "test"@myodbc5; COUNT(*) ---------- 1835008 |
2.10 MOS参考文档
配置Oracle至MySQL DBlink：
Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (文档 ID 1320645.1)
Odbc Connection From Oracle To SQL*Server Fails With Errors Ora-28546 and Ora-2063 When Using Connection via Database Link. (文档 ID 1389492.1)
到此，相信大家对"怎么配置Oracle DBlink连接MySQL库"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
