Introduction
In Oracle, a heterogeneous database link allows you to connect to non-Oracle databases from an Oracle database. This is useful for querying and manipulating data across different database systems, such as SQL Server, MySQL, or others, without needing a separate ETL process just to read data. This article walks through creating an Oracle to MySQL database link step by step, using Oracle's Heterogeneous Services and the ODBC driver for MySQL.
Prerequisites
- Network connectivity established between the source and target environments. Verify with
pingortelnet. - The MySQL ODBC connector package installed on the Oracle DB server. Verify with
rpm -qa | grep mysql. You should see packages similar to:mysql-community-client-8.0.35-1.el8.x86_64mysql-connector-odbc-8.0.26-1.el7.x86_64
Environment Used in This Guide
| ## | Source | Target |
|---|---|---|
| Database | Oracle 19c | MySQL |
| IP | 192.168.80.33 | 10.192.225.144 |
| DB Name | ORCLPRD | mysql_db |
| Port | 1521 | 5544 |
| Username | sys | datalake |
Step 1: Install the MySQL ODBC Connector
The heterogeneous connection relies on an ODBC driver to translate between Oracle's Heterogeneous Services and MySQL's native protocol. Install the connector package, either via yum or by downloading the RPM directly from Oracle Yum and installing it manually with rpm -ivh "package" as root.
[root@oracle01~]# yum install mysql-connector-odbc-8.0.26-1.el7.x86_64
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package mysql-connector-odbc.x86_64 0:8.0.26-1.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
====================================================================================
Package Arch Version Repository Size
====================================================================================
Installing:
mysql-connector-odbc x86_64 8.0.26-1.el7 mysql-connectors-community 4.1M
Transaction Summary
====================================================================================
Install 1 Package
Total download size: 4.1 M
Installed size: 22 M
Is this ok [y/d/N]: y
Downloading packages:
mysql-connector-odbc-8.0.26-1.el7.x86_64.rpm | 4.1 MB 00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mysql-connector-odbc-8.0.26-1.el7.x86_64 1/1
Success: Usage count is 1
Success: Usage count is 1
Verifying : mysql-connector-odbc-8.0.26-1.el7.x86_64 1/1
Installed:
mysql-connector-odbc.x86_64 0:8.0.26-1.el7
Complete!
[root@oracle01~]#
Install the mysql-community-client package the same way, it provides the mysql command-line client we'll use later to independently verify connectivity.
Step 2: Create the ODBC Data Source
Create or edit /etc/odbc.ini to define the MySQL data source. If a file already exists, back it up first before adding these entries, and note this typically requires root access to edit.
[oracle@oracle01 admin]$ cat /etc/odbc.ini
[mysql_db]
Description = MYSQL
Driver = /usr/lib64/libmyodbc8a.so
Server = 10.192.225.144
User = datalake
Password = YourP@ssw0rd
Port = 5544
Database = mysql_db
OPTION = 0
TRACE = OFF
[oracle@oracle01 admin]$
Step 3: Create the Heterogeneous Services Init File
Navigate to $ORACLE_HOME/hs/admin and create a file named to match your data source, in the format initDATASOURCENAME.ora. This file tells Oracle's Heterogeneous Services agent how to connect through the ODBC driver.
[oracle@oracle01 admin]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/hs/admin
[oracle@oracle01 admin]$ cat initmysql_db.ora
HS_FDS_CONNECT_INFO = mysql_db
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_FDS_FETCH_ROWS = 1
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=american_america.we8iso8859P1
HS_NLS_NCHAR=UCS2
Step 4: Configure the Listener and TNS Entries
Add a new SID_DESC entry to listener.ora using the dg4odbc program, this is what tells the listener to route connections for this service through the heterogeneous gateway rather than a normal Oracle instance. Then add a matching TNS entry in tnsnames.ora with (HS=OK), which marks it as a heterogeneous services connection.
[oracle@oracle01 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle01)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCLPRD)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = ORCLPRD)
)
(SID_DESC=
(SID_NAME=mysql_db)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)
(PROGRAM=dg4odbc)
(ENV="LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/product/19.0.0/dbhome_1/lib")
)
)
[oracle@oracle01 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCLPRD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLPRD)
)
)
mysql_db =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=192.168.80.33)(PORT=1521)) # IP Oracle Database
(CONNECT_DATA=(SID=mysql_db))
(HS=OK)
)
[oracle@oracle01 admin]$
After saving these files, reload the listener with lsnrctl reload LISTENER so it picks up the new service. Alternatively, you can run a separate listener on a different port dedicated to MySQL connections, in which case you'd only need to start the new listener rather than restart the existing one.
Step 5: Verify the Listener and TNS Connectivity
Confirm the new service is registered with the listener, then use tnsping to verify the TNS alias resolves and the target is reachable.
[oracle@oracle01 admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 20-FEB-2025 11:50:17
Copyright (c) 1991, 2022, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle01)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 20-FEB-2025 11:50:17
Uptime 0 days 8 hr. 10 min. 48 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracle01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle01)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORCLPRD" has 2 instance(s).
Instance "ORCLPRD", status UNKNOWN, has 1 handler(s) for this service...
Instance "ORCLPRD", status READY, has 1 handler(s) for this service...
Service "ORCLPRDXDB" has 1 instance(s).
Instance "ORCLPRD", status READY, has 1 handler(s) for this service...
Service "mysql_db" has 1 instance(s).
Instance "mysql_db", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracle01 admin]$
[oracle@oracle01 admin]$ tnsping "mysql_db"
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 20-FEB-2025 11:51:07
Copyright (c) 1997, 2022, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=192.168.80.33)(PORT=1521)) (CONNECT_DATA=(SID=mysql_db)) (HS=OK))
OK (0 msec)
Step 6: Test the ODBC Connection Independently
Before creating the database link itself, it's worth confirming the ODBC connection and raw MySQL connection both work on their own. This isolates whether any later issue is with the database link configuration or with the underlying connectivity.
[oracle@oracle01 ~]$ isql -v mysql_db
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> show databases
+-----------------------------------------------------------------+
| Database |
+-----------------------------------------------------------------+
| information_schema |
| daily_schedular_db |
| mysql_db |
| mysql |
| performance_schema |
| test |
+-----------------------------------------------------------------+
SQLRowCount returns 6
6 rows fetched
SQL>
[oracle@oracle01 ~]$ mysql -udatalake -p -h10.192.225.144 -P5544
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1094140
Server version: 5.6.47 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| daily_schedular_db |
| mysql_db |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
Quick reference for the mysql command flags used above: -u is the user, -p prompts for the password, -h is the host, -P is the port, and -v (used with isql) enables verbose output.
Step 7: Create the Database Link
With connectivity confirmed at every layer, we can finally create the database link itself. If you want a private database link instead, log into Oracle as the specific user who should own it. Here we're creating a public link, available to any user with the appropriate grants.
[oracle@oracle01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 20 12:07:08 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> CREATE PUBLIC DATABASE LINK ORACLE_TO_MYSQL_DBLINK CONNECT TO "datalake" IDENTIFIED BY "YourP@ssw0rd" USING 'mysql_db';
Database link created.
SQL> set lines 333 pages 333
SQL> col OWNER for a20
SQL> col DB_LINK for a80
SQL> col USERNAME for a20
SQL> col HOST for a30
SQL> col CREATED for a10
SQL> select OWNER, DB_LINK, USERNAME, HOST, CREATED from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
-------------------- -------------------------------------- -------------------- ------------------------------ ----------
SYS SYS_HUB SEEDDATA 17-APR-19
PUBLIC ORACLE_TO_MYSQL_DBLINK datalake mysql_db 20-FEB-25
SQL> select sysdate from dual@ORACLE_TO_MYSQL_DBLINK;
SYSDATE
---------
20-FEB-25
SQL> select * from dual@ORACLE_TO_MYSQL_DBLINK;
D
-
X
SQL>
The successful queries against dual@ORACLE_TO_MYSQL_DBLINK confirm the link is working end to end, Oracle is routing the query through Heterogeneous Services, over ODBC, to MySQL, and getting a valid response back.
What Next --> Oracle to MSSQL Server DB Link
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment