Oracle到MySQL的dblink

环境说明：

DB	version	IP	OS
Oracle	12.1.0.1	10.5.28.134	Redhat 7.1
MySQL	5.7.22	10.5.94.208	Redhat 7.1

 

步骤：

1.判断dg4odbc是32位还是64位

[oracle@dhr ~]$ file $ORACLE_HOME/bin/dg4odbc

/u01/app/oracle/product/12.1.0/dbhome_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]=4675eeb874fe889b960a29d3d084f9c1f4c43107, not stripped

 

2.下载并安装ODBC Driver Manager

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

   解压并编译安装

   tar -zxvf unixODBC-2.3.tar .gz

   cd unixodbc-2.3

   ./configure --prefix=$HOME/unixodbc

   make && make install

 

    配置环境变量

     vi .bash_profile

      export PATH=$PATH:/home/oracle/unixodbc/bin

      export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/oracle/unixodbc/lib

    

    source  .bash_profile

    

   验证是否安装成功

   odbcinst -j

 

3.下载[MySQL Connector/ODBC driver]

  下载地址：https://dev.mysql.com/downloads/connector/odbc/下载mysql odbc的驱动包

  

     解压缩文件后，将文件夹重命名

      tar -zxvf mysql-connector-odbc-8.0.18-linux-glibc2.12-x86-64bit.tar.gz

      mv mysql-connector-odbc-8.0.18-linux-glibc2.12-x86-64bit.tar.gz  my5

 

4. 配置odbc.ini

[oracle@dhr ~]$ cat  /home/oracle/unixodbc/etc/odbc.ini
[message]   
Driver = /home/oracle/my5/lib/libmyodbc8a.so
SERVER = 10.5.94.208         #mysql服务器ip
PORT =  3307                      #mysql数据库端口
USER = lgsl                         #mysql数据库用户名
PASSWORD = lgsladmin     #mysql数据库密码
DATABASE = message        #mysql数据库
OPTION = 0
TRACE =  OFF

 

    验证ODBC是否配置成功

     isql message -v

 

 

5. 配置 listener.ora

 

在$ORACLE_HOME/network/admin/listener.ora中添加如下信息

     

LISTENER2 =

  (DESCRIPTION_LIST =

     (DESCRIPTION =

        (ADDRESS=(PROTOCOL = TCP)(HOST = 10.5.28.134 )(PORT = 1524))

      )

    )

SID_LIST_LISTENER2 =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = message)
     (ORACLE_HOME =/home/oracle/app/oracle/product/12.1.0)
     (PROGRAM = dg4odbc)
     (ENVS=LD_LIBRARY_PATH=$ORACLE_HOME/lib:/home/oracle/unixodbc/lib)
    )
  ) 

 

 

启动LISTENER2的监听

lsnrctl start LISTENTER2

lsnrctl status LISTENER2

 

6. 配置 tnsnames.ora

在$ORACLE_HOME/network/admin/tnsnames.ora后面添加如下信息

message =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 10.5.28.134)(PORT = 1524))
   (CONNECT_DATA =
     (SERVICE_NAME = message))
     (HS=OK)
    )
  )

 

  使用tnsping message看是否可以ping通

 

7. 配置监听的初始化文件

   cd $ORACLE_HOME/hs/admin/

   cp initdg4odbc.ora initmessage.ora

   cat initmessage.ora

HS_FDS_CONNECT_INFO = message
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /home/oracle/unixodbc/lib/libodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
HS_NLS_NCHAR=UCS2
set ODBCINI= /home/oracle/unixodbc/etc/odbc.ini

 

8. 创建dblink,并验证

create public database link db_test connect to "lgsl" identified by "lgsladmin" using 'message';

Select * from dual@db_test;