HOW-TO : Install Mariadb CONNECT engine with Oracle 12c ODBC in Ubuntu 14.04 64-bit

Introduction

Oracle Dbase users might be familiar with database links which enable data from other oracle remote database queried directly from local database. Unfortunately, this only works with oracle databases only.

This HOW-TO will show steps required to enable Maria-DB 10.0.11 server to connect with remote Oracle 12c Database via CONNECT engine. This will allow data in Oracle DB to be queried directly from MariaDB like database link.

Note : As of today, CONNECT engine only allows SELECT operation to remote dbase. The engine still under heavy development and will support other operation such as INSERT , UPDATE and DELETE in the future.

Software Required

Step 1 : Install MariaDB-Server 10.0.11

Open your terminal

You'll be asked for administration password during installation.

Step 2 : Install Oracle Instant Cilent

Download all files in rpm format required from : http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

Then, install all the RPM files :

After installation, setup LD_LIBRARY_PATH by issuing this command :

and add the oracle library path as the first line :

Then run ldconfig :

The setup ORACLE_HOME by issuing this command :

Step 3 : Install unixodbc

Open your terminal and issue this command :

Check the correct 64-bit unixodbc is installed :

This message should be displayed if you installed the correct unixodbc :

If not, make sure to purge the unixodbc driver and then issue sudo apt-get autoremove to make sure the OS is clean from any dependencies to previous unixodbc. Look for further details in Troubleshooting section below

Step 4 : Install MariaDB CONNECT engine

Open your terminal and issue this command :

Step 5 : Configure ODBC Connection

To configure odbc connection, you'll have to edit 2 files (/etc/odbc.ini and /etc/odbcinst.ini).

First, setup odbc.ini by issuing this command

Then put these lines into the file :

*Don't forget to modify values for ServerName, UserName and Password in above file accordingly.

After that, setup odbcinst.ini by issuing this command

Then put these lines into the file :

Step 6 : Test your ODBC Connection

Check odbc connection by issuing this command :

or

You'll get "Connected!" result if the odbc connection is successful.

Step 7 : Create your CONNECT Table

1. Open your terminal

You'll be prompted with password. Enter correct password.

2. Now create a CONNECT table of ODBC type with 'emp' name. We will make this table point to Oracle 12c 'scott.emp' table :

3. Congratulation! You should now be able to issue SELECT statement to emp table which actually linked directly to scott.emp table in oracle database.

Troubleshooting

If you issue isql -v ifas command and get error message like this :

it means you have either one of this condition :

A. You installed the wrong unixodbc (32 bit)

OR

B. libsqora.so.12.1 is missing some of the dependency files

To rectify the Condition A :

1. Remove current unixodbc

2. Reinstall unixodbc

3. To verify you installed the correct version:

The output should be like this :

To Rectify Condition B :

To check which dependency file is missing, issue this command :

You'll be getting output somewhat like this :

In this example the libodbcinst.so.2 is missing. Most of the time the library is actually already exist in the OS but installed in the wrong location. Now, search the file :

Output :

Yup, the file is actually installed but in a wrong place!. To rectify it just create a symlink in /usr/lib :

Thats it! test your odbc connection again using :

You should be asked for username/password this time. Good Luck!