Linux下ORACLE 使用dblink连接mysql

1、切换到ORACLE 账号

      su - oracle

2、查看DG4ODBC版本 ，因为32位和64位的配置不一样，64位更复杂一些，因此我们首先得确定Oracle和DG4ODBC是32位还是64位：

    file $ORACLE_HOME/bin/dg4odbc

    /data/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]=cd4f5a9e70d4d0b8238d37dd9e13416a615062c0, not stripped

   从上面的输出可知是64位。     

3、下载unixODBC-2.3.0.tar.gz  64位版本 

      下载地址：http://www.unixodbc.org/download.html

4、将 unixODBC-2.3.0.tar.gz 上传到home/oracle 目录下面

      解压ODBC安装包

      tar -zxvf unixODBC-2.3.0.tar.gz    

5、安装unixODBC-2.3.0.tar.gz

     ./configure --prefix=/usr/local/unixODBC-2.3.7 --includedir=/usr/include --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etc

6、安装成功后测试

     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

6、下载mysql的odbc 驱动，下载地址

      https://dev.mysql.com/downloads/file/?id=498680

      对应安装包：mysql-connector-odbc-5.3.14-1.el7.x86_64.rpm

7、用root 账号安装odbc 驱动   

     rpm -ivh mysql-connector-odbc-5.3.14-1.el7.x86_64.rpm

8、在etc目录下创建odbc.ini

      [myodbc5]  
Driver = /usr/lib64/libmyodbc5w.so 
Description = Connector/ODBC 5.2 Driver DSN  
SERVER = *
PORT = 3306  
USER = *  
PASSWORD = *  
DATABASE = *  
OPTION = 0  
TRACE = OFF

9、验证连接是否成功

     isql myodbc5 -v   

   +---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

10、 配置ORACLE监听器

       在oracle安装目录下/product/11.2.0/db_1/network/admin 下的tnsnames.ora 增加

      myodbc5 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.98.102)(PORT = 1521))
    (CONNECT_DATA =
      (SID=myodbc5)
    )
      (HS=OK)
  )

  配置listener.ora  增加红色部分监听

 SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_NAME = wmstest)
      (ORACLE_HOME =/data/oracle/product/11.2.0/db_1)
      (SID_NAME = wmstest)
    )
 (SID_DESC=
      (SID_NAME=myodbc5)
      (ORACLE_HOME=/data/oracle/product/11.2.0/db_1)
      (PROGRAM=dg4odbc)
      (ENVS=LD_LIBRARY_PATH=/usr/lib:/data/oracle/product/11.2.0/db_1/lib)
    )
  )

11、/product/11.2.0/db_1/network/hs/admin/  增加 initmyodbc5.ora文件

HS_FDS_CONNECT_INFO=myodbc5 # Data source  name   in  odbc.ini  
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS= FALSE
HS_LANGUAGE=AMERICAN_AMERICA.UTF8
HS_NLS_NCHAR=UCS2  
# ODBC env variables  
set  ODBCINI=/etc/odbc.ini 

 

12、重新加载监听器

       lsnrctl reload

       lsnrctl status

     Service "myodbc5" has 1 instance(s).
     Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...

     tnsping myodbc5

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 15-JAN-2021 17:52:20

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/data/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
 )HS=OK)odbc5) =TOCOL = TCP)(HOST = 192.168.98.102)(PORT = 1521))
OK (0 msec)
 

13、创建dblink 

create public database link mysqltest connect to "*" identified by "*" using 'myodbc5';