怎么配置Oracle DBlink连接MySQL库
本篇内容主要讲解“怎么配置Oracle DBlink连接MySQL库”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么配置Oracle DBlink连接MySQL库”吧!
一 背景描述某客户业务需求，需要在Oracle数据库上通过网络连接获取MySQL数据库中业务数据。现针对该需求，配置Oracle连接至MySQL库的dblink。
二 配置Oracle DBlink2.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 ManagerODBC驱动管理器介质下载地址：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 DBlinkSQL> create public database link myodbc5 connect to "backup" identified by "mysql" using 'myodbc5';
2.9 验证Oracle DBlinkSQL> 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库”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
