Check database connectivity using Shell script

user_441694 0
Viewed 75488

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?

shell linux database
8 Answers

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"
user_106469580 
#!/bin/sh
echo "exit" | sqlplus -S -L uid/pwd@dbname
if [ $? -eq 0 ] 
then
   echo "OK"
else
   echo "NOT OK"
fi

For connection validation -S would be sufficient.

The "silent" mode doesn't prevent terminal output. All it does is:

-S             Sets silent mode which suppresses the display of
               the SQL*Plus banner, prompts, and echoing of
               commands.

If you want to suppress all terminal output, then you'll need to do something like:

sqlplus ... > /dev/null 2>&1
user_114390530

This was my one-liner for docker container to wait until DB is ready:

until sqlplus -s sys/Oracle18@oracledbxe/XE as sysdba <<< "SELECT 13376411 FROM DUAL; exit;" | grep "13376411"; do echo "Could not connect to oracle... sleep for a while"; sleep 3; done

And the same in multiple lines:

until sqlplus -s sys/Oracle18@oracledbxe/XE as sysdba <<< "SELECT 13376411 FROM DUAL; exit;" | grep "13376411"; 
do 
  echo "Could not connect to oracle... sleep for a while"; 
  sleep 3; 
done

So it basically does select with magic number and checks that correct number was actually returned.

user_3600600
Related