This Blog describes how to set up a Database link from an Oracle Database to a MySQL Database. As an example serves an Oracle Standard Edition Two Version 19c (19.14.0) Database with the Standard Edition High Availability (SEHA) configuration on Oracle Linux 8.
Packageinstallation
For the installation two RPM-Packages are needed:
yum install unixODBC- Download
mysql-connector-odbc-8.0.28-1.el8.x86_64.rpm rpm -ihv mysql-connector-odbc-8.0.28-1.el8.x86_64.rpm
The package unixODBC installs the basic libraries including /usr/lib64/libodbc.so which is needed later and the files /etc/odbc.ini as well as /etc/odbcinst.ini. Furthermore the tool /usr/bin/isql belongs to that package
The package mysql-connector-odbc then installs the specific mysql libraries. The file /etc/odbcinst.ini is also going to be adjusted
ODBC Configuration
For the next step the ODBC client will be configured. For this, the configuration for one or more connections is entered in /etc/odbc.ini.
Example:
[TESTMYDB] Description = Test-MYSQL-Datenbank Driver = /usr/lib64/libmyodbc8w.so Trace = no Server = 192.168.168.168 Port = 3306 Database = daten User = TESTUSER Password = passwort
It is important to have the name of the entry (TESTMYDB) and the correct driver. The driver for a unicode MySQL Database is libmyodbc8w.so. Otherwise libmyodbc8a.so can be used for a MySQL Database with a 1-Byte character set.
With this it should be possible to connect to the MySQL database from the server.
isql TESTMYDB TESTUSER passwort
Gateway Installation
For the next step, the Oracle Gateway software will be installed. Unfortunately it is still necessary to unpack the software first and then install it:
cd /u05/share/software unzip LINUX.X64:193000_gateways.zip cd gateways ./runInstaller
In the example the gateway for MS-SQL is also installed. This is not important for this configuration.
Thus the gateway is installed in the directory /u01/app/oracle/product/19c/gwhome_1.
Gateway Configuration
Three components are responsible for the configuration:
listener.oratnsnames.orainit<GATEWAYNAME>.ora
listener.ora
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))
(ADDRESS=(PROTOCOL=TCP)(HOST=seha01)(PORT=1521))
)
)
SID_LIST_LISTENER =
(SID_LIST=
(SID_DESC=
(SID_NAME=TESTMYDB)
(ORACLE_HOME=/u01/app/oracle/product/19/gwhome_1)
(PROGRAM=dg4odbc)
)
)
The SID_NAME must correspond to the name in the /etc/odbc.ini file. Of course there can be further SIDs for databases or further ODBC connections. On the second SEHA server the parameter “HOST” must be adjusted.
tnsnames.ora
TESTMYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = seha01)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = seha02)(PORT = 1521))
(CONNECT_DATA =
(SID = TESTMYDB)
)
(HS = OK)
)
For RAC or SEHA databases, the scan address cannot be used here. Instead, the HOSTS has to be specified explicitly.
The TNS alias may have a different name than the SID, which again must be identical to the entry in /etc/odbc.ini.
initTESTWEBDB.ora
HS_FDS_CONNECT_INFO = TESTWEBDB #HS_FDS_TRACE_LEVEL = OFF #HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P15 HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
The name of the file must correspond to the name of the odbc.ini entry.
It is important that the parameter HS_FDS_SHAREABLE_NAME is not the ODBC library of MySQL but the so called “ODBC Drive Manager“. In this case it is the libodbc.so from the unixODBC package.
Database-Link
After all the preparation, the database link can now be created as usual:
CREATE DATABASE LINK jostest CONNECT TO "TESTUSER" IDENTIFIED BY "password" USING 'TESTMYWEBDB";
With this the connection to the MySQL database should work.
If the following error occurs:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [
This is probably because the MySQL database is not a Unicode database. The easiest way to solve the problem is to set the following parameter in the initTESTMYDB.ora file:
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P15
When accessing the MySQL databases, please note that tables and databases are usually written in lower case. So it is better to put the database and table names in double quotation marks.
SELECT * FROM "daten"."test"@jostest;
That’s it.
If something is still missing or errors occurred, then please send a short message to me or leave a comment on the blog.
Good luck!
please give the configuration of link to mysql in windows platform
Sorry Mohamed, but I don’t have any experience on setting up MySQL on Windows. MySQL ist mostly used on Linux OS.
I have been using oracle gateway 19c for ms sql. We did not have install two rpm’s that is needed for MySQL to setup odbc. Looks oracle did not provide any gateway for MySQL so Extra steps here?
You are right. We installed the gateway for MS-SQL as well – without any additional packages.
I’m on RHEL 9. Everything set up fine for the Oracle GW. I’m using the GW installed with my oracle 19c. I have this all setup on a OEL7 server and it works fine. The problem is : I can do a describe on a table across the db link but cannot select from the table. It wants to use the mysql database that it originally logs into from the /etc/odbc.ini file. This all works fine for both a table describe and query (select) using the “isql” tool.
logged in as from the /etc/odbc.ini file.
desc “”.”table1″@mysql_link This runs OK!. <– dbname2 is another db (schema) located in the same remote mysql db
select count(*) from "”.”table1″@mysql_link;
ERROR at line 1:
ORA-00942: table or view does not exist
[MySQL][ODBC 8.3(w) Driver][mysqld-8.0.33]Table ‘.table’ <– it wants to user dbname1. This db does not contain the the dbname2 does. doesn’t exist {42S02,NativeErr = 1146}
ORA-02063: preceding 2 lines from MYSQL
The query wants to use dbname1. This db does not contain the the dbname2 does.
This works on our RHEL 7 server just fine. For some reason it won’t honor the dbname2 database (schema) when doing a query. Do you know about this?
Thanks for the article!
This is a better copy of the error. The db and table are fictitious
ERROR at line 1:
ORA-00942: table or view does not exist
[MySQL][ODBC 8.3(w) Driver][mysqld-8.0.33]Table ‘. ‘dbname1.table1’ {42S02,NativeErr = 1146}
ORA-02063: preceding 2 lines from MYSQL
It wants to user the dbname1 database on the query. Not the dbname2 like in the describe which works.
Hi Bill,
sorry but I don’t have a solution to this problem. But here are my thoughts:
1. Describe ist somewhat weak to remote databases as it isn’t a SQL but SQL*Plus command.
2. The error message is very straight: the connect doesn’t have sufficient privileges.
3. Are you sure that /etc/odbc.ini is the right location (it can be changed using the
set ODBCINI=/etc/odbc.ini is in my $OH/hs/admin/init.ora file. The odbc.ini file is in the /etc directory. This all works on our OEL 7 server. We can do a describe and a select using the exact same setup.
We are now on RHEL 9 on our new server. We can describe and select in “isql”. We can describe but not select using the gateway. I wants to use the schema that is set in the odbc.ini (Database=). The select is actually selecting a “view” that was created in another schema . It wants to use .view_name and not .view_name. This all works on our OEL 7 ODA.
We did not have to ever install the Oracle GW. We are using the installed $HO/bin/dg4odbc program on both servers.
Thank you so much for looking at this!! Sorry that we did not get an answer. Next stop MOS! 🙂
set ODBCINI=/etc/odbc.ini is in my $OH/hs/admin/initmyodbc8w.ora file. Clarification.
I typed out a reply but I don’t see it here. Thank you so much for your reply!! I’ll check back here tomorrow to see if my reply took. If not then I will reply again. I will have to open up a ticket with MOS. Thanks again!!
Did you ever get this resolved. I too am having this problem. I have a default database named in the odbc.ini to make the connection but am unable to access any other schema s/databases on the remote database in mysql. I find it hard to believe I need a database link for each mysql schema