579   Linux MySQL Oracle

上篇文章讲述了如何配置odbc的驱动程序管理器和驱动程序来连接mysql数据库,现在说说如何配置oracle的gateway来调用odbc的驱动程序管理器,进而通过mysql驱动程序连接mysql数据库,达到oracle到mysql数据库之间的交互

二,配置oracle的gateway,oracle 10 以前的版本使用hsodbc做为gateway,后来oracle 11版本使用dg4odbc,其实就是hsodbc的升级版。hsodbc官网上说是不支持64位的,悲剧啊,我的环境是64位的!
既然oracle 10 以前的版本使用hsodbc做为gateway是32位的,那么使用32的odbc驱动程序管理器就得啦,上篇文章讲到mysql-connector-odbc-5.1.10-1.rhel5是32的,不仅可以作为mysql驱动程序,还是当做驱动程序管理器,于是乎灵机一动,就是它啦。

1,配置hsodbc的init文件,路径:$ORACLE_HOME/hs/admin,命名方式init+SID.ora,这个SID将会再后面配置listener.ora和tnsnames.ora用到

[oracle@v0A-123-177-205 admin]$ pwd
/Oracle/product/10.2.0/db_1/hs/admin
[oracle@v0A-123-177-205 admin]$ 
[oracle@v0A-123-177-205 admin]$ vim initmysql_aop_test.ora 
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
# 数据源名字,在/etc/odbc.ini配置内容的首行
HS_FDS_CONNECT_INFO = mysql_aop_test_odbc_name
# 日志级别,调试时设置为 4,正常使用后设置为OFF,不然日志好大,严重影响查询效率
HS_FDS_TRACE_LEVEL = OFF

# 是odbc驱动程序管理器的路径,不是驱动程序,参考英文文档:http://docs.oracle.com/cd/B19306_01/server.102/b14232/apa.htm#i634635
# 因为mysql-connector-odbc-5.1.10-1.rhel5即可以是驱动程序,也有驱动程序管理器的作用,所以直接填写驱动程序的路劲,32位的
HS_FDS_SHAREABLE_NAME = /usr/lib/libmyodbc5.so


# 解决中文乱码的问题
HS_FDS_SQLLEN_INTERPRETATION = 32

#
# ODBC specific environment variables
#

# 数据源配置文件的路径,经测试,这个设置没效果,默认就是读取ODBCINI=/etc/odbc.ini,暂时没找到原因
set ODBCINI=/etc/odbc.ini


#
# Environment variables required for the non-Oracle system
#
#set =

/etc/odbc.ini内容

[root@v0A-123-177-205 lib]# cat /etc/odbc.ini
#数据源名称
[mysql_aop_test_odbc_name]
#mysql数据库驱动程序 ,32位的
Driver = /usr/lib/libmyodbc5.so
Description = MySQL
#数据库地址、端口、用户、密码、数据库名等 
Server = 10.123.98.92
Port = 3306 
User = aop_test
#UID = 290
Password = 123456
Database = gd
Option = 3 
Socket = 
charset = gbk
STMT = SET NAMES 'GBK'

如果使用odbc的isql工具访问mysql_aop_test_odbc_name会报错,因为这里的isql是64位的,而mysql数据库驱动是32位,

[root@v0A-123-177-205 lib]# isql -v mysql_aop_test_odbc_name
[01000][unixODBC][Driver Manager]Can't open lib '/usr/lib/libmyodbc5.so' : /usr/lib/libmyodbc5.so: wrong ELF class: ELFCLASS32
[ISQL]ERROR: Could not SQLConnect
[root@v0A-123-177-205 lib]# 

不过没关系,因为等一会是通过oracle 10 的gateway,hsodbc来连接数据源的,它是32位,所以不会有冲突

2,配置listener.ora,目录:$ORACLE_HOME/netword/admin

[oracle@v0A-123-177-205 admin]$ pwd
/Oracle/product/10.2.0/db_1/network/admin
[oracle@v0A-123-177-205 admin]$ vim listener.ora
# listener.ora Network Configuration File: /Oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =


    (SID_DESC =
      # hsodbc的初始化文件init.ora的SID名称
      (SID_NAME = mysql_aop_test)
      (ORACLE_HOME = /Oracle/product/10.2.0/db_1)
     # gateway 使用 hsodbc 方式连接odbc驱动程序管理器
      (PROGRAM = hsodbc)
     # 加载lib
      (ENVS=LD_LIBRARY_PATH=/Oracle/product/10.2.0/db_1/lib32:/usr/lib:/usr/lib64:/Oracle/product/10.2.0/db_1/lib)
    )


  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = v0A-123-177-205)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )


