Configure Oracle Database Gateway for MYSQL DB

 

Setup a connection between Oracle & MYSQL database.

Source is Oracle & Target is MYSQL.

ENVIRONMENT DETAILS

Description	Oracle Database	Oracle Gateway	MySQL DB Server
Hostname / IP	orcldb01 / orcldb02	sqlgateway  192.xx.xxx.x1	mysqldb01 / 192.xx.xx.xx0
Version	Database 12.1.0.2 < 12c RAC Cluster >	12c Gateway	Database – MYSQL DB
Database Name	PROD	Odbc gateway driver	TransDB
Operating system	Windows Server 2012 R2	Windows Server 2012 R2	Windows

Oracle Database Gateway for MySQL DB

 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 DB Server

Step 1:

 

Install the MYSQL odbc driver, on the gateway server. You can use MYSQLDB host as gateway server,

but it is better to have it installed on separate host.

 Gives an error

Solution:-

Install the latest Microsoft Visual Redistributable by downloading the below highlighted exe media

 

        Retry the installation of MYSQL odbc connector

 

 

 

 

 

       Let’s create a DSN

      Go to Control Panel > Administrative Tools > ODBC Data Sources

 

      Select MySQL odbc driver and Click on Add Button

**Connection was failing – Need to adjust some configuration in MYSQLDB.

Set allowed connections from remote host to % from MYSQLDB console.

Step 2: Install Oracle Gateway

There is no specific gateway product for MYSQLDB, so we will be using general ODBC gateway driver.

Step 3 :

Now go to cd $ORACLE_HOME\hs\admin\

Copy initdg4odbc.ora to initMSLINK.ora

          < Here MSLINLK is the name of DSN source we have created in step 2 >

Edit the file initMSLINK.ora under location C:\oracle\oragateway\product\12.1.0\tghome_1\hs\admin

             HS_FDS_CONNECT_INFO = MSLINK
             HS_FDS_TRACE_LEVEL = 0
             HS_DB_NAME = MSLINK
             HS_DB_DOMAIN = XXXX.COM

Step 4 :

cd $ORACLE_HOME\network\admin
And add the following entry into listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = C:\oracle\oragateway\product\12.1.0\tghome_1)
      (SID_NAME = MSLINK)
      (PROGRAM = dg4odbc)
    )
  )

--> Bounce the listener

Step 5 :

Copy the tns entry to both the nodes on Oracle RAC DB servers.

 MSLINK =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sqlgateway)(PORT = 1521)) )
    (CONNECT_DATA =      (SID = MSLINK)  )
     (HS = OK)
  )

Create a DBLINK from Oracle Database
CREATE DATABASE LINK myslink CONNECT TO "root"
IDENTIFIED BY "Password” USING 'MSLINK';