8.147 Connecting to an Oracle Database with an ODBC Driver
Context
This documentation shows how to set up and configure Moab to connect to an Oracle database using the ODBC driver.
To connect to an Oracle database with an ODBC driver
- Install and configure the Oracle Instant Client with ODBC supporting libraries. For instructions, see Installing the Oracle Instant Client.
-
USEDATABASE ODBC
# Turn on stat profiling
USERCFG[DEFAULT] ENABLEPROFILING=TRUE
GROUPCFG[DEFAULT] ENABLEPROFILING=TRUE
QOSCFG[DEFAULT] ENABLEPROFILING=TRUE
CLASSCFG[DEFAULT] ENABLEPROFILING=TRUE
ACCOUNTCFG[DEFAULT] ENABLEPROFILING=TRUE
NODECFG[DEFAULT] ENABLEPROFILING=TRUE
- Configure the Oracle ODBC Driver. The odbcinst.ini file must be contained in /etc.
[root]# vim /etc/odbcinst.ini
Run the following command to find the Oracle Instant Client driver. You could also query the Oracle Instant Client package that was installed.
[root]# updatedb && locate libsqora
- Because the driver installed in step 1 is a shared library, run ldd to verify that it and all of its dependencies are installed and working.
[root]# ldd /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
linux-vdso.so.1 => (0x00007fff631ff000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f8afbe83000)
libm.so.6 => /lib64/libm.so.6 (0x00007f8afbbff000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f8afb9e1000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f8afb7c8000)
libclntsh.so.11.1 => /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 (0x00007f8af8e59000)
libodbcinst.so.1 => not found
libc.so.6 => /lib64/libc.so.6 (0x00007f8af8ac5000)
/lib64/ld-linux-x86-64.so.2 (0x0000003bdb000000)
libnnz11.so => /usr/lib/oracle/11.2/client64/lib/libnnz11.so (0x00007f8af86f8000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007f8af84f6000)
If the command returns libodbcinst.so.1 => not found, create a symbolic link from /usr/lib64/libodbcinst.so.1 to /usr/lib64/libodbcinst.so.2. This is a known Red Hat issue. See Red Hat Bugzilla for more information.
[root]# locate libodbcinst
/usr/local/lib/libodbcinst.so.2
[root]# cd /usr/lib64
[root]# ln -s libodbcinst.so.2 libodbcinst.so.1
Rerun ldd. It should load libsqora.so.11.1 without error, as shown in the ldd example above.
If the ldd command returns a warning like this: "ldd: warning: you do not have execution permission for `/usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1'", run the following command:
[root]# chmod 755 /usr/lib/oracle/11.2/client64/lib/lib*
Rerun ldd. It should load libsqora.so.11.1 without error, as shown in the ldd example above.
- Configure Moab to use the Oracle ODBC driver. This example assumes that a Moab user exists and has been granted read and write privileges to the MOAB database instance referred to on the Installing the Oracle Instant Client page.
[root]# vim $MOABHOMEDIR/dsninfo.dsn
Add the following lines the file, but change ServerName, UserName, and Password to suit your own system. ServerName is the name of the Oracle database instance. Username and Password are the credentials used to connect to that instance.
[ODBC]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle 11g ODBC driver
DSN = ODBC
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MaxLargeData = 0
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = MOAB
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID = moab
Password = moab
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F
MaxTokenSize=8192
- Add the contents of the dsninfo.dsn file to /etc/odbc.ini. Because the contents of dsninfo.dsn are required in both files, use the following command to concatenate the contents of dsninfo.dsn to /etc/odbc.ini. If the odbc.ini file already has content, verify that there are no conflicts.
[root]# cat $MOABHOMDIR/dsninfo.dsn >> /etc/odbc.ini
- Create a directory to store the tnsnames.ora file you will create in the next step.
[root]# mkdir /etc/oracle
- Create the tnsnames.ora file. The ServerName in $MOABHOMEDIR/dsninfo.dsn tells the Oracle ODBC driver what tnsnames.ora entry to use (MOAB). The MOAB tnsnames entry tells the Oracle ODBC driver to connect to server adaptive-oracle on the local domain (ac) on port 1561 using TCP and to connect to the Oracle instance named MOAB (The SID is the unique name of the instance).
[root]# cat >/etc/oracle/tnsnames.ora <<EOL
MOAB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = adaptive-oracle)(PORT = 1561))
)
(CONNECT_DATA =
(SID = MOAB)
)
)
EOL
- Create a profile script (oracle-instant-client.sh) to be invoked by the operating system at startup. This script will set the ORACLE_HOME, TWO_TASK, and TNS_ADMIN environment variables required by Oracle and will amend the LD_LIBRARY_PATH to include required Oracle client libraries in the library search path.
[root]# cat >/etc/profile.d/oracle-instant-client.sh <<EOL
# Set ORACLE_HOME to the directory where the bin and lib directories are located for the oracle client
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
# No need to add ORACLE_HOME to the linker search path. oracle-instant-client.conf in
# /etc/ld.so.conf.d should already contain /usr/lib/oracle/11.2/client64.
# Alternately, you can set it here by uncommenting the following line:
# export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
# Define the default location where Oracle should look for the server
export TWO_TASK=//adaptive-oracle:1561/listener
# Define where to find the tnsnames.ora file
export TNS_ADMIN=/etc/oracle
EOL
- Source the oracle-instant-client.sh script and verify that each environment variable is set correctly.
[root]# source /etc/profile.d/oracle-instant-client.sh
[root]# echo $ORACLE_HOME
[root]# echo $LD_LIBRARY_PATH
[root]# echo $TWO_TASK
[root]# echo $TNS_ADMIN
- Modify either the Moab startup script (/etc/init.d/moab) – recommended – or the moabd script (/opt/moab/sbin/moabd) to source oracle-instant-client.sh.
- Moab startup script (recommended): the following example suggests a location to source the oracle-instant-client.sh script within the Moab startup script.
...
# Export all environment variables required by the Oracle Instant Client
. /etc/profile.d/oracle-instant-client.sh
export MOABHOMEDIR=/opt/moab
...
- moabd shell script: the following example will resemble the moabd script in /opt/moab/sbin. Note that the moabd script is not invoked by the Moab startup script; The Moab startup script invokes the Moab binary (/opt/moab/sbin/moab) by default.
#!/bin/sh
#
# Copyright (C) 2012 by Adaptive Computing Enterprises, Inc. All Rights Reserved.
#
# Export all environment variables required by the Oracle Instant Client
. /etc/profile.d/oracle-instant-client.sh
MOABHOMEDIR="/opt/moab" LD_LIBRARY_PATH="/opt/moab/lib:$LD_LIBRARY_PATH" moab "$@"
- Moab startup script (recommended): the following example suggests a location to source the oracle-instant-client.sh script within the Moab startup script.
- Verify the Oracle ODBC driver is working.
isql -v ODBC
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
- The odbcinst.ini and odbc.ini files are usually assumed to be located in /etc, but that is not always true. Use the odbcinst -j command to determine the assumed location of the files in your configuration.
[root#] odbcinst -j
unixODBC 2.2.12
DRIVERS............: /etc/unixODBC/odbcinst.ini
SYSTEM DATA SOURCES: /etc/unixODBC/odbc.ini
USER DATA SOURCES..: /home/adaptive/.odbc.ini
- Because odbcinst.ini and odbc.ini are expected in /etc/unixODBC, not /etc, move them from /etc to /etc/unixODBC.
- Use the strace command to determine where isql expects the odbc.ini and odbcinst.ini files. Note the location in which isql expects these files.
$ strace isql -v ODBC
- If you have not already done so, create the database tables in Oracle using the moab-db-oracle-create.sql script located in the contrib/sql directory.
This contrib/sql directory is either in the $MOABHOMEDIR (if you used RPM method) or in the expanded tarball directory (if you used Manual method).
This example assumes that you are logged into the MOAB database instance (referred to on the Installing the Oracle Instant Client page) as Moab user with read and write privileges.
SQL> @./contrib/sql/moab-db-oracle-create.sql
- Verify that the database schema installed correctly by listing the tables. Your results should look like this:
SQL> select table_name from all_tables where owner = 'MOAB';
+-------------------------------+
| TABLE_NAME |
+-------------------------------+
| TRIGGERS |
| MOAB |
| OBJECTTYPE |
| VCS |
| EVENTTYPE |
| JOBHISTORY |
| MCHECKPOINT |
| NODES |
| EVENTS |
| NODESTATSGENERICRESOURCES |
| JOBS |
| RESERVATIONS |
| GENERICMETRICS |
| REQUESTS |
| GENERALSTATS |
| NODESTATS |
+-------------------------------+
SQLRowCount returns -1
16 rows fetched
- Restart Moab.
[root]# mschedctl -R
- Verify Moab is correctly configured to write to the Oracle database by doing each of the following steps:
- Tail the moab.log file for ODBC errors.
# Check the $MOABHOMEDIR/log/moab.log file for ODBC errors. You should see a few hits even if there are no errors.
[root]# tail -f $MOABHOMEDIR/log/moab.log | grep -i odbc
- Log in to the Moab Oracle database.
In the first example below, isql will search /etc/odbc.ini for "[ODBC]". unixODBC will then use the Oracle 11g ODBC driver defined in /etc/odbcinst.ini to establish a connection. The ServerName in /etc/odbc.ini tells the Oracle driver to reference the MOAB tnsnames entry in /etc/oracle/tnsnames.ora for connection parameters.
The second example uses sqlplus and a connect string to connect.
Try both connection methods.
# Log in to Oracle. Try both isql and sqlplus64 clients.
[root]# isql -v ODBC
[root]# sqlplus64 moab/moab@adaptive-oracle:1561/MOAB
- Select some data from one or more of the tables (Nodes, Events, and the like) to verify that data is being stored in the Moab Oracle instance.
# sqlplus64 moab/moab@adaptive-oracle:1561/MOAB
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 4 14:59:02 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
JOBS
REQUESTS
RESERVATIONS
VCS
EVENTTYPE
GENERALSTATS
GENERICMETRICS
NODESTATS
NODESTATSGENERICRESOURCES
EVENTS
JOBHISTORY
MCHECKPOINT
NODES
TRIGGERS
MOAB
OBJECTTYPE
16 rows selected.
- Tail the moab.log file for ODBC errors.
Add the following text to the file.
[Oracle 11g ODBC driver]
Description = Oracle ODBC driver for Oracle 11g
Driver = /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
Setup =
FileUsage =
CPTimeout =
CPReuse =
Driver Logging = 7
[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log
ForceTrace = Yes
Pooling = No
DEBUG = 1
Driver Logging is set high (level 7) so that you can debug during the installation and configuration process if necessary. You can decrease the setting or remove the directive once you finish the process.
To configure the location of the ODBC log (/tmp/odbc.log), set the TraceFile attribute shown in the example above. See "unixODBC without the GUI" on the unixODBC website for more information.
If you encounter any errors using the isql command, there was a problem setting up the ODBC to Oracle connection. Try the following debugging steps to resolve the issue:
Related Topics