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

none of the proposed solutions works for me, as my script is executed in machines running several countries, with different locales, I can't simply check for one String simply because this string in the other machine is translated to a different language. As a solution I'm using SQLcl

https://www.oracle.com/database/technologies/appdev/sqlcl.html

which is compatible with all sql*plus scripts and allow you to test the database connectivity like this:

echo "disconnect" | sql -L $DB_CONNECTION_STRING > /dev/null || fail "cannot check connectivity with the database, check your settings"

Answer 2

#!/bin/bash
output=`sqlplus -s "user/[e-mail] " <<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 3

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 4

Use a script like this:

#!/bin/sh
echo "exit" | sqlplus -L uid/pwd@dbname | 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 5

#! /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.

Answer 6

Here's a good option which does not expose the password on the command line

#!/bin/bash
CONNECT_STRING=<USERNAME>/<PASS>@<SID>
sqlplus -s -L /NOLOG <<EOF
whenever sqlerror exit 1
whenever oserror exit 1
CONNECT $CONNECT_STRING
exit
EOF

SQLPLUS_RC=$?
echo "RC=$SQLPLUS_RC"
[ $SQLPLUS_RC -eq 0 ] && echo "Connected successfully"
[ $SQLPLUS_RC -ne 0 ] && echo "Failed to connect"

exit SQLPLUS_RC