Oracle使用dblink连接MySQL以及遇到的一些问题
防爬虫:https://www.cnblogs.com/PiscesCanon/p/16977636.html
主要参考了mos文档:Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (文档 ID 1320645.1)
但实际上文档个人觉得还不算特别详细,而且unixODBC实际是可以不用编译安装的,减少改动风险。
其中,涉及了一些概念:| [ORACLE] <---> [DG4ODBC] <---> [ODBC Driver Manager] <---> [ODBC Driver] |
先按照文档步骤来,根据需要会做些改动。
步骤1.确定[Oracle] and [DG4ODBC]位数
[oracle@qadb ~]$ file $ORACLE_HOME/bin/dg4odbc
/u01/app/oracle/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.32, BuildID[sha1]=8f5a341e1cb474a4aabc76f1c3ba26d70be2d2a5, not stripped
现在应该基本都是64位了吧。
步骤2.安装ODBC Driver Manager
文档一句话带过,就让你安装配置ODBC Driver Manager。
目前下载最新安装包为:unixODBC-2.3.11.tar.gz,跟Nginx,openssh之类的一样是需要编译安装的,
但是实际上Oracle服务器,比如11G版本的Oracle是要求安装:传送门,引用关键信息如下:
-
On Oracle Linux 6, Red Hat Enterprise Linux 6, and Asianux Server 4:
unixODBC-2.2.14-11.el6 (x86_64) or later unixODBC-2.2.14-11.el6.i686 or later unixODBC-devel-2.2.14-11.el6 (x86_64) or later unixODBC-devel-2.2.14-11.el6.i686 or later
-
On Oracle Linux 7, and Red Hat Enterprise Linux 7:
unixODBC-2.3.1-6.el7.x86_64 or later unixODBC-2.3.1-6.el7.i686 or later unixODBC-devel-2.3.1-6.el7.x86_64 or later unixODBC-devel-2.3.1-6.el7.i686 or later
因此,ODBC Driver Manager在一台正常运行的Oracle服务器上已经默认安装好了的,无需通过编译源码安装的方式增加风险。
[oracle@qadb ~]$ rpm -qa | grep unix
unixODBC-2.3.1-14.0.1.el7.x86_64
unixODBC-devel-2.3.1-14.0.1.el7.x86_64
unixODBC-devel-2.3.1-14.0.1.el7.i686
unixODBC-2.3.1-14.0.1.el7.i686
查看相关配置:
[oracle@qadb ~]$ odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/oracle/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
步骤3:安装ODBC Driver
方式1:rpm方式
官网:
http://repo.mysql.com/yum/mysql-connectors-community/el/7/x86_64/
https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc-8.0.31-1.el7.x86_64.rpm
[root@qadb soft]# rpm -ivh mysql-connector-odbc-8.0.31-1.el7.x86_64.rpm
warning: mysql-connector-odbc-8.0.31-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-connector-odbc-8.0.31-1.el7################################# [100%]
Success: Usage count is 1
Success: Usage count is 1
方式2(有问题):下载tar.gz包解压安装,看mos文档和一些网上的资料是解压后不需要编译安装,但是实际上会有一些问题,暂时没解决。
官网:http://dev.mysql.com/downloads/connector/odbc/#downloads
下载上传后解压重命名:
[oracle@qadb soft]$ tar -zxvf mysql-connector-odbc-8.0.31-linux-glibc2.27-x86-64bit.tar.gz
[oracle@qadb soft]$ mv mysql-connector-odbc-8.0.31-linux-glibc2.27-x86-64bit mysql-connector-odbc-8.0.31
在步骤5后会出现如下故障:
(怀疑是版本问题,后边换成mysql-connector-odbc-5.3.14-linux-glibc2.12-x86-32bit.tar.gz之后isql还是报一样的问题。只不过ldd不会报错。)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
[root@qadb lib]# export ODBCINI=/etc/odbc.ini
[root@qadb lib]# export LD_LIBRARY_PATH=/soft/mysql-connector-odbc-8.0.31/lib:$LD_LIBRARY_PATH
[root@qadb lib]# isql mysqldb -v
[01000][unixODBC][Driver Manager]Can't open lib '/soft/mysql-connector-odbc-8.0.31/lib/libmyodbc8w.so' : file not found
[ISQL]ERROR: Could not SQLConnect
[root@qadb lib]# ll /soft/mysql-connector-odbc-8.0.31/lib/libmyodbc8w.so
-rw-r--r-- 1 oracle oinstall 15541312 Sep 2 00:02 /soft/mysql-connector-odbc-8.0.31/lib/libmyodbc8w.so
[root@qadb lib]# ldd libmyodbc8w.so
ldd: warning: you do not have execution permission for `./libmyodbc8w.so'
./libmyodbc8w.so: /lib64/libstdc++.so.6: version `GLIBCXX_3.4.20' not found (required by ./libmyodbc8w.so)
./libmyodbc8w.so: /lib64/libstdc++.so.6: version `CXXABI_1.3.8' not found (required by ./libmyodbc8w.so)
./libmyodbc8w.so: /lib64/libstdc++.so.6: version `CXXABI_1.3.9' not found (required by ./libmyodbc8w.so)
./libmyodbc8w.so: /lib64/libstdc++.so.6: version `GLIBCXX_3.4.21' not found (required by ./libmyodbc8w.so)
./libmyodbc8w.so: /lib64/libc.so.6: version `GLIBC_2.25' not found (required by /soft/mysql-connector-odbc-8.0.31/lib/./private/libcrypto.so.1.1)
linux-vdso.so.1 => (0x00007ffc303fb000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007ff3f47d1000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007ff3f45cd000)
libcrypto.so.1.1 => /soft/mysql-connector-odbc-8.0.31/lib/./private/libcrypto.so.1.1 (0x00007ff3f40fd000)
libssl.so.1.1 => /soft/mysql-connector-odbc-8.0.31/lib/./private/libssl.so.1.1 (0x00007ff3f3e69000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007ff3f3c4f000)
libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007ff3f3a3d000)
libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007ff3f3735000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007ff3f351f000)
libc.so.6 => /lib64/libc.so.6 (0x00007ff3f3151000)
/lib64/ld-linux-x86-64.so.2 (0x00007ff3f53d6000)
libltdl.so.7 => /lib64/libltdl.so.7 (0x00007ff3f2f47000)
libm.so.6 => /lib64/libm.so.6 (0x00007ff3f2c45000)
[root@qadb lib]# strings /lib64/libstdc++.so.6 | grep GLIBC
GLIBCXX_3.4
GLIBCXX_3.4.1
GLIBCXX_3.4.2
GLIBCXX_3.4.3
GLIBCXX_3.4.4
GLIBCXX_3.4.5
GLIBCXX_3.4.6
GLIBCXX_3.4.7
GLIBCXX_3.4.8
GLIBCXX_3.4.9
GLIBCXX_3.4.10
GLIBCXX_3.4.11
GLIBCXX_3.4.12
GLIBCXX_3.4.13
GLIBCXX_3.4.14
GLIBCXX_3.4.15
GLIBCXX_3.4.16
GLIBCXX_3.4.17
GLIBCXX_3.4.18
GLIBCXX_3.4.19
GLIBC_2.3
GLIBC_2.2.5
GLIBC_2.14
GLIBC_2.4
GLIBC_2.3.2
GLIBCXX_DEBUG_MESSAGE_LENGTH
步骤4:配置ODBC数据源
[root@qadb ~]# vi /etc/odbc.ini
[mysqldb]
Driver = /usr/lib64/libmyodbc8w.so
Description = MySQL ODBC 8.0 Unicode Driver
SERVER = 192.168.1.124
PORT = 3306
USER = root
PASSWORD = root
DATABASE = cbepcapture
OPTION = 0
TRACE = OFF
步骤5:验证ODBC连接
[root@qadb soft]# isql mysqldb -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> show databases;
+-----------------------------------------------------------------+
| Database |
+-----------------------------------------------------------------+
| cbepcapture |
| information_schema |
| mysql |
| performance_schema |
| sys |
+-----------------------------------------------------------------+
SQLRowCount returns 5
5 rows fetched
步骤6:配置静态监听并重启
[oracle@qadb ~]$ vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=mysql124)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(PROGRAM=dg4odbc)
)
)
[oracle@qadb admin]$ lsnrctl stop
[oracle@qadb admin]$ lsnrctl start
......
Service "mysql124" has 1 instance(s).
Instance "mysql124", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
官方强烈建议添加(ENV="LD_LIBRARY_PATH=/home/dbs/app/unixodbc-2.2.14/lib:/home/dbs/app/Ora/product/11.2.0/dbhome_1/lib"),不过我是使用rpm方式的ODBC driver manager and the ODBC driver,这里就不加了。
这里的mysql124随意指定,可以认为是表示service_name,也可以理解为SID也行,指代远程将要访问的mysql的映射。
步骤7:配置tnsnames.ora
[oracle@qadb admin]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
mysql124 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.152)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mysql124)
)
(HS=OK)
)
[oracle@qadb admin]$ tnsping mysql124
......
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.152)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = mysql124)) (HS=OK))
OK (0 msec)
其中:
第一个的mysql124表示tnsname的名称,随意命名,
第二个也就是SERVICE_NAME的值必须等于步骤6中SID_NAME的值,就是mysql124。
(HS=OK):表示为异构数据库。
(HS=OK)必须指定,不然会报错:
ERROR at line 1:
ORA-28546: connection initialization failed, probable Net8 admin error
ORA-02063: preceding line from MYSQL124
(HS=OK)不能包含在CONNECT_DATA里边,不然会报错:
ERROR at line 1:
ORA-28546: connection initialization failed, probable Net8 admin error
ORA-02063: preceding line from MYSQL124
参考:How to Resolve Common Errors Encountered while using Database Gateways (DG4IFMX, Dg4MSQL, DG4SYBS), DG4ODBC or Generic Connectivity (文档 ID 234517.1)
步骤8:配置透明网关的参数文件
[oracle@qadb admin]$ vi $ORACLE_HOME/hs/admin/initmysql124.ora
HS_FDS_CONNECT_INFO=mysqldb
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
HS_NLS_NCHAR=UCS2
HS_FDS_CONNECT_INFO的值是odbc.ini中的DSN name,就是[]中的名字。
文件名格式为init<sid>.ora,sid必须为步骤6中SID_NAME的值,也就是mysql124。
必须添加HS_NLS_NCHAR=UCS2,不然会有如下报错:
参考ORA-28500: Connection From ORACLE To A Non-Oracle System Returned This Message: C (文档 ID 2325424.1),跟字符集和语言设置相关,虽然报错不太像但是解决了。
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[
步骤9:创建dblink
10:04:37 SYS@qadb(37)> create public database link mysql124 connect to "root" identified by "root" using 'mysql124';
Database link created.
Elapsed: 00:00:00.03
10:09:55 SYS@qadb(1201)> select count(*) from cbep_inventory_body@mysql124;
COUNT(*)
----------
115470
Elapsed: 00:00:02.00
其中,用户密码是mysql的。
暂时告一段落。