How to use tnsping programmatically
Strange Cat @ Work
I'd like to know if it is possible to use tnsping programmatically in order
to check if Oracle server is available... I've been asked not to continuosly
try connection and disconnection in order to save network/system resources.
I will be using DotNet 2.0SP1 but any clue is welcome.
Thanx in advance
Paolo
ddf
tnsping verifies that the listener is running, and not much else. It
doesn't connect to the database so it can't tell you if the database
is down or not, as this example proves:
c:\>tnsping sning
TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production
on 08-APR-2009 07:45:53
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
c:\o_racle\Ora11g\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST
= snarg.dinglebop.net)(PORT = 1521)) (CONNECT_DATA = (SERVER =
DEDICATED)
(SERVICE_NAME = sning)))
OK (40 msec)
c:\>sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Apr 8 07:46:03 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
c:\>tnsping sning
TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production
on 08-APR-2009 07:47:00
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
c:\o_racle\Ora11g\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST
= snarg.dinglebop.net)(PORT = 1521)) (CONNECT_DATA = (SERVER =
DEDICATED)
(SERVICE_NAME = sning)))
OK (20 msec)
c:\>
David Fitzjarrell
Mark D Powell
You can call tnsping from a shell script or a program, but how often
are you planning on running this check?
How often were you running your last check that managment or the
network team asked you not to run?
There is generally no real need to continuously check to see if the
database is there. If the database is not there you will know in
fairly short order when the phone calls start coming in. From within
an application you can just check the return code on the connect
call. If the connect fails you will get an error code and you can
pass this on to an alert system (feature) to provide the database down/
unreachable error information.
HTH -- Mark D Powell --
Strange Cat @ Work
Thanx for answering.
Bye
Paolo
Strange Cat @ Work
> are you planning on running this check?
> How often were you running your last check that managment or the
> network team asked you not to run?
> There is generally no real need to continuously check to see if the
> database is there. If the database is not there you will know in
> fairly short order when the phone calls start coming in. From within
> an application you can just check the return code on the connect
> call. If the connect fails you will get an error code and you can
> pass this on to an alert system (feature) to provide the database down/
> unreachable error information.
Thanx for answering Mark,
the need is to avoid attempting the connection if the Oracle instance
appears to be unreachable. We're developing a panel pc app that has to
connect to an Oracle server on demand (user touching screen and booking for
canteen).
Problem is that the Oracle server happens to be on the other side of the
mountains (and this is for real, no joke) and we've been warned about lack
of connectivity/bandwidth on this connection.
So, to make a long story short, what i'm trying to do is avoid connection
(and users waiting for a timeout to occur in front of pc) if Oracle is not
tnspingable...
Other ideas are welcome, of course :)
> HTH -- Mark D Powell --
Thanx again
Paolo
Michel Cadot
"Strange Cat @ Work" <du...@dummy.com> a �crit dans le message de news: 49dcaa04$0$1121$4faf...@reader1.news.tin.it...
Being tnspingable does not mean instance is reachable, just listener is.
Why not just try to connect ?
Regards
Michel
Michael Austin
> "Strange Cat @ Work" <du...@dummy.com> a écrit dans le message de news: 49dcaa04$0$1121$4faf...@reader1.news.tin.it...
exactly - tnsping is no more network intensive as a connect/disconnect -
not enough to matter anyway... unless you are trying to reconnect every
second.. You could also configure tcpip to have a shorter timeout on
the client.
see: http://support.microsoft.com/kb/170359
From an application standpoint, why not use something that has a
connection pool where it "stays" attached by pinging dual (select * from
dual every n-minutes). However, given your explanation of unreliable
connections, this still in no way will guarantee the connection will be
available/usable when the person walks up to the kiosk n-seconds later.
ddf
On Apr 8, 8:43 am, "Strange Cat @ Work" <du...@dummy.com> wrote:
> > You can call tnsping from a shell script or a program, but how often
> > are you planning on running this check?
> > How often were you running your last check that managment or the
> > network team asked you not to run?
> > There is generally no real need to continuously check to see if the
> > database is there. If the database is not there you will know in
> > fairly short order when the phone calls start coming in. From within
> > an application you can just check the return code on the connect
> > call. If the connect fails you will get an error code and you can
> > pass this on to an alert system (feature) to provide the database down/
> > unreachable error information.
>
> Thanx for answering Mark,
>
> the need is to avoid attempting the connection if the Oracle instance
> appears to be unreachable.
Simply because you can tnsping the listener doesn't mean the database
is available.
> We're developing a panel pc app that has to
> connect to an Oracle server on demand (user touching screen and booking for
> canteen).
>
Again, just because you can tnsping the listener does not guarantee
you can connect to the Oracle database, so your tnsping effort may
succeed only to have the users receive the following error:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested
in connect descriptor
Or this error, if the database is hard-coded into the listener.ora
file:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
> Problem is that the Oracle server happens to be on the other side of the
> mountains (and this is for real, no joke) and we've been warned about lack
> of connectivity/bandwidth on this connection.
Yes, the tnsping utility *can* prove you do have connectivity to the
server if the listener is up. If the listener is down you'll receive
an ORA-12541 error, which might indicate you can get to the server,
and it might not.
>
> So, to make a long story short, what i'm trying to do is avoid connection
> (and users waiting for a timeout to occur in front of pc) if Oracle is not
> tnspingable...
>
Again I say that simply because tnsping returns error-free doesn't
mean your database is running or that a connection attempt will be
successful. Honestly I am not being negative, I am just trying to
provide a realistic view of this situation.
> Other ideas are welcome, of course :)
>
> > HTH -- Mark D Powell --
>
> Thanx again
> Paolo
David Fitzjarrell
joel garry
> > You can call tnsping from a shell script or a program, but how often
> > are you planning on running this check?
> > How often were you running your last check that managment or the
> > network team asked you not to run?
> > There is generally no real need to continuously check to see if the
> > database is there. If the database is not there you will know in
> > fairly short order when the phone calls start coming in. From within
> > an application you can just check the return code on the connect
> > call. If the connect fails you will get an error code and you can
> > pass this on to an alert system (feature) to provide the database down/
> > unreachable error information.
I think the idea is to get to work on it before the phones light
up :-)
>
> Thanx for answering Mark,
>
> the need is to avoid attempting the connection if the Oracle instance
> appears to be unreachable. We're developing a panel pc app that has to
> connect to an Oracle server on demand (user touching screen and booking for
> canteen).
>
> Problem is that the Oracle server happens to be on the other side of the
> mountains (and this is for real, no joke) and we've been warned about lack
> of connectivity/bandwidth on this connection.
>
> So, to make a long story short, what i'm trying to do is avoid connection
> (and users waiting for a timeout to occur in front of pc) if Oracle is not
> tnspingable...
>
> Other ideas are welcome, of course :)
Just thinking out loud:
If your systems are on unix, watch for the process, send periodic
heartbeats, try connection if heartbeat lost. Like:
while `ps -ef|grep dbw0_$ORACLE_SID|grep -v grep`
do
sleep 60
echo "put date/time via rcp or mail here"
done
echo "THE FLOOD WATERS ARE RISING!!!"
And of course, do whatever the heck you want on the monitoring side.
If not on unix, email heartbeat from Oracle, deal with possibilities
when you don't get it.
EM has alerts, why not just use those? It emails me when it can't get
to the agent, which means something wrong. If your net is so
unreliable you can't get email, you need more basic monitoring than at
the oracle level.
The old Big Brother used to use simple scripts to generate statistics
on the monitored side, and simple browser scripts to check those files
on the monitor side. Haven't checked in a while if that stuff is
still around.
jg
--
@home.com is bogus.
http://www.computerworld.com.au/article/298623/mammoth_digital_array_blasts_star_trek_australian_premiere
Ruud de Koter
>> You can call tnsping from a shell script or a program, but how often
>> are you planning on running this check?
>> How often were you running your last check that managment or the
>> network team asked you not to run?
>> There is generally no real need to continuously check to see if the
>> database is there. If the database is not there you will know in
>> fairly short order when the phone calls start coming in. From within
>> an application you can just check the return code on the connect
>> call. If the connect fails you will get an error code and you can
>> pass this on to an alert system (feature) to provide the database down/
>> unreachable error information.
>
> Thanx for answering Mark,
>
> the need is to avoid attempting the connection if the Oracle instance
> appears to be unreachable. We're developing a panel pc app that has to
> connect to an Oracle server on demand (user touching screen and booking
> for canteen).
>
> Problem is that the Oracle server happens to be on the other side of the
> mountains (and this is for real, no joke) and we've been warned about
> lack of connectivity/bandwidth on this connection.
If the issue is actually in the network connection, tnsping seems a bit
of an overkill. Why don't you use ping (or something like it) to
ascertain the network connection is useable. I would assume the rest of
the technology (listener, Oracle instance, OS) to be robust enough to
risk the occasional error message.
>
> So, to make a long story short, what i'm trying to do is avoid
> connection (and users waiting for a timeout to occur in front of pc) if
> Oracle is not tnspingable...
When using ping there is no garantuee that you will have connectivity
even milliseconds after your last succesful test. Others have already
pointed this out for the tnsping approach. So all you 'll be doing is
avoiding timeouts. If that's your goal, then simply testing the network
connewction might work just as well.
Regards,
Ruud de Koter.
Shakespeare
> Hi Paolo,
<knip knip>
>
>>
>> So, to make a long story short, what i'm trying to do is avoid
>> connection (and users waiting for a timeout to occur in front of pc)
>> if Oracle is not tnspingable...
>
> When using ping there is no garantuee that you will have connectivity
> even milliseconds after your last succesful test.
Not even *while* you are testing.... TNSPING does not guarantee
connectivity at all! Even a 'dead' db can give correct tnspings...
<knip knip>
>
> Regards,
>
> Ruud de Koter.
>>
>> Other ideas are welcome, of course :)
>>
>>> HTH -- Mark D Powell --
>>
>> Thanx again
>> Paolo
Shakespeare
(What's in a ping?)
Strange Cat @ Work
thanks for being so supportive.
I will find my way through all the ideas that have been thrown on the table
here :)
Bye
Paolo
Frank van Bortel
Suggest you have a look at timeouts on network level,
and test for those in your code.
Particularly interesting could be:
- sqlnet.expire_time (in case you last a connection while a work)
- sqlnet.inbound_connect_timeout (will generate ORA-12547 or ORA-12637)
(and specify inbound_connect_timeout_listener on the server!)
- sqlnet.recv_timeout, in combination with sqlnet.send_timeout
http://download-west.oracle.com/docs/cd/B19306_01/network.102/b14213/sqlnet.htm
has more information
--
Regards,
Frank van Bortel
Strange Cat @ Work
Thanx for the answer, Frank!
Bookmarked and will have a look next.
Bye
Paolo
johnb...@sbcglobal.net
Why not just use "ping" not tnsping to see if a server is available?