I can't login! The server is down!

Yup. I'm tired of hearing this from users.

Since I changed jobs from a software development house to a more focused DBA role I hear this a lot. As much as I'de like to just log into their app server and comb through logs to find the problem like the old days... I have to debug what I can from the server side and get the app server folks looking from their end.

Here is a quickie script that should work on most Unices.

It just tries to login and logout using SQLPLUS and prints a date/time and # of seconds to connect to the screen. It also collects the SQLPLUS output in a separate file so that it can be reviewed for ORA errors and such. If you can, set up this script on the machine that is having problems connecting.

You could also call sqlplus -s to run SQLPLUS without all the version number barfing. This would enable you to insert a query and collect results to a file.

The script below is customized to the version of Oracle I am using, but update the paths and it should work with most any version. I cheated and used a perl -e to get the number of seconds past the epoch. I am constrained to ksh most of the time and don't have all the fancy options on the date command like I do on bash/linux.

#!/bin/ksh

ORACLE_SID=MYSID; export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/10.2.0.3; export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib; export LD_LIBRARY_PATH

BEGINTIME=`perl -e 'print time;'`

${ORACLE_HOME}/bin/sqlplus user/pass@tnsname >> /u01/app/oracle/sqlplus.log
exit
EOF

ENDTIME=`perl -e 'print time;'`

SECS=`expr $ENDTIME - $BEGINTIME`

TIME=$(date +%Y-%m-%d\ %T)

echo $TIME ,  $SECS

Here is an example of how to run it every 15 minute via cron:

00,15,30,45 * * * * /u01/app/oracle/testlogin.sh >> /u01/app/oracle/testlogin.log

testlogin.log will collect the output from each run into a comma delimited file that you can graph in Excel and send to your app server folks to let them know you were able to log into the database fine every 15 minutes for the past week :)

After you are done setting it up take a few minutes and watch http://www.thewebsiteisdown.com while you are waiting for cron to kick in.

-- Dave