怎么配置Oracle DBlink连接MySQL库

62次阅读
没有评论

共计 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 网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-28发表,共计8501字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)