How to connect to a DB installed on another host using shell script?

UNIX for Beginners Q & A
1

Hi Experts

There are two hosts say A and B. On host B, Oracle DB is installed and running. I am trying to check if the DB on host B is up and running fine from a script on host A. Is this possible. I tried using commands like sql plus but those doesn't seem to help. Could you please guide.

2

IMHO you need to see 2 things running on a server to consider an oracle instance is up, and perhaps running:
1) the listener
2) a oracle pmon process associated to that instance
Using sqlplus wont work if no listener to start with...

So a shell script would be to connect to the host B and using ps command and the adequate grep to return the wanted information
and your shell would be using a Here doc formating

3

Thank you vbe for the reply. This is what i have written. Could you please see if this is going to work:

Script on Host B:

#Variables
rval=0

#Check if DB is up
ps -ef|grep -v grep|grep pmon > /dev/null
rval=$?
if (( $rval == 0 ))
then
   echo "DB is up and running."
else
    echo "Waiting for DB to start"
fi

while [ "$rval" != "0" ]
 do
    echo " Trying again after 10 seconds"
    sleep 10
ps -ef|grep -v grep|grep pmon > /dev/null
rval=$?
echo "DB is up and running."
done

Script on Host A:

#Variables
rval1=0
local checkDB
checkDB=$(ssh user@server /scripts/checkDB.sh)
echo $?
if (( $rval == 0 ))
then
ps -ef|grep -v grep|grep server.sh > /dev/null
rval1=$?
if (( $rval1 == 0 ))
then
   echo "Server is already running."
else
   echo "Starting Server ..."
   $home/bin/startup.sh
   ps -ef|grep -v grep|grep server.sh > /dev/null
   rval1=$?
   if (( $rval1 == 0 ))
   then
      echo "Server is starting."
   fi
fi
 exit $rval
4

Thanks for posting this. What error messages/output do you actually get? Please post them (in CODE tags for clarity) and we can try to help. Does it say sqlplus: not found or connection refused , something about credentials or what? It could be many things at present.

If there is not useful output because your script covers it up, try running your script with -x set. You don't mention a shell, but it might work as one of these or a similar variation:-

ksh -x /path/to/script
bash -x /path/to/script

Indenting your code will help clarity and show you more easily where conditions and loops are effective.

Your "script on server A" also has this statement if (( $rval == 0 )) yet you do not set this. Your previous active statement may generate a return code or some output, but you don't use them. The return code from the script on Server B will not be passed back to server A. Server A will see the return code of the ssh command itself, not what was run by it so that will just tell you if ssh connected.

Have you considered tnsping to determine database state?

Thanks, in advance,
Robin

1 Like
5

I'm afraid that none of the scripts posted will do what you expect. On the other hand, it's not too well known what your targets are. Some comments:

Host B:

  • is that script's path /scripts/checkDB.sh ?
  • it will output lines after lines "Trying ..." and "DB is up ..." if the pmon test fails. This is important for the planned evaluation on host A.
  • try ps -ef | grep -q [p]mon in lieu of your lenghtier approach.

Host A:

  • the local variable checkDB will be filled with text (at least "DB is up ... if not with lines after lines) but never evaluated.
  • rval is set to 0 and not modified before the first zero test.
  • what is the process test for server.sh for?
  • why do you run startup.sh on host A if the DB should run on host B?
  • same comment on ps ... | grep ... as for host B.
6

Thank you for getting back. I am trying to bring up a application server on Host A if the DB is up on host B. tnsping and sqlplus are not working when trying to execute on Host A. So i thought of doing an ssh to host B and if the process ID is up or retry till the process ID on host B is up and then start the application server on Host A.

7

So, given that my crystal ball is in for servicing at the moment, when you say "not working", can you actually show us the errors? Without them I might suggest:-

  • You don't have the Oracle client installed
  • Your network configuration is wrong 'somewhere'
  • tnsnames.ora is incomplete
  • The server is not listening on the defined port
  • You have a spelling mistake
  • Is the power on even?

It might seem a little flippant and I apologise, but if we cannot see the symptoms, we can't help to diagnose it.

Kind regards,
Robin

1 Like
8

Thank you for getting back. When i use sqlplus or tnsping, both of them return 'command not found' error on Host A probably because oracle client is not installed on host A but is on Host B.

9

Hi Experts

This is the code on Host A:

<

rval1=0
#local checkDB
checkDB=$(ssh user@host
expect "password"
send "password\r"
interact
/home/test.sh)
echo $?

/>

Code on Host B:

<

rval=0

#Check if DB is up
ps -ef|grep -v grep|grep pmon > /dev/null
rval=$?
if (( $rval == 0 ))
then
   echo "DB is up and running."
else
    echo "Waiting for DB to start"
fi

while [ "$rval" != "0" ]
 do
    echo " Trying again after 10 seconds"
    sleep 10
ps -ef|grep -v grep|grep pmon > /dev/null
rval=$?
echo "DB is up and running."
done
exit $rval

/>

When i run the script on host A, it again prompts for password and when i explicitly give the password, it keeps running without exit and when i kill the process id on host B, then the below errors are shown on Host A:

user@host's password:
Connection to uslxcd1619 closed by remote host.
Connection to uslxcd1619 closed.
couldn't read file "password": no such file or directory
./filename.sh: line 9: send: command not found
./filename.sh: line 10: interact: command not found
 ./filename.sh: line 11: /home/test.sh: No such file or directory