Month: March 2015

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