Oracle to MySQL Connection

I don' t know whether you want to connect a different database to Oracle, but it is sometimes necesarry to do that. Since MySQL is opensource and freely distributable, it comes embedded with some applications and you are subject to make the connection.

Purpose

Make an external database link between Oracle 10g Database and MySQL 5.1 server.

Prerequisites

Platforms and databases that will be used are:

Oracle 10g Database (10.2.0.3.0) on Windows XP / 2003 / 2008

MySQL Server 5.1 on Windows XP / 2003 / 2008

Also, since I am going to use the ODBC connection:

Openlink ODBC Driver for MySQL 5.1(UDA-Single Tier)

The reason not to use the free MySQL Oracle ODBC driver is that when I use the classic one, I faced with some problems about querying string characters. OpenLink’s ODBC driver solved this problem.

General

There are three steps to do this:

1. MySQL Server-Side Configuration

2. MySQL Driver Installaton and Configuration

3. Oracle Heterogeneous Services Configuration

MySQL Server-Side Configuration

It is better to create an addtional account and give appropriate permissions for this purpose. But, I am going to use the “root” account to access MySQL, so I enable access from remote machines for “root” account.

MySQL Driver Installation and Configuration

As you know, MySQL driver has to be installed on the server where Oracle Database resides.

After the installation, first we have to create the ODBC connection. To do this, we go to the ODBC Data Sources by typing “odbcad32” to the Run Window. In the System DSN tab, we add a new Data Source using Openlink Lite for MySQL 5[6.0] driver. The next window asks a name for this datasource. This name is important because you are going to use this name in Oracle-Side configuration. I recommend to use MYSQL as name because I will use this name in my examples. Configuration continues with hostname, port number, database name and login credentials. Enter the computer name or Ip address for the hostname, MySQL database port number and the database name that you want to connect from Oracle. On login credentials, use the account that you have created in MySQL configuration side.

E.g. Hostname : 192.168.1.12

Port : 3306

Database : Mysql

Login ID : root

Password : *****

Other configuration parameters are not that important but by setting up these parameters, you can make some constraints to your connections such as read-only connectivity, character set obligation and so on. At the end, it will be helpful to use Test Data Source button to diagnose problems.

Oracle Heterogeneous Services Configuration

In this section, we are going to tell to Oracle Database to use the configured ODBC driver in order to reach the datas in MySQL server. In general, there are several ways to connect non-Oracle databases to an Oracle database like Transparent Gateway and Generic Connectivity. But, MySQL server do not let us to use Transparent Gateway technology, so we use Heterogeneous Services ODBC agent.

First; we have to make a copy of inithsodbc.ora file and rename it like init'SID'.ora in $ORACLE_HOME/hs/admin folder. This file will be responsible of which ODBC driver will be used in ODBC agent. 'SID' parameter can be anything but it is better to note it down because it will be used in tnsnames.ora and listener.ora files. In this file, two parameters HS_FDS_CONNECT_INFO and HS_FDS_TRACE_LEVEL must be set correctly. HS_FDS_CONNECT_INFO parameter has to set to the name of ODBC Data Source created in MySQL Driver Instalaltion and Configuration Side. HS_FDS_TRACE_LEVEL is set to 0 generally.

E.g. initMYSQL.ora

HS_FDS_CONNECT_INFO = MYSQL

HS_FDS_TRACE_LEVEL = 0

After saving the initMYSQL.ora file, we have to set the SID_LSIT_'LISTENER_NAME' part of the listener.ora file. (The listener.ora file locates under $ORACLE_HOME/network/admin folder.)

We add another SID_DESC entry in SID_LIST:

( SID_DESC =

(SID_NAME = MYSQL)

(ORACLE_HOME= C:\oracle\product\10.2.0\db)

(PROGRAM = hsodbc)

)

Any changes in listener.ora file leads us to restart the Listener service. To do this, you can use lsnrctl stop and lsnrctl start commands in Run Window.

After restarting the Listener service, we have to configure the tnsnames.ora file which is in the same folder with listener.ora file. The configuration is only another entry like the database connection:

MYSQL=

(

DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST='ORACLE_DB_SERVER_NAME')(PORT='PORT_NUMBER'))

(CONNECT_DATA=

(SID='SID')

)

(HS = OK)

)

MYSQL = The connection identifier name, it can be anything, in my example is MYSQL

'ORACLE_DB_SERVER_NAME' = The hostname or ip address of Oracle Database server.

'PORT_NUMBER'= The Oracle database port number; default value is 1521.

'SID'= It stands fort he value given above. In our example it is MYSQL.

(HS = OK) means that this connection will use Oracle heterogeneous services.

Conclusion

After all of this configuration, the connection must work properly. The last part is to create a classic database link using the new entry in tnsnames.ora file and use it. The commands are:

Create database link DB_LINK

connect to “root” identified by “******”

using ‘MYSQL’;

select * from employees@DB_LINK;

DB_LINK is the database link name, root is the privileged username to connect MySQL server and mysql database and ‘MYSQL’ is the tnsnames.ora entry.

Hope this will be useful.