Thursday, 2 January 2014

Connect Oracle to MySQL Server through ODBC database link

I will setup 11gR2 Oracle gateway for ODBC to connect between a version Oracle database and a version 5.5 MySQL database on different machines.. The Oracle database is on a 64-bit SunOS , while MySQL database is on a 64-bit SunOS  server. I will provide some tips and tricks along the way.
Unless specified, all the steps are performed on the Oracle gateway server.

Scope:- MySQL Connectors - Version 3.51 to 5.1 [Release 3.51 to 5.1]
Oracle Server - Enterprise Edition - Version to [Release 11.1 to 11.2]
Information in this document applies to any platform.

Oracle database server uses DG4ODBC as the data gateway for connecting to non-Oracle RDBMS. This requires the proper setup for all components involved in transferring data from Oracle to MySQL and back. These components interact with each other in the following way:
+-----------------------------<ODBC Client Host>---------------------------+
| |
| [ORACLE] <---> [DG4ODBC] <---> [ODBC Driver Manager] <---> [ODBC Driver] |
| |
       +--<MySQL Server Host>--+
            |                       |
            | [MySQL Server] |
            |                       |
In this article we will configure each component and diagnose problems that are most likely to be encountered during the setup process. NOTE: There are few articles in the internet that skip [ODBC Driver Manager] and connect directly [DG4ODBC] <- - -> [ODBC Driver]. It might work for some
drivers such as DataDirect or specific configurations, but beware that MySQL [ODBC Driver] (versions 3.51.x and 5.1.x) is not supposed to be loaded directly. The recommended driver manager is UnixODBC v.2.2.14 or newer. Configuring ODBC connections in 32-bit OS might be slightly easier than in 64-bit OS. The latter can execute 32 and 64-bit code and more attention must be paid to the components versions. In other words, when configuring ODBC you cannot mix 32-bit and 64-bit components within the Client Host. This is so because 32-bit binaries code can only load 32-bit binaries and 64-bit binaries can only load 64-bit binaries. [MySQL Server] is always independent because all communication with the driver is done through the network protocol. You have the choice to place [MySQL Server] on <ODBC Client Host> or on another physical host in the network <MySQL Server Host>. In any case, the bit depth of [MySQL Server]

Step 1:-
We shall start from determining if [Oracle] and [DG4ODBC] are 32/64-bits:             
$ file $ORACLE_HOME/bin/dg4odbc
/home/dbs/app/Ora/product/11.2.0/dbhome_1/bin/dg4odbc: ELF 64-bit LSB
executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped

The above command output says that we must use 64-bit [ODBC Driver Manager] and 64-bit [ODBC Driver]

Step 2

Getting and installing [ODBC Driver Manager] UnixODBC 2.2.14.
If your OS provides UnixODBC 2.2.14 or newer update package you have to install it. Otherwise, go to UnixODBC Downloads at sourceforge:

Choose the version (2.2.14 is recommended).

| NOTE: RPM packages are not offered, so the package has to be installed |
| from tar.gz. This is good for few reasons: |
|                                                           |
| - No root access is required to do so        |
| - You can keep few different versions of UnixODBC at once |
If binary package for your OS is not available in the list, go to UnixODBC web site to find out how to build it from sources:
In this example we will get unixODBC-2.2.14-linux-x86-64.tar.gz:
Download the package into the home directory (/home/dbs/).
Create a new directory for UnixODBC:
   $ mkdir ~/app/unixodbc-2.2.14
   $ cd ~/app/unixodbc-2.2.14

Unpack the package into the newly created directory:

   $ gunzip -c ~/unixODBC-2.2.14-linux-x86-64.tar.gz | tar xvf -
