Checking SQL DB Availability

Shell Programming and Scripting
1

Hello everybody. I have a little question. Im creating a script for checking the status of different servers, and everything is running fine except for one thing. I have to check if certain DBs are connectables, I dont care anything else, nor running querys nor updating tables, the only thing I need to know is if the DB is connectable. It should be an easy task but frankly Im not very sure how to do it, so I tried the code below. And the problem I got with it is an "sqlplus : not found" from the unix shell. If anyone could help me itd be great. Thanks in advance.

Here goes what I have until now. If anyone knows another way thats great, I really dont care HOW to do it, just that it works. :smiley:

#!/bin/bash
dbconnection=1
echo "Setting environment"
ssh -l myuser UNIXSERVER ". /apps/Ascential/DataStage/DSEngine/dsenv"
echo "Connecting to SQL"
dbcoonection=$(ssh -l myuser UNIXSERVER "sqlplus ????@????/????")
dbconnection=$?
echo "Status = $dbconnection"

if [ "$dbconnection" -lt "1" ]; then
echo "Connected"
else
echo "Not connected"
fi

2

Try sourcing .profile :

ssh -l myuser UNIXSERVER ". ~/.profile; sqlplus ????@????/????"

OR use absolute path of sqlplus:

ssh -l myuser UNIXSERVER "/path_to_sqlplus/sqlplus ????@????/????"
1 Like
3

Ill try this. Didnt know. ill post the results.:b:

---------- Post updated at 06:00 PM ---------- Previous update was at 05:14 PM ----------

HI. Your idea didnt work, but you give another, so I used this:

ssh -l myuser UNIXSERVER ". /apps/Ascential/DataStage/DSEngine/dsenv;sqlplus -l ????@????/????"

And It worked. The only issue is that I got the sql prompt and its supposed to be a automatization so, do u know if there is a third command I can use for exiting sql in the same line. I used "sqlplus quit" and didnt work. Maybe some flag in the first command to turn it promptless?

4

Why you want to test connectivity remotely using ssh?

I would suggest you to create your own tnsnames.ora with database address for establishing connection.

Then point TNS_ADMIN variable to the path where tnsnames.ora is placed and test connectivity locally.

1 Like
5

Because I really dont know much about managing DBs, Im working just testing the status of many many servers and this was the way I do it manually, so I think that it would be very simple to just convert it to an script. I have no idea how to create a .ora file, and honestly, I dont want to learn. haha. I jsut need to make this fast and efficient. Thanks for your advices. If this doesnt work I will try to do what u just told me, but ill have to read a little bit first.:b:

---------- Post updated at 06:49 PM ---------- Previous update was at 06:20 PM ----------

I was able to do it. Tnak you very much for your help!!!!!