怎么配置Oracle DBlink連接MySQL庫

138次閱讀

共計 8501 個字符，預(yù)計需要花費 22 分鐘才能閱讀完成。

本篇內(nèi)容主要講解“怎么配置 Oracle DBlink 連接 MySQL 庫”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學(xué)習(xí)“怎么配置 Oracle DBlink 連接 MySQL 庫”吧!

一
背景描述

某客戶業(yè)務(wù)需求，需要在 Oracle 數(shù)據(jù)庫上通過網(wǎng)絡(luò)連接獲取 MySQL 數(shù)據(jù)庫中業(yè)務(wù)數(shù)據(jù)。現(xiàn)針對該需求，配置 Oracle 連接至 MySQL 庫的 dblink。

二
配置 Oracle DBlink2.1
確認(rèn) [Oracle] 和[DG4ODBC]位數(shù)

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

通過檢查確認(rèn)，[Oracle]和 [DG4ODBC] 均是 64 位，這就要求 [ODBC Driver Manager] 和[ODBC Driver]也是 64 位組件

2.2
下載及安裝 ODBC Driver Manager

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

2.3
下載及安裝 ODBC Driver

下載地址：

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/

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

創(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

驗證 ODBC 至 MySQL Server 端的連接

# isql myodbc5 -v

+————————+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+————————-+

SQL

2.5
配置 listener.ora

編輯監(jiān)聽配置文件，創(chuàng)建 LISTENER2 并對實例 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

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

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
配置網(wǎng)關(guān)參數(shù)文件 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
創(chuàng)建 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 網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！

正文完