Goal
Creating database links between Oracle databases is quite base knowledge for DBA's. But in this post I will describe how you can make a link between from an Oracle Database to a MySQL database instance. Both databases are installed on 2 different Oracle Linux machines.
Solution
If not already installed, install the unixODBC library on your Oracle Linux:
[root@ol6db1 ~]# yum install unixODBC
Download the ODBC driver from the MySQL website and install the RPM:
It can be downloaded as an rpm or a tar.gz file that needs to be installed from source.
I downloaded the rpm and installed it:
[root@ol6db1 ~]# rpm -ivh mysql-connector-odbc-5.3.4-1.el6.x86_64.rpm
Create an ini-file with the connection data to the MySQL database:
[oracle@ol6db1 ~]$ more ~/odbc.ini
[myodbc5]
Driver = /usr/lib64/libmyodbc5a.so
Description = Connector/ODBC x.x Driver DSN
SERVER = ol7mysql
PORT = 3306
USER = root
PASSWORD = mysql
DATABASE = tom
OPTION = 0
TRACE = OFF
Define the ODBCINI and LD_LIBRARY_PATH environment variables:
[oracle@ol6db1 ~]$ export ODBCINI=/home/oracle/odbc.ini
[oracle@ol6db1 ~]$ echo $LD_LIBRARY_PATH
/dbsoft/oracle/app/oracle/product/12.1.0/dbhome_1/lib
[oracle@ol6db1 ~]$ export LD_LIBRARY_PATH=/usr/lib64:$LD_LIBRARY_PATH
Test the connection to the MySQL database:
[oracle@ol6db1 dbhome_1]$ isql myodbc5 -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_tom |
+-----------------------------------------------------------------+
| TESTTAB |
| PERSONS |
+-----------------------------------------------------------------+
SQLRowCount returns 2
2 rows fetched
SQL> quit
[oracle@ol6db1 dbhome_1]$
Add this entry to the $ORACLE_HOME/network/admin/tnsnames.ora file (note: take care of the preceding spaces):
myodbc5 =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP) (HOST=ol6db1) (PORT=1531)
)
(CONNECT_DATA=
(SID=myodbc5)
)
(HS=OK)
)
In the $ORACLE_HOME/network/admin/listener.ora file, add this entry (note: the space indentations are very important!):
SID_LIST_LISTENER =
(SID_LIST=
(SID_DESC=
(SID_NAME=myodbc5)
(ORACLE_HOME=/dbsoft/oracle/app/oracle/product/12.1.0/dbhome_1)
(PROGRAM=dg4odbc)
(ENV="LD_LIBRARY_PATH=/usr/lib64:/dbsoft/oracle/app/oracle/product/12.1.0/dbhome_1/lib")
)
)
In the $ORACLE_HOME/hs/admin/ directory, create the file “initmyodbc5.ora”:
[oracle@ol6db1 admin]$ pwd
/dbsoft/oracle/app/oracle/product/12.1.0/dbhome_1/hs/admin
[oracle@ol6db1 admin]$ more initmyodbc5.ora
HS_FDS_CONNECT_INFO=myodbc5
# Data source name in odbc.ini
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
#
# ODBC env variables
set ODBCINI=/home/oracle/odbc.ini
Start the listener:
[oracle@ol6db1 admin]$ lsnrctl start LISTENER
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 18-MAY-2015 11:37:36
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /dbsoft/oracle/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.1.0 - Production
System parameter file is /dbsoft/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /dbsoft/oracle/app/oracle/diag/tnslsnr/ol6db1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol6db1)(PORT=1531)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1531))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 18-MAY-2015 11:37:36
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /dbsoft/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /dbsoft/oracle/app/oracle/diag/tnslsnr/ol6db1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol6db1)(PORT=1531)))
Services Summary...
Service "myodbc5" has 1 instance(s).
Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Test with “tnsping” if the database instance is reachable:
[oracle@ol6db1 admin]$ tnsping myodbc5
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 18-MAY-2015 11:51:12
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=ol6db1) (PORT=1531)) (CONNECT_DATA= (SID=myodbc5)) (HS=OK))
OK (0 msec)
Create a public database link:
[oracle@ol6db1 admin]$ sqlplus system@apexwin
SQL*Plus: Release 12.1.0.1.0 Production on Mon May 18 11:42:56 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create public database link myodbc5 connect to "root" identified by "mysql" using 'myodbc5';
Database link created.
SQL> select * from testtab@"myodbc5";
name
--------------------------------------------------
tom
Conclusion
The database link works perfectly!
