1,首先在Oracle所在计算机安装MySQL的Client端软件,并且x86_64和i386版本的都需要安装,以便可以连接MySQL数据库。查看Client安装情况: 代码如下:rpm -qa |grep mysql mysql-5.0.45-7.el5 mysql-5.0.45-7.el5
得到两条记录,一条是x86_64的,一条是i386的。 如果看到还没有安装mysql客户端软件,则需要安装: 代码如下:yum install mysql yum install mysql.i386
验证在此Oracle所在计算机是可以连接目标主机MySQL数据库: 代码如下:mysql -h 192.168.1.1 -u root -p mysql
2,检查Oracle所在计算机是否已安装MySQL ODBC客户端,并且x86_64和i386版本的都需要安装。 代码如下:rpm -qa |grep mysql-connect
如果没有安装mysql-connector-odbc,则用下面命令下载和安装: 下载 mysql-connector-odbc: 代码如下:wget ftp://mirror.switch.ch/pool/3/mirror/centos/5.2/os/i386/CentO S/mysql-connector-odbc-3.51.12-2.2.i386.rpm
安装mysql-connector-odbc: 代码如下:rpm -ivh mysql-connector-odbc-3.51.12-2.2.i386.rpm
得到提示 代码如下:libltdl.so.3 is needed by mysql-connector-odbc-3.51.12-2.2.i386 ,发现需要安装libtool的i386版本,因此通过如下命令安装libtool-ltdl.i386: yum list *.i386|grep libtool yum install libtool-ltdl.i386
3,在Oracle所在计算机编辑/etc/odbc.ini文件,测试ODBC工作 代码如下:vi /etc/odbc.ini
#odbc.ini内容如下 代码如下:[test] Driver=/usr/lib64/libmyodbc3.so Description=MySQL Server=192.168.1.1(MySQL Server IP) Port=3306 User= (MySQL Username) UID= (MySQL Username) Password= (MySQL PWD) Database= (MySQL Database Name) Option=3 Socket=
在命令行中执行下列命令,应能够顺利登入MySQL Client窗口,即证明ODBC功能正常: 代码如下:isql -v test quit
4,编辑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
5,编辑Oracle所在计算机的Oracle listener的配置文件,建立一个模拟Oracle Listener的监听方式,为将来建立dblink做准备:
vi /ora10g/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使Listener生效:
代码如下:su – oracle lsnrctl reload LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 09-FEB-2009 13:59:38 Copyright (c) 1991,2007,Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) The command completed successfully lsnrctl status LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 12-FEB-2009 08:56:00
Copyright (c) 1991,Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production Start Date 03-JAN-2009 03:47:39 Uptime 40 days 5 hr. 8 min. 20 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /ora10g/network/admin/listener.ora Listener Log File /ora10g/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc",status UNKNOWN,has 1 handler(s) for this service... Service "test" has 1 instance(s). Instance "test",has 1 handler(s) for this service... The command completed successfully
6,编辑Oracle所在计算机中的tnsnames.ora文件,便于建立dblink。注意,此tnsnames的配置可以支持tnsping,但是不能支持sqlplus登录,只用于dblink: 代码如下:vi /ora10g/network/admin/tnsnames.ora test = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA = (SID = test) ) (HS = OK) )
7,在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) )';
要注意用户名和密码处需要用双引号,否则Oracle所传输的都是大写字母,可能无法登录进入MySQL。
8,由于MySQL中的表名的大小写敏感,因此需要在进行SQL查询时对表名用双引号扩起来 代码如下:select * from "tablename"@test
(编辑:鄂州站长网)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|