Latest UnixODBC packages put lib and bin directories in /usr/local inside the
package, so we need to move them to the upper level for convenience
   $ mv ~/app/usr/local/* .
   $ rm -r usr
Check the directory contents:
$ ls -l
drwxrwxr-x 2 dbs dbs 4096 Nov 20 2008 bin
drwxrwxr-x 2 dbs dbs 4096 Nov 20 2008 include
drwxrwxr-x 2 dbs dbs 4096 Nov 20 2008 lib
later we will have to set LD_LIBRARY_PATH to point to /home/dbs/app/unixodbc-2.2.14/lib

Step 3

Getting and installing [ODBC Driver].
Similar to UnixODBC, it is possible to have several different versions of MySQL Connector/ODBC driver. Installing the driver from tar.gz package does not require root privileges and allows installing the driver at custom locations, so we will do so.
More details about installing MySQL Connector/ODBC can be found here:

Download the tar.gz package from the following page: 
the driver into ~/app director

$ cd ~/app$ gunzip -c mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit.tar.gz | tar xvf -

This command creates mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit directory and extracts all needed files in it. Create a symbolic link with a shorter name:
$ ln -s mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit myodbc-5.1.8

Step 4
Configuring ODBC data source for MySQL Connector/ODBC driver is described here:
So, we will create odbc.ini file in ~/etc:
   Driver = /home/dbs/app/myodbc-5.1.8/lib/
   Description = Connector/ODBC 5.1 Driver DSN
   PORT = 3306
   USER = mysql_user
   PASSWORD = *****
   DATABASE = test
   OPTION = 0

more /export/home/mysql-connector/odbc.ini

Step 5 (Optional)
Verifying the ODBC connection using isql command line.
$ export ODBCINI=/home/dbs/etc/odbc.ini
$ export LD_LIBRARY_PATH=/home/dbs/app/unixodbc-2.2.14/lib:$LD_LIBRARY_PATH
$ cd ~/app/unixodbc-2.2.14/bin/
$ ./isql myodbc5 -v
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
The above output will display if connection has been established successfully. Next, trying to send a simple query to list tables in the database:
SQL> show tables;
| Tables_in_test |
| tab1 |
| tab2 |
SQLRowCount returns 2
2 rows fetched

That was the good scenario when everything went smoothly. However, you might get the following errors:
Error 1:
[IM002][unixODBC][Driver Manager]Data source name not found,
no default driver specified
ISQL]ERROR: Could not SQLConnect
error usually comes if ODBCINI variable is not pointing to the correct
odbc.ini file. To fix this error try:
$ cat $ODBCINI
The command should display the contents of odbc.ini file with all settings we have configured on Step 4. If the file is there and the same error comes again, check the data source name. The parameter name for isql must be exactly the same as the section name in odbc.ini file.

Error 2:
./isql: error while loading shared libraries: cannot open shared object file: No such file or directory This error means that LD_LIBRARY_PATH is set wrong and the linker cannot find the main UnixODBC [Driver Manager] library The solution is to export the directory containing ito LD_LIBRARY_PATH env variable
as shown at the beginning of Step 5.
The following command must not show failing dependencies:
$ ldd isql => (0x00007fffe4ffc000) => /home/dbs/app/unixodbc-2.2.14/lib/
(0x00002ae5263e8000) => /lib64/ (0x00000036b1c00000) => /usr/lib64/ (0x00000036b1000000) => /usr/lib64/ (0x00000036c4400000) => /lib64/ (0x00000036b2000000) => /lib64/ (0x00000036b1400000)
/lib64/ (0x00000036b0c00000)

Error 3:
[S1000][unixODBC][MySQL][ODBC 5.1 Driver]Access denied for user 'dbs'@'%' (using password: YES)
[ISQL]ERROR: Could not SQLConnect
In some cases this error message is not obvious. The user name and password might be correct and mysql command line might work perfectly with the user name and password specified in odbc.ini file. Check the database name in odbc.ini file (DATABASE parameter). As explained in the note on Step 4, the database names are case sensitive in MySQL and the connection might be rejected because the database does not exist or user has no privileges to access a database with this name.
Checking the connectivity from mysql command line is a good idea too.
| NOTE: mysql command line does not use ODBC, so it might work even if ODBC |
| fails. The idea of this check is to make sure we provided the correct                |
| connection credentials to ODBC driver.                                                         |
mysql command line should use EXACTLY the same user name, host, password, port and be executed on the same host with [Oracle] and [DG4ODBC]:
$ mysql "user=mysql_user "password=****** --host= --port=3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.52-community-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
| Database |
| information_schema |
| entitytest |
| mysql |
| test |
| test2 |
| test_db |
6 rows in set (0.07 sec)

Step 6:
Configuring tnsnames.ora.
Add the following lines to $ORACLE_HOME/network/admin/tnsnames.ora
myodbc5 =
(PROTOCOL=TCP) (HOST=localhost) (PORT=1521)


Step 7:
Configuring listener.ora
Open $ORACLE_HOME/network/admin/listener.ora in a text editor, find
SID_LIST_LISTENER definition and add a new entry for myodbc5:
# Another entry in the list
# (SID_NAME=sampleDSN)
# (ORACLE_HOME=/home/dbs/app/Ora/product/11.2.0/dbhome_1)
# (PROGRAM=dg4odbc)
# (ENVS=LD_LIBRARY_PATH=/home/dbs/app/unixodbc-2.2.14)
# )
We strongly recommend to add the LD_LIBRARY_PATH to the listener.ora file to avoid any conflicts with already existing ODBC driver managers.
The LD_LIBRARY_PATH must contain the fully qualified path to the $ORACLE_HOME/lib and also the library paths of the ODBC driver manager and the ODBC driver itself.

Step 8:
Configuring gateway init<sid>.ora file.
This file does not exist and you have to create it.
In listener.ora, tnsnames.ora we use the name myodbc5 just for convenience,
but it can be literally anything. The init file (initmyodbc5.ora) is a
different story because HS_FDS_CONNECT_INFO is the DSN name in odbc.ini.
$ vi $ORACLE_HOME/hs/admin/initmyodbc5.ora
HS_FDS_CONNECT_INFO=myodbc5 # Data source name in odbc.ini
# ODBC env variables

set ODBCINI=/home/dbs/etc/odbc.ini
NOTE: HS_FDS_SHAREABLE_NAME must point to the [ODBC Driver Manager] library. It is an error to put there the [ODBC Driver] library. The [ODBC Driver] DSN is referenced in HS_FDS_CONNECT_INFO=myodbc5.
| NOTE: 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. I the present case AMERICAN_AMERICA.WE8ISO8859P15 |
| worked ok |

Step 9
Applying the settings in the configuration files.
 The listeners must be restarted in order to pick up the changes we just made into tnsnames.ora, listener.ora and initmyodbc5.ora:
$ lsnrctl stop
$ lsnrctl start
NOTE: The start status has to contain information about the service:
Services Summary...
Service "myodbc5" has 1 instance(s).
Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

EXP:- lsnrctl start listener_test

Step 10 (Optional)
Checking the service status:

$ lsnrctl status
LSNRCTL for Linux: Version - Production on 06-MAY-2011 19:42:10
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
Version TNSLSNR for Linux: Version - Production
Start Date 06-MAY-2011 19:41:39
Uptime 0 days 0 hr. 0 min. 30 sec
Trace Level off
Security ON: Local OS Authentication
Listener Parameter File /home/dbs/app/Ora/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /home/dbs/app/Ora/diag/tnslsnr/dbs-pc/listener/alert/log.xml
Listening Endpoints Summary...
Services Summary...
Service "myodbc5" has 1 instance(s).
Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
The command prints the status of "myodbc5" service. UNKNOWN is not a problem
in this case because we have not tried using the service yet.
$ tnsping myodbc5
TNS Ping Utility for Linux: Version - Production on
06-MAY-2011 19:42:22
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=localhost)
(PORT=1521)) (CONNECT_DATA= (SID=myodbc5)) (HS=OK))
OK (10 msec)
If the above commands report errors check thsnames.ora and listener.ora and set them as shown on Step 6-7.

Step 11:
Creating the database link and getting the data.

Start sqlplus and type the following command (user and password are the same as in odbc.ini):
SQL> create public database link myodbc5 connect to "mysql_user" identified by
"********" using 'myodbc5';
Database link created.
SQL> select * from "tab1"@myodbc5;
some text
some more text

create public database link myodbc5 connect to "mysql_user" identified by "********" using 'myodbc5';

NOTE: When selecting data from MySQL linked table it is recommended to enclose the table name into double quotes as "tab1" unless MySQL Server is set to ANSI_QUOTES |

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

No comments:

Post a Comment