Checking SQL DB Availability


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Checking SQL DB Availability
# 1  
Old 02-13-2014
Checking SQL DB Availability

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. Smilie

#!/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  
Old 02-13-2014
Try sourcing .profile:
Code:
ssh -l myuser UNIXSERVER ". ~/.profile; sqlplus ????@????/????"

OR use absolute path of sqlplus:
Code:
ssh -l myuser UNIXSERVER "/path_to_sqlplus/sqlplus ????@????/????"

This User Gave Thanks to Yoda For This Post:
# 3  
Old 02-13-2014
Quote:
Originally Posted by Yoda
Try sourcing .profile:
Code:
ssh -l myuser UNIXSERVER ". ~/.profile; sqlplus ????@????/????"

OR use absolute path of sqlplus:
Code:
ssh -l myuser UNIXSERVER "/path_to_sqlplus/sqlplus ????@????/????"

Ill try this. Didnt know. ill post the results.Smilie

---------- 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  
Old 02-13-2014
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.
This User Gave Thanks to Yoda For This Post:
# 5  
Old 02-13-2014
Quote:
Originally Posted by Yoda
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.
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.Smilie

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

Quote:
Originally Posted by SeaSoul
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.Smilie

I was able to do it. Tnak you very much for your help!!!!!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Programming

Dynamically checking rules in database tables using SQL

I want to check for rows in a table where all values (except the key) is empty. I am using MySQL 5.5. I plan to do this mechanically, so the approach should work for any table in my database schema. Suppose for illustration purposes I start with the following table: CREATE TABLE `sources` ( ... (4 Replies)
Discussion started by: figaro
4 Replies

2. SCO

Stop boot system at "Checking protected password and checking subsystem databases"

Hi, (i'm sorry for my english) I'm a problem on boot sco unix 5.0.5 open server. this stop at "Checking protected password and checking subsystem databases" (See this image ) I'm try this: 1) http://www.digipedia.pl/usenet/thread/50/37093/#post37094 2) SCO: SCO Unix - Server hangs... (9 Replies)
Discussion started by: buji
9 Replies

3. Shell Programming and Scripting

Script should keep checking for availability of files.

Hi, I want my script to check for availability of few files contineously for some time. Lets say from 10:00 AM to 10:30 AM script should keep checking on a particular location for each file one by one. If all the files are available then only it should complete the next processing part, else... (2 Replies)
Discussion started by: amit.mathur08
2 Replies

4. Programming

Checking columns in SQL, comparing user input and sizes.

I'm writing a KSH shell script that's using SQL though DB2. If I have a table defined and populated db2 "create table tb(num int,letter char(4))" db2 "insert into tb values(111,a) db2 "insert into tb values(112,b) db2 "insert into tb values(111,c) How can I check if a letter user... (0 Replies)
Discussion started by: busdude
0 Replies

5. Shell Programming and Scripting

Checking availability of service

HI CAN ANY ONE HELP ME FOR BELOW QUESTION CHECKING AVAILABILITY OF SERVICE IN LINUX (2 Replies)
Discussion started by: satheeshkr_cse
2 Replies

6. Shell Programming and Scripting

checking sql connectivity in unix

hi, i am using a sqlplus connection in one of my shell scripts. i want to check if the connection is set properly or not.. can i use '$?' to check the status of the sql connectivity..? or is there any other option to do that..? thanks, (1 Reply)
Discussion started by: kripssmart
1 Replies

7. Solaris

Printer availability

Hi, Im new in shell programming. I need to know the availability for specific printers. We send requests from solaris to printers located at an NT server. My idea is creating a shell that will monitor availbaility and send an email when something is wrong. I have a file with printers and its... (2 Replies)
Discussion started by: agsarm1971
2 Replies

8. Shell Programming and Scripting

Checking availability of Web pages through Unix script

Hi Guru's, I need to check the availability of a web page for every one hour through a script. Following is the requirement. 1. Go to http://vsn.srivsn.com 2. If any error encountered in openeing the home page, it should trigger an email with the appropriate error message. 3. If page opens... (6 Replies)
Discussion started by: srivsn
6 Replies

9. Shell Programming and Scripting

Checking for availability of remote server

Hi. In my KSH script I'm running a command on a remote server. I want to know how I can test to see if the remote server is available (accessible) prior to running the command. Any ideas? My command looks like: `ssh $USER@$TARGET_SERVER_DNS ls -l $REMOTE_FOLDER ` This check should be... (4 Replies)
Discussion started by: dylanmilks
4 Replies

10. IP Networking

Checking for availability of remote server

Hi. In my KSH script I'm running a command on a remote server. I want to know how I can test to see if the remote server is available (accessible) prior to running the command. Any ideas? My command looks like: `ssh $USER@$TARGET_SERVER_DNS ls -l $REMOTE_FOLDER ` This check should be... (1 Reply)
Discussion started by: dylanmilks
1 Replies
Login or Register to Ask a Question