|
|
|
|
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: The 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:
Attention: 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
Note: 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:
|
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:
[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:
Oracle TNS Listener
Let's start by adding the following new SID to the 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.
| Note: 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
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:
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
Jeffrey Hunter graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science. Jeff is an Oracle Certified Professional, Java Development Certified Professional, Author, and currently works as a Senior Database Administrator for The DBA Zone, Inc. His work includes advanced performance tuning, Java programming, capacity planning, database security, and physical / logical database design in a UNIX, Linux, and Windows NT environment. Jeff's other interests include mathematical encryption theory, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. Jeff has been a Sr. Database Administrator and Software Engineer for over 11 years and maintains his own website site at: http://www.iDevelopment.info.