一 背景描述
某客户业务需求，需要在 Oracle 数据库上通过网络连接获取 MySQL 数据库中业务数据。现针对该需求，配置 Oracle 连接至 MySQL 库的 dblink 。
二 配置 Oracle DBlink
确认 [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 位组件
下载及安装 ODBC Driver Manager
ODBC 驱动管理器介质下载地址： 为了方便测试，我这里直接调用操作系统自带的 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 |
下载及安装 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/ |
配置 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> |
配置 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 |
配置 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) |
配置网关参数文件 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 |
创建 Oracle DBlink
|
SQL> create public database link myodbc5 connect to "backup" identified by "mysql" using 'myodbc5'; |
验证 Oracle DBlink
|
SQL> select count(*) from "test"@myodbc5; COUNT(*) ---------- 1835008 |
参考文档
配置 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)
