這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)碛嘘P(guān)oracle如何通過dblink連接MySQL實(shí)施，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

創(chuàng)新互聯(lián)建站是一家網(wǎng)站設(shè)計(jì)公司，集創(chuàng)意、互聯(lián)網(wǎng)應(yīng)用、軟件技術(shù)為一體的創(chuàng)意網(wǎng)站建設(shè)服務(wù)商，主營產(chǎn)品：響應(yīng)式網(wǎng)站建設(shè)、品牌網(wǎng)站建設(shè)、成都全網(wǎng)營銷推廣。我們專注企業(yè)品牌在網(wǎng)站中的整體樹立，網(wǎng)絡(luò)互動(dòng)的體驗(yàn)，以及在手機(jī)等移動(dòng)端的優(yōu)質(zhì)呈現(xiàn)。做網(wǎng)站、網(wǎng)站設(shè)計(jì)、移動(dòng)互聯(lián)產(chǎn)品、網(wǎng)絡(luò)運(yùn)營、VI設(shè)計(jì)、云產(chǎn)品.運(yùn)維為核心業(yè)務(wù)。為用戶提供一站式解決方案，我們深知市場的競爭激烈，認(rèn)真對(duì)待每位客戶，為客戶提供賞析悅目的作品，網(wǎng)站的價(jià)值服務(wù)。

本周由于公司一個(gè)系統(tǒng)的oracle數(shù)據(jù)庫需要采集另一個(gè)系統(tǒng)mysql數(shù)據(jù)庫的數(shù)據(jù)，于是對(duì)oracle通過dblink連接mysql進(jìn)行了研究，測試環(huán)境是單實(shí)例oracle 10g 64位，折騰了半天搞定了，但是生產(chǎn)環(huán)境是10g rac，原本以為會(huì)很順利，但是折騰了一天多都沒搞定，問題主要出在監(jiān)聽上面，原本打算放棄，采用中間件連接mysql取數(shù)據(jù)，但是憑直覺感覺應(yīng)該是單實(shí)例和rac的監(jiān)聽配置差異造成的，于是在rac節(jié)點(diǎn)上單獨(dú)創(chuàng)建默認(rèn)監(jiān)聽，問題果然解決了。

當(dāng)配置有問題的時(shí)候都會(huì)報(bào)下面的錯(cuò)誤：

ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535

遇到的主要問題和解決辦法有：

1、oracle 10g 64位的hsodbc應(yīng)該有問題，文件大小為0，執(zhí)行hsodbc沒有任何信息；

解決：拷貝32機(jī)器下的hsodbc到$ORACLE_HOME/bin目錄下，替換原h(huán)sodbc，注意oracle要有權(quán)限，下載：http://space.itpub.net/?uid-28321441-action-viewspace-itemid-750733

2、rac監(jiān)聽不能監(jiān)聽到hsodbc服務(wù)；

解決：netca選擇single node configuration創(chuàng)建監(jiān)聽來監(jiān)聽hsodbc服務(wù)，監(jiān)聽端口選擇1522；

3、連接mysql查詢數(shù)據(jù)亂碼。

解決：編輯/etc/odbc.ini，增加

charset      =gbk
STMT         =SET NAMES 'GBK'

其中g(shù)bk是MYSQL字符集。

具體實(shí)施步驟如下：

--oracle服務(wù)器：
-- 操作系統(tǒng)：Red Hat Enterprise Linux 5.3
-- 數(shù)據(jù)庫：  Oracle RDBMS 10.2.0.3.0

--mysql服務(wù)器：
-- 操作系統(tǒng)：windows server 2003
-- 數(shù)據(jù)庫：  MySQL 5.5

單實(shí)例操作如下，操作都在oracle服務(wù)器上完成：

--檢查需要的rpm包，需要的包如下，由于hsodbc應(yīng)該是只支持32位，所以32位包是必須的：
libtool-ltdl-1.5.22-6.1.i386.rpm
libtool-ltdl-1.5.22-6.1.x86_64.rpm
mysql-5.0.77-3.el5.i386.rpm
mysql-5.0.77-3.el5.x86_64.rpm
mysql-connector-odbc-3.51.26r1127-1.el5.i386.rpm
mysql-connector-odbc-3.51.26r1127-1.el5.x86_64.rpm
perl-DBI-1.52-2.el5.x86_64.rpm
unixODBC-2.2.11-7.1.i386.rpm
unixODBC-2.2.11-7.1.x86_64.rpm

上面unixODBC-2.2.11-7.1、mysql-5.0.77-3.el5、mysql-connector-odbc是需要的包，其他是安裝這些包的前提。
安裝完后檢查
# rpm -qa |grep unixODBC

unixODBC-2.2.11-7.1
unixODBC-2.2.11-7.1

# rpm -qa |grep mysql

mysql-5.0.45-7.el5
mysql-5.0.45-7.el5
mysql-connector-odbc-3.51.26r1127-1.el5
mysql-connector-odbc-3.51.26r1127-1.el5

--編輯/etc/odbc.ini
vi /etc/odbc.ini

#[ODBC Data Sources]
#myodbc3     = MySQL ODBC 3.51 Driver DSN

