Oracle10g通过DBLink访问MySQL示例

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

前阵子在商店经过Oracle访谈MySQL,测量试验意况:CentOS5_X64, Oracle10g_X64,
MySQL5 。
把一些资历分享给大家!

一、环境
OS:Linux myhostname 2.6.9-42.ELsmp #1 SMP Sat Aug 12 09:39:11 CDT 2006
i686 i686 i386 GNU/Linux
CentOS release 4.4 (Final)
Oracle:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 –
Prod,数据库已经设置好
mysql:5.1.34-community for windows

得到两条记下,一条是x86_64的,一条是i386的。假设看见还尚无设置mysql客户端软件,则需求设置:复制代码 代码如下:yum install mysql yum install
mysql.i386 验证在这里Oracle所在计算机是足以连接目的主机MySQL数据库:复制代码 代码如下:mysql -h 192.168.1.1 -u root
-p mysql

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所在Computer是不是已设置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

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

  1. 安装unixODBC,用root用户
    rpm -Uvh unixODBC-2.2.12-1.el4s1.1.i386.rpm
  2. 安装mysql ODBC,用root用户
    rpm -Uvh mysql-connector-odbc-5.1.5-0.i386.rpm
  3. 安装oracle gateway,用oracle用户
    本身装的是10201_gateways_linux32.zip
    unzip 10201_gateways_linux32.zip
    cd gateways
    ./runInstaller
    设置方式和oracle db
    软件同样,笔者把gateway和db装一齐了,共用一个OracleHOME
  4. 配置/etc/odbc.ini
    [DSName]
    Driver =/usr/lib/libmyodbc5.so
    Description =MySQL
    Server =xxx.xxx.xxx.xxx
    Port =3306
    User =root
    UID =root
    Password = mypass
    Database =mysqldbname
    Option =3
    Socket =
    charset =utf8
    测试ODBC
    isql -v DSName root mypass
  5. 配置$ORACLE_HOME/hs/admin/initDSName.ora
    HS_FDS_CONNECT_INFO = DSName
    HS_FDS_TRACE_LEVEL = 0
    HS_FDS_SHAREABLE_NAME = /usr/lib/libmyodbc5.so
  6. 配备listener.ora,加黄铜色部分
    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /usr/u01/app/oracle/product/10.2.0/db_1)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = BOSS)
    (ORACLE_HOME = /usr/u01/app/oracle/product/10.2.0/db_1)
    (SID_NAME = BOSS)
    )
    (SID_DESC =
    (SID_NAME = phpcms)
    (ORACLE_HOME = /usr/u01/app/oracle/product/10.2.0/db_1)
    (PROGRAM = hsodbc)
    )
    )
  7. 配置tnsnames.ora,添加
    DSName =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.125)(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = DSName))
    (HS = OK)
    )
  8. 重启监听器并测量试验
    lsnrctl reload
    lsnrctl service
    Service “DSName” has 1 instance(s).
    Instance “DSName”, status UNKNOWN, has 1 handler(s) for this
    service…
    Handler(s):
    “DEDICATED” established:3 refused:0
    LOCAL SERVER
    The command completed successfully
    tnsping DSName
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
    (PROTOCOL = TCP)(HOST = 192.168.0.125)(PORT = 1521))) (CONNECT_DATA =
    (SERVICE_NAME = DSName)) (HS = OK))
    OK (0 msec)
  9. 建立dblink
    CREATE PUBLIC DATABASE LINK linkname
    CONNECT TO “root”
    IDENTIFIED BY <PWD>
    USING ‘DSName’;
  10. 测试
    select “name” from t1@linkname;
    三、遗留难点
  11. 字符集难题,最棒oracle和mysql是utf8,否则中文有毛病
  12. text字段会报错:
    select “textcol” from t1@linkname;
    ORA-28500: 连接 ORACLE 到非 Oracle 系统时重回此新闻:
    [Generic Connectivity Using ODBC][MySQL][ODBC 5.1
    Driver][mysqld-5.1.34-community]You have an error in your SQL syntax;
    check the manual that corresponds to your MySQL server version for the
    right syntax to use near ‘”t1″ WHERE “id”=1’ at line 1 (SQL State:
    37000; SQL Code: 1064)
    ORA-02063: 紧接着 2 lines (起自 DSName)
    自然想用datadirect的mysql ODBC试试,可只帮忙mysql
    enterprise版本,实在倒霉找,今后有空子再说吧。

3, 在Oracle所在微型机编辑/etc/odbc.ini文件,测量试验ODBC工作 复制代码 代码如下:vi /etc/odbc.ini

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

#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
NameState of QatarOption=3 Socket= 在指令行中试行下列命令,应能够胜利登录MySQL
Client窗口,即申明ODBC功效寻常: 复制代码
代码如下:isql -v testquit

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
澳门金沙vip,HS_FDS_SHAREABLE_NAME=/usr/lib/libmyodbc3.so
set ODBCINI=/etc/odbc.ini

4, 编辑hs配置文件 vi
/ora10g/hs/admin/inittest.ora(注意文件名米白色部分为odbc.ini棕威尼斯绿名称卡塔尔(قطر‎复制代码 代码如下:HS_FDS_CONNECT_INFO
= testHS_FDS_TRACE_LEVEL = on 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