Site icon ORACLEAGENT BLOG

DATABASE LINK FROM ORACLE TO MySQL

DATABASE LINK FROM ORACLE TO MySQL

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.
  1. 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:-
HS_FDS_CONNECT_INFO = MySQL
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = /home/oracle/odbc_trace.txt
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5.so
#HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
#HS_NLS_NCHAR = AMERICAN_AMERICA.WE8ISO8859P1
set ODBCINI= /etc/odbc.ini
HS_FDS_SUPPORT_STATISTICS= FALSE
HS_FDS_SQLLEN_INTERPRETATION = 64
HS_RPC_FETCH_REBLOCKING= OFF
HS_FDS_FETCH_ROWS = 1
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 depending on your Configuration:-
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ENVS = LD_LIBRARY_PATH=/usr/lib64:/usr/lib64/libmyodbc5.so:/u01/app/oracle/product/11.2.0.4/db_1/lib)
      (SID_NAME = dg4odbc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
      (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 on where you installed unixODBC and MySQL connecter.
5- Program Indicate to init…ora name.
Now After editing Listener.ora Save it, this step requires restarting Listener.ora
> Reload LISTENER
lsnrctl reload
Step #10:-
Finally, add new entire to tnsnames.ora
dg4odbc  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=bosporadb01)(PORT=1622))
    (CONNECT_DATA=(SID=dg4odbc))
    (HS=OK)
  )
tnsping dg4odbc should work.
Step #11:-
This step will create a database link for MySQL.
          SQL > create public database link mysql_dblink connect to “csmigration” identified by ” welcome123!@#” using ‘dg4odbc’;
Now Test it:-
 SQL > Select * from “cs_memberdata_staging”@ mysql_dblink;
We have done the setup.
MySQL Server Details:
               Server Name: <server-name/host name>
Database: cs_dm_stage
Username: <username>
Password: <password>

 

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:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Exit mobile version