DATABASE LINK FROM ORACLE TO MySQL

All bold statements are shell commands or configuration steps.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

1. Introduction

This document describes steps to create a database link between Oracle to MySQL (Heterogeneous Services) / Oracle Gateway, Regarding Oracle Documentation Gateways will integrate with any number of non-Oracle systems from an Oracle application.

All bold statements are shell commands or configuration steps.

2. Database Link Configuration

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

It is a connection between two physical database servers that allows a client to access them as one logical database.

2.1 Configure Database Link from ORACLE to MySQL

To start Configure Database Link we need to follow the below steps:

Step #1:-

Determine the Version for dg4odbc on the Operating system using

>file $ORACLE_HOME/bin/dg4odbc

This Command Explain which version of ODBC Driver.

Step #2:-

Install unixODBC and MySQL connector (This step should be done as root user).

# yum install unixODBC

# yum install mysql-connector-odbc

Step #4:-

(As Oracle User). Export Variables.

In Linux – .bash_profile

In Solaris -.profile

export LD_LIBRARY_PATH=/usr/lib64:/usr/lib64/libmyodbc5.so:$ORACLE_HOME/lib

export ODBCINI=/etc/odbc.ini

export ODBCSYSINI=/etc

Step #5:-

(As Root User).

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

# Driver from the mysql-connector-odbc package

# Setup from the unixODBC package

[MySQL]

Description = ODBC for MySQL

Driver = /usr/lib64/libmyodbc5.so

Setup = /usr/lib64/libodbcmyS.so

# Driver64 = /usr/lib64/libmyodbc5.so

# Setup64 = /usr/lib64/libodbcmyS.so

FileUsage = 1

DATABASE = cs_dm_stage

UID = <username>

PWD = welcome123!@#

Option = 3

SERVER = nyads2hslrdb02.na.weightwatchers.net

PORT = 3306

It’s configured.

Step #6:-

As Oracle User:-

[root@server etc]# cd /usr/lib64

[root@server lib64]# ldd libmyodbc5.so

linux-vdso.so.1 => (0x00007fff1afff000)

libltdl.so.7 => /usr/lib64/libltdl.so.7 (0x00007f4b16c01000)

libmysqlclient_r.so.16 => /usr/lib64/mysql/libmysqlclient_r.so.16 (0x00007f4b1686c000)

libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f4b16635000)

libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f4b1641c000)

libm.so.6 => /lib64/libm.so.6 (0x00007f4b16197000)

libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f4b15f7a000)

libssl.so.10 => /usr/lib64/libssl.so.10 (0x00007f4b15d0d000)

libcrypto.so.10 => /usr/lib64/libcrypto.so.10 (0x00007f4b15928000)

libz.so.1 => /lib64/libz.so.1 (0x00007f4b15712000)

libodbcinst.so.2 => /usr/lib64/libodbcinst.so.2 (0x00007f4b15501000)

libc.so.6 => /lib64/libc.so.6 (0x00007f4b1516c000)

libdl.so.2 => /lib64/libdl.so.2 (0x00007f4b14f68000)

libfreebl3.so => /usr/lib64/libfreebl3.so (0x00007f4b14d65000)

/lib64/ld-linux-x86-64.so.2 (0x0000003f64c00000)

libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f4b14b20000)

libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f4b14839000)

libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f4b14635000)

libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f4b14408000)

libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f4b141fd000)

libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f4b13ff9000)

libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f4b13ddf000)

libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f4b13bc0000)

The Most Important Thing in the above output is to check if there is nothing in the library “Not Found”.

Step #7:-

(As Oracle User).

Test connection using odbc only, Only on OS Level

# ./isql –v MySQL

The below is output:-

Connected

Until this step we 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 a template located in $ORACLE_HOME/hs/admin just copy it with the new name, the default one is initdg4odbc.ora we can leave without change and edit it.

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

**SQL > Select * from “cs_memberdata_staging”@ mysql_dblink;**