DBA Tips Archive for Oracle

  


How to Setup HSODBC (Oracle9i on Solaris) to MySQL on Linux

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Overview
  2. Setting up Grant Tables in MySQL
  3. Setting up ODBC Driver Manager for Sun Solaris
  4. Setting up MySQL ODBC Driver for Sun Solaris
  5. Configure Environment Variables for the oracle User ID
  6. Testing the MySQL ODBC Driver
  7. Configuring the HSODBC process
  8. Testing the HSODBC Configuration



Overview

This article provides step by step instructions for installing and configuring Oracle's Heterogeneous Services (Oracle9i Release 2) to connect from an Oracle9i database running on Sun Solaris to MySQL running on a remote Linux machine.

The type of connection that we will be performing in this article will utilize Oracle's Heterogeneous Services ODBC - generic connectivity (HSODBC) on Sun Solaris (Oracle 9.2.0.8.0).

This article is divided into the following sections:

  Note that Heterogeneous Services requires the 32-bit version of ODBC.

Before moving into the details on the how to configure HSODBC, let's first discuss the setup I will be using for this article. This configuration contains two servers:

  1. Sun SPARC
    • Solaris 10 Operating Environment
    • Oracle RDBMS 9.2.0.8.0
    • Host name = alex
    • Database Name = ORA920

  2. Linux Server
    • Red Hat Linux 4 Update 4
    • MySQL 4.1.20
    • Host name = linux2
    • Database Name = test

  This article should work for Oracle 8i, 9.0.1, and 10g releases running on either Solaris 9 or Solaris 10.

Also note that this article has been tested with MySQL 4.0.20-standard running on Red Hat Linux - (Fedora Core 1) as well as MySQL 4.1.20 running on Red Hat Linux 4 Update 4 - (actually CentOS 4.4).



Setting up Grant Tables in MySQL

As noted in the Overview section (above), we will be using Oracle's Heterogeneous Services to connect from an Oracle9i database running on machine alex to a MySQL database running on machine linux2. This setup will require us to configure the grant tables for the test database. We will setup the grant tables to allow the oracle user to connect from machine alex with a password of manager. Connect to the mysql database on alex and perform the following:
# mysql -u root mysql

mysql> GRANT ALL PRIVILEGES ON test.* TO oracle@alex
    -> IDENTIFIED BY 'manager' WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES;


Setting up ODBC Driver Manager for Sun Solaris

In order for Oracle's Heterogeneous Services agent to connect to the remote MySQL database, we will need to install the MySQL ODBC Driver (next section) to the machine running the Oracle database. Note however that the MySQL ODBC driver needs an ODBC driver manager. So before installing and configuring the MySQL ODBC driver, we must first install and configure an ODBC driver manager.

In this article, I will be using a popular UNIX ODBC driver manager which can be downloaded from www.unixodbc.org. From the topics menu, click on the "Download" link. Download the unixODBC distribution / Solaris 8 Package format. For the purpose of this article, I downloaded the file "unixodbc-2.1.1-sol8-sparc-local.gz". The ODBC driver manager can also be downloaded from here.

To install unixODBC, copy the file unixodbc-2.1.1-sol8-sparc-local.gz to a temporary directory and follow the steps below to install to /usr/local:

# gunzip unixodbc-2.1.1-sol8-sparc-local.gz
# pkgadd -d unixodbc-2.1.1-sol8-sparc-local

  I used Internet Explorer to download the file unixodbc-2.1.1-sol8-sparc-local.gz. During the download of the file, it would automatically uncompress (gunzip) the file. Because of this, I did not need to perform the gunzip of the file, but rather just rename the file:
# gunzip unixodbc-2.1.1-sol8-sparc-local.gz

gunzip: unixodbc-2.1.1-sol8-sparc-local.gz: not in gzip format

# mv unixodbc-2.1.1-sol8-sparc-local.gz unixodbc-2.1.1-sol8-sparc-local
# pkgadd -d unixodbc-2.1.1-sol8-sparc-local



