欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle10g通过DBLink访问MySQL示例

程序员文章站 2023-11-22 15:24:34
1, 首先在oracle所在计算机安装mysql的client端软件,并且x86_64和i386版本的都需要安装,以便可以连接mysql数据库。查看client安装情况:复...

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, 2007, 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", status unknown, 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