I. Environment
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, the Database has been installed
Mysql: 5.1.34-community for windows
Ii. installation and configuration
1. Install unixODBC with the root user
Rpm-Uvh unixODBC-2.2.12-1.el4s1.1.i386.rpm
2. Install mysql ODBC with the root user
Rpm-Uvh mysql-connector-odbc-5.1.5-0.i386.rpm
3. Install the oracle gateway.
I installed 10201_gateways_linux32.zip
Unzip 10201_gateways_linux32.zip
Cd gateways
./RunInstaller
The installation method is the same as that of the oracle db software. I have installed gateway and db together to share an OracleHOME
4. Configure/etc/odbc. ini
[DSName]
Driver =/usr/lib/libmydbc5.so
Description = MySQL
Server = xxx. xxx
Port = 3306
User = root
UID = root
Password = mypass
Database = mysqldbname
Option = 3
Socket =
Charset = utf8
Test 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/libmydbc5.so
6. Configure listener. ora and 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 and add
DSName =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.125) (PORT = 1521 ))
)
(CONNECT_DATA = (SERVICE_NAME = DSName ))
(HS = OK)
)
8. Restart the listener and test the listener.
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. Create a 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 problems. It is best to use utf8 for oracle and mysql; otherwise, there is a problem with Chinese characters.
2. The text field will report 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 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: followed by 2 lines (from DSName)
I would like to try using datadirect's mysql ODBC, but only the mysql enterprise version is supported. It is hard to find it. I will discuss it later.