724   Linux MySQL Oracle

上两篇文章已经说过,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
QQ图片20151113214736

 

还有很多讨论也说到这点,

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

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