MYSQL TO ORACLE DATABASE LINK CREATION USING HETEROGENEOUS SERVICES

Share via:

MYSQL TO ORACLE DATABASE LINK CREATION USING HETEROGENEOUS SERVICES

A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server.

The remote database can be same oracle or it can be non-oracle database.

To access non-Oracle systems you must use Oracle Heterogeneous Services.

Software’s required:

  • Oracle RDBMS software
  • MySQL software
  • ODBC Drivers
  • MySQL Connector

 

Prerequisites:

  • Oracle database should be up and running.
  • MySQL database should be up and running.
  • Oracle net services should be up and running.
  • ODBC and ODBC agent should be configure, up and running.

 

Please check ORACLE database link configuration for basic database link information.

DATABASE LINK IN ORACLE

Process for heterogeneous database link configuration:

STEP-1

Oracle Database Configuration:

Check the database version

Note: ORACLE RDBMS running with 64bit

 

MySQL Database Configuration:

Note: MySQL running with 64bit

Download 64 bit ODBC and MySQL database connector and install.

 

STEP-2

Configure the ODBC:

 Note: From  Oracle 11g, the Oracle Heterogeneous Service (HS)  executable name is now called DG4ODBC. If you’re using a 64-bit version of Oracle you must use a 64-bit ODBC driver. If you’re using a 32-bit version of Oracle, you must use a 32-bit ODBC driver.

Check the Oracle Heterogeneous Service (HS) executable

 

ODBC Drivers you can download (RPM) from the below link and install.

https://dev.mysql.com/downloads/connector/odbc/

https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-installation-binary-unix-rpm.html

 

STEP-3

ORACLE NET SERVICE CONFIGURATION:

Configure the oracle Net services using listener.ora and tnsnames.ora

Configure Listener

Configure the New listener using below info:

Use the below configuration:

Note:

LISTENER NAME = ktuser

SID_NAME = ktexperts

HOST = SERVER1

PORT = 1522

 

Start and Check the status of listener ktuser

 

Configure the Tnsnames

 Tnasnames.ora

Use the below configuration:

Check the Connection

 

STEP-4

Create the user:

Create the MySQL database and MySQL user and grant the necessary privileges to user.

                                                                ——————————-KTUSER—————————————–
FOR THE SERVER: SERVER1

FOR THE SERVER:  ANY SERVER

FOR THE SERVER: LOCAL HOST

 

CHECK THE CONNECTION:

 

CREATE THE DATABASE AND TABLE :

 

Create the Table with name ktmytab

 

Insert few values into the table.

 

STEP-5

Configure the ODBC:

Add the below content:

Note:

User:  MySQL user

Password:  MySQL user

Database: MySQL local Database

 

STEP-6

Configure the initialization file for the Oracle Heterogeneous Service (HS).

 

Modify the content  as like below:

 

STEP-7

Connect the Oracle database and create public database link to access MySQL database data.

Note:

user :  MySQL user

Password:  MySQL user

Tns Entry  : Oracle Net Tnsnames alias

 

 

 

 

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (6 votes, average: 5.00 out of 5)
Loading...

One thought on “MYSQL TO ORACLE DATABASE LINK CREATION USING HETEROGENEOUS SERVICES

Add Comment