共计 8501 个字符，预计需要花费 22 分钟才能阅读完成。
本篇内容主要讲解“怎么配置 Oracle DBlink 连接 MySQL 库”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“怎么配置 Oracle DBlink 连接 MySQL 库”吧!
一
背景描述
某客户业务需求，需要在 Oracle 数据库上通过网络连接获取 MySQL 数据库中业务数据。现针对该需求，配置 Oracle 连接至 MySQL 库的 dblink。
二
配置 Oracle DBlink2.1
确认 [Oracle] 和[DG4ODBC]位数
SQL select * from v$version where rownum
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 库”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
