solaris-Oracle11g于linux-mysql相连

Oracle11g(solaris64sparc)+mysql(linux)实验

 

此实验目的,实现公司ebs R12 连mysql上的短信平台.预警和提示ebs中信息,

一,环境

主机名        ip                                          平台                 数据库       dbname

ebs234            192.168.1.234                   solaris 10 64 sparc       oralce 11g                    ebstest

mysql                192.168.1.247                  linux redhad up4              mysql 5.1        mccdb

二,此连接原理

+-----------------------------<ODBC Client Host>---------------------------+

| |

| [ORACLE] <---> [DG4ODBC] <---> [ODBC Driver Manager] <---> [ODBC Driver] |

| |                                                                 |

+-------------------------------------------------------------------|-------+

                                                                   /|\

                                                                    |

                                                                 NETWORK

                                                                    |

                                                                   \|/

                                                        +--<MySQL Server Host>--+

                                                        |                       |

                                                            | [MySQL Server] |

                                                        |                       |

                                                        +-----------------------+  

这是个核心的连接过程.现分路段来配制这个过程,

我们倒着配制.就能慢慢明白其中原理

先确定一下二个数据库及期系统的版本

file $ORACLE_HOME/bin/dg4odbc

系统版本和linux版本及mysql版本都容易查,不重复了

在mysql上建个用户先

grant all privileges on mccdb.* to APPS@192.168.1.234 identified by '123456' with grant option;

flush privileges;(刷新权限设置)

以下操作均在oracle主机

1)odbc drvier -->mysql

  mysql是在192.168.1.247主机上. 现solaris 平台上的oracle 11g要连接mysql.就是通过odbc这个驱动.

所以要在solaris 平台安装odbc 驱动for mysql.同时又要装一个管理这个odbc驱动的管理软件,即odbc driver manager

a,下载地址odbc driver manager  选择合适的驱动, 下地直任选

   http://sourceforge.net/projects/unixodbc/files/unixODBC/

   http://www.unixodbc.org/download.html

b, 下载地址odbc driver

http://www.mysql.com/downloads/connector/odbc/5.1.html

c ,安装(oratest用户)

   mkdir /ebstest/app/etc

   mkdir /ebstest/app/myodbc

      mkidr /ebstest/app/unixodbc-2.2.14

把odbc driver 移入myodbc中

把odbc driver manager ,即unixodbc-2.2.14-sunos-sparc-64.tar.gz移到unixodbc-2.2.14中

分别解压

把odbc dirver manager 中解开的目录稍调整一下.即把生成的usr下的文件bin lib include移到unixodbc-2.2.14目录下.

目的是目录不要那么深,好看一些,并把usr删掉

把myodbc中解开的长文件名更名一下.mv 长文件名 mysqlodbc也是好看一些

好,现在基本工作就绪

1,配置环境变量

PATH=/ebstest/app/unixodbc-2.2.14/bin:/ebstest/app/prod/apps/tech_st/10.1.2/OPatch/opatch:$PATH

export PATH

LD_LIBRARY_PATH=/ebstest/app/unixodbc-2.2.14/lib:$LD_LIBRARY_PATH

export LD_LIBRARY_PATH

DISPLAY=192.168.1.196:0.0; export DISPLAY

ODBCINI=/ebstest/app/etc/odbc.ini

export ODBCINI

ODBCSYSINI=/ebstest/app/etc

export ODBCSYSINI

2, 注册生成odbcinst.ini

在myodbc目录中进入mysqlodbc/bin

Register a driver;

    $ myodbc3i -a -d -t"MySQL ODBC 3.51 Driver;Driver=/usr/lib/libmyodbc3.so;Setup=/usr/lib/libmyodbc3S.so"

  Create a user data source name;

    $ myodbc3i -a -su -t"DSN=MyDSN;Driver=MySQL ODBC 3.51 Driver;Server=localhost;UID=pharvey"

根据这二个命令

改一下 ./myodbc3i -a -d -t "MySQL ODBC 3.51 Driver;Driver=/ebstest/app/myodbc/mysqlodbc/lib/libmyodbc3.so;Setup=/ebstest/app/myodbc/mysqlodbc/lib/libmyodbc3_r.so"

这样就在环境变量规定的目录中生成了odbcinst.ini

又根据生成user data source name;这个命令来生成odbc.ini文件

./myodbc3i -a -su -t "DSN=mccdb;Driver=/ebstest/app/myodbc/mysqlodbc/lib/libmyodbc3.so;Server=192.168.1.247;UID=apps"

----------------------------

./configure --prefix=/ebs/app/myodbc/unixODBC --includedir=/usr/include --libdir=/usr/lib --bindir=/usr/bin --sysconfdir=/ebs/app/myodbc/etc

make

make install