3,配置tnsnames.ora,目录:$ORACLE_HOME/netword/admin


# tns 名字
mysql_aop_test_tns =
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = v0A-123-177-205)(PORT = 1521))
    (CONNECT_DATA =
      # listener.ora配置文件中SID_NAME,与hsodbc的初始化文件init.ora的SID名称相同
      (SID = mysql_aop_test)
    )
     # 启动HS,即通过Heterogeneous Services Component组建,调用gateway,相关介绍:http://www.cnblogs.com/wwwiori/archive/2009/06/22/1508068.html
      (HS = OK)
  )

4,重启侦听,并查看

[oracle@v0A-123-177-205 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 12-NOV-2015 17:58:23

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=v0A-123-177-205)(PORT=1521)))
The command completed successfully
[oracle@v0A-123-177-205 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 12-NOV-2015 17:58:35

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=v0A-123-177-205)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                10-NOV-2015 19:12:43
Uptime                    1 days 22 hr. 45 min. 51 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /Oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /Oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=v0A-123-177-205)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "mysql_aop_prod" has 1 instance(s).
  Instance "mysql_aop_prod", status UNKNOWN, has 1 handler(s) for this service...
Service "mysql_aop_test" has 1 instance(s).
  Instance "mysql_aop_test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@v0A-123-177-205 admin]$ tnsping mysql_aop_test_tns

TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 12-NOV-2015 18:01:06

Copyright (c) 1997,  2010, Oracle.  All rights reserved.

Used parameter files:
/Oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = v0A-123-177-205)(PORT = 1521)) (CONNECT_DATA = (SID = mysql_aop_test)) (HS = OK))
OK (0 msec)

可以看到Service “mysql_aop_test”存在了,unknown状态表示没进行数据交互,这属于正常现象;
mysql_aop_test_tns也存在了,

5,新建dblink,调用mysql_aop_test_tns,

# link 名称可以随意命名,这里是mysql_aop_test
# using 后面是tns的名称,这里是mysql_aop_test_tns
create public database link mysql_aop_test
  connect to "username" identified  by "password"
  using 'mysql_aop_test_tns';

6,最后最后的测试,激动人心的时刻到了

[oracle@v0A-123-177-205 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 12 18:11:09 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from "app_user_info"@mysql_aop_test;

   user_id register_ login_tim
---------- --------- ---------
         1 16-SEP-15 16-SEP-15
         4 23-SEP-15 24-SEP-15
         6 24-SEP-15 24-SEP-15
         7 24-SEP-15 25-SEP-15
        10 24-SEP-15 24-SEP-15
        11 19-OCT-15 19-OCT-15
        12 19-OCT-15 19-OCT-15
        13 19-OCT-15 05-NOV-15
        14 26-OCT-15 12-NOV-15
        20 29-OCT-15 05-NOV-15
        21 29-OCT-15 29-OCT-15

   user_id register_ login_tim
---------- --------- ---------
        22 29-OCT-15 29-OCT-15
        23 29-OCT-15 29-OCT-15
        24 05-NOV-15 12-NOV-15
        25 05-NOV-15 05-NOV-15
        26 05-NOV-15 05-NOV-15
        27 06-NOV-15 06-NOV-15
        28 06-NOV-15 06-NOV-15
        29 06-NOV-15 06-NOV-15
        30 06-NOV-15 09-NOV-15
        31 06-NOV-15 06-NOV-15
        32 06-NOV-15 06-NOV-15

22 rows selected.

SQL> select "user_id","register_time" from "app_user_info"@mysql_aop_test;

   user_id register_
---------- ---------
         1 16-SEP-15
         4 23-SEP-15
         6 24-SEP-15
         7 24-SEP-15
        10 24-SEP-15
        11 19-OCT-15
        12 19-OCT-15
        13 19-OCT-15
        14 26-OCT-15
        20 29-OCT-15
        21 29-OCT-15

   user_id register_
---------- ---------
        22 29-OCT-15
        23 29-OCT-15
        24 05-NOV-15
        25 05-NOV-15
        26 05-NOV-15
        27 06-NOV-15
        28 06-NOV-15
        29 06-NOV-15
        30 06-NOV-15
        31 06-NOV-15
        32 06-NOV-15

22 rows selected.

SQL> 

显然已经连接上了,oracle终于经过千山万水访问到mysql了,弄了好几天才成功,特此做笔记!
其实弄的过程遇到各种各样的问题,感谢百度谷歌了。




Leave a Reply

Your email address will not be published. Required fields are marked *