[test]
Driver       = /usr/lib/libmyodbc3.so
Description  = MySQL ODBC 3.51 Driver DSN
SERVER       = 24.1.20.149
PORT         = 3306
USER         = root
Password     = laopo
Database     = teng_push
OPTION       = 3
SOCKET       =
charset      =gbk
STMT         =SET NAMES 'GBK'

--最后兩個(gè)是解決中文亂碼問題，gbk是MYSQL字符集

--切換到oracle用戶
# su - oracle
--編輯環(huán)境變量配置文件，主要是LD_LIBRARY_PATH和最后面兩項(xiàng)
$ vi ~/.bash_profile

export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=orcl
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
#export LANG=zh_CN.GB18030
export PATH=.:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:/bin:/usr/bin:/usr/sbin
:/usr/local/bin:/usr/X11R6/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
#ulimit -u 16384 -n 65536
umask 022
export EDITOR=vi

ODBCINI=/etc/odbc.ini; export ODBCINI
ODBCSYSINI=/etc; export ODBCSYSINI

--使配置生效
source ~/.bash_profile

--查看odbc版本及參數(shù)文件路徑
$odbcinst -j

unixODBC 2.2.11
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
USER DATA SOURCES..: /usr/local/etc/odbc.ini

--測試 My SQL ODBC 驅(qū)動(dòng)

$isql test -v(測試前需要將/etc/odbc.ini中的Driver= /usr/lib/libmyodbc3.so 修改成/usr/lib64/libmyodbc3.so，測試完后還原)

isql test oracle manager -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

SQL> quit

--配置 HSODBC 程序
vi $ORACLE_HOME/hs/admin/inittest.ora

HS_FDS_CONNECT_INFO = test
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME=/usr/lib/libmyodbc3.so
set DBCINI=/etc/odbc.ini

--確認(rèn) hsodbc 的配置是否正確，如果正確可以看到版本號(hào)
--注意：如果是64位oracle,由于hsodbc應(yīng)該是只支持32位，這里需要拷貝32位機(jī)器上的hsodbc到$ORACLE_HOME/bin下替換原有的hsodbc

$ hsodbc

Oracle Corporation --- FRIDAY    DEC 06 2012 12:20:44.240

Heterogeneous Agent Release 10.2.0.3.0 - Production  Built with
   Driver for ODBC

--修改監(jiān)聽文件，增加下面服務(wù)：
    (SID_DESC =
      (PROGRAM = hsodbc)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (SID_NAME = test)
      (ENVS=LD_LIBRARY_PATH = /oracle/product/10.2.0/db_1/lib32:/usr/lib:/oracle
/product/10.2.0/db_1/lib)
    ) 

$ vi $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (PROGRAM = hsodbc)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (SID_NAME = test)
      (ENVS=LD_LIBRARY_PATH = /oracle/product/10.2.0/db_1/lib32:/usr/lib:/oracle
/product/10.2.0/db_1/lib)
    ) 
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

--修改tnsnames.ora
$ vi $ORACLE_HOME/network/admin/tnsnames.ora

test =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) 
    )    
    (CONNECT_DATA =
      (SID= test)
    )
    (HS=OK) 
  ) 

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

--重啟監(jiān)聽，要有test服務(wù)
$ lsnrctl stop
$ lsnrctl start

Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

--tnsping測試服務(wù)
$ tnsping test

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =(PROTOCOL = TCP)(HOST = 24.1.20.30)(PORT = 1521))) (CONNECT_DATA = (SID= test)) (HS=OK))
OK (0 msec)

--創(chuàng)建dblink
CREATE PUBLIC DATABASE LINK mysql CONNECT TO "root" IDENTIFIED BY "laopo" USING 'test';

--測試

select * from "emp"@mysql where "id"=1;

至此，單節(jié)點(diǎn)配置完成。

rac環(huán)境下每個(gè)節(jié)點(diǎn)都需要配置，和單實(shí)例唯一的區(qū)別是監(jiān)聽的配置，我試著多種方式去配置監(jiān)聽，但都沒有監(jiān)聽到hsodbc的test服務(wù)，最后通過netca選擇single node configuration創(chuàng)建監(jiān)聽來監(jiān)聽test服務(wù)才成功，注意監(jiān)聽端口改成1522

rac環(huán)境節(jié)點(diǎn)1的監(jiān)聽文件如下：

$vi $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER_RAC01 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/ora10g/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER_RAC01 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac01-vip)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 24.1.20.246)(PORT = 1521)(IP = FIRST))
    )
  )

SID_LIST_LISTENER1 =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM=hsodbc)
      (ORACLE_HOME=/opt/ora10g/product/10.2.0/db_1)
      (SID_NAME=test)
      (ENVS=LD_LIBRARY_PATH=/opt/ora10g/product/10.2.0/db_1/lib32:/lib:/usr/lib:
/opt/ora10g/product/10.2.0/db_1/lib)
    )
  )

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 24.1.20.246)(PORT = 1522))
    )
  )

上述就是小編為大家分享的oracle如何通過dblink連接mysql實(shí)施了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。