Check database connectivity using Shell script

Question

I am trying to write a shell script to check database connectivity. Within my script I am using the command

sqlplus uid/pwd@database-schemaname

to connect to my Oracle database.

Now I want to save the output generated by this command (before it drops to SQL prompt) in a temp file and then grep / find the string "Connected to" from that file to see if the connectivity is fine or not.

Can anyone please help me to catch the output and get out of that prompt and test whether connectivity is fine?

Answer 1

You can avoid the SQL prompt by doing:

sqlplus uid/pwd@database-schemaname < /dev/null

SqlPlus exits immediately.

Now just grep the output of the above as:

if sqlplus uid/pwd@database-schemaname < /dev/null | grep 'Connected to'; then
   # have connectivity to Oracle
else
   # No connectivity
fi

Answer 2

Use a script like this:

#!/bin/sh
echo "exit" | sqlplus -L tsquared/ts@tsquared | grep Connected > /dev/null
if [ $? -eq 0 ] 
then
   echo "OK"
else
   echo "NOT OK"
fi

echo "exit" assures that your program exits immediately (this gets piped to sqlplus). -L assures that sqlplus won't ask for password if credentials are not ok (which would make it get stuck as well).

(> /dev/null just hides output from grep, which we don't need because the results are accessed via $? in this case)

Answer 3

#!/bin/bash
output=`sqlplus -s "user/[email protected] " <<EOF
           set heading off feedback off verify off
           select distinct machine from v\\$session;
           exit
EOF
`

echo $output
if [[ $output =~ ERROR ]]; then
     echo "ERROR"
else

     echo "OK"
fi 

Answer 4

#! /bin/sh  

if echo "exit;" | sqlplus UID/PWD@database-schemaname 2>&1 | grep -q "Connected to"
then echo connected OK
else echo connection FAIL
fi

Not knowing whether the "Connected to" message is put to standard output or standard error, this checks both. "qrep -q" instead of "grep... >/dev/null" assumes Linux.