oracle to mysql databaselink

DATABASE LINK FROM ORACLE TO MYSQL

Introduction

This document describe steps to create database link between oracle to MySQL (Heterogeneous

Services) / Oracle Gateway, Regarding to Oracle Documentation Gateways will integrate with any

number of non-Oracle systems from an Oracle application.

Environment:-

Operating System: Solaris 11.1

Database: - 11.2.0.3

In my case Grid Infrastructure installed on both node, so I choose one node to configure database link.

Software Needed:-

1- UnixODBC (Generic), Download From here.

2- MySQL ODBC/Connector, Download From Here.

Notes:-

Command Line will be in Red Color.

Output for command Line will be in Blue Color.

http://www.unixodbc.org/

http://dev.mysql.com/downloads/connector/odbc/


About the Author

Osama Mustafa has progressive experience in Oracle Products, community. He recently served as

Oracle Database Administrator.

Provide Database Implementation Solutions, High Availability Solution, Infrastructure and Storage

Planning, Install, Configure, Implement and manage Oracle E-Business Suite environments. Architect,

build and support highly-available Oracle EBS, Database and Fusion Middleware environments including

appropriate reporting, Installs, configures, upgrades, tunes, and maintains production, development and

test databases.

He entered Oracle ACE Program in 2013, he is author for the book Oracle Penetration Testing, Osama

Mustafa Certified OCP 10g,11g, Linux Implementations , Certified Ethical hacker and LPT , and Solaris

Administrator.

Include to all this Osama Mustafa is international Speaker in Oracle User Group and Oracle OTN Tour,

Published Online Articles in His blog about Oracle Technology.

Twitter: @OsamaOracle G+: Osama Mustafa SlidShare: Osama Mustafa LinkedIn: http://www.linkedin.com/in/osamamustafa Blog: https://osamamustafa.blogpsot.com.

https://twitter.com/OsamaOracle

https://plus.google.com/107240185832592449830

http://www.slideshare.net/OsamaMustafa

http://www.linkedin.com/in/osamamustafa

https://osamamustafa.blogpsot.com/


To start Configure Database Link you need to follow the below steps first, to make easier I already upload

the necessary files you will need on my account here: -

https://github.com/OsamaOracle/db4odbc.git

You Can Download it and Change Path depend on files and Library Locations, You can create your entire

in Listener.ora and Tnsnames.ora but Sometimes problem related to space and “(“ “)” will show up.

Step #1:-

Determine Version for dg4odbc on Operating system using

file $ORACLE_HOME/bin/dg4odbc

ELF 64-bit LSB executable, Intel x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically

linked (uses shared libs), for GNU/Linux 2.6.9, not stripped

This Command Explain which version of ODBC Driver I should use Which 64 bit.

Step #2:-

(This step should be done as root user).

It’s Time to Download and Install unixODBC, I will assume you already download the file and upload to

the server, I am using Solaris 11.2 but as I already mentioned before I will build unixODBC manually

therefore I will not use unixODBC.pkg.

In the website http://unixODBC.org they already mentioned step to configure it but I will re mention it here

again.

I prefer to setup and configure unixODBC under /usr/local/unixODBC.

Installation Steps:-

# unzip unixODBC.

https://github.com/OsamaOracle/db4odbc.git

http://unixodbc.org/


# cd UnixODBC.

# export CFLAGS="-m64"

# ./configure --prefix=/usr/local/unixODBC

# make

# make install

Step #3:-

(As Oracle User).

Now unixODBC installed and configured under /usr/local/unixODBC, Next Step Download MySQL ODBC

Connecter, Choose the right one for your OS depend on MySQL Version.

Upload connector to the server and unzip it only (without do anything else) under /u01/app/oracle/.

Step #4:-

(As Oracle User).

Export Variables.

In Linux .bash_profile

In Solairs .profile

First Variable:-

export LD_LIBRARY_PATH= /u01/app/oracle/product/11.2.0/db_home1/lib32:/usr/local/unixODBC/lib/:/u01/app/oracle/mysql-connector-odbc-5.1.6-solaris11-x86-32bit/lib/libmyodbc5.so

Second Variable:-

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

export ODBCSYSINI= /usr/local/unixODBC/etc

This step is very important and without will cause problem with Library.


Step #5:-

( As Root User ).

Configure odbc.ini located in ( /usr/local/unixODBC/etc ), the file should look like the below :-

[DSN-NAME] Driver = /u01/app/oracle/mysql-connector-odbc-3.51.30-solaris10-sparc-64bit/lib/libmyodbc3.so DATABASE = <MySQL-Database-name> DESCRIPTION = MySQL ODBC 5.2.5 Connector Sample PORT =<listing Port for MySQL> Default 3306 SERVER = < MySQL Server IP > UID= username PWD = password (should be complex). Option=3 Examples:-

[MySQL] Driver = /u01/app/oracle/mysql-connector-odbc-3.51.30-solaris10-sparc-64bit/lib/libmyodbc3.so DATABASE = 4s DESCRIPTION = MySQL ODBC 5.2.5 Connector Sample PORT = 3306 SERVER = 172.16.2.120 UID= root PWD = Osama123 Option=3

It’s configured.


Step #6:-

