Oracle访问mysql透明网关配置笔记

参考文章：Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (文档 ID 1320645.1)

http://blog.itpub.net/26736162/viewspace-2144661/

https://www.cnblogs.com/rangle/p/8967643.html

 

 

 

一、访问拓扑图

+-----------------------------<ODBC Client Host>---------------------------+

| |

| [ORACLE] <---> [DG4ODBC] <---> [ODBC Driver Manager] <---> [ODBC Driver] |

| |

+--------------------------------------------------------------------------+

                     /|\

                       |

                 NETWORK

                        |

                      \|/

       +--<MySQL Server Host>--+

            |                       |

            | [MySQL Server] |

            |                       |

       +-----------------------+

 

二、实施步骤

1、查看DG4ODBC配置

注：默认情况下dg4odbc在安装oracle软件时已经自动安装，无需再额外安装gateway。

 

# su – oracle

$ 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.18, not stripped

 

如上返回结果，代表dg4odbc是64bit配置

 

 

 

2、下载并安装ODBC Driver Manager

 

下载地址：

http://sourceforge.net/projects/unixodbc/files/unixODBC/2.2.14/unixODBC-2.2.14-linux-x86-64.tar.gz/download

 

 

解压软件(root用户执行),解压后会生成usr目录

# cd /software/

# tar -zxvf unixODBC-2.2.14-linux-x86-64.tar.gz

 

 

3、下载并安装ODBC Driver for MySQL

下载地址：

http://dev.mysql.com/downloads/connector/odbc/5.2.html#downloads

 

 

解压软件(root用户执行)

# cd /software/

# tar –zxvf mysql-connector-odbc-5.2.7-linux-el6-x86-64bit.tar.gz

 

更改目录名为mysql(便于书写，无其他作用)

# mv mysql-connector-odbc-5.2.7-linux-el6-x86-64bit mysql

 

更改lib文件权限，让oracle用户可访问

# chown oracle:oinstall /software/mysql/

# chown oracle:oinstall /software/mysql/lib/

# chown oracle:oinstall /software/mysql/lib/lib*

 

 

 

4、配置ODBC Driver(root用户执行)

# vi /etc/odbc.ini

[rmt]

Driver = /software/mysql/lib/libmyodbc5w.so

Description = MySQL ODBC 5.1 Driver DSN

SERVER = 192.168.10.11

PORT = 3306

USER = test

Password = 123456

Database = test

OPTION = 3

 

参数解释：

[rmt] ：代表数据源名字

Driver ：代表驱动文件

SERVER ：代表mysql数据库服务器地址

PORT ：代表mysql数据库端口

USER ：代表mysql数据库用户

Password ：代表mysql数据库用户密码

Database ：代表访问的mysql数据库名，大小写敏感

 

 

 

 

 

 

5、验证ODBC连接(oracle用户执行)

$ export export ODBCINI=/etc/odbc.ini

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/software/usr/local/lib:$LD_LIBRARY_PATH

$ isql rmt -v

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL>

 

 

将ODBCINI、LD_LIBRARY_PATH环境变量写入oracle用户的.bash_profile文件

$ cat ~/.bash_profile

# .bash_profile

 

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

 

# User specific environment and startup programs

 

PATH=$PATH:$HOME/bin

 

export PATH

ORACLE_BASE=/u01/app/oracle

export ORACLE_BASE

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

umask 022

ORACLE_SID=orcl1

PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch

export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH

 

export ODBCINI=/etc/odbc.ini

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/software/usr/local/lib:$LD_LIBRARY_PATH

 

注：linux设置LD_LIBRARY_PATH环境变量，AIX下设置LIBPATH环境变量

6、配置tnsnames.ora(oracle用户执行)

$ cd $ORACLE_HOME/network/admin/

$ cat tnsnames.ora

添加以下内容：

rmt =

  (DESCRIPTION=

    (ADDRESS=

        (PROTOCOL=TCP) (HOST=192.168.10.11) (PORT=1521)

    )

    (CONNECT_DATA=

      (SID=rmt)

    )

    (HS=OK)

)

 

 

注：tnsnames.ora配置文件中的alies(别名)必须和odbc.ini配置文件中的数据源名一致。

 

 

 

7、配置listener.ora文件(单机oracle,rac grid)

$ cat listener.ora

