How to configure database gateway for ODBC in OCI database cloud service

Accessing to non-Oracle database data from Oracle database brings some complexities for lot of companies. They often need it for database consolidation and reporting purpose. It is nowadays mandatory for the various non-oracle database systems to work with Oracle databases to fulfill simplest reporting need for large organizations. They usually require direct and faster access using database link between heterogeneous databases.

So, Oracle Database Gateway makes it possible to connect Database Cloud service to other heterogeneous environment. Because it provides the ability to transparently integrate with non-Oracle systems from an Oracle environment.

Today I will show you how to connect to MySQL database from your Oracle DBCS in Oracle Cloud Infrastructure using DG4ODBC.

What do you need and how to do it?

1. Database Service in OCI and all network access is configured. I provisioned the 19c database instance using VM2.1 image. I believe it is easiest step and you already have your active database running and consuming good amount.

2. ODBC drivers. I installed both unixODBC.x86_64 and mysql-connector-odbc-8.0.22-1.el7.x86_64 drivers on my DBCS machine.

3. Now you need to give ssh access to your oracle user. Because you will need to install some software with oracle privileges later stage.

To enable SSH access for the oracle user, edit the AllowUsers parameter in the /etc/ssh/sshd_config file by using vi or any other text editor. You can see step by step more detailed information in below link

4. Enable x11 to run installation.

By default, Oracle Cloud Infrastructure Linux instances are used by entering the command line via an SSH session only. Since you have sudo privilege you can remove this limitation, but it is not recommended. I simply followed this material provided by oracle to enable x11.

5. Database gateway installation.
I downloaded database gateway for 19c and extracted in /home/oracle/GW/gateways location in my database machine. Now access your DBCS using oracle privileges and run Installer.

6. Now you should run NETCA from oracle user. It will configure listener service. I have to add it to new port 1523, since 1521 and 1522 were already used.

But I modified it according to my need. Because I need to have 2 mysql instances, so additionally, it was necessary to edit my listener.ora parameter in below.

7. Update your tnsnames.ora file too! I added DG4ODBC and MYSQL entries.

8. There will be some files created in $ORACLE_HOME/hs/admin location after your database gateway installation. I modified and created additionally according to SID's in my listener file. It has to be init<SID>.ora, you can take a look in below example.

9. Let's edit /etc/odbc.ini file, which was created after your mysql driver installation. Since I have 2 mysql instances, I also have to have 2 of them in this file. First mysql instance is installed in remote server and second mysql instance is installed on localhost as next to my DBCS.

You can test your ODBC connection using isql. For example I tested dg4odbc.

10. It seems everything is okay and working. Now login to oracle and create database links.

11. It's time to test your databse link to mysql databases!

I used following MOS notes to configure and this pdf file. It may differ some parameters and odbc driver version etc, however generic idea is still same. Hope you liked it.

Good luck and join our discord community https://discord.gg/nrF3HuF