1

I'm writing a unix script to check for database connectivity in a server. When my database connection gets errored out or when there is delay observed in connecting to the database, I want the output as "Not connected". In case it gets connected, my output should be "Connected". It is a Oracle databse.

When there is delay in database connectivity, my code is not working and my script gets hung. What changes should I make in my code so that it is able to handle both the conditions(when I get an error connecting to the database and when there is delay observed in connecting to the database)??

if sqlplus $DB_USER/$DB_PASS@$DB_INSTANCE< /dev/null | grep 'Connected to'; then
echo "Connectivity is OK"
else
echo "No Connectivity"
fi
Shubham Chaurasia
  • 31
  • 3
  • 2
    So, how long is it hung for? 10 seconds? 10 minutes? There are several questions on checking status of an Oracle database here; most of them say basically "it isn't easy" But you can do a quick, but not fully accurate, check, via `tnsping` command. That just says whether the listener for the database is accessible. – Mark Stewart May 28 '21 at 20:50
  • 1
    Even if a simple connect works fine the database can have any problems. But for a simple check it could be enough, depends on requirements. I do not know much about unix batch processing, but you need at least two processes: One as an observer, that takes the time and kills the other if needed and one, that tries to connect. I would do this in python, lazarus or anything like this. But probably it is possible via batch too. – am2 May 28 '21 at 20:56
  • http://www.dba-oracle.com/t_fixing_slow_connections_10g.htm for ideas – Nic3500 May 29 '21 at 01:00
  • @Mark Stewart I want a mail to trigger if database is not connecting for more than 5 mins. How should I modify my code?? – Shubham Chaurasia May 30 '21 at 05:41
  • What flavor of Unix / Linux shell script language are you using? `ksh` or `bash` or a different one? Please [edit] your question and replace the [unix] tag with the appropriate script language, or add that information to your question text. – Mark Stewart Jun 01 '21 at 16:40

2 Answers2

1

The first thing to add to your code is a timeout. Checking database connectivity is not easy and there can be all kinds of problems in the various layers that your connection passes. A timeout gives you the option to break out of a hanging session and continue the task with reporting that the connection failed.

googleFu gave me a few nice examples: Timeout a command in bash without unnecessary delay

0

If you are using Linux, you can use the timeout command to do what you want. So the following will have three outcomes, setting the variable RC as follows:

  1. "Connected to" successful: RC set to 0
  2. "Connected to" not found: RC set to 1
  3. sqlplus command timed out after 5 minutes: RC set to 124
WAIT_MINUTES=5
SP_OUTPUT=$(timeout ${WAIT_MINUTES}m sqlplus $DB_USER/$DB_PASS@$DB_INSTANCE < /dev/null )
CMD_RC=$?
    
if [ $CMD_RC -eq 124 ]
then
        ERR_MSG="Connection attempt timed out after $WAIT_MINUES minutes"
        RC=$CMD_RC
else
        echo $SP_OUTPUT | grep -q 'Connected to'
        GREP_RC=$?
        if [ $GREP_RC -eq 0 ]
        then
            echo "Connectivity is OK"
            RC=0
        else
            ERR_MSG="Connectivity or user information is bad"
            RC=1
        fi
fi

if [ $RC -gt 0 ]
then
        # Add code to send email with subject of $ERR_MSG and body of $SP_OUTPUT
        echo Need to email someone about $ERR_MSG
fi

exit $RC

I'm sure there are several improvements to this, but this will get you started.

Briefly, we use the timeout command to wait the specified time for the sqlplus command to run. I separated out the grep as a separate command to allow the use of timeout and to allow more flexibility in checking additional text messages.

There are several examples on StackOverflow on sending email from a Linux script.

Mark Stewart
  • 2,046
  • 4
  • 22
  • 32