ORACLE连接MYSQL-使用DG4ODBC.

最近做了一次Oracle连接到MySQL的实验,主要是通过DG4ODBC来连接的.以下是实验环境和实验步骤.
Oracle服务器:
IP:192.168.0.193
Database Version:11.2.0.1
MySQL服务器
IP:192.168.0.100
MySQL Version:5.5.18

1.首先要检查Oracle和DG4ODBC是32位还是64位.

[oracle@11g ~]$ file $ORACLE_HOME/bin/dg4odbc
/oracle/app/oracle/product/11.2.0/db_1/bin/dg4odbc: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped

这里可以看到是64位的.所以必须使用64位的ODBC Driver Manager和64位的ODBC Driver.

2.下载并安装64位的ODBC Driver Manager UnixODBC 2.2.14.
下载地址:http://sourceforge.net/projects/unixodbc/files/unixODBC/2.2.14/unixODBC-2.2.14-linux-x86-64.tar.gz/download

#这里下载到了/home/oracle目录下面
[oracle@11g ~]$ ls -l unixODBC*
-rw-r--r-- 1 oracle dba 756418 Dec 13 18:17 unixODBC-2.2.14-linux-x86-64.tar.gz
[oracle@11g ~]$ mkdir -p ~/app/unixodbc-2.2.14
[oracle@11g ~]$ cd ~/app/unixodbc-2.2.14/
[oracle@11g unixodbc-2.2.14]$ gunzip -c ~/unixODBC-2.2.14-linux-x86-64.tar.gz | tar xvf -
#因为UnixODBC的包lib和bin是在/usr/local文件夹里面.为了方便其见,我们把他们移动到上层目录上来.
[oracle@11g unixodbc-2.2.14]$ mv ~/app/unixodbc-2.2.14/usr/local/* .
[oracle@11g unixodbc-2.2.14]$ rm -rf usr/
#检查一下目录的内容
[oracle@11g unixodbc-2.2.14]$ ls -l
total 12
drwxr-xr-x 2 oracle oinstall 4096 Nov 20 2008 bin
drwxr-xr-x 2 oracle oinstall 4096 Nov 20 2008 include
drwxr-xr-x 2 oracle oinstall 4096 Nov 20 2008 lib

3.下载并安装ODBC Driver
下载地址:http:http://ftp.ntu.edu.tw/pub/MySQL/Downloads/Connector-ODBC/5.1/

#这里下载到了/home/oracle目录下面
[oracle@11g ~]$ ls -l mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit.tar.gz
-rw-r--r-- 1 oracle oinstall 5725402 Dec 14 22:40 mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit.tar.gz
[oracle@11g ~]$ cd app/
[oracle@11g app]$ gunzip -c ~/mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit.tar.gz | tar xvf -
[root@11g app]# ln -s mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit myodbc-5.18

4.配置MYSQL Connector(ODBC driver)
首先在mysql服务器上新建一个用户,并建相关的表

mysql> grant all privileges on test.* to mysql_user@localhost identified by "mysql_user";
Query OK, 0 rows affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table t (id int);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into t values (20);
Query OK, 1 row affected (0.04 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

然后在Oracle所在的服务器上面的/oracle/home/app下面建立odbc.ini文件,添加加以下内容

[myodbc5]
Driver = /home/dbs/app/myodbc-5.18/lib/libmyodbc5.so
Description = Connector/ODBC 5.1 Driver DSN
SERVER = 192.168.0.100
PORT = 3306
USER = mysql_user
PASSWORD = mysql_user
DATABASE = test
OPTION = 0
TRACE = OFF

5.使用isql来进行验证

[oracle@11g ~]$ export ODBCINI=/home/oracle/app/odbc.ini
[oracle@11g ~]$ export LD_LIBRARY_PATH=/home/oracle/app/unixodbc-2.2.14/lib:$LD_LIBRARY_PATH
[oracle@11g bin]$ ./isql myodbc5 -v
[S1000][unixODBC][MySQL][ODBC 5.1 Driver]Can't connect to MySQL server on '192.168.0.100' (113)
[ISQL]ERROR: Could not SQLConnect

这里报错.这是因为mysql新建的mysql_user用户不允许其他ip访问.切换到mysql服务器上面进行检查.

[root@buddy ~]# mysql --user=mysql_user --password=mysql_user --host=192.168.0.100 --port=3306
ERROR 1130 (HY000): Host '192.168.0.100' is not allowed to connect to this MySQL server

进入数据库查询可以清楚的看到只有localhost才能访问mysql_user;

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user from user;
+-----------+------------+
| host      | user       |
+-----------+------------+
| 127.0.0.1 | root       |
| ::1       | root       |
| buddy     |            |
| buddy     | root       |
| localhost |            |
| localhost | mysql_user |
| localhost | root       |
+-----------+------------+
7 rows in set (0.00 sec)
#修改权限.
mysql> grant all privileges on test.* to 'mysql_user'@'%' identified by 'mysql_user';
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from user;
+-----------+------------+
| host      | user       |
+-----------+------------+
| %         | mysql_user |
| 127.0.0.1 | root       |
| ::1       | root       |
| buddy     |            |
| buddy     | root       |
| localhost |            |
| localhost | mysql_user |
| localhost | root       |
+-----------+------------+
8 rows in set (0.00 sec)

如果还是不行,就需要把mysql服务器的防火墙关闭.

[root@buddy init.d]# /etc/init.d/iptables stop

然后切换到oracle服务器上执行isql命令测试

[oracle@11g bin]$ ./isql myodbc5 -v
+----------------------+
| Connected!           |
|                      |
| sql-statement        |
| help [tablename]     |
| quit                 |
|                      |
+----------------------+
SQL> show tables;
+----------------------+
| Tables_in_test       |
+----------------------+
| t                    |
+----------------------+
SQLRowCount returns 1
1 rows fetched

6.配置tnsnames.ora文件,增加下列内容.

myodbc5=
(DESCRIPTION=
(ADDRESS=(PROTOCOL = TCP) (HOST = 192.168.0.193) (PORT = 1521))
(CONNECT_DATA =
(SID = myodbc5)
)
(HS=OK)
)

7.配置listenner.ora文件,增加下列内容.

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=myodbc5)
(ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db_1)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=/home/oracle/app/unixodbc-2.2.14)
)
)

8.配置静态监听,初始化参数文件.initmyodbc5.ora

[oracle@11g ~]$ vi $ORACLE_HOME/hs/admin/initmyodbc5.ora
#加入下列内容
[注意]:HS_FDS_CONNECT_INFO是Data Source的名字.这个名字是odbc.ini文件里面定义的.
HS_FDS_CONNECT_INFO=myodbc5 # Data source name in odbc.ini
# HS_FDS_TRACE_LEVEL=user
HS_FDS_SHAREABLE_NAME=/home/oracle/app/unixodbc-2.2.14/lib/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN.AMERICA.ZHS16GBK
# ODBC env variables
set ODBCINI=/home/oracle/app/odbc.ini

9.重启监听,让刚才修改的文件生效.

[oracle@11g ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 15-DEC-2011 19:50:34

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.193)(PORT=1521)))
The command completed successfully
[oracle@11g ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 15-DEC-2011 19:50:45

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Starting /oracle/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/11g/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.193)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.193)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 15-DEC-2011 19:50:45
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/11g/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.193)(PORT=1521)))
Services Summary...
Service "myodbc5" has 1 instance(s).
Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

重启监听之后,可以看到myodbc5已经注册上了,但是状态是UNKNOWN,这种情况是正常的.因为我们还没有尝试使用该服务,最后尝试去TNSPING一下.看看能不能ping通.

[oracle@11g admin]$ tnsping myodbc5
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 15-DEC-2011 20:00:10

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL = TCP) (HOST = 192.168.0.193) (PORT = 1521)) (CONNECT_DATA = (SID = myodbc5)) (HS=OK))
OK (0 msec)

10.创建Database Link.

[oracle@11g admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 15 20:02:16 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create public database link myodbc5 connect to "mysql_user" identified by "mysql_user" using 'myodbc5';

Database link created.

SQL> select * from "t"@myodbc5;

id
----------
20

[注意]:当通过Link查询mysql的表,推荐在命名使用双引号,除非MySQL Server上设置了ANSI_QUOTES.
至此.Oracle已经能够正常通过DG4ODBC连接到MySQL了.

http://www.dbrabbit.com/archivers/oracle连接mysql-使用dg4odbc.html