How To Check If Oracle Odbc Driver Is Installed

viii.147 Connecting to an Oracle Database with an ODBC Driver

Context

This documentation shows how to gear up and configure Moab to connect to an Oracle database using the ODBC commuter.

To connect to an Oracle database with an ODBC driver

1. Install and configure the Oracle Instant Client with ODBC supporting libraries. For instructions, run into Installing the Oracle Instant Client.
2. Open your Moab configuration file ($MOABHOMEDIR/moab.cfg) and add the following lines to the end of the file.

   USEDATABASE ODBC

   # Turn on stat profiling

   USERCFG[DEFAULT]        ENABLEPROFILING=Truthful

   GROUPCFG[DEFAULT]       ENABLEPROFILING=True

   QOSCFG[DEFAULT]         ENABLEPROFILING=TRUE

   CLASSCFG[DEFAULT]       ENABLEPROFILING=TRUE

   ACCOUNTCFG[DEFAULT]     ENABLEPROFILING=Truthful

   NODECFG[DEFAULT]        ENABLEPROFILING=True

3. Configure the Oracle ODBC Driver. The odbcinst.ini file must be contained in /etc.

   [root]# vim /etc/odbcinst.ini

   

   Run the following command to detect the Oracle Instant Client commuter. Yous could also query the Oracle Instant Customer package that was installed.

   [root]# updatedb && locate libsqora

Add the post-obit 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.eleven.one

Setup           =

FileUsage       =

CPTimeout       =

CPReuse         =

Driver Logging  = vii

[ODBC]

Trace = Aye

TraceFile = /tmp/odbc.log

ForceTrace = Yeah

Pooling = No

DEBUG = 1



Driver Logging is ready high (level 7) so that you can debug during the installation and configuration procedure if necessary. You lot can subtract the setting or remove the directive one time you finish the process.



To configure the location of the ODBC log (/tmp/odbc.log), gear up the TraceFile attribute shown in the example higher up. Run into "unixODBC without the GUI" on the unixODBC website for more data.

4. Because the driver installed in step 1 is a shared library, run ldd to verify that information technology and all of its dependencies are installed and working.

   [root]# ldd /usr/lib/oracle/xi.2/client64/lib/libsqora.so.11.one

                   linux-vdso.and then.1 =>  (0x00007fff631ff000)

                   libdl.so.2 => /lib64/libdl.so.2 (0x00007f8afbe83000)

                   libm.so.six => /lib64/libm.and so.half-dozen (0x00007f8afbbff000)

                   libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f8afb9e1000)

                   libnsl.so.1 => /lib64/libnsl.then.1 (0x00007f8afb7c8000)

                   libclntsh.so.11.ane => /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 (0x00007f8af8e59000)

                   libodbcinst.so.1 => not institute

                   libc.so.6 => /lib64/libc.so.vi (0x00007f8af8ac5000)

                   /lib64/ld-linux-x86-64.so.2 (0x0000003bdb000000)

                   libnnz11.and then => /usr/lib/oracle/11.2/client64/lib/libnnz11.so (0x00007f8af86f8000)

                   libaio.so.i => /lib64/libaio.so.1 (0x00007f8af84f6000)

   

   If the command returns libodbcinst.then.1 => not found, create a symbolic link from /usr/lib64/libodbcinst.so.1 to /usr/lib64/libodbcinst.so.two. This is a known Red Lid issue. See Blood-red Chapeau Bugzilla for more information.

   [root]# locate libodbcinst

   /usr/local/lib/libodbcinst.so.2

   [root]# cd /usr/lib64

   [root]# ln -s libodbcinst.so.ii libodbcinst.then.1

   Rerun ldd. Information technology should load libsqora.and so.11.ane without mistake, as shown in the ldd instance above.

   

   If the ldd command returns a warning similar this: "ldd: alert: yous do not accept execution permission for `/usr/lib/oracle/xi.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.xi.1 without error, as shown in the ldd example above.