Setting up MySQL ODBC Driver for Sun Solaris

Let's continue by downloading the MySQL ODBC driver. The driver can be found at http://www.mysql.org. At the time of this writing, the current production release of the MySQL ODBC Driver (for Sun Solaris) is version Connector/ODBC 3.51 and can be downloaded here. The MySQL ODBC driver I used for this article (release 3.51.06 / Sun Solaris 2.9 SPARC) can also be downloaded here.

  Because of a bug in the Solaris version of tar, we will need to download GNU tar (gtar) to unpack the MySQL ODBC Driver download. Download gtar to your Solaris machine and change the execution bit:
# cp gtar /usr/local/bin
# chmod 755 /usr/local/bin/gtar

At this point, we should have two files: gtar and MyODBC-3.51.06-sun-solaris2.9-sparc.tar.gz. Move the file MyODBC-3.51.06-sun-solaris2.9-sparc.tar.gz to a temporary directory and perform the following actions to install the MySQL ODBC Driver:

# cp MyODBC-3.51.06-sun-solaris2.9-sparc.tar.gz /usr/local
# cd /usr/local
# gunzip MyODBC-3.51.06-sun-solaris2.9-sparc.tar.gz
# gtar xvf MyODBC-3.51.06-sun-solaris2.9-sparc.tar
# cp MyODBC-3.51.06-sun-solaris2.9-sparc/libmyodbc3* lib
# mv etc/odbc.ini etc/odbc.ini.backup
# cp MyODBC-3.51.06-sun-solaris2.9-sparc/odbc.ini etc
# cd lib
# rm libmyodbc3.so libmyodbc3_r.so
# ln -s libmyodbc3-3.51.06.so libmyodbc3.so
# ln -s libmyodbc3_r-3.51.06.so libmyodbc3_r.so
Modifying the odbc.ini File
After installing the MySQL ODBC Driver (above), we will need to modify the file /usr/local/etc/odbc.ini. For this article, I will be using the [myodbc3] DSN entry. Here are the changes that will need to be made to this file:

Here is an example copy of my working /usr/local/etc/odbc.ini file:

Example odbc.ini File
[ODBC Data Sources]

myodbc      = MySQL ODBC 2.50 Driver DSN
myodbc3     = MySQL ODBC 3.51 Driver DSN


[myodbc]
Driver       = /usr/local/lib/libmyodbc.so
Description  = MySQL ODBC 2.50 Driver DSN
SERVER       = localhost
PORT         =
USER         = root
Password     =
Database     = test
OPTION       = 3
SOCKET       =


[myodbc3]
Driver       = /usr/local/lib/libmyodbc3.so
Description  = MySQL ODBC 3.51 Driver DSN
SERVER       = linux2
PORT         = 3306
USER         = oracle
Password     = manager
Database     = test
OPTION       = 3
SOCKET       =


[Default]
Driver       = /usr/local/lib/libmyodbc3.so
Description  = MySQL ODBC 3.51 Driver DSN
SERVER       = localhost
PORT         =
USER         = root
Password     =
Database     = test
OPTION       = 3
SOCKET       =



Configure Environment Variables for the oracle User ID

We now need to setup several environment variables for the oracle user id on the machine running the Oracle database (alex in this article). More importantly, this is the UNIX account that starts the Oracle TNS listener. In almost all cases, this is the account that owns and runs the Oracle9i RDBMS software and will (through Oracle Heterogeneous Services), make the connection to the MySQL database. The following should be added to the .profile (or equivalent startup file) for the UNIX oracle user id:
LD_LIBRARY_PATH=/usr/local/lib:$LD_LIBRARY_PATH:/usr/lib:/usr/openwin/lib:/usr/dt/lib
export LD_LIBRARY_PATH

ODBCINI=/usr/local/etc/odbc.ini
export ODBCINI

