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] |

有条件的尽量结合跟着我提到的MOS文档看。

先按照文档步骤来，根据需要会做些改动。

步骤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

官网：http://www.unixodbc.org/

文档一句话带过，就让你安装配置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不会报错。）

[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

View Code

步骤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的。

暂时告一段落。

posted @ 2022-12-13 08:42 PiscesCanon 阅读(1472) 评论(0) 编辑收藏举报

刷新页面返回顶部

PiscesCanon

Oracle使用dblink连接MySQL以及遇到的一些问题

Oracle使用dblink连接MySQL以及遇到的一些问题

步骤1.确定[Oracle] and [DG4ODBC]位数

步骤2.安装ODBC Driver Manager

步骤3：安装ODBC Driver

步骤4：配置ODBC数据源

步骤5：验证ODBC连接

步骤6：配置静态监听并重启

步骤7：配置tnsnames.ora

步骤8：配置透明网关的参数文件

步骤9：创建dblink

公告