折腾了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;
如对本文有疑问， 点击进行留言回复！！
Oracle 基本概念 Dadabase,schema,user,table...
dbeaver连接Oracle中文乱码的解决方案--druid
Oracle常用语句（时间格式、去重、去特殊符号、查询表空间等）
我要评论
网友评论