Oracle

Installing an rpm on Debian

My days of supporting Debian are numbered.  That isn’t a complaint, it will actually be nice to support one platform soon.  Until then I thought I’d share a little.

I needed to install oci8 to support a PHP application.  In doing this I ran into the following two problems, here are the solutions that worked for me (results may vary).

# apt-get install alien
# alien -d oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm 
error: db5 error(-30969) from dbenv->open: BDB0091 DB_VERSION_MISMATCH: Database environment version mismatch
error: cannot open Packages index using db5 - (-30969)
error: cannot open Packages database in /tmp/.rpmdb
error: db5 error(-30969) from dbenv->open: BDB0091 DB_VERSION_MISMATCH: Database environment version mismatch
error: cannot open Packages index using db5 - (-30969)
error: cannot open Packages database in /tmp/.rpmdb

The solution for this to do the following:

# rpm --rebuilddb
# alien -d oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm

That worked, next up the fix the pecl issue:

# pecl install oci8
Warning: Invalid argument supplied for foreach() in Command.php on line 249
PHP Warning: Invalid argument supplied for foreach() in /usr/share/php/PEAR/Command.php on line 249
PHP Stack trace:
PHP 1. {main}() /usr/share/php/peclcmd.php:0
PHP 2. require_once() /usr/share/php/peclcmd.php:31
PHP 3. PEAR_Command::getCommands() /usr/share/php/pearcmd.php:54
PHP 4. PEAR_Command::registerCommands() /usr/share/php/PEAR/Command.php:302

This one was not as obvious, it required a reinstallation as follows:

# apt-get purge php*-xml
# apt-get autoremove php*-xml
# apt-get install php-xml php7.0-xml
# apt-get purge php*-xml
# pecl install oci8
    (now add extension=oci8.so to the follwing ini files)
# vim /etc/php/7.0/apache2/php.ini 
# vim /etc/php/7.0/cli/php.ini 
# php --ri oci8

That should be it.  Good luck

Testing Database Connectivity

Working with databases and new application installations can be really fun.  Problem is, when there is a problem, everyone starts the blame game.  Nothing unusual about that, part of an administrators job is to troubleshoot and prove where the problem starts.  When dealing with external databases, there can be numerous problem, the firewall could be blocking, the local or remote port could be blocked on the system, or the database credentials could be incorrect.  Testing for the last helps troubleshoot all of these.  Ruling out the database connection helps focus the application administrator on the real problem!  Testing a remote oracle database is pretty simple if you have the oracle client configured with tnsnames, etc.  But if that isn’t necessary you may not have it configured.  When you don’t this is the easiest way to test the database connection via the command line:

sqlplus 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))'

Prior to this make sure your ORACLE_HOME environment variable is set correctly.  You may also need the LD_LIBRARY_PATH set to $ORACLE_HOME/lib.

UPDATE: 21may2015:

Now that you are in you may want to check a few things out.  To give you a quick reminder of the syntax here are a few to get the lay of the land:

To list all tables in a database (accessible to the current user):

SQL> select table_name from user_tables;

 

To list the contents of a specific table, type:

SQL> select * from name_of_table;

You can find more info about views all_tablesuser_tables, and dba_tables in Oracle Documentation.

 

OCI on RHEL6

Our developers had to have OCI.  Now that I got that out of the way. 😉

We use Oracle as our DB for most applications (calm down, like you couldn’t have figured that out).  In setting up a new application server for a custom application it came to my attention that the application used oci calls.  What a pain to get working on Red Hat!  There is a ton of documentation for Oracle Linux, but that wasn’t an option.  So here is what I had to do to get things working.

yum install php-pecl-apc php-pear gcc php-devel glibc glibc-devel

rpm -iv oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm 
pear download pecl/oci8
tar -xvzf oci8-1.4.10.tgz
./configure --with-oci8=shared,instantclient,/apps/oracleClient/oraInventory/product/11.1.0/client_1
cd oci8-1.4.10
./configure --with-oci8=shared,instantclient,/usr/lib/oracle/11.2/client64
rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm
echo '/usr/lib/oracle/11.2/client64/lib' > /etc/ld.so.conf.d/oracle-instantclient-x86_64.conf
ldconfig
yum install php-devel
wget http://pecl.php.net/get/oci8-2.0.2.tgz
pecl install oci8-2.0.2.tgz 
echo 'extension=oci8.so' > /etc/php.d/oci8.ini
/etc/init.d/httpd restart