Oracle使用dblink连接MySQL以及遇到的一些问题
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)
[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的。
暂时告一段落。