In this demo, I will setup 11gR2 Oracle gateway for ODBC to connect between a version 11.2.0.3
Oracle database and a version 5.0.29 MySQL database on different machines.. The Oracle
database is on a 64-bit Red Hat Enterprise Linux 5 server, while MySQL database is on a Windows
server. I will provide some tips and tricks along the way.
Unless specified, all the steps are performed on the Oracle gateway server, which is also on a Red Hat Enterprise Linux 5 server.
1. Install 11gR2 Oracle gateway:
file used: linux.x64_11gR2_gateways.zip.
Follow the steps
listed in Oracle documentation, as seen reference below. The gateway home (or $ORACLE_HOME) is:
/opt/app/oracle/product/11.2.0/gateway/
2. Create a new directory for both ODBC driver manager and
ODBC driver:
cd $ORACLE_HOME
mkdir app
3. Download and install UnixODBC, or ODBC driver manager:
a. Download unixODBC-2.2.14-linux-x86-64.tar.gz from http://sourceforge.net/projects/unixodbc/files/unixODBC/
a. Download unixODBC-2.2.14-linux-x86-64.tar.gz from http://sourceforge.net/projects/unixodbc/files/unixODBC/
b. Install ODBC
driver manager:
cd $ORACLE_HOME/app
$mkdir unixODBC-2.2.14
cd unixODBC-2.2.14
unzip
unixODBC-2.2.14-linux-x86-64.tar.gz
mv
usr/local/* .
rm -r usr
4. Download and install ODBC driver, also called MySQL
Connector:
a. Download from:
http://dev.mysql.com/doc/refman/5.5/en/connector-odbc-installation-binary-unix.html
File to
download: mysql-connector-odbc-5.1.12-linux-glibc2.5-x86-64bit.tar.gz
b. Install the ODBC
driver:
cd
$ORACLE_HOME/app
unzip
mysql-connector-odbc-5.1.12-linux-glibc2.5-x86-64bit.tar.gz
ls -s
mysql-connector-odbc-5.1.12-linux-glibc2.5-x86-64bit odbc-5.1.12
We now have the ODBC driver manager in:
/opt/app/oracle/product/11.2.0/gateway/app/unixodbc-2.2.14
and the ODBC driver in:
/opt/app/oracle/product/11.2.0/gateway/app/myodbc-5.1.12
5. Configure ODBC data source file for ODBC driver:
a. Create the etc directory under the $ORACLE_HOME:
cd
$ORACLE_HOME
mkdir etc
b. Create the odbc.ini file
in etc directory:
$more odbc.ini
[myodbc5]
Driver = /opt/app/oracle/product/11.2.0/gateway/app/myodbc-5.1.12/lib/libmyodbc5.so
Description = Connector/ODBC 5.1 Driver DSN
SERVER =ip_address_of_mysql_server
PORT = 3306
USER = user1 (note: this is username used to log in MySQL database)
PASSWORD = password1 (note: this is the password of user1)
DATABASE = mydb (note: this is the name of MySQL database, case sensitive)
OPTION = 0
TRACE = OFF
Description = Connector/ODBC 5.1 Driver DSN
SERVER =ip_address_of_mysql_server
PORT = 3306
USER = user1 (note: this is username used to log in MySQL database)
PASSWORD = password1 (note: this is the password of user1)
DATABASE = mydb (note: this is the name of MySQL database, case sensitive)
OPTION = 0
TRACE = OFF
a. Grant permissions
to allow remote access to MySQL database:
On MySQL
database server:
mysql -u
root -p
Enter
password:
mysql> use mysql
mysql> GRANT ALL PRIVILEGES ON mydb1 TO
'user1'@'ip_address_of_gateway_server' IDENTIFIED BY 'password1';
mysql> FLUSH PRIVILEGES;
b. On the gateway
server:
export ODBCINI=/opt/app/oracle/product/11.2.0/gateway/etc/odbc.ini
export
LD_LIBRARY_PATH=/opt/app/oracle/product/11.2.0/gateway/app/unixodbc-2.2.14/lib:$LD_LIBRARY_PATH
cd
/opt/app/oracle/product/11.2.0/gateway/app/unixodbc-2.2.14/bin/
./isql myodbc5 -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select version()
+----------+
| version()|
+----------+
| 5.0.95 |
+----------+
SQLRowCount returns 1
1 rows fetched
SQL>quit
7. Configure Oracle Net Listener for the gateway:
Modify the listener.ora created by gateway installer. For
this setup, the listener name is set to MYSQLGWAY and port# is 1523.
cd /opt/app/oracle/product/11.2.0/gateway/network/admin
$more listener.ora
# listener.ora Network Configuration File:
/opt/app/oracle/product/11.2.0/gateway/network/admin/listener.ora
# Generated by Oracle configuration tools.
ADR_BASE_MYSQLGWAY = /opt/app/oracle/product/11.2.0/gateway
MYSQLGWAY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = ip_address_of_gateway_server)(PORT = 1523))
)
)
SID_LIST_MYSQLGWAY=
(SID_LIST=
(SID_DESC=
(SID_NAME=mydb)
(ORACLE_HOME=/opt/app/oracle/product/11.2.0/gateway)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=/opt/app/oracle/product/11.2.0/gateway/app/myodbc-5.1.12/lib:/opt/app/oracle/product/11.2.0/gateway/app/unixodbc-2.2.14/lib:/opt/app/oracle/product/11.2.0/gateway/lib)
)
)
Note: make sure lines follows DESCRIPTION_LIST and SID_LIST
are indented. Otherwise, Oracle may not be able to read the file correctly.
8. Add an entry to the tnsnames.ora file for the gateway on the database
server:
The database could be on the same server as the gateway server,
or on a different server.
Add following lines to
$ORACLE_HOME/network/admin/tnsnames.ora on the database server:
mysqlmydb =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=ip_address_of_gateway_server)(PORT=1523))
(CONNECT_DATA=(SID=mydb))
(HS=OK)
)
Note: again make sure lines follow DESCRIPTION are indented.
9. Configure the Gateway Initialization Parameter File:
Make a copy of sample initialization parameter file
initdg4odbc.ora and rename it to include sid of MySQL database (in our
case, it is mydb).
cd /opt/app/oracle/product/11.2.0/gateway/hs/admin
cp initdg4odbc.ora initmydb.ora
$more initmydb.ora
# This is a sample agent init file that contains the HS
parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=myodbc5
#HS_FDS_TRACE_LEVEL =
HS_FDS_SHAREABLE_NAME
=/opt/app/oracle/product/11.2.0/gateway/app/unixodbc-2.2.14/lib/libodbc.so
#
# Based on metalink note 1320645.1,
# UnixODBC (ODBC Driver Manager) might not support any
character set
# used in oracle, so the connection string will be
corrupted.
# To avoid the connection string corruption it is
recommended to set
# HS_LANGUAGE parameter.
#
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
#
# ODBC specific environment variables
#
set ODBCINI=/opt/app/oracle/product/11.2.0/gateway/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set =
set
LD_LIBRARY_PATH=/opt/app/oracle/product/11.2.0/gateway/app/unixodbc-2.2.14/lib
10. Restart the listener:
export ORACLE_HOME=/opt/app/oracle/product/11.2.0/gateway
lsnrctl start MYSQLGWAY
11. Create a database link in Oracle database to connect to
MySQL database:
Create public database link mysqlmydb connect to
"user1" identified by "password1" using 'mysqlmydb';
Note: because MySQL is case sensitive, make sure to use
double quotes on username and password. Otherwise, you may get following error:
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system
returned this message:
[unixODBC][MySQL][ODBC 5.1 Driver]Access denied for user
'USER1'@'XXXX' (using password: YES) {HY000,NativeErr =
1045}
ORA-02063: preceding 2 lines from MYSQLMYDB
12. Test the db link from Oracle to MySQL:
select count(*) from "tbl1"@mysqlmydb;
COUNT(*)
----------
8
References:
1. Installing Oracle Database Gateway for ODBC:
http://docs.oracle.com/cd/E11882_01/gateways.112/e12013/installodbc.htm#BHCJEEBA
2. Oracle support note #1320645.1 Detailed Overview of
Connecting Oracle to MySQL Using DG4ODBC Database Link
3. Troubleshooting ORA-28500 error:
https://forums.oracle.com/thread/2227263
