Re: checkdb up/down script for Windows

Anthony wrote:
> I would say your script would work for most cases but not in some.
> Say you do not run on your server but rather on a different server
> which monitors,say 50 different databases. For me, I would like to
> cover all cases , eg,
> if the database is up or not
> anything wrong with network
> anything wrong with the tnsnames.ora or the oracle name server or ldap
> server (whatever name resolution)
> anyone changing the password without informing the dba
> database is in mounted state but not in.
> etc
> etc
> My script force a normal login and check the response. That simple. If
> it does not return the expected normal string, I would consider
> something is wrong and deserve investigation. Of course, my script is
> still not totally thorough. I may improve as I get along. But this is
> my best solution in Windows Environment. In UNIX, I have a slightly
> different script.
>
> Rauf Sarwar wrote:
> > Anthony wrote:
> > > The script posted may not work properly. ORA-01017 indicates invalid
> > > username/password only. What if the database string is simply
> > > non-existence or something wrong with sqlnet setup etc, network etc?
> > > The command of echo exit|sqlplus username/password
> > > will always yield a success return no matter what happens (same for
> > > UNIX and Windows)
> > >
> > > The only sure way is to examine the output to see if it is success or
> > > not.
> > >
> > > I run something like this:
> > >
> > > sqlplus -s /nolog @check
> > > type check.lst|findstr /b 'okay' 1>null 2>&1
> > > if errorlevel == 1 (echo error)
> > >
> > > where check.sql is something like this:
> > >
> > > spool check_list
> > > connect username/password
> > > select 'okay--' from dual;
> > > exit
> > >
> > > The Database would be up and available only if we get the string 'okay'
> > > in the response. Of course, everything is parameter driven and email
> > > sent out if it happens etc.
> > >
> > > Rauf Sarwar wrote:
> > > > Jigar2Reshma_at_gmail.com wrote:
> > > > > Thanks guys.
> > > > >
> > > > >
> > > > > Matthias Hoys wrote:
> > > > > > "Chuck" <skilover_nospam_at_bluebottle.com> wrote in message
> > > > > > news:AJbtg.7566$Zf.1085_at_trnddc04...
> > > > > > > Jigar2Reshma_at_gmail.com wrote:
> > > > > > >> Hey everyone : I wanted to see if any one can suggest me or pass any
> > > > > > >> windows related script tp me on ho to check if an Oracle database on
> > > > > > >> windows is up/down. if down then send email. I am not a windows expert
> > > > > > >> but i can write a little batch script.
> > > > > > >>
> > > > > > >> any help is appreciated.
> > > > > > >>
> > > > > > >> thanks
> > > > > > >>
> > > > > > >
> > > > > > > The first thing I do on any Windows system is install a copy of CygWIN
> > > > > > > so I can use a *real* command line.
> > > > > > >
> > > > > > > As a couple of other posters have already stated, depending on the
> > > > > > > version of Oracle you installed either OEM or Grid Control already does
> > > > > > > this for you.
> > > > > >
> > > > > > You can do everything and beyond with VBS/WMI, no need to install a "UNIX"
> > > > > > command interface on Windows.
> > > > > > But like everyone else was saying, the built-in 10g DB Console has
> > > > > > everything you need, and is very easy to configure (check the docs). Of
> > > > > > course if your DB Console is down, your monitoring is down also :-) So you
> > > > > > could add a little vbs script (scheduled with the Windows task scheduler) to
> > > > > > monitor the status of the DB Console service.
> > > > > >
> > > > > > HTH
> > > > > > Matthias Hoys
> > > >
> > > > Please do not top post.
> > > >
> > > > Copy this to a batch script. %1 can be passed as @database or for local
> > > > connections, just ignore it. Call this script from any other batch
> > > > script. Return code 0 means database is online.
> > > >
> > > > -----
> > > > @echo off
> > > > echo exit|sqlplus -s ___/___%1|findstr /i "ORA-01017" >NUL 2>&1
> > > > exit %errorlevel%
> > > > -------
> > > >
> > > > Regards
> > > > /Rauf
> >
> > Please do not top post.
> >
> > Check the %errorlevel% in the following example.
> >
> > C:\>set oracle_sid=loc920
> >
> > C:\>sqlplus "sys as sysdba"
> >
> > SQL*Plus: Release 9.2.0.7.0 - Production on Fri Jul 14 11:52:17 2006
> >
> > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
> >
> > Enter password:
> > Connected to an idle instance.
> >
> > SQL> exit
> > Disconnected
> >
> > C:\>echo exit|sqlplus -s ___/___|findstr /i "ORA-01017" >nul
> >
> > C:\>echo %errorlevel%
> > 1
> >
> > C:\>sqlplus "sys as sysdba"
> >
> > SQL*Plus: Release 9.2.0.7.0 - Production on Fri Jul 14 11:53:09 2006
> >
> > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
> >
> > Enter password:
> > Connected to an idle instance.
> >
> > SQL> startup
> > ORACLE instance started.
> >
> > Total System Global Area 80813664 bytes
> > Fixed Size 454240 bytes
> > Variable Size 67108864 bytes
> > Database Buffers 12582912 bytes
> > Redo Buffers 667648 bytes
> > Database mounted.
> > Database opened.
> > SQL> exit
> > Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 -
> > Production
> > With the Partitioning, OLAP and Oracle Data Mining options
> > JServer Release 9.2.0.7.0 - Production
> >
> > C:\>echo exit|sqlplus -s ___/___|findstr /i "ORA-01017" >NUL 2>&1
> >
> > C:\>echo %errorlevel%
> > 0
> >
> > Regards
> > /Rauf

Once again...please do not top post.

I am not comparing scripts here as there is always more then one way to find a solution and you can always add to any script to suit your liking. I tend to go for simple solution plus read the OP... he/she ONLY wants to check if database is up/down, not whether the network is preventing connections or listener is running or not.

Regards
/Rauf Received on Fri Jul 14 2006 - 12:12:13 CDT