UNIMAS Official Wiki » Dashboard » HOW-TO, Tutorial & User Manual » HOW-TO : Install Mariadb CONNECT engine with Oracle 12c ODBC in Ubuntu 14.04 64-bit

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

Last modified by Khairilzamrie bin Rosle on 2015/01/31 08:34

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

- Oracle Instant Client 12c 64-bit (Basic, SDK/devel, odbc, sqlplus)

- Ubuntu 14.04 64-bit

- MariaDB 10.0.11 64-bit

- unixodc 2.2.14 64bit

Step 1 : Install MariaDB-Server 10.0.11

Open your terminal

sudo apt-get install software-properties-common

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db

sudo add-apt-repository 'deb http://ftp.kaist.ac.kr/mariadb/repo/10.0/ubuntu trusty main'

sudo apt-get update && sudo apt-get install mariadb-server

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

oracle-instantclient12.1-basic-12.1.0.1.0-1.x86_64.rpm

oracle-instantclient12.1-sqlplus-12.1.0.1.0-1.x86_64.rpm

oracle-instantclient12.1-devel-12.1.0.1.0-1.x86_64.rpm

oracle-instantclient12.1-odbc-12.1.0.1.0-1.x86_64.rpm

Then, install all the RPM files :

sudo apt-get install alien

sudo alien -i oracle-instantclient-basic*.rpm

sudo alien -i oracle-instantclient-sqlplus*.rpm

sudo alien -i oracle-instantclient-devel*.rpm

After installation, setup LD_LIBRARY_PATH by issuing this command :

sudo nano /etc/ld.so.conf.d/oracle.conf

and add the oracle library path as the first line :

/usr/lib/oracle/12.1/client64/lib

Then run ldconfig :

sudo ldconfig

The setup ORACLE_HOME by issuing this command :

sudo nano /etc/profile.d/oracle.sh

export ORACLE_HOME= /usr/lib/oracle/12.1/client64

Step 3 : Install unixodbc

Open your terminal and issue this command :

sudo apt-get install unixodc

Check the correct 64-bit unixodbc is installed :

which isql

file <the output above>

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

ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.24, BuildID[sha1]=62f4ed55b29f0d9ad136122448b672d7725ea01f, stripped

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 :

sudo apt-get install mariadb-connect-engine-10.0

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

sudo nano /etc/odbc.ini

Then put these lines into the file :

[MYORACLEDSN]

Application Attributes = T

Attributes = W

BatchAutocommitMode = IfAllSuccessful

CloseCursor = F

DisableDPM = F

DisableMTS = T

Driver = Oracle

EXECSchemaOpt =

EXECSyntax = T

Failover = T

FailoverDelay = 10

FailoverRetryCount = 10

FetchBufferSize = 64000

ForceWCHAR = F

Lobs = T

Longs = T

MetadataIdDefault = F

QueryTimeout = T

ResultSets = T

ServerName = //<oracledbIPorDomainName>:1521/<SID>

SQLGetData extensions = F

Translation DLL =

Translation Option = 0

UserName = <username to oracle dbase>

Password = <password for above username>

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

After that, setup odbcinst.ini by issuing this command

sudo nano /etc/odbcinst.ini

Then put these lines into the file :

[Oracle]

Description = Oracle ODBC Connection

Driver = /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1

Setup =

FileUsage =

CPTimeout =

CPReuse =

Step 6 : Test your ODBC Connection

Check odbc connection by issuing this command :

isql -v MYORACLEDSN

or

isql MYORACLEDSN <username> <password>

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

Step 7 : Create your CONNECT Table

1. Open your terminal

mysql -u root -p

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 :

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 3

Server version: 10.0.11-MariaDB-log MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

MariaDB [test]> create table emp ENGINE=CONNECT TABLE_TYPE=ODBC tabname='emp' CONNECTION='DSN=MYORACLEDSN;UID=scott;PWD=manager1';

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 :

[01000][unixODBC][Driver Manager]Can't open lib '/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1' : file not found

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

sudo apt-get purge unixodbc:i386

sudo apt-get autoremove

2. Reinstall unixodbc

sudo apt-get install unixodbc

3. To verify you installed the correct version:

which isql

file <the output above>

The output should be like this :

ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.24, BuildID[sha1]=62f4ed55b29f0d9ad136122448b672d7725ea01f, stripped

To Rectify Condition B :

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

ldd /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1'

You'll be getting output somewhat like this :

linux-vdso.so.1 => (0x00007fff64727000)

libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007fc607792000)

libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007fc60748c000)

libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007fc60726d000)

libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007fc607053000)

librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007fc606e4b000)

libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1 (0x00007fc60415d000)

libodbcinst.so.2 => not found

libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fc603d97000) /lib64/ld-linux-x86-64.so.2 (0x00007fc607e0a000)

libnnz12.so => /usr/lib/oracle/12.1/client64/lib/libnnz12.so (0x00007fc603680000)

libons.so => /usr/lib/oracle/12.1/client64/lib/libons.so (0x00007fc60343c000)

libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007fc60323a000)

libclntshcore.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1 (0x00007fc602ce9000)

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 :

sudo updatedb

locate libodbcinst

Output :

/usr/lib/x86_64-linux-gnu/libodbcinst.so.1

/usr/lib/x86_64-linux-gnu/libodbcinst.so.1.0.0

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

cd /usr/lib

sudo ln -s /usr/lib/x86_64-linux-gnu/libodbcinst.so.1 libodbcinst.so.2

Thats it! test your odbc connection again using :

isql -v MYORACLEDSN

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

Start Here

Welcome to the UNIMAS WIKI.
To start, use search function by entering keywords in the search box below :
 

Quick Navigation

About UNIMAS

HOW-TO, Tutorial & User Manual

HOW-TO, Tutorial &amp; User Manual

Macros

STB2242 - PLANT BIOTECHNOLOGY

signatureattach


This wiki is licensed under a Creative Commons 2.0 license
XWiki Enterprise 5.0.3 - Documentation