rpm -qa |grep mysql-connect
2.如果没有则下载mysql-connector-odbc
wget https://cdn.mysql.com//Downloads/Connector-ODBC/5.3/mysql-connector-odbc-5.3.7-1.el7.x86_64.rpm
3.安装mysql-connector-odbc
rpm -ivh mysql-connector-odbc-5.3.7-1.el7.x86_64.rpm
#4.若涌现libltdl.so is neeed by mysql-connector-odbc缺点，则须要安装下依赖libtool
#yum list .i386|grep libtool
#yum install libtool-ltdl.i386
#
# 若涌现libc.so依赖
# 则须要安装glibc
######查看系统支持的版本
# strings /lib64/libc.so.6 |grep GLIBC_
######
#对付xz格式的解压缩
# tar.xz解压
# xz -d .tar.xz 之后 tar xvf .tar
# tar.xz压缩
# tar cvf .tar good/ 之后 xz -z .tar
# wget http://mirrors.ustc.edu.cn/gnu/libc/glibc-2.14.tar.gz
# tar zxvf glibc-2.14.tar.gz
# cd glibc-2.14
# mkdir build
# cd build
# ../configure --prefix=/usr/local/glibc-2.14
# make -j4
# make install
#
5.编辑oracle所在的打算机上的odbc.ini文件
vi /etc/odbc.ini
在个中输入以下内容:
[test]
#对应mysql-connector-odbc安装下的so文件
Driver=/usr/lib64/libmyodbc3.so
Description=MySQL
#对应的mysql连接ip
Server=192.168.1.1(MySQL Server IP)
#对应mysql的端口号
Port=3306
#对应的mysql用户名
User= (MySQL Username)
UID= (MySQL Username）
#对应的mysql密码
Password= (MySQL PWD)
#对应的mysql数据库名
Database= (MySQL Database Name)
Option=3
Socket=
##############mysql-connector文档参考配置################
#####
[myodbc3]
Driver = /usr/local/lib/libmyodbc3.so
Description = Connector/ODBC 3.51 Driver DSN
SERVER = localhost
PORT =
USER = root
Password =
Database = test
OPTION = 3
SOCKET =
[Default]
Driver = /usr/local/lib/libmyodbc3.so
Description = Connector/ODBC 3.51 Driver DSN
SERVER = localhost
PORT =
USER = root
Password =
Database = test
OPTION = 3
SOCKET =
##########################################################
利用下列命令进行测试，如果可以进入Mysql Client，则证明odbc功能配置正常了。
[root@iZbp1hzuom8ghisagm03qfZ zip]# odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
isql -v test
#6.编辑hs配置文件 vi /ora10g/hs/admin/inittest.ora(把稳文件名中蓝色部分为odbc.ini中蓝色名称)
#HS_FDS_CONNECT_INFO = test
#HS_FDS_TRACE_LEVEL = on（正式利用后，不须要排错的时候应设为off）
#HS_FDS_TRACE_FILE_NAME = test.trc
#HS_FDS_SHAREABLE_NAME=/usr/lib/libmyodbc3.so
#set ODBCINI=/etc/odbc.ini
7.编辑oracle所在打算机上的oracle listener配置文件
vi /oracle/network/admin/listener.ora
加入如下语句:
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = /ora10g)
(PROGRAM = hsodbc)
(ENVS=LD_LIBRARY_PATH=/ora10g/lib32:/usr/lib64:/ora10g/lib)
)
listener.ora文件现在的内容变成：
SID_LIST_LISTENER = (
SID_LIST =
(SID_DESC =
(ORACLE_HOME = /ora10g)
(PROGRAM = extproc)
(GLOBAL_DBNAME=prod)
(SID_NAME=prod)
)
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = /ora10g)
(PROGRAM = hsodbc)
(ENVS=LD_LIBRARY_PATH=/ora10g/lib32:/usr/lib64:/ora10g/lib)
)
)
LISTENER = (
DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) )
)
实行lsnrctl reload使配置生效.
su – oracle
lsnrctl reload
8.编辑oracle所在打算机上的tnsnames.ora文件
vi /ora10g/network/admin/tnsnames.ora
test =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(CONNECT_DATA =
(SID = test)
)
(HS = OK)
)
9.在oracle database建立dblink
create public database link MYSQL
connect to "MYSQL Username" identified by "MySQL PWD"
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT =1521) )
(CONNECT_DATA = (SID= test) )
(HS=OK)
)';
这里的“MySQL username”为mysql用户名，“MySQL PWD”为mysql密码
9.查询mysql中的表名
select from "your tableName"@test
把稳:
your tableName 为mysql表名
test 为oracle配置的SID
1、oracle 10g 64位的hsodbc该当有问题，文件大小为0，实行hsodbc没有任何信息；
办理：拷贝32机器下的hsodbc到$ORACLE_HOME/bin目录下，更换原hsodbc，把稳oracle要有权限，下载：http://space.itpub.net/?uid-28321441-action-viewspace-itemid-750733
2、rac监听不能监听到hsodbc做事；
办理：netca选择single node configuration创建监听来监听hsodbc做事，监听端口选择1522；
3、连接mysql查询数据乱码。
办理：编辑/etc/odbc.ini，增加
charset =gbk
STMT =SET NAMES 'GBK'
个中gbk是MYSQL字符集。
详细履行步骤如下：
--oracle做事器：
-- 操作系统：Red Hat Enterprise Linux 5.3
-- 数据库： Oracle RDBMS 10.2.0.3.0
--mysql做事器：
-- 操作系统：windows server 2003
-- 数据库： MySQL 5.5
单实例操作如下，操作都在oracle做事器上完成：
--检讨须要的rpm包，须要的包如下，由于hsodbc该当是只支持32位，以是32位包是必须的：
libtool-ltdl-1.5.22-6.1.i386.rpm
libtool-ltdl-1.5.22-6.1.x86_64.rpm
mysql-5.0.77-3.el5.i386.rpm
mysql-5.0.77-3.el5.x86_64.rpm
mysql-connector-odbc-3.51.26r1127-1.el5.i386.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.i386.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.11-7.1
unixODBC-2.2.11-7.1
# rpm -qa |grep mysql
mysql-5.0.45-7.el5
mysql-5.0.45-7.el5
mysql-connector-odbc-3.51.26r1127-1.el5
mysql-connector-odbc-3.51.26r1127-1.el5
--编辑/etc/odbc.ini
vi /etc/odbc.ini
#[ODBC Data Sources]
#myodbc3 = MySQL ODBC 3.51 Driver DSN
[test]
Driver = /usr/lib/libmyodbc3.so
Description = MySQL ODBC 3.51 Driver DSN
SERVER = 24.1.20.149
PORT = 3306
USER = root
Password = laopo
Database = teng_push
OPTION = 3
SOCKET =
charset =gbk
STMT =SET NAMES 'GBK'
--末了两个是办理中文乱码问题，gbk是MYSQL字符集
--切换到oracle用户
# su - oracle
--编辑环境变量配置文件，紧张是LD_LIBRARY_PATH和末了面两项
$ vi ~/.bash_profile
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=orcl
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
#export LANG=zh_CN.GB18030
export PATH=.:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:/bin:/usr/bin:/usr/sbin
:/usr/local/bin:/usr/X11R6/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
#ulimit -u 16384 -n 65536
umask 022
export EDITOR=vi
ODBCINI=/etc/odbc.ini; export ODBCINI
ODBCSYSINI=/etc; export ODBCSYSINI
--使配置生效
source ~/.bash_profile
--查看odbc版本及参数文件路径
$odbcinst -j
unixODBC 2.2.11
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
USER DATA SOURCES..: /usr/local/etc/odbc.ini
--测试 My SQL ODBC 驱动
$isql test -v(测试前须要将/etc/odbc.ini中的Driver= /usr/lib/libmyodbc3.so 修正成/usr/lib64/libmyodbc3.so，测试完后还原)
isql test oracle manager -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> quit
--配置 HSODBC 程序
vi $ORACLE_HOME/hs/admin/inittest.ora
HS_FDS_CONNECT_INFO = test
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME=/usr/lib/libmyodbc3.so
set DBCINI=/etc/odbc.ini
--确认 hsodbc 的配置是否精确，如果精确可以看到版本号
--把稳：如果是64位oracle,由于hsodbc该当是只支持32位，这里须要拷贝32位机器上的hsodbc到$ORACLE_HOME/bin下更换原有的hsodbc
$ hsodbc
Oracle Corporation --- FRIDAY DEC 06 2012 12:20:44.240
Heterogeneous Agent Release 10.2.0.3.0 - Production Built with
Driver for ODBC
--修正监听文件，增加下面做事：
(SID_DESC =
(PROGRAM = hsodbc)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
(SID_NAME = test)
(ENVS=LD_LIBRARY_PATH = /oracle/product/10.2.0/db_1/lib32:/usr/lib:/oracle
/product/10.2.0/db_1/lib)
)
$ vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(PROGRAM = hsodbc)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
(SID_NAME = test)
(ENVS=LD_LIBRARY_PATH = /oracle/product/10.2.0/db_1/lib32:/usr/lib:/oracle
/product/10.2.0/db_1/lib)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
--修正tnsnames.ora
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID= test)
)
(HS=OK)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
--重启监听，要有test做事
$ lsnrctl stop
$ lsnrctl start
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
--tnsping测试做事
$ tnsping test
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =(PROTOCOL = TCP)(HOST = 24.1.20.30)(PORT = 1521))) (CONNECT_DATA = (SID= test)) (HS=OK))
OK (0 msec)
--创建dblink
CREATE PUBLIC DATABASE LINK mysql CONNECT TO "root" IDENTIFIED BY "laopo" USING 'test';
--测试
select from "emp"@mysql where "id"=1;
至此，单节点配置完成。
rac环境下每个节点都须要配置，和单实例唯一的差异是监听的配置，我试着多种办法去配置监听，但都没有监听到hsodbc的test做事，末了通过netca选择single node configuration创建监听来监听test做事才成功，把稳监听端口改成1522
rac环境节点1的监听文件如下：
$vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER_RAC01 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/ora10g/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER_RAC01 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac01-vip)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 24.1.20.246)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(PROGRAM=hsodbc)
(ORACLE_HOME=/opt/ora10g/product/10.2.0/db_1)
(SID_NAME=test)
(ENVS=LD_LIBRARY_PATH=/opt/ora10g/product/10.2.0/db_1/lib32:/lib:/usr/lib:
/opt/ora10g/product/10.2.0/db_1/lib)
)
)
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 24.1.20.246)(PORT = 1522))
)
)
