Create Database link with MySQL Server in Oracle

Create Database link with MySQL Server in Oracle

In My Case, both MySQL and Oracle is installed on one system.

1. Create the entry for MySQL setting in Windows System by opening ODBC Data Source in System DNS as follows — Go to the System tabl –> Press Add button –> Select MySQL drivers –> Put all necessary details as follows:

2. Configure the HS directory present in Oracle Home:

Advertisements

Report this ad

Note: Name MySQL as SID is used as same as created in Window ODBC Data Source in System DNS:

Go to directory: C:\Oracle\dbhomeXE\hs\admin
Copy paste the initdg4odbc.ora file to new file as init<systemdnsname>.ora
Example:

initMySQL.ora

Edit this file initMySQL.ora file and add following entries:
HS_FDS_CONNECT_INFO = MySQL
HS_FDS_TRACE_LEVEL = off

3. Configure the Listener.ora file in the Network folder present in Oracle Home.

Note: Name MySQL as SID is used as same as created in Window ODBC Data Source in System DNS:

LISTENER.ORA:
Adding following SID entry in Listener.ora file for MySQL:
Note: Name MySQL as SID is used as same as created in Window ODBC Data Source in Sytem DNS:
(SID_DESC =
   (SID_NAME = MySQL)
   (ORACLE_HOME = C:\oracle\dbhomexe)
   (PROGRAM = dg4odbc)
)

Example of Listener file:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\Oracle\dbhomeXE)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\dbhomeXE\bin\oraclr18.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = PDB1)
      (ORACLE_HOME = C:\Oracle\dbhomeXE)
      (SID_NAME = PDB1)
    )
    (SID_DESC =
     (SID_NAME = MySQL)
     (ORACLE_HOME = C:\oracle\dbhomexe)
     (PROGRAM = dg4odbc)
    )
  )

4. Configure the TNSNAMES.ORA file in the Network folder.

Adding MySQL tnsentry in TNSNAMES.ora:

MySQL=
   (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT = 1521))
    (CONNECT_DATA=(SID=MySQL))
    (HS=OK)
 )

Check the TNSPING command 

SQL> host tnsping MySQL

TNS Ping Utility for 64-bit Windows: Version 18.0.0.0.0 - Production on 08-APR-2022 10:39:22

Copyright (c) 1997, 2018, Oracle.  All rights reserved.

Used parameter files:
C:\Oracle\dbhomeXE\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT = 1521)) (CONNECT_DATA=(SID=MySQL)) (HS=OK))
OK (20 msec)

5. Create the database link in the Oracle using MySQL TNS entry:

SQL> create database link mysqlconnect connect to "root" identified by "Newpassword1" using 'MySQL';

Database link created.

SQL> select count(*) from city@mysqlconnect;

  COUNT(*)
----------
      4079

Error:

SQL> create database link mysqlconnect connect to root identified by Newpassword1 using 'MySQL';

Database link created.

SQL> select count(*) from city@mysqlconnect;
select count(*) from city@mysqlconnect
                          *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[MySQL][ODBC 8.0(a) Driver]Access denied for user 'ROOT'@'localhost' (using password: YES) {HY000,NativeErr = 1045}
ORA-02063: preceding 2 lines from MYSQLCONNECT

Cause: Created database link with the quotations:

Solution:

SQL> drop database link mysqlconnect;

Database link dropped.


SQL> create database link mysqlconnect connect to "root" identified by "Newpassword1" using 'MySQL';

Database link created.

SQL> select count(*) from city@mysqlconnect;

  COUNT(*)
----------
      4079