In most of the enterprise application setups there will be many application communicating with each other, and some of them might be in the different databases as well. There will be a requirement to have the data being pulled to do some operations on it. Recently I came across the same requirement, we needed to be able to query the MySql database tables data in our Oracle database. How to do it? Simple terms have the DB link created and start using the same. But, in order to do so you need to have Heterogeneous DBlink to be setup. In the blow steps you’ll learn how to do so.
To enable the communication you need to setup the ODBC Data source name in your Oracle DB server. Below are the steps to setup the same.
Step 1: Create mysql user on mysql database and provide the necessary grants and access.
CREATE USER 'mysqlusr'@'192.168.0.1' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'mysqlusr'@'192.168.0.1'; FLUSH PRIVILEGES;
Note the above command will grant all privileges to all the db in mysql, one can change as per their needs. And “192.168.0.1” is the Oracle database server IP.
Step 2: Install unixODBC drivers on Oracle Database server
yum install unixODBC
Step 3: Create Data Source name (DSN) in Oracle DB server in file /etc/odbc.ini
[oracle@itzprod1b admin]$ cat /etc/odbc.ini [ODBC Data Sources] mydb = MySQL ODBC 3.51 Driver DSN [mydb] Driver = /usr/lib64/libmyodbc5.so Description = MySQL ODBC 3.51 Driver DSN SERVER = 192.168.0.4 PORT = 3306 USER = mysqlusr Password = password Database = MY_DB OPTION = 3 SOCKET =
Note , here “mydb” is your DSN.
Step 4: Create listener with static sid list and add tns entry in oracle server
======================== [oracle@prod admin]$ cat listener.ora ormig = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1566)) ) ) ) SID_LIST_ormig= (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1) (SID_NAME = mydb) (PROGRAM=dg4odbc) (ENV="LD_LIBRARY_PATH=/usr/lib64/:/u01/app/oracle/product/11.2.0.4/dbhome_1/lib/") ) ) ======================== [oracle@prod admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. mysqlusr_MY_DB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1566)) ) (CONNECT_DATA = (SID = mydb) ) (HS = OK) )
Step 5: Start listener and check tnsping
[oracle@prod admin]$ lsnrctl status ormig LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-FEB-2020 16:08:27 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.1)(PORT=1566))) STATUS of the LISTENER ------------------------ Alias ormig Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 28-FEB-2020 15:59:40 Uptime 0 days 0 hr. 8 min. 47 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/prod/ormig/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.1)(PORT=1566))) Services Summary... Service "mydb" has 1 instance(s). Instance "mydb", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@prod admin]$ [oracle@prod admin]$ tnsping mysqlusr_MY_DB TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 28-FEB-2020 16:08:36 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1566))) (CONNECT_DATA = (SID = mydb)) (HS = OK)) OK (0 msec) [oracle@prod admin]$
Step 6: Go to $ORACLE_HOME/hs/admin and create new init file with DSN name . And configure as below
[oracle@prod admin]$ cd - /u01/app/oracle/product/11.2.0.4/dbhome_1/hs/admin [oracle@prod admin]$ ls -ltr -rw-r--r-- 1 oracle oinstall 826 Feb 28 15:26 initmydb.ora ================ [oracle@prod admin]$ cat initmydb.ora | grep -v ^# HS_FDS_CONNECT_INFO = mydb HS_FDS_TRACE_LEVEL = off HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5.so set ODBCINI=/etc/odbc.ini
Step 7:- Create the public database link on oracle database as below:-
create public database link mysqlusr_MY_DB connect to "mysqlusr" identified by "password" using 'mysqlusr_MY_DB'; col owner for a22 col host for a66 select * from dba_db_links; OWNER DB_LINK USERNAME HOST CREATED ---------------------- -------------------------------- ------------------------------ ---------------------------- --------- PUBLIC mysqlusr_MY_DB mysqlusr mysqlusr_MY_DB 28-FEB-20
Step 8: Test the sql and table is accessible from oracle.
desc "mysqltable"@mysqlusr_MY_DB; Name Null? Type -------------------------------------- -------- ---------------------- SrNo NUMBER(10) xxxcode VARCHAR2(20) RequestDate DATE ConfirmDate DATE ip_address VARCHAR2(40) Program VARCHAR2(15) Status CHAR(1)
Voila! and that is how you can query mysql table from oracle database.
Oracle DBA solutions 