./myodbc-installer -d -a -n "MySQL ODBC 5.1 Driver" -t "DRIVER=/ebstest/app/myodbc/mysqlodbc5/lib/libmyodbc5.so;Setup=/ebstest/app/myodbc/mysqlodbc5/lib/libmyodbc5.so"

./myodbc-installer -s -a -c2 -n "mccdb" -t "DRIVER=MySQL ODBC 5.1 Driver;SERVER=192.168.1.247;DATABASE=mccdb;UID=apps;PWD=123456"

5.1版本问题按上述改

----------------------------

执行一下.也许生成不了.没关系可以手动在/ebstest/app/etc中建一个odbc.ini

内容如下:

$ more odbc.ini    --这个实际是dsn驱动名

[ODBC Data Sources]

data_source_name = mccdb

[mccdb]

Driver          = /ebstest/app/myodbc/mysqlodbc/lib/libmyodbc3.so

DATABASE        = mccdb

DESCRIPTION     = MySQL ODBC 3.51 Driver

SERVER          = 192.168.1.247

UID             = apps

PASSWORD        = 123456

PORT            = 33306

SOCKET          =

具体内容,要看情况更改

以上odbc驱动文件全部配好了.现需要注册一下在odbc driver mangaer中.

cd /ebstest/app/unixodbc-2.2.14/bin

./odbcinst -i -d -f /ebstest/app/etc/odbcinst.ini     -i (install) -d (drvier) -q (query)

./odbcinst -i -s -l -f /etc/odbc.ini                              -i(install) -s(data source) -l system dsn -f (file name of template.ini follows)

                                                                             可以不用-f

------------------------

$ ./odbcinst -i -d -f /ebstest/app/etc/odbcinst.ini

odbcinst: Driver installed. Usage count increased to 2.

    Target directory is /ebstest/app/etc

$ ./odbcinst -i -s -l -f /ebstest/app/etc/odbc.ini

5.1版本如上

------------------------

检查是否连通

进入/ebstest/app/unixodbc-2.2.14/bin

./odbcinst -s -q 看是否能检测到连接dsn驱动名mccdb

./isql mccdb -v

看是否连通

理论是没什么问题的.当然出问题得查找原因

2) [ORACLE] <---> [DG4ODBC] 配制这一段 (oracle 11g已经有dg4odbc了,不用装gateway)

这一段要明白oracle 11g是连dg4odbc  也就是配tnsnames.ora 和listener.ora

tnsnames.ora  (oracle_home/network/中)

增加如下一段

mccdb =

        (DESCRIPTION =

                 (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.234)(port=1529))

   (CONNECT_DATA =

    (SID = mccdb)   )

   (HS=OK)

  )

要想清楚host地址,端口不要重用

listener.ora 增加如下二段

mccdb =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = ebs2.riso.com.cn)(PORT = 1529))

    )

  )

SID_LIST_MCCDB=

  (SID_LIST =

    (SID_DESC =

    (PROGRAM=dg4odbc)

    (SID_NAME = mccdb)

    (ENV=/ebstest/app/unixodbc-2.2.14/lib)

    )

  )

配完以后.看能不能不起动.lsnrctl start mccdb

tnsping mccdb

成功则说明没问题了. oracle 11g连DG4ODBC这段成功了.

3) [DG4ODBC] <---> [ODBC Driver Manager] 连这一段,其实这一段的真正核心就是hs目录中的初始化参数文件

cd $ORACLE_HOME//hs/admin

cp initdg4odbc.ora initmccdb.ora

修改后文件如下:

$ more initmccdb.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 = mccdb

HS_FDS_TRACE_LEVEL = Debug

HS_FDS_SHAREABLE_NAME = /ebstest/app/unixodbc-2.2.14/lib/libodbc.so     --这一行是关键,千万别折腾到myodbc目中去了.myodbc是连mysql. 现在是dg4odbc->odbc driver

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

#HS_LANGUAGE=american_america.al32utf8

HS_NLS_DATE_FORMAT=YYYY-MM-DD

#HS_FDS_FETCH_ROWS=100

#

# ODBC specific environment variables

#

set ODBCINI=/ebstest/app/etc/odbc.ini

set ODBCINSTINI=/ebstest/app/etc/odbcinit.ini

set ODBCSYSINI=/ebstest/app/etc

set ORACLE_HOME=/ebstest/app/prod/db/tech_st/11.1.0

#

# Environment variables required for the non-Oracle system

#

在开启debug后.

先创建一个dblink

create public database link myodbc connect to apps identified by 123456 using 'mccdb';

再连接对方表试试

select * from msg_table@myodbc;

根据出错的信息.在hs/admin/log中查询出错原因

如果理解清楚,肯定容易找出问题所在

最终可能问题

 一,是版本问题,只能查出一行

二,是乱码问题.