oracle gateway for odbc 配置

Oracle gateway for odbc配置

1. 安装gateway

建议新建用户gateway用于安装
useradd -g dba gateway

在.bash_profile中设置ORACLE_HOME,PATH

export ORACLE_HOME=/u02/app/gateway
export PATH=$ORACLE_HOME/bin:$PATH

运行runInstaller，一直下一步即可




2.安装unixODBC和mysql-connector-odbc

检查是否已安装这两个rpm,建议使用最新的版本




3.配置odbc

编辑/etc/odbc.ini

[ODBC Data Sources]
demo = MySQL ODBC Driver 5.5

[demo]
Driver      = /usr/lib/libmyodbc5.so
DATABASE    = test
DESCRIPTION = MySQL ODBC 5.5.17 Connector Sample
PORT        = 3306
SERVER      = 127.0.0.1
# UID         = demo
# PWD         = demo
CHARSET     = utf8
TRACEFILE   = /tmp/myodbc-demodsn.trc
TRACE       = OFF

测试odbc的连接

[root@ydqp143 etc]# isql demo
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>




4.配置gateway

cd $ORACLE_HOME/hs/admin

编辑initdg4odbc.ora

HS_FDS_CONNECT_INFO = demo     //前面配置的odbc条目
HS_FDS_TRACE_LEVEL = debug
HS_FDS_SHAREABLE_NAME = /usr/local/unixODBC/lib/libodbc.so //unixODBC的odbc manager位置
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1 //为避免双字节bug设置单字节的字符集
#
# ODBC specific environment variables
#
set ODBCINI=/usr/local/unixODBC/etc/odbc.ini

cd $ORACLE_HOME/network/admin

编辑listener.ora  //cp listener.ora.sample，只修改hostname就可以

LISTENER =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=ydqp143)(PORT=1821))
 )

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4odbc)
         (ORACLE_HOME=/u02/app/gateway)
         (PROGRAM=dg4odbc)
      )
  )
 
 lsnrctl start



 
 5. 配置用于连接mysql的oracle数据库

cd $ORACLE_HOME/network/admin

编辑tnsnames.ora  //cp tnsnames.ora.sample，值修改hostname就可以

dg4odbc  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=cyber)(PORT=1821))
    (CONNECT_DATA=(SID=dg4odbc))
    (HS=OK)
  )
 
  tnsping dg4odbc 测试一下
 
  在oracle数据库中建立dblink,如
 
  CREATE PUBLIC DATABASE LINK DEMO CONNECT TO "test" IDENTIFIED BY USING 'dg4odbc';  //注意用户名加"",mysql区分大小写



 
  6. 完成
 
  配置完成后就可以
 
  select * from "test"@demo;
 
  查看mysql数据