MYSQL TO ORACLE DATABASE LINK CREATION USING HETEROGENEOUS SERVICES

osted on January 21, 2019 in MySQL, MySQL DBA, Oracle DB Admin

Share via:
 

FacebookTwitterLinkedInWhatsApp

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.

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:
 

FacebookTwitterLinkedInWhatsApp

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)
 
https://www.ktexperts.com/mysql-to-oracle-database-link-creation-using-heterogeneous-services/
posted @ 2020-01-09 15:15  seasonzone  阅读(...)  评论(...编辑  收藏