添加以下内容：

SID_LIST_LISTENER=

  (SID_LIST=

    (SID_DESC=

      (SID_NAME=rmt)

      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

      (PROGRAM=dg4odbc)

      (ENVS=LD_LIBRARY_PATH=/software/usr/local/lib:/usr/lib64:/usr/local/lib:/u01/app/oracle/product/11.2.0/db_1/lib:$LD_LIBRARY_PATH)

    )

  )

 

 

 

注：SID_NAME与odbc.ini数据源名一致，为了避免和其它已存在的ODBC Driver Manager冲突，强烈设置LD_LIBRARY_PATH在listener.ora。

 

 

8、创建透明网关参数文件(oracle用户执行)

$ cd $ORACLE_HOME/hs/admin

$ cat initrmt.ora

HS_FDS_CONNECT_INFO=rmt # Data source name in odbc.ini

HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so

HS_FDS_SUPPORT_STATISTICS=FALSE

HS_NLS_NCHAR = UCS2

HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8

HS_FDS_SQLLEN_INTERPRETATION=64

 

# ODBC env variables

set ODBCINI=/etc/odbc.ini

set LD_LIBRARY_PATH=/software/mysql/lib:$LD_LIBRARY_PATH

 

 

 

 

 

注：以上配置的是数据库实例名(数据源名)、odbc lib包，oracle数据库字符集、odbc配置文件路径。

 

 

 

9、静态监听生效(单机oracle,rac grid)

$ lsnrctl reload

$ lsnrctl status

 

Service "rmt" has 1 instance(s).

  Instance "rmt", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

 

 

 

 

10、验证透明网关连通性(oracle用户执行)

$ tnsping rmt

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 20-AUG-2018 09:04:31

 

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

 

Used parameter files:

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=192.168.10.11) (PORT=1521)) (CONNECT_DATA= (SID=rmt)) (HS=OK))

OK (40 msec)

 

 

 

11、创建dblink(oracle用户执行)

$ sqlplus / as sysdba

 

SQL> create public database link mysqltest connect to "test" identified by "123456" using 'rmt';

 

 

 

12、数据访问测试

SQL> select * from "t1"@mysqltest;

 

        id

----------

         1

 

SQL>

 

 

 

注：oracle通过透明网关访问mysql只能通过dblink方式进行数据访问。

 

 

 

附录：错误信息及处理方法

1、错误01

SQL> select * from t1@mysqltest;

select * from t1@mysqltest

*

ERROR at line 1:

ORA-00942: table or view does not exist

[MySQL][ODBC 5.2(w) Driver][mysqld-5.1.71]Table 'test.T1' doesn't exist

{42S02,NativeErr = 1146}

ORA-02063: preceding 2 lines from MYSQLTEST

SQL>

 

错误原因以及处理方法：执行的查询操作，表名需要带双引号，因为mysql默认表名是区分大小写，而oracle是不区分大小写的(oracle默认自动转换成大写)。

 

 

 

2、错误02

SQL> select * from t1@mysqltest;

select * from t1@mysqltest;

                  *

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

ORA-02063: preceding line from MYSQLTEST

 

错误原因以及处理方法：hs/admin/init[sid].ora里配置的HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so不正确，应该是odbc的Lib包

 

 

 

3、错误03

SQL> select * from t1@mysqltest;

select * from t1@mysqltest;

                  *

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[

 

 

错误原因以及处理方法：hs/admin/init[sid].ora里配置的HS_LANGUAGE=AMERICAN_AMERICA.zhs16gbk字符集不正确，应该是oracle数据库字符集，

并且需要注意mysql数据库的数据库、表、列字符集;

需在hs/admin/init[sid].ora配置文件调试以下两个参数

HS_NLS_NCHAR = UCS2

HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8

 

 

 

 

 

 

 

 

4、错误04,oracle访问mysql字符乱码问题

若访问数据依然存在中文乱码，字符乱码等问题，可尝试进行字符集转换进行访问，需要注意访问mysql的表、列需要使用双引号严格区分大小写。

 

select "badgenumber","NAME","date","time",CONVERT("checktime", 'AL32UTF8', 'UTF8' )"checktime",CONVERT("created_date", 'AL32UTF8', 'UTF8' )"created_date" from "user_checkinout_v"@mysql;