一、环境
OS:Linux myhostname 2.6.9-42.ELsmp #1 SMP Sat Aug 12 09:39:11 CDT 2006 i686 i686 i386 GNU/Linux
CentOS release 4.4 (Final)
Oracle:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod,数据库已经安装好
MySQL:5.1.34-community for windows
二、安装配置
1. 安装unixODBC,用root用户
rpm -Uvh unixODBC-2.2.12-1.el4s1.1.i386.rpm
2. 安装mysql ODBC,用root用户
rpm -Uvh mysql-connector-odbc-5.1.5-0.i386.rpm
3. 安装oracle gateway,用oracle用户
我装的是10201_gateways_linux32.zip
unzip 10201_gateways_linux32.zip
cd gateways
./runInstaller
安装方法和oracle db 软件一样,我把gateway和db装一起了,共用一个OracleHOME
4. 配置/etc/odbc.ini
[DSName] Driver =/usr/lib/libmyodbc5.so Description =MySQL Server =xxx.xxx.xxx.xxx Port =3306 User =root UID =root Password = mypass Database =mysqldbname Option =3 Socket = charset =utf8
测试ODBC
isql -v DSName root mypass
5. 配置$ORACLE_HOME/hs/admin/initDSName.ora
HS_FDS_CONNECT_INFO = DSName HS_FDS_TRACE_LEVEL = 0 HS_FDS_SHAREABLE_NAME = /usr/lib/libmyodbc5.so
6. 配置listener.ora,加红色部分
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /usr/u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = BOSS) (ORACLE_HOME = /usr/u01/app/oracle/product/10.2.0/db_1) (SID_NAME = BOSS) ) (SID_DESC = (SID_NAME = phpcms) (ORACLE_HOME = /usr/u01/app/oracle/product/10.2.0/db_1) (PROGRAM = hsodbc) ) )
7. 配置tnsnames.ora,添加
DSName = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.125)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = DSName)) (HS = OK) )
8. 重启监听器并测试
lsnrctl reload
lsnrctl service
Service "DSName" has 1 instance(s). Instance "DSName", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:3 refused:0 LOCAL SERVER The command completed successfully
tnsping DSName
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.125)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DSName)) (HS = OK)) OK (0 msec)
9. 建立dblink
CREATE PUBLIC DATABASE LINK linkname CONNECT TO "root" IDENTIFIED BY <PWD> USING 'DSName';
10. 测试
select "name" from t1@linkname;
三、遗留问题
1. 字符集问题,最好oracle和mysql是utf8,否则中文有问题
2. text字段会报错:
select "textcol" from t1@linkname;
ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息: [Generic Connectivity Using ODBC][MySQL][ODBC 5.1 Driver][mysqld-5.1.34-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"t1" WHERE "id"=1' at line 1 (SQL State: 37000; SQL Code: 1064) ORA-02063: 紧接着 2 lines (起自 DSName)
本来想用datadirect的mysql ODBC试试,可只支持mysql enterprise版本,实在不好找,以后有机会再说吧。