How to connect Oracle Server to Mysql - dg4odbc

Connect - oracle to mysql

linux console:

. /home/oracle/.bash_profile

Verify version dg4odbc - 64bits or 32

file $ORACLE_HOME/bin/dg4odbc

from http://unixODBC.org download last version

cd ~/Downloads
wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.4.tar.gz
tar -zxvf unixODBC-2.3.4.tar.gz
cd unixODBC-2.3.4
export CFLAGS="-m64"
./configure --prefix=/usr/local/unixODBC
make
make install


cd ~/Downloads
wget http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm
yum localinstall mysql57-community-release-el6-7.noarch.rpm

Verify if repository got installed
yum repolist enabled | grep "mysql.*-community.*"

yum install mysql-connector-odbc-setup.x86_64
At warning message Retrieving key - type Y

update index for search on your server linux

updatedb

search
locate libmyodbc5w.so

At results should apear /usr/lib64/libmyodbc5w.so ( Driver Mysql for odbc)

at profile file from user oracle

vi /home/oracle/.bash_profile

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/unixODBC/lib:/usr/lib64:/u01/app/oracle/product/12.1.0/db_1/hs/lib
export ODBCINI=/usr/local/unixODBC/etc/odbc.ini
export ODBCSYSINI=/usr/local/unixODBC/etc
export NLS_LANG=ROMANIAN_ROMANIA.AL32UTF8

vi /usr/local/unixODBC/etc/odbcinst.ini

append or add

[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/lib/psqlodbc.so
Setup=/usr/lib/libodbcpsqlS.so
Driver64=/usr/lib64/psqlodbc.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1
UsageCount=3

[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1
UsageCount=3

[MySQL ODBC 5.3 Unicode Driver]
Driver=/usr/lib64/libmyodbc5w.so
SETUP=/usr/lib64/libmyodbc5S.so
UsageCount=1

[MySQL ODBC 5.3 ANSI Driver]
Driver=/usr/lib64/libmyodbc5a.so
SETUP=/usr/lib64/libmyodbc5S.so
UsageCount=1

vi /usr/local/unixODBC/etc/odbc.ini

append or add

[SQLSERVER1]
Driver          = /usr/lib64/libmyodbc5w.so
PORT            = 3306
SERVER          = 192.168.0.33
USER            = user
PASSWORD        = password
Database        = database_name
Option          = 3
QuotedId        = Yes
EnableQuotedIdentifiers         = 1


We need to edit

vi /u01/app/oracle/product/12.1.0/db_1/hs/admin/initSQLSERVER1.ora
add

HS_FDS_CONNECT_INFO = SQLSERVER1
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/local/unixODBC/lib/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
set ODBCINI=/usr/local/unixODBC/etc/odbc.ini
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15


vi /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
add

SQLSERVER1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1521))
    (CONNECT_DATA =
#      (SERVER = DEDICATED)
      (SERVICE_NAME = SQLSERVER1)
    )
    (HS=OK)
  )



vi /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
add

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=SQLSERVER1)
      (ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1)
      (PROGRAM=dg4odbc)
      (ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/db_1/lib:/lib:/usr/lib:/usr/local/unixODBC/lib:/usr/lib64:/u01/app/oracle/product/12.1.0/db_1/hs/lib)
    )
  )


We can verify if we can connect to mysql from our server with unixodbc

cd /usr/local/unixODBC/bin
./isql -v SQLSERVER1

SQL console should apear -  we make our test with a select statement and if something apear, than we are good to go.

quit

To be able accessing from oracle instance:



su oracle
. /home/oracle/.bash_profile

lsnrctl stop
lsnrctl start
lsnrctl status

sqlplus / as sysdba

Create dblink for mysql
CREATE PUBLIC DATABASE LINK SQLSERVER1 CONNECT TO "user" IDENTIFIED BY "password" USING 'SQLSERVER1';

and we test with a select statment

select * from "users"@SQLSERVER1;

( If we want to drop ddblink, use: DROP PUBLIC DATABASE LINK SQLSERVER1; )