Step by Step procedure for Sphinx installation and Configuration to fetch data from Oracle DB using ODBC on CentOS 6.4 Linux.

Step by Step procedure for Sphinx installation and Configuration to fetch data from Oracle DB using ODBC on CentOS 6.4 Linux.

Step1: Installing unixODBC
 Download unixODBC from http://www.unixodbc.org/unixODBC-2.3.1.tar.gz
 Extract the tar.gz file inside /opt folder &  Install UnixODBC using
# cd /opt/unixODBC-2.3.1
#./configure --enable-gui=no
# make
# make install

Step2: Installing Sphinx with unixODBC
 Download sphinx from http://sphinxsearch.com/downloads/sphinx-2.0.8-release.tar.gz
 Extract files to /opt folder
#cd /opt/sphinx-2.0.8-release
#./configure –prefix=/usr/local/sphinx –with-unixodbc

 Note :Here, you may replace ”/usr/local/sphinx" with the directory in which you want to install sphinx,"–with-unixodbc" parameter looks up for unixodbc installed on your system (which is done in step 1) and configures Sphinx to use unixODBC
# make
# make install
Sphinx is now installed.

Step 3: Installing Oracle Instant Client
Download the following files from Oracle - http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
Download the following files:
oracle-instantclient12.1-basic-12.1.0.1.0-1.x86_64
oracle-instantclient12.1-devel-12.1.0.1.0-1.x86_64
oracle-instantclient12.1-jdbc-12.1.0.1.0-1.x86_64
oracle-instantclient12.1-odbc-12.1.0.1.0-1.x86_64
oracle-instantclient12.1-sqlplus-12.1.0.1.0-1.x86_64

Install all the rpms by command line using “#rpm -ivh oracle-*”
Update your DB using command “#updatedb”

Step 4: Creating ORA files
The Oracle Instant Client contains Oracle ODBC Driver “libsqora.so.12.1”. We have to use this driver file in creating an ODBC DSN and connecting to the Oracle Database.
Locate the file using the command - #locate libsqora.so.12.1
This file must be located in /usr/lib/oracle/12.1/client64/lib folder.
Now create 3 files in /usr/lib/oracle/12.1/client64/bin folder as under:

#touch /usr/lib/oracle/12.1/client64/bin/tnsnames.ora
#touch /usr/lib/oracle/12.1/client64/bin/listener.ora
#touch /usr/lib/oracle/12.1/client64/bin/sqlnet.ora

 -> Edit listener.ora and enter the following in it. Replace HOST, SERVICE_NAME and SID Parameters with your values.

LISTENER =
 (DESCRIPTION_LIST =
    (DESCRIPTION =
       (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
       )
       (SERVICE_NAME = YourServiceName)
       (SID = YourSID)
    )
 )

 -> Edit sqlnet.ora and enter the following in it.

SQLNET.AUTHENTICATION_SERVICES= (NONE)

 -> Edit tnsnames.ora and enter the following in it. Replace HOST, SERVICE_NAME and SID Parameters with your values.

MYDATABASE =
 (DESCRIPTION =
    (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
    )
 (CONNECT_DATA =
      (SERVICE_NAME=YourServiceName)
(SID=YourSID)
 ) )

Step 5: Setup ODBC Variables
Create a file named odbcinst.ini in /usr/local/etc using command “#touch /usr/local/etc/odbcinst.ini”
Edit odbcinst.ini and insert the following parameters in it and save:

[Oracle]
 Description= ODBC for Oracle
 Driver = /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
 Setup =
 FileUsage = 1
 CPTimeout =
 CPReuse =

Create a file named odbc.ini in /usr/local/etc using command “#touch /usr/local/etc/odbc.ini”
Edit odbci.ini and insert the following parameters in it. Make sure you replace [mydsn], DSN = mydsn and ServerName = MYDATABASE with your values.

[mydsn]
 Application Attributes = T
 Attributes = W
 BatchAutocommitMode =
 IfAllSuccessful BindAsFLOAT = F
 CloseCursor = F
 DisableDPM = F
 DisableMTS = T
 Driver = Oracle
 DSN = mydsn
 EXECSchemaOpt =
 EXECSyntax = T
 Failover = T
 FailoverDelay = 10
 FailoverRetryCount = 10
 FetchBufferSize = 64000
 ForceWCHAR = F
 Lobs = T
 Longs = T
 MetadataIdDefault = F
 QueryTimeout = T
 ResultSets = T
 ServerName = MYDATABASE
 SQLGetData extensions = F
 Translation DLL =
 Translation Option = 0
 DisableRULEHint = T
 UserID =

Step 6: Setup Environment Variables
Edit #vim ~/.bash_profile and insert the following at the end:
LD_LIBRARY_PATH=“$LD_LIBRARY_PATH:/usr/lib/oracle/12.1/client64/lib:/usr/local/lib” export LD_LIBRARY_PATH
ODBCINI=”/usr/local/etc/odbc.ini” export ODBCINI
ODBCINST=”/usr/local/etc/odbcinst.ini” export ODBCINST
ORACLE_PATH=”/usr/lib/oracle/12.1/client64/bin” export ORACLE_PATH
TNS_ADMIN=”/usr/lib/oracle/12.1/client64/bin” export TNS_ADMIN
ORACLE_HOME=”/usr/lib/oracle/12.1/client64/bin” export ORACLE_HOME

Update the environment variables by executing command: “source ~/.bash_profile”

Step 7: 
Create a file oracle.conf in path.. /etc/ld.so.conf.d using the following command:
#touch /etc/ld.so.conf.d/oracle.conf
insert the following text in it and save.
/usr/lib/oracle/12.1/client64/lib
run  “#ldconfig” to update cache

Step 8: Test your environment

Test out isql utility : “isql -v mydsn user password” . This should connect you to the Oracle database using ODBC and give a prompt for issuing SQL Commands.
Test out sqlplus utility : “sqlplus user/pass@database”. This should connect you to the database and arrive at SQL Prompt.

Step 9:
Once the test environment is OK, edit sphinx.conf file, you should be able to derive data with the following parameters :

type = odbc
sql_host = 192.168.0.1
sql_user = user
sql_pass = password
sql_db = databasename
sql_port = 1521
odbc_dsn    = DSN=mydsn; Driver={'Oracle'};Dbq=192.168.3.121:1521/mydatabase;Uid=user;Pwd=password

Now your able to index from Oracle DB. :)