本篇內(nèi)容主要講解“怎么配置Oracle DBlink連接MySQL庫”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“怎么配置Oracle DBlink連接MySQL庫”吧!
成都創(chuàng)新互聯(lián)主營裕民網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,成都app開發(fā),裕民h5小程序制作搭建,裕民網(wǎng)站營銷推廣歡迎裕民等地區(qū)企業(yè)咨詢
某客戶業(yè)務(wù)需求，需要在Oracle數(shù)據(jù)庫上通過網(wǎng)絡(luò)連接獲取MySQL數(shù)據(jù)庫中業(yè)務(wù)數(shù)據(jù)?，F(xiàn)針對該需求，配置Oracle連接至MySQL庫的dblink。
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 |
通過檢查確認(rèn)，[Oracle]和[DG4ODBC]均是64位，這就要求[ODBC Driver Manager]和[ODBC Driver]也是64位組件
ODBC驅(qū)動管理器介質(zhì)下載地址：www.unixodbc.org
為了方便測試，我這里直接調(diào)用操作系統(tǒng)自帶的ODBC驅(qū)動管理器，不難看出ODBC驅(qū)動管理器rpm已經(jīng)安裝
# 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驅(qū)動管理器rpm包安裝后相關(guān)文件
# 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/
解壓介質(zhì)并安裝
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/ |
# 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 |
創(chuàng)建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 |
驗(yàn)證ODBC至MySQL Server端的連接
# isql myodbc5 -v +------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +-------------------------+ SQL> |
編輯監(jiān)聽配置文件，創(chuàng)建LISTENER2并對實(shí)例myodbc5進(jìn)行靜態(tài)注冊
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") ) ) |
啟動監(jiān)聽LISTENER2并查看監(jiān)聽狀態(tài)
$ lsnrctl start LISTENER2 $ lsnrctl status LISTENER2 |
$ vi tnsnames.ora myodbc5 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1522)) (CONNECT_DATA = (SID = myodbc5) ) (HS = OK) ) |
驗(yàn)證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) |
$ 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 |
SQL> 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庫”有了更深的了解，不妨來實(shí)際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
重慶分公司
重慶分公司
