上篇文章讲述了如何配置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