Oracle Database Gateway for MySQL

An organization have lot of choices to choose from the RDBMS world. Oracle Database, Microsoft SQL Server, IBM DB2,PostgreSQL, MySQL are few of them. Most of the organizations out there do have a mix of all these which are used to store their data and in course of time it is inevitable that they would want to transfer data from ‘this database’ to ‘that database’. Oracle, the uncrowned king of this RDBMS world call this “Heterogeneous Connectivity” and have incorporated this facility into their database and this is installed by default.

The steps are:

• Install Oracle Transparent Gateway for MySQL
• Create the ODBC data source
• Configure the gateway initialization parameter file
• Configure the listener
• Configure the Oracle database tnsnames.ora
• Creating DB link from Oracle database to MySQL Server

Step 1 :

Create System DSN for MySQL:

Go to Control Panel > Administrative Tools > ODBC Data Sources

Select MySQL odbc driver and Click on Add Button

Click On Finish

Add Data Source Name and MySQL server credentials :

Click OnTest Button :

Step 2 :
Now go to cd $ORACLE_HOME\hs\admin\
Update following lines to initdg4odbc.ora file :

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
# HS init parameters
HS_FDS_CONNECT_INFO = demo  #dsn name we have recently created
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SUPPORT_STATISTICS = FALSE
#HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
#HS_FDS_TIMESTAMP_MAPPING=DATE
# Environment variables required for the non-Oracle system
#set <envvar>=<value>
# ODBCINI: full path of odbc.ini
set ODBCINI =C:\Windows\ODBC.ini

Step 3 :
Add following lines to listener.ora file :

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\oracle\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(SID_NAME = dg4odbc)
(ORACLE_HOME=D:\oracle\product\11.2.0\dbhome_1)
(PROGRAM = dg4odbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = admin-PC)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = D:\oracle

Step 4 : Restart listener

C:\Users\Administrator>LSNRCTL RELOAD
LSNRCTL for 32-bit Windows: Version 11.2.0.4.0 - Production on 06-NOV-2017 17:40:09
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Admin-PC)(PORT=1521)))
The command completed successfully

Step 5 :Check listener status

C:\Users\Administrator>LSNRCTL STATUS
LSNRCTL for 32-bit Windows: Version 11.2.0.4.0 - Production on 06-NOV-2017 17:40:13
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Admin-PC)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.4.0 - Production
Start Date 06-NOV-2017 10:02:01 Uptime 0 days 7 hr. 38 min. 13 sec
Trace Level off
Security ON: Local OS Authentication SNMP OFF
Listener Parameter File D:\oracle\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File D:\oracle\Administrator\diag\tnslsnr\Admin-PC\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Admin-PC)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 2 handler(s) for this service...
Service "dg4odbc" has 1 instance(s).
Instance "dg4odbc", status UNKNOWN, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).

Append following lines to TNSNAMES.ora file :

dg4odbc =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST =localhost)(PORT = 1521) )
(CONNECT_DATA =(SID = dg4odbc) )
(HS = OK)
)

Check tnsping :

[oracle@cbsdb01 admin]$ tnsping dg4odbc
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 04-NOV-2017 16:52:17
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SID = dg4odbc)) (HS = OK))
OK (10 msec)
[oracle@cbsdb01 admin]$

Create a Database link using above mentioned tnsname entry and MySQL Credential :

SQL> CREATE DATABASE LINK Mysql1 CONNECT TO "root" IDENTIFIED BY "redhat" USING 'dg4odbc';
Database link created.
Now fetch data using DatabaseLink :
SQL> select count(*) from address@mysql1;
COUNT(*)
----------
20000
SQL>

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:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAASFacebook Page: OracleHelp