怎么配置Oracle DBlink连接MySQL库

本篇内容主要讲解“怎么配置Oracle DBlink连接MySQL库”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么配置Oracle DBlink连接MySQL库”吧!某客户业务需求，需要在Oracle数据库上通过网络连接获取MySQL数据库中业务数据。现针对该需求，配置Oracle连接至MySQL库的dblink。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位组件ODBC驱动管理器介质下载地址：www.unixodbc.org为了方便测试，我这里直接调用操作系统自带的ODBC驱动管理器，不难看出ODBC驱动管理器rpm已经安装# yum list|grep -i unixodbcunixODBC.x86_64 2.2.14-14.el6 @dvdunixODBC-devel.x86_64 2.2.14-14.el6 @dvdunixODBC.i686 2.2.14-14.el6 dvdunixODBC-devel.i686 2.2.14-14.el6 dvdODBC驱动管理器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下载地址：https://downloads.mysql.com/archives/c-odbc/解压介质并安装sftp> put -r “C:UsersxhDesktopmysql-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/# vi /etc/odbc.ini[myodbc5]Driver = /usr/local/mysql-connector-odbc/lib/libmyodbc5.soDescription = Connector/ODBC 5.1 Driver DSNSERVER = 192.168.210.125PORT = 3306USER = backupPASSWORD = mysqlDATABASE = zj20_sunftOPTION = 0TRACE = 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>编辑监听配置文件，创建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$ vi tnsnames.oramyodbc5 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1522)) (CONNECT_DATA = (SID = myodbc5) ) (HS = OK) )验证myodbc5连接串配置$ tnsping myodbc5TNS Ping Utility for Linux: Version 11.2.0.4.0 – Production on 03-SEP-2018 18:54:56Copyright (c) 1997, 2013, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1522)) (CONNECT_DATA = (SID = myodbc5)) (HS = OK))OK (10 msec)$ cd $ORACLE_HOME/hs/admin$ vi initmyodbc5.oraHS_FDS_CONNECT_INFO=myodbc5# Data source name in odbc.iniHS_FDS_TRACE_LEVEL=ONHS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.soHS_FDS_SUPPORT_STATISTICS=FALSEHS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15## ODBC env variablesset ODBCINI=/etc/odbc.iniSQL> create public database link myodbc5 connect to “backup” identified by “mysql” using ‘myodbc5’;SQL> select count(*) from “test”@myodbc5; COUNT(*)———- 1835008配置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库”有了更深的了解，不妨来实际操作一番吧！这里是百云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

相关推荐: Oracle 12c数据库安装

系统：CentOS 6.5 数据库：Oracle 12.2.0.1检查包括：系统磁盘要充足，最好提前规划好，尽量不要将Oracle安装到根目录 ”/“ 下，避免因为系统日志或者Oracle相关日志导致根目录磁盘容量占满导致出现问题。将数据库安装到足够容量的磁盘…