Thursday, August 6, 2015

Database link from Oracle Database to MySQL


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!

No comments:

Post a Comment