5. 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 Customer folio.

   [root]# vim $MOABHOMEDIR/dsninfo.dsn

   Add the following lines the file, but change ServerName, UserName, and Countersign to adjust your own organization. ServerName is the proper name of the Oracle database case. Username and Password are the credentials used to connect to that example.

   [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

   Countersign = moab

   StatementCache=F

   CacheBufferSize=20

   UseOCIDescribeAny=F

   MaxTokenSize=8192

6. 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 at that place are no conflicts.

   [root]# cat $MOABHOMDIR/dsninfo.dsn >> /etc/odbc.ini

7. Create a directory to shop the tnsnames.ora file you will create in the next pace.

   [root]# mkdir /etc/oracle

8. Create the tnsnames.ora file. The ServerName in $MOABHOMEDIR/dsninfo.dsn tells the Oracle ODBC driver what tnsnames.ora entry to employ (MOAB). The MOAB tnsnames entry tells the Oracle ODBC commuter to connect to server adaptive-oracle on the local domain (air conditioning) on port 1561 using TCP and to connect to the Oracle instance named MOAB (The SID is the unique name of the instance).

   [root]# true cat >/etc/oracle/tnsnames.ora <<EOL

   MOAB =

                   (Clarification =

                   (ADDRESS_LIST =

                   (ADDRESS = (PROTOCOL = TCP)(HOST = adaptive-oracle)(PORT = 1561))

                   )

                   (CONNECT_DATA =

                   (SID = MOAB)

                   )

                   )

   EOL

9. Create a profile script (oracle-instant-client.sh) to be invoked past the operating organization at startup. This script will set the ORACLE_HOME, TWO_TASK, and TNS_ADMIN environment variables required by Oracle and will ameliorate the LD_LIBRARY_PATH to include required Oracle client libraries in the library search path.

   [root]# cat >/etc/profile.d/oracle-instant-customer.sh <<EOL

   # Ready ORACLE_HOME to the directory where the bin and lib directories are located for the oracle client

   export ORACLE_HOME=/usr/lib/oracle/xi.2/client64

   # No need to add ORACLE_HOME to the linker search path. oracle-instant-client.conf in

   # /etc/ld.and so.conf.d should already contain /usr/lib/oracle/eleven.2/client64.

   # Alternately, y'all tin can set it here past uncommenting the post-obit line:

   # export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

   # Define the default location where Oracle should await for the server

   export TWO_TASK=//adaptive-oracle:1561/listener

   # Define where to find the tnsnames.ora file

   export TNS_ADMIN=/etc/oracle

   EOL

10. Source the oracle-instant-client.sh script and verify that each surroundings variable is set correctly.

    [root]# source /etc/profile.d/oracle-instant-customer.sh

    [root]# echo $ORACLE_HOME

    [root]# repeat $LD_LIBRARY_PATH

    [root]# repeat $TWO_TASK

    [root]# echo $TNS_ADMIN

11. 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 Customer

      . /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. Annotation that the moabd script is not invoked past the Moab startup script; The Moab startup script invokes the Moab binary (/opt/moab/sbin/moab) by default.

      #!/bin/sh

      #

      #   Copyright (C) 2022 by Adaptive Computing Enterprises, Inc. All Rights Reserved.

      #

      # Consign all environment variables required past the Oracle Instant Customer

      . /etc/contour.d/oracle-instant-customer.sh

      MOABHOMEDIR="/opt/moab" LD_LIBRARY_PATH="/opt/moab/lib:$LD_LIBRARY_PATH" moab "$@"

12. Verify the Oracle ODBC driver is working.

    isql -five ODBC

    +---------------------------------------+

    | Connected!                            |

    |                                       |

    | sql-statement                         |

    | help [tablename]                      |

    | quit                                  |

    |                                       |

    +---------------------------------------+

If you encounter whatever errors using the isql command, there was a trouble setting upward the ODBC to Oracle connection. Try the following debugging steps to resolve the upshot:

1. The odbcinst.ini and odbc.ini files are usually causeless to be located in /etc, but that is not ever 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

2. Because odbcinst.ini and odbc.ini are expected in /etc/unixODBC, not /etc, move them from /etc to /etc/unixODBC.
3. Utilise the strace command to determine where isql expects the odbc.ini and odbcinst.ini files. Note the location in which isql expects these files.
13. If y'all 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 lot 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

14. Verify that the database schema installed correctly by listing the tables. Your results should await similar 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

    sixteen rows fetched

15. Restart Moab.
16. Verify Moab is correctly configured to write to the Oracle database by doing each of the post-obit steps:
    1. Tail the moab.log file for ODBC errors.

       # Cheque the $MOABHOMEDIR/log/moab.log file for ODBC errors. Yous should see a few hits even if there are no errors.

       [root]# tail -f $MOABHOMEDIR/log/moab.log | grep -i odbc

    2. Log in to the Moab Oracle database.

       In the start example below, isql will search /etc/odbc.ini for "[ODBC]". unixODBC will and then use the Oracle 11g ODBC driver defined in /etc/odbcinst.ini to constitute a connectedness. 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 2nd example uses sqlplus and a connect string to connect.

       Endeavor both connection methods.

       # Log in to Oracle. Try both isql and sqlplus64 clients.

       [root]# isql -five ODBC

       [root]# sqlplus64 moab/moab@adaptive-oracle:1561/MOAB

    3. Select some data from one or more than of the tables (Nodes, Events, and the similar) to verify that information is being stored in the Moab Oracle instance.

       # sqlplus64 moab/moab@adaptive-oracle:1561/MOAB

       SQL*Plus: Release 11.ii.0.iv.0 Production on Friday October 4 14:59:02 2022

       Copyright (c) 1982, 2022, Oracle.  All rights reserved.

       Continued to:

       Oracle Database 11g Release xi.2.0.1.0 - 64bit Product

       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

       xvi rows selected.

Related Topics

Installing the Oracle Instant Client

Connecting to a MySQL Database with an ODBC Driver

Connecting to a PostgreSQL Database with an ODBC Commuter

Database Configuration

Source: http://docs.adaptivecomputing.com/9-1-0/MWS/Content/topics/moabWorkloadManager/topics/databases/oracle.html

Posted by: mcalisterandister.blogspot.com

Share this post