ODBCSYSINI=/usr/local/etc     # (for unixODBC)
export ODBCSYSINI

  With the LD_LIBRARY_PATH, we need to ensure that it contains the path for both libraries we just installed - ODBC Driver Manager and MySQL ODBC Driver. For this example, we installed both sets of libraries to /usr/local/lib, so this will need to be included in the LD_LIBRARY_PATH environment variable.



Testing the MySQL ODBC Driver

Finally, we get to start testing our MySQL ODBC database driver. To test the connection, unixODBC provides a test tool called isql. The isql binary will be located in /usr/local/bin.

Login to the database server (for the purpose of this article, my database server is alex) as the Oracle software owner (i.e. oracle) and run the following:

$ id
uid=175(oracle) gid=115(dba)

$ isql myodbc3 oracle manager -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> create table emp (id int, name varchar(100));
0 rows affected

SQL> insert into emp values (100, 'Jeff');
1 rows affected

SQL> insert into emp values (100, 'Melody');
1 rows affected

SQL> commit;
0 rows affected

SQL> quit
$
Looks great! Now let's setup Oracle's Heterogeneous Services for ODBC (generic) connectivity.


Configuring the HSODBC process

This section assumes that everything is done in the Oracle user account that starts the listener. For this article, my database server is named alex and the database (more importantly, the TNS listener) is running as the UNIX oracle User ID. The MySQL database that I will be connecting to is named test and is running on an instance on machine linux2.

In general the following things must be configured:

  1. Oracle TNS listener
  2. tnsnames.ora file
  3. init<SID>.ora of the HS subsystem (also known as the HS Gateway)
  4. environment
  5. Oracle database

Oracle TNS Listener

Let's start by adding the following new SID to the listener.ora file:
(SID_DESC =
  (PROGRAM = hsodbc)
  (ORACLE_HOME = /u01/app/oracle/product/9.2.0)
  (SID_NAME = myodbc3)
  (ENVS=LD_LIBRARY_PATH = /usr/local/lib:/u01/app/oracle/product/9.2.0/lib)
)

Example listener.ora File
# LISTENER.ORA Network Configuration File:
#   /u01/app/oracle/product/9.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = alex)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/9.2.0)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (PROGRAM = hsodbc)
      (ORACLE_HOME = /u01/app/oracle/product/9.2.0)
      (SID_NAME = myodbc3)
      (ENVS=LD_LIBRARY_PATH = /usr/local/lib:/u01/app/oracle/product/9.2.0/lib)
    )
  )

Correct the ORACLE_HOME entry and the ENVS entry according to your installation.

  • ORACLE_HOME must point to your ORACLE_HOME directory
  • The ENVS string contains entries for the LD_LIBRARY_PATH
  • The minimum of the LD_LIBRARY_PATH setting must contain the Oracle library and the odbc library path; both 32 bit
  • HSODBC is a 32 bit libray and thus it needs a 32 bit ODBC driver, however, see NOTE below

  Due to a staging problem with Oracle 9iR2 this hsodbc configuration must have the $ORACLE_HOME/lib directory instead of the lib32 directory in the ENVS path setting; the lib32 directory does not contain ALL required libraries!

With Oracle 10g all libraries are placed in the lib32 directory again.

A correct setting of the path can be verified by typing

$ hsodbc

Oracle Corporation --- WEDNESDAY FEB 28 2007 12:39:38.741

Heterogeneous Agent Release 9.2.0.8.0 - Production  Built with
   Driver for ODBC

If the LD_LIBRARY_PATH contains the correct libraries, the version number of the HSODBC agent should be displayed.

Be aware to restart the listener after changing the listener.ora!

# lsnrctl stop
# lsnrctl start
Oracle TNS Listener
The tnsnames.ora needs an entry for the HSODBC alias:
HSODBC.IDEVELOPMENT.INFO =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = alex.idevelopment.info)
        (PORT = 1521)
      )
    )
    (CONNECT_DATA =
      (SID= myodbc3)
    )
    (HS=OK)
  )