I choose to put this as steps because it will avoid time wasting which library test. As Oracle User:- # ldd libmyodbc3.so The output will be like:- libsocket.so.1 => /lib/64/libsocket.so.1 libnsl.so.1 => /lib/64/libnsl.so.1 libm.so.2 => /lib/64/libm.so.2 librt.so.1 => /lib/64/librt.so.1 libz.so.1 => /lib/64/libz.so.1 libodbcinst.so.1 => /usr/lib/64/libodbcinst.so.1 libdl.so.1 => /lib/64/libdl.so.1 libthread.so.1 => /lib/64/libthread.so.1 libc.so.1 => /lib/64/libc.so.1 libmp.so.2 => /lib/64/libmp.so.2 libmd.so.1 => /lib/64/libmd.so.1 libltdl.so.7 => /usr/lib/64/libltdl.so.7 libpthread.so.1 => /lib/64/libpthread.so.1 libcryptoutil.so.1 => /lib/64/libcryptoutil.so.1 /lib/sparcv9/../libm/sparcv9/libm_hwcap1.so.2 The Most Important thing in the above output to check if there is nothing in library “Not Found”

Step #7:-

(As oracle User).


Test connection using odbc only, Only on OS Level, go the /usr/local/unixODBC/bin. # ./isql –v [DSN-NAME] Example:- # ./isql –v MySQL.

The below is output:-

+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>

Until this step you didn’t do anything only configure odbc, next step will explain how to configure Oracle.

Step #8:-


Configuration for Oracle Start with initSID.ora file. Oracle already create template located in

$ORACLE_HOME/hs/admin just copy with new name, the default one initdg4odbc.ora you can leave

without change and edit it.

Edit the file and set the new variables like the below:-

# #HS init parameters # HS_FDS_CONNECT_INFO= [DSN_NAME] same as odbc.ini HS_FDS_TRACE_LEVEL= DEBUG Trace level. HS_FDS_SHAREABLE_NAME= /usr/local/unixODBC/lib/libodbc.so HS_FDS_SUPPORT_STATISTICS= FALSE HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1 #HS_NLS_DATE_FORMAT=YYYY-MM-DD # # ODBC specific environment variables # set ODBCINI=/usr/local/unixODBC/etc/odbc.ini

Example:-

# #HS init parameters # HS_FDS_CONNECT_INFO= MySQL HS_FDS_TRACE_LEVEL= off HS_FDS_SHAREABLE_NAME= /usr/local/unixODBC/lib/libodbc.so HS_FDS_SUPPORT_STATISTICS= FALSE HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1 #HS_NLS_DATE_FORMAT=YYYY-MM-DD # # ODBC specific environment variables # set ODBCINI=/usr/local/unixODBC/etc/odbc.ini

Step #9:


This Step will describe how to configure Listener.ora, You can download Listener.ora and copy/Paste the

entire to make sure there’s nothing wrong or you can create your own.

Open Listener.ora and add the below line depend on your Configuration :-

SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=dg4odbc) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_home1) (ENVS=LD_LIBRARY_PATH=/usr/local/unixODBC/lib:/u01/app/oracle/product/11.2.0/db_home1/lib:/u01/app/oracle/mysql-connector-odbc-3.51.30-solaris10-sparc-64bit/lib) (PROGRAM=dg4odbc) ) )

Some Notes about the above:-

1- SID_LIST_LISTENER Indicate to your LISTENER NAME. 2- SID_NAME= Depend init….ora file. 3- Change ORACLE_HOME. 4- LD_LIBRARY_PATH Depend where you installed unixODBC and MySQL connecter. 5- Program Indicate to init…ora name.

Now After edit Listener.ora Save it, this step require to restart Listener.ora LSNRCTL > stop LISTENER LSNRCTL > Start LISTENER

Output:-


Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production Start Date 16-JUN-2014 15:51:47 Uptime 0 days 1 hr. 50 min. 11 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/Node1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Node1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Node2)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "PRDSBL" has 1 instance(s). Instance "PRDSBL1", status READY, has 1 handler(s) for this service... Service "PRDSBLXDB" has 1 instance(s). Instance "PRDSBL1", status READY, has 1 handler(s) for this service... Service "dg4odbc" has 1 instance(s). Instance "dg4odbc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully

Step #10:-

Finally add new entire to tnsnames.ora

dg4odbc = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=sbl-prd-db1)(PORT=1521)) (CONNECT_DATA=(SID=dg4odbc)) (HS=OK) )

tnsping dg4odbc should work.

Step #11:-


SQL > create public database link <DB-LINK-NAME> connect "usename" identified by "password" using

'odbc';

Notes:

1- Username & password should be the same as odbc.ini ( in my case root and Osama123);

2- Tnsnames.ora should be the same as entire in tnsnames.ora.

Now Test it :-

SQL > Select * from “Table-name”@DB-Link

Reference:-

How to Resolve Common Errors Encountered while using Database Gateways (DG4IFMX, Dg4MSQL, DG4SYBS), DG4ODBC or Generic Connectivity (Doc ID 234517.1)

Database Gateway and Generic Connectivity (DG4ODBC) Licensing Considerations (Doc ID 232482.1)

Gateway Configuration Utility for Database Gateway for ODBC - DG4ODBC - to Connect to Non-Oracle Databases For Example - DB2, SQL*Server, Sybase, Informix, MySQL (Doc ID 1274143.1)

Master Note for Oracle Gateway Products (Doc ID 1083703.1)

What Are The Options To Connect From Oracle Via a Database Link Using DG4ODBC To MySQL Using An ODBC Driver? (Doc ID 1526864.1)

Thank you Osama Mustafa Oracle ACE.