First, the environment
Os:linux myhostname 2.6.9-42.elsmp #1 SMP Sat Aug 09:39:11 CDT 2006 i686 i686 i386 gnu/linux
CentOS Release 4.4 (Final)
Oracle:oracle DB 10g Enterprise Edition release 10.2.0.1.0-prod, database installed
Mysql:5.1.34-community for Windows
Second, install the configuration
1. Install UNIXODBC, with root user
RPM-UVH unixodbc-2.2.12-1.el4s1.1.i386.rpm
2. Install MySQL ODBC, with root user
RPM-UVH mysql-connector-odbc-5.1.5-0.i386.rpm
3. Install Oracle Gateway, with Oracle users
I pretended to be 10201_gateways_linux32.zip.
Unzip 10201_gateways_linux32.zip
CD Gateways
./runinstaller
Installation method as with Oracle DB software, I have the gateway and DB installed together, sharing a oraclehome
4. Configure/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
Testing ODBC
Isql-v DSName Root Mypass
5. Configure $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. Configure Listener.ora, add the red part
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. Configure Tnsnames.ora, add
DSName =
(DESCRIPTION =
(Address_list =
(address = (PROTOCOL = TCP) (HOST = 192.168.0.125) (PORT = 1521))
)
(Connect_data = (service_name = dsname))
(HS = OK)
)
8. Reboot the Listener and test
Lsnrctl Reload
Lsnrctl Service
Service "DSName" has 1 instance (s).
Instance "DSName", Status UNKNOWN, has 1 handler (s) for the This service ...
Handler (s):
"Dedicated" Established:3 refused:0
Local SERVER
The command completed successfully
Tnsping DSName
Attempting to contacts (DESCRIPTION = (Address_list = (address = (PROTOCOL = TCP) (HOST = 192.168.0.125) (PORT = 1521)) (CON Nect_data = (service_name = dsname)) (HS = OK))
OK (0 msec)
9. Establishment of Dblink
CREATE Public DATABASE LINK linkname
CONNECT to "root"
Identified by <PWD>
USING ' DSName ';
10. Test
Select "Name" from T1@linkname;
Iii. Legacy Issues
1. Character set problem, the best Oracle and MySQL is UTF8, otherwise Chinese have problems
2. Text field will be an error:
Select "Textcol" from T1@linkname;
ORA-28500: Returns this information when connecting Oracle to a non-ORACLE system:
[Generic connectivity Using ODBC] [MySQL] [ODBC 5.1 Driver] [Mysqld-5.1.34-community] You have a 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 Lin E 1 (SQL state:37000; SQL code:1064)
ORA-02063: Shortly thereafter 2 lines (from DSName)
Originally want to use DataDirect MySQL ODBC try, can only support MySQL Enterprise version, it is not easy to find, there is a chance to say later.