The domain of the TNS alias can differ from the one used above (idevelopment.info), depending on the parameter in the sqlnet.ora:
NAMES.DEFAULT_DOMAIN = idevelopment.info
But the important entry is the (HS=) or (HS=OK) key word. This key word must be added manually and keep in mind that opening the Net Configuration Assistants will remove this entries from your tnsnames.ora file! The (HS=OK) parameter must be outside the SID section and specifies that this connector uses the Oracle Heterogeneous Service Option.

After adding the tnsnames alias and restarting the listener, a connectivity check is to use: tnsping <alias>:

# tnsping hsodbc
TNS Ping Utility for Solaris: Version 9.2.0.8.0 - Production on 28-FEB-2007 12:52:39

Copyright (c) 1997, 2006, Oracle Corporation.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/9.2.0/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = alex.idevelopment.info) (PORT = 1521))) (CONNECT_DATA = (SID= myodbc3)) (HS=OK))
OK (70 msec)
should come back with a successful message (as shown above).
init.ora of the gateway
The SID name used in this article to configure Oracle HS functionality is called myodbc3. This was defined in the ODBC driver manager configuration file: /usr/local/etc/odbc.ini. There are some restrictions on how to name the SID (described in the Net Administrators Guide in detail). A short note about the name of the SID — don't use dots in the SID and keep it short!

The SID is also relevant for the init.ora file of the gateway. The name of the file is init<SID>.ora. In this article it is called initmyodbc3.ora. The file should be located in $ORACLE_HOME/hs/admin.

It should contain the following entries:

$ORACLE_HOME/hs/admin/initmyodbc3.ora
# This is a sample agent init file that contains the HS parameters that are 
# needed for an ODBC Agent. 
  
# 
# HS init parameters 
# 
HS_FDS_CONNECT_INFO = myodbc3
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so
# 
# ODBC specific environment variables 
# 
set ODBCINI=/usr/local/etc/odbc.ini 
#

Short explanation of the parameters:

  • HS_FDS_CONNECT_INFO — points to the ODBC DSN configured earlier in this article.
  • HS_FDS_SHAREABLE_NAME — points to the ODBC Driver Manager library at $unixODBC/lib/<ODBC_Driver_MANAGER_LIB>.
  • The set ODBCINI=... points to the location of an odbc.ini file you want to use with this hsodbc configuration.


Testing the HSODBC Configuration

At this point, all configuration for HS is complete and now it is time to test it. The following steps can be used to test our HSODBC configuration.

We first create a database link that connects us to the MySQL database. We will be connecting to the test database (running on linux2) as the oracle@alex user. The syntax to create the database link:

CREATE [public] DATABASE LINK <name> 
CONNECT TO <UID> IDENTIFIED BY <pwd> USING '<tnsalias>';
In other words, to connect to the MySQL database, the syntax must be:
$ sqlplus system/manager

SQL> CREATE PUBLIC DATABASE LINK testdb
  2  CONNECT TO "oracle" IDENTIFIED BY "manager" USING 'HSODBC';

  The db link name is testdb. Username and password must be in double quotes, because the username and password are case sensitive. 'HSODBC' points to the alias in the tnsnames.ora file that calls the HS subsystem (gateway). If everything is configured well, a select of a table should be successful.

Now let's test the HS agent by performing some DML to our emp table created earilier in this article.

  Tables within the MySQL database are case sensitive. Thus table names must be surrounded by double quotes.

SQL> column id format 999
SQL> column name format a15

SQL> select * from "emp"@testdb;

  id name
---- ---------------
 100 Jeff
 100 Melody

SQL> insert into "emp"@testdb values(100, 'Alex');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from "emp"@testdb;

  id name
---- ---------------
 100 Jeff
 100 Melody
 100 Alex


Copyright (c) 1998-2014 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Saturday, 18-Sep-2010 17:36:05 EDT
Page Count: 113464