上两篇文章已经说过，oracle如何通过hsodbc访问mysql数据库，
由于本人的服务器环境是64的redhat，数据库64位的oracle 10版本，hsodbc却是32位的，虽然能正常交互数据，但是出现各种异常，比如字符编码混乱以及数据转换出错等，无奈之下继续google找解决方法；
oracle 11版本使用dg4odbc作为gateway方式，其实就是hsodbc的升级版，幸运的是，dg4odbc支持64位，而且支持oracle 10.2.0.4以上的版本，官网上是这样讨论的，地址：https://community.oracle.com/message/10166998#10166998
还有很多讨论也说到这点，
https://community.oracle.com/message/10166998#10166998
https://community.oracle.com/message/9375935#9375935
https://community.oracle.com/message/9961067#9961067
https://community.oracle.com/thread/1133613
好吧，又得重新开始折腾了！
首先得安装这个64的dg4odbc，官网提供了单独gateway下载，解压出来就可以安装了。
安装需要图形界面，redhat可以使用xmanager;
资源介绍和安装方法：
https://community.oracle.com/thread/953944?tstart=0
http://www.doc88.com/p-0902638199262.html
下载地址：http://www.oracle.com/technology/software/products/database/index.html
下面配置dg4odbc
1，配置dg4odbc的init文件，路径：$GATEWAY_HOMOE_HOME/hs/admin，这个路径是新安装的gateway，要与原来的$ORACLE_HOME区分开来；
命名方式init+SID.ora，这个SID将会再后面配置listener.ora和tnsnames.ora用到
initmysql_aop_prod.ora
[root@v0A-123-177-205 admin]# cat initmysql_aop_prod.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 # # 数据源名称 HS_FDS_CONNECT_INFO = mysql_aop_prod_odbc_name # 日志级别，目录：$GATEWAY_HOMOE_HOME/hs/log HS_FDS_TRACE_LEVEL = OFF # 使用64位的odbc驱动程序管理器 HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so #HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15 #HS_LANGUAGE=GERMAN_GERMANY.WE8ISO8859P1 # 中文乱码 HS_FDS_SQLLEN_INTERPRETATION = 32 # # ODBC specific environment variables # #set ODBCINI=/etc/odbc.ini # # Environment variables required for the non-Oracle system # #set <envvar>=<value>
/etc/odbc.ini
[root@v0A-123-177-205 admin]# cat /etc/odbc.ini # 数据源名称 [mysql_aop_prod_odbc_name] # 使用64位的mysql驱动程序 Driver = /usr/lib64/libmyodbc3_r.so Description = MySQL # 数据库IP，端口，账号，密码，数据库名称 Server = 10.123.106.200 Port = 3306 User = aop #UID = 290 Password = mysql_aop Database = gd Option = 3 Socket = charset = gbk STMT = SET NAMES 'GBK' [root@v0A-123-177-205 admin]#
测试主机linux是否正常链接到mysql数据库：
[root@v0A-123-177-205 admin]# isql -v mysql_aop_prod_odbc_name +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> show databases; +-----------------------------------------------------------------+ | Database | +-----------------------------------------------------------------+ | information_schema | | gd | | test | +-----------------------------------------------------------------+ SQLRowCount returns 3 3 rows fetched SQL>
因为isql是64位的，mysql数据库驱动Driver = /usr/lib64/libmyodbc3_r.so也是64位的，所以正常链接上了
2，配置listener.ora，目录：$ORACLE_HOME/netword/admin
[root@v0A-123-177-205 admin]# cat 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 = (SID_NAME = PLSExtProc) (ORACLE_HOME = /Oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = mysql_aop_test) (ORACLE_HOME = /Oracle/product/10.2.0/db_1) (PROGRAM = hsodbc) (ENVS=LD_LIBRARY_PATH=/Oracle/product/10.2.0/db_1/lib32:/usr/lib:/usr/lib64:/Oracle/product/10.2.0/db_1/lib) ) (SID_DESC = # dg4odbc 配置文件的SID (SID_NAME = mysql_aop_prod) # 指定dg4odbc 即gateway安装的目录 (ORACLE_HOME = /Oracle/product/10.2.0/tg_1) # 使用新安装的64位dg4odbc作为gateway，不是只支持32的默认自带的hsodbc (PROGRAM = dg4odbc) # 指定gateway的home目录和lib目录 (ENVS=LD_LIBRARY_PATH=/Oracle/product/10.2.0/tg_1/lib32:/usr/lib:/usr/lib64:/Oracle/product/10.2.0/tg_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 名字，建立dblink时用到
mysql_aop_prod_tns =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = v0A-123-177-205)(PORT = 1521))
(CONNECT_DATA =
# listener.ora配置文件中SID_NAME，与dg4odbc的初始化文件init.ora的SID名称相同
(SID = mysql_aop_prod)
)
# 启动HS，即通过Heterogeneous Services Component组建，调用gateway，相关介绍：http://www.cnblogs.com/wwwiori/archive/2009/06/22/1508068.html
(HS = OK)
)
4，重启侦听，并查看
[oracle@v0A-123-177-205 ~]$ lsnrctl reload LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 13-NOV-2015 22:16:41 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 ~]$ tnsping mysql_aop_prod_tns TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 13-NOV-2015 22:17: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_prod)) (HS = OK)) OK (0 msec) [oracle@v0A-123-177-205 ~]$ lsnrctl status LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 13-NOV-2015 22:17:26 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 3 days 3 hr. 4 min. 43 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 "GDDGWM1.db.chinaunicom.cn" has 1 instance(s). Instance "NEWMALL", status READY, has 1 handler(s) for this service... Service "GDDGWM1_XPT.db.chinaunicom.cn" has 1 instance(s). Instance "NEWMALL", status READY, has 1 handler(s) for this service... Service "NEWMALLXDB.db.chinaunicom.cn" has 1 instance(s). Instance "NEWMALL", status READY, has 1 handler(s) for this service... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "gd" has 1 instance(s). Instance "gd", status UNKNOWN, has 1 handler(s) for this service... 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 ~]$
5，新建dblink，调用mysql_aop_prod_tns，
# link 名称可以随意命名，这里是mysql_aop_prod # using 后面是tns的名称，这里是mysql_aop_prod_tns create public database link mysql_aop_prod connect to "username" identified by "password" using 'mysql_aop_prod_tns';
6，最后最后的测试，激动人心的时刻到了
[oracle@v0A-123-177-205 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Fri Nov 13 22:21:17 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 userinfo."user_id",userinfo."register_time" from "app_user_info"@mysql_aop_prod userinfo where userinfo."user_id" <= 10; user_id register_ ---------- --------- 1 24-SEP-15 3 24-SEP-15 4 25-SEP-15 5 25-SEP-15 6 30-SEP-15 7 30-SEP-15 8 10-OCT-15 9 13-OCT-15 10 19-OCT-15 9 rows selected. SQL>
Leave a Reply