Oracle通过ODBC连接MySql

折腾了oracle到mysql的dblink访问，分享给大家：

要连接的两端：

oracle rhel 6.5 64位 11.2.0.4

mysql 64 5.5 utf8

操作都在oracle服务器上完成：

-----------------------------------------------------------------

--检查需要的rpm包，需要的包如下：

libtool-ltdl-1.5.22-6.1.x86_64.rpm

mysql-5.0.77-3.el5.x86_64.rpm

mysql-connector-odbc-3.51.26r1127-1.el5.x86_64.rpm

perl-dbi-1.52-2.el5.x86_64.rpm

unixodbc-2.2.11-7.1.x86_64.rpm

上面unixodbc-2.2.11-7.1、mysql-5.0.77-3.el5、mysql-connector-odbc是需要的包，其他是安装这些包的前提。

---------------------------------------------------------------

安装完后检查

# rpm -qa |grep unixodbc

unixodbc-2.2.14-12.el6_3.x86_64

unixodbc-devel-2.2.14-12.el6_3.x86_64

# rpm -qa |grep mysql

qt-mysql-4.6.2-26.el6_4.x86_64

mysql-server-5.1.71-1.el6.x86_64

mysql-libs-5.1.71-1.el6.x86_64

mysql-5.1.71-1.el6.x86_64

mysql-connector-odbc-5.1.5r1144-7.el6.x86_64

mysql-devel-5.1.71-1.el6.x86_64

--编辑/etc/odbc.ini

vi /etc/odbc.ini

[myodbc]

driver = /usr/lib64/libmyodbc5.so

description = mysql odbc 5.1 driver dsn

server = 192.168.0.2

port = 3306

user = my_sql

password = my_sql

database = my_db

option = 3

socket =

#charset = gbk

--切换到oracle用户

# su - oracle

--编辑环境变量配置文件，主要是ld_library_path和最后面两项

$ vi ~/.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

export oracle_base=/u01/app/oracle

export oracle_home=$oracle_base/product/11.2.0/dbhome_1

export oracle_sid=nop

export ld_library_path=$oracle_home/lib

export ld_library_path=$ld_library_path:$oracle_home/hs/lib

export ld_library_path=$ld_library_path:/usr/lib:/usr/lib64

export ld_library_path=$ld_library_path:$oracle_home/jkd/jre/lib/i386

export ld_library_path=$ld_library_path:$oracle_home/jkd/jre/lib/i386/server

export ld_library_path=$ld_library_path:$oracle_home/rdbms/lib

export class_path=$oracle_home/jre:$oracle_home/jlib

export class_path=$calss_path:$oracle_home/rdbms/jlib

export class_path=$calss_path:$oracle_home/network/jlib

export tns_admin=$oracle_home/network/admin

export nls_lang=american_america.al32utf8

export oracle_term=xterm

export editor=vi

export path=$oracle_home/bin:$path

export lang=en_us

odbcini=/etc/odbc.ini; export odbcini

odbcsysini=/etc; export odbcsysini

odbcinstini=/etc/odbc.ini

export odbcinstini

--使配置生效

source ~/.bash_profile

--查看odbc版本及参数文件路径

$odbcinst -j

unixodbc 2.2.14

drivers............: /etc/odbcinst.ini

system data sources: /etc/odbc.ini

file data sources..: /etc/odbcdatasources

user data sources..: /etc/odbc.ini

sqlulen size.......: 8

sqllen size........: 8

sqlsetposirow size.: 8

--测试 my sql odbc 驱动

$isql myodbc

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

| connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

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

sql> quit

--配置 hsodbc 程序

vi $oracle_home/hs/admin/initmyodbc.ora

hs_fds_connect_info = myodbc

hs_fds_trace_level = debug

hs_fds_trace_level = 4

hs_fds_shareable_name = /usr/lib64/libmyodbc5.so

hs_language=american_america.utf8 --此处要跟目标mysql的字符集一致

hs_nls_nchar = ucs2

hs_idle_timeout =1440

hs_long_piece_transfer_size=1258291

hs_fds_sqllen_interpretation=64 --此处要跟目标mysql的位数一致

set odbcini = /etc/odbc.ini

--配置监听

$ cd $tns_admin

$ ls

listener.ora samples shrept.lst tnsnames.ora

$ vim listener.ora

# listener.ora network configuration file: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# generated by oracle configuration tools.

listener =

(description_list =

(description =

(address = (protocol = tcp)(host = mypc)(port = 1521))

(address = (protocol = ipc)(key = extproc1521))

)

)

sid_list_listener=

(sid_list=

(sid_desc=

(program = dg4odbc)

(sid_name= myodbc)

(oracle_home= /u01/app/oracle/product/11.2.0/dbhome_1)

(envs=ld_library_path=/u01/app/oracle/product/11.2.0/dbhome_1/lib:/u01/app/oracle/product/11.2.0/dbhome_1/odbc/lib:/usr/lib:/usr/local/lib:/u01/app/oracle/product/11.2.0/dbhome_1/hs/lib:/usr/lib64)

)

)

adr_base_listener = /u01/app/oracle

vim tnsnames.ora

添加：

myodbc =

(description =

(address_list =

(address = (protocol = tcp)(host = mypc)(port = 1521))

)

(connect_data =

(sid = myodbc)

)

(hs = ok)

)

--重启监听，要有 myodbc 服务

$ lsnrctl stop

$ lsnrctl start

--tnsping测试服务

$ tnsping myodbc

tns ping utility for linux: version 11.2.0.4.0 - production on

copyright (c) 1997, 2013, oracle. all rights reserved.

used parameter files:

used tnsnames adapter to resolve the alias

attempting to contact (description = (address_list = (address = (protocol = tcp)(host = mypc)(port = 1521))) (connect_data = (sid = myodbc)) (hs = ok))

ok (0 msec)

--创建dblink

create public database link mysql connect to "my_sql" identified by "my_sql" using 'myodbc';

--测试

select * from "my_tab"@mysql;