折腾了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;