oracle mysql 长连接_Oracle与MySQL连接

环境：oracle 11.2.0.1.0 RHEL5.3 64bit   mysql Server version: 5.5.9

1：下载[ODBC Driver Manager] unionODBC2.2.14,解压后放入/u01/app/tools/unionodbc  目录下

[oracle@wonder tools]$ ls /u01/app/tools/unionodbc/

bin  include  lib

2：下载mysql-connector-odbc-3.51.30-linux-rhel5-x86-64bit.tar.gz解压重命名到my5

[oracle@szmlserver12_32 tools]$ ls /u01/app/tools/

my5  odbc.ini  unionodbc

3：配置 ODBC data source for MySQLConnector/ODBC driver

[oracle@wonder tools]$ cat /u01/app/tools/odbc.ini

[myodbc3]

Driver = /u01/app/tools/my5/lib/libmyodbc3.so

SERVER = 22.4.22.16

PORT = 3306

USER = soft

PASSWORD = Ugfd3589

DATABASE = estore

OPTION = 3

SOCKET =

4：设置环境变量

[oracle@wonder tools]$ cat ~/.bash_profile

PATH=$PATH:$HOME/bin

export PATH

export ORACLE_BASE=/u01/app

export ORACLE_HOME=/u01/app/oracle

export ORACLE_SID=hidb

export

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/tools/unionodbc/lib

export ODBCINI=/u01/app/tools/odbc.ini

export PATH=$ORACLE_HOME/bin:$PATH:/u01/app/tools/unionodbc/bin

红色为新增部分

5：测试连接成功

[oracle@wonder tools]$ isql myodbc3 -v

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

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

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

SQL>

6：配置listener.ora

[grid@wonder ~]$ cat /u01/app/11.2.0/grid/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = wonder.easou.com)(PORT = 1521))

)

)

ADR_BASE_LISTENER = /u01/app/11.2.0

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME= myodbc3)

(ORACLE_HOME= /u01/app/oracle)

(PROGRAM = dg4odbc)

(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/bin:/u01/app/tools/unionodbc/lib:/u01/app/tools/my5/lib)

)

)

7:配置tnsnames.ora,最后增加如下内容

myodbc3=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SID = myodbc3)

)

(HS = OK)

)

8：配置HS

[oracle@wonder tools]$ cat /u01/app/oracle/hs/admin/initmyodbc3.ora

HS_FDS_CONNECT_INFO = myodbc3

HS_FDS_TRACE_LEVEL = off

HS_FDS_SHAREABLE_NAME = /u01/app/tools/unionodbc/lib/libodbc.so

HS_LANGUAGE=american_america.we8iso8859p1

HS_NLS_NCHAR=UCS2

#

# ODBC specific environment variables

#

set ODBCINI= /u01/app/tools/odbc.ini

9：重启监听

[grid@wonder ~]$ lsnrctl stop

[grid@wonder ~]$ lsnrctl start

10：配置DBLINK

create database link myodbc connect to "soft" identified by "Ugfd3589" using 'myodbc3';   --前面要双引号才不报错

SQL> select count(*) from "acct_user"@myodbc;

COUNT(*)

----------

13

但是发现 select * from "acct_user"@myodbc 时，只会显示一条数据，晕倒了，经google发现，通过更新驱动可以得到解决

下面的步骤是在上面的基础上更换了最新ODBC 5.5.2解决了问题，以及一oracle主机连接2个mysql服务器例子：

下载mysql-connector-odbc-5.2.2-linux-glibc2.3-x86-64bit.tar

http://www.mysql.com/downloads/connector/odbc/#downloads

1:更换配置odbc.ini

[oracle@wonder hs]$ cat /u01/app/tools/odbc.ini

[mysql203]

Driver = /u01/app/tools/my5/lib/libmyodbc5w.so

SERVER = 120.2.93.3

PORT = 3306

USER = search_wei

PASSWORD = aaaa

DATABASE = search_wei

OPTION = 3

SOCKET =

[mysql213]

Driver = /u01/app/tools/my5/lib/libmyodbc5w.so

SERVER = 125.91.0.213

PORT = 3306

USER = droid

PASSWORD = bbbb

DATABASE = droid

OPTION = 3

SOCKET =

2： hs/admin 中

[oracle@wonder hs]$ ls admin/

extproc.ora      initmysql203.ora  listener.ora.sample

initdg4odbc.ora  initmysql213.ora  tnsnames.ora.sample

[oracle@wonder hs]$ cat admin/initmysql213.ora

HS_FDS_CONNECT_INFO = mysql213

HS_FDS_TRACE_LEVEL = debug

HS_FDS_SHAREABLE_NAME = /u01/app/tools/unionodbc/lib/libodbc.so

HS_LANGUAGE=american_america.we8iso8859p1

HS_NLS_NCHAR=UCS2

#SELECT NULL COLUMN

HS_FDS_SQLLEN_INTERPRETATION = 32

#DIRECT ACESS MYSQL INT COLUM

HS_FDS_FETCH_ROWS=1

set ODBCINI= /u01/app/tools/odbc.ini

[oracle@wonder hs]$ cat admin/initmysql203.ora

HS_FDS_CONNECT_INFO = mysql203

HS_FDS_TRACE_LEVEL = debug

HS_FDS_SHAREABLE_NAME = /u01/app/tools/unionodbc/lib/libodbc.so

HS_LANGUAGE=american_america.we8iso8859p1

HS_NLS_NCHAR=UCS2

set ODBCINI= /u01/app/tools/odbc.ini

3： tnsnames.ora

mysql213=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SID = mysql213)

)

(HS = OK)

)

mysql203=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SID = mysql203)

)

(HS = OK)

)

4： listener.ora

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME= mysql203)

(ORACLE_HOME= /u01/app/oracle)

(PROGRAM = dg4odbc)

(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/bin:/u01/app/tools/unionodbc/lib:/u01/app/tools/my5/lib)

)

(SID_DESC=

(SID_NAME= mysql213)

(ORACLE_HOME= /u01/app/oracle)

(PROGRAM = dg4odbc)

(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/bin:/u01/app/tools/unionodbc/lib:/u01/app/tools/my5/lib)

)

)

5：重启监听

6：分别建立到3和203的dblink，验证通过

create database link mysql203 connect to "search_wei" identified by "aaaa" using 'mysql203';

select * from "t_info_stat"@mysql203;

SQL> create database link mysql213 connect to "droid" identified by "bbbb" using 'mysql213';

select * from "ums_module"@mysql213;

哈哈！大功搞成！