Re: checkdb up/down script for Windows

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 14 Jul 2006 03:58:20 -0700
Message-ID: <1152874700.444492.304710@m79g2000cwm.googlegroups.com>

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

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

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 - 05:58:20 CDT