怎么配置Oracle DBlink连接MySQL库

发布时间：2021-09-14 15:17:32 作者：chen
来源：亿速云阅读：151

本篇内容主要讲解“怎么配置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

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库”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！