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
Received on Fri Jul 14 2006 - 10:09:19 CDT
