Setting up Oracle Gateway for ODBC for Connections between an Oracle Database and a MySQL Database

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/

  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

 6. Verifying the ODBC connection:
 

  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