共计 8501 个字符，预计需要花费 22 分钟才能阅读完成。

本篇内容主要讲解“怎么配置 Oracle DBlink 连接 MySQL 库”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“怎么配置 Oracle DBlink 连接 MySQL 库”吧!

一
背景描述

某客户业务需求，需要在 Oracle 数据库上通过网络连接获取 MySQL 数据库中业务数据。现针对该需求，配置 Oracle 连接至 MySQL 库的 dblink。

二
配置 Oracle DBlink2.1
确认 [Oracle] 和[DG4ODBC]位数

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 位组件

2.2
下载及安装 ODBC Driver Manager

ODBC 驱动管理器介质下载地址：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 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 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！