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
- 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!