天达云 科技型企业 | 亚太互联网络信息中心(APNIC)成员 | 注册免费体验
天达云首页 > 互联网学习教程 > 关系型数据库 > 怎么配置Oracle DBlink连接MySQL库

怎么配置Oracle DBlink连接MySQL库
更新：HHH   时间：2023-1-7

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

一 背景描述

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

二 配置Oracle DBlink

2.1 确认[Oracle]和[DG4ODBC]位数

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

通过检查确认，[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库”有了更深的了解，不妨来实际操作一番吧！这里是天达云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
返回关系型数据库教程...
400-837-6568
7 * 24小时全天全国服务热线400电话
天达云·专业的云计算服务平台 全国400服务电话：400-837-6568
Copyright © 2011-2025 Tdyun.com. All Rights Reserved. 天达云 版权所有 粤ICP备18083267号-7