The Ora DBA: Oracle to MySQL : Heterogeneous Database Link Configuration Step by Step

Oracle DBA Database Link DG4ODBC Heterogeneous Services MySQL Oracle 19c Oracle Linux 8

Applications often need to read data that lives outside Oracle. A common example is a MySQL database used by a separate application, while reports or PL/SQL code in Oracle need access to the same data. Instead of building an export/import process, Oracle can query MySQL directly through a heterogeneous database link.

This is done with Oracle Database Gateway for ODBC (DG4ODBC), which ships with the Oracle Database home, together with the MySQL Connector/ODBC driver and the unixODBC driver manager. Once configured, MySQL tables can be queried from Oracle using normal @dblink syntax.

How it works: Oracle session → DB link → TNS alias with (HS=OK) → listener static SID → dg4odbc process → unixODBC → MySQL ODBC driver → MySQL server.

Environment Used in This Guide

The procedure below was performed on an Oracle 19c database running on Oracle Linux 8 with Grid Infrastructure (the listener runs from the Grid home). The target is a MySQL 8.4 server.

Component	Source (Oracle)	Target (MySQL)
Host	`oracle-vm`	`mysql-vm`
IP Address	`192.168.56.101`	`192.168.56.110`
OS	`Oracle Linux 8.8`	–
Version	`Oracle 19c`	`MySQL 8.4`
PDB / Database	`PRODPDB`	`salesdb`
User / Schema	`APPUSER`	`app_reader`
Port	`1521`	`3306`

Item	Value Used in This Guide
DB home (oracle user)	`/u01/app/oracle/product/19.0.0.0/dbhome_1`
Grid home (grid user)	`/u01/app/19.0.0.0/grid`
ODBC DSN / HS SID / TNS alias	`MYSQLDSN`
Database link	`MYSQL_LINK`

Naming tip: Using the same name for the ODBC DSN, the HS SID, the init file (init<SID>.ora) and the TNS alias keeps the configuration easy to follow and avoids mismatches between the layers.

Prerequisites

Network access from the Oracle server to the MySQL server on port 3306.
A MySQL user allowed to connect from the Oracle server's IP address, with the required privileges (normally SELECT) on the target database.
Root access on the Oracle server to install packages and edit /etc/odbc.ini.
Access to the oracle OS user, and to the grid OS user if the listener runs from Grid Infrastructure.
CREATE DATABASE LINK privilege for the Oracle schema that will own the link.

[root@oracle-vm ~]# nc -zv 192.168.56.110 3306

Take backups first: Before editing listener.ora, tnsnames.ora or /etc/odbc.ini, take a dated copy of each file. The listener file in particular is shared by every database on the server.

Step 1: Check the OS Version

The MySQL repository package depends on the OS major version, so confirm it first.

[root@oracle-vm ~]# cat /etc/os-release | grep -E '^(NAME|VERSION_ID)'

Output

NAME="Oracle Linux Server"
VERSION_ID="8.8"

Step 2: Install the MySQL Community Repository

The mysql-connector-odbc package is not available in the default Oracle Linux repositories; it is published in MySQL's own yum repository. Import the MySQL GPG key and install the repository package for EL8:

[root@oracle-vm ~]# rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2023
[root@oracle-vm ~]# dnf install -y https://dev.mysql.com/get/mysql84-community-release-el8-1.noarch.rpm

Output

Installing       : mysql84-community-release-el8-1.noarch
Running scriptlet: mysql84-community-release-el8-1.noarch
 Warning: native mysql package from platform vendor seems to be enabled.
  Please consider to disable this before installing packages from repo.mysql.com.
  Run: yum module -y disable mysql

Installed:
  mysql84-community-release-el8-1.noarch

Complete!

Note: The warning about the mysql module only matters when installing MySQL Server from this repository. It can be ignored when installing only the ODBC connector.

Step 3: Install MySQL Connector/ODBC

[root@oracle-vm ~]# dnf install -y mysql-connector-odbc

Output

Installing:
 mysql-connector-odbc     x86_64     26.10.0-1.el8      mysql-connectors-community     12 M
Installing dependencies:
 unixODBC                 x86_64     2.3.7-2.el8_10     ol8_appstream                 453 k

Installed:
  mysql-connector-odbc-26.10.0-1.el8.x86_64     unixODBC-2.3.7-2.el8_10.x86_64

Complete!

The unixODBC driver manager is installed automatically as a dependency, and the driver is registered in /etc/odbcinst.ini. Verify the registered drivers and libraries:

[root@oracle-vm ~]# odbcinst -q -d
[root@oracle-vm ~]# ls /usr/lib64/libmyodbc*

Output

[PostgreSQL]
[MySQL]
[FreeTDS]
[MariaDB]
[MySQL ODBC 26.10 Unicode Driver]
[MySQL ODBC 26.10 ANSI Driver]

/usr/lib64/libmyodbc26a.so  /usr/lib64/libmyodbc26w.so

libmyodbc26a.so is the ANSI driver and libmyodbc26w.so is the Unicode driver. The ANSI driver is the more reliable choice with DG4ODBC.

Note: The generic [MySQL] entry is a stock unixODBC template and may point to a library that does not exist. Use the versioned MySQL ODBC xx.xx ANSI Driver entry.

Step 4: Create the ODBC Data Source (DSN)

Add a DSN to /etc/odbc.ini as root. The password is deliberately not stored here; the database link supplies the credentials.

[root@oracle-vm ~]# cat >> /etc/odbc.ini <<'EOF'
[MYSQLDSN]
Description = MySQL salesdb for Oracle DG4ODBC
Driver      = MySQL ODBC 26.10 ANSI Driver
SERVER      = 192.168.56.110
PORT        = 3306
DATABASE    = salesdb
CHARSET     = utf8mb4
EOF

[root@oracle-vm ~]# chmod 644 /etc/odbc.ini

Note: The file must be readable by the OS user that runs the listener. With Grid Infrastructure that user is grid, not oracle.

Step 5: Test ODBC Connectivity with isql

Test the DSN on its own before involving Oracle. If this step fails, the database link will fail too.

[oracle@oracle-vm ~]$ isql -v MYSQLDSN app_reader '<password>'

Output

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> show tables;
SQL> quit

Tip: Wrap the password in single quotes if it contains special characters such as ], = or $. Use quit to leave isql.

Step 6: Create the Gateway Init File

As the oracle user, create init<SID>.ora in $ORACLE_HOME/hs/admin. The file name must match the SID used in the listener.

[oracle@oracle-vm ~]$ cat > $ORACLE_HOME/hs/admin/initMYSQLDSN.ora <<'EOF'
HS_FDS_CONNECT_INFO = MYSQLDSN
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so.2
HS_FDS_TRACE_LEVEL = OFF
HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8
HS_NLS_NCHAR = UCS2
HS_FDS_SUPPORT_STATISTICS = FALSE
set ODBCINI = /etc/odbc.ini
EOF

[oracle@oracle-vm ~]$ chmod 644 $ORACLE_HOME/hs/admin/initMYSQLDSN.ora

Parameter	Purpose
`HS_FDS_CONNECT_INFO`	The ODBC DSN name from `/etc/odbc.ini`.
`HS_FDS_SHAREABLE_NAME`	The unixODBC driver manager library (not the MySQL driver itself).
`HS_LANGUAGE`	Character set used between the gateway and the ODBC driver.
`HS_NLS_NCHAR`	National character set used for Unicode data.
`HS_FDS_SUPPORT_STATISTICS`	Disables remote statistics collection, which avoids errors with MySQL.
`set ODBCINI`	Points the gateway to the ODBC configuration file.

Important: Use /usr/lib64/libodbc.so.2. The base unixODBC package does not provide the plain libodbc.so symlink; that link only comes with unixODBC-devel.

Step 7: Add a Static SID Entry to the Listener

The gateway is started by the listener, so the listener needs a static SID_DESC entry with PROGRAM=dg4odbc. First identify which home the running listener belongs to:

[grid@oracle-vm ~]$ ps -ef | grep tnslsnr

Output

grid  28621  1  0 Aug26 ?  07:47:58 /u01/app/19.0.0.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit

In this environment, the listener runs from the Grid home as the grid user. The entry therefore goes into the Grid home's listener.ora, but its ORACLE_HOME must point to the Database home, because that is where dg4odbc and hs/admin live.

[grid@oracle-vm ~]$ cp /u01/app/19.0.0.0/grid/network/admin/listener.ora \
/u01/app/19.0.0.0/grid/network/admin/listener.ora.bak_$(date +%Y%m%d)

[grid@oracle-vm ~]$ vi /u01/app/19.0.0.0/grid/network/admin/listener.ora

Append the following block:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = MYSQLDSN)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0.0/dbhome_1)
      (PROGRAM = dg4odbc)
      (ENVS = "LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/product/19.0.0.0/dbhome_1/lib,ODBCINI=/etc/odbc.ini")
    )
  )

Note: If SID_LIST_LISTENER already exists, add only the new SID_DESC inside it. Write the Oracle home path literally; do not use $ORACLE_HOME inside listener.ora. Without Grid Infrastructure, add the same block to the DB home's listener.ora as the oracle user.

Step 8: Reload the Listener

A reload rereads listener.ora without stopping the listener, so existing database connections are not affected.

[grid@oracle-vm ~]$ lsnrctl reload LISTENER
[grid@oracle-vm ~]$ lsnrctl status LISTENER | grep -A1 MYSQLDSN

Output

Service "MYSQLDSN" has 1 instance(s).
  Instance "MYSQLDSN", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Expected: Status UNKNOWN is normal for a static entry. The listener does not check the gateway until a connection request arrives.

Step 9: Add the TNS Entry

As the oracle user, add an alias to the DB home's tnsnames.ora. The (HS = OK) clause tells Oracle this is a Heterogeneous Services connection.

[oracle@oracle-vm ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora

MYSQLDSN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-vm)(PORT = 1521))
    (CONNECT_DATA = (SID = MYSQLDSN))
    (HS = OK)
  )

[oracle@oracle-vm ~]$ tnsping MYSQLDSN

Note: Use SID, not SERVICE_NAME, in CONNECT_DATA. The gateway registers only through the static SID entry.

Step 10: Create the Database Link

Connect to the PDB as the schema that will own the link and create it.

[oracle@oracle-vm ~]$ sqlplus APPUSER@PRODPDB

SQL> CREATE DATABASE LINK MYSQL_LINK
       CONNECT TO "app_reader" IDENTIFIED BY "<password>"
       USING 'MYSQLDSN';

Output

Database link created.

Important: Put both the username and the password in double quotes. MySQL usernames are case-sensitive, and without quotes Oracle converts the username to uppercase. Quoting the password also protects special characters.

Step 11: Test the Database Link

SQL> SELECT SYSDATE FROM DUAL@MYSQL_LINK;

Output

SYSDATE
---------
28-OCT-26

Now query an actual MySQL table. MySQL object names must be written in lowercase inside double quotes:

SQL> SELECT COUNT(*) FROM "customers"@MYSQL_LINK;

SQL> SELECT "customer_id", "customer_name"
     FROM   "customers"@MYSQL_LINK
     WHERE  ROWNUM <= 10;

Common mistake: SELECT * FROM customers@MYSQL_LINK fails because Oracle uppercases unquoted names to CUSTOMERS, which does not exist in MySQL on Linux, where table names are case-sensitive.

Step 12: Verify the Link in the Data Dictionary

SQL> ALTER SESSION SET CONTAINER = PRODPDB;

SQL> SET LINES 200 PAGES 100
SQL> COLUMN owner    FOR a15
SQL> COLUMN db_link  FOR a40
SQL> COLUMN username FOR a15
SQL> COLUMN host     FOR a12

SQL> SELECT owner, db_link, username, host, created, valid
     FROM   dba_db_links
     WHERE  host = 'MYSQLDSN';

Output

OWNER           DB_LINK                                  USERNAME        HOST         CREATED   VALID
--------------- ---------------------------------------- --------------- ------------ --------- -----
APPUSER         MYSQL_LINK                                app_reader      MYSQLDSN     28-OCT-26 YES

Database Link Created Successfully: Oracle can now query the MySQL database through MYSQL_LINK using standard SQL and the @dblink syntax.

The Ora DBA

Wednesday, October 28, 2026

Oracle to MySQL : Heterogeneous Database Link Configuration Step by Step

Environment Used in This Guide

Prerequisites

Step 1: Check the OS Version

Step 2: Install the MySQL Community Repository

Step 3: Install MySQL Connector/ODBC

Step 4: Create the ODBC Data Source (DSN)

Step 5: Test ODBC Connectivity with isql

Step 6: Create the Gateway Init File

Step 7: Add a Static SID Entry to the Listener

Step 8: Reload the Listener

Step 9: Add the TNS Entry

Step 10: Create the Database Link

Step 11: Test the Database Link

Step 12: Verify the Link in the Data Dictionary

Report Abuse

Labels