RE: Looking for simple monitor script dbup.bat

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 06 Feb 2003 11:37:21 -0800
Message-ID: <F001.005464F4.20030206113721@fatcity.com>

If I understand you correctly, the problem is that you don't get an ERRORLEVEL set when you are unable to connect to a database. Have you tried sqlplus/nolog? (Ignore the french text, I have Oracle installed in french on my PC)

D:\>sqlplus /nolog
SQL*Plus: Release 8.1.7.0.0 - Production on Je Fev 6 11:22:28 2003 (c) Copyright 2000 Oracle Corporation. All rights reserved.

SP2-0640: Non connect?
SP2-0640: Non connect?
SP2-0640: Non connect?

SQL> whenever sqlerror exit 1
SQL> connect x/y_at_z
ERROR:
ORA-12154: TNS : l'adresse symbolique n'a pas pu ?tre r?solue D:\>echo %ERRORLEVEL%
1

> -----Original Message-----
> From: Bob Metelsky [mailto:bmetelsky_at_CPS92.COM]
>
> Good morning everyone Win2k pro, oracle 8.17
>
> Ive been investigating a simple monitor script which will email me if
> the database is not available. I need this to be as simple as possible
> (ideally a batch file)
>
> Ive come across this on orafaq
> http://www.orafaq.net/scripts/win/isdbup.txt
>
> But dosnt seem to work. If you pass an incorrect sid, or file
> parameters
> the script just hangs
>
> I have a modified version
> ##############################################
> Rem dbup.bat
> @echo off
> set ORACLE_SID=locdb
> sqlplus internal/pw@%ORACLE_SID% @C:\conn.sql
>
> if {%ERRORLEVEL%} == {0} (
> echo Database %ORACLE_SID% is available
> ) else (
> echo Database %ORACLE_SID% is NOT available
> )
> ##############################################
>
> [conn.sql]
> set echo off feed off pages 0
> -- whenever sqlerror exit sql.sqlcode
> WHENEVER SQLERROR EXIT 1
> select * from global_name;
> exit ;
> ##############################################
>
> Although this works (gets into the error level) AFTER you hit enter to
> walk throough the errors
> The script gets stuck on Errors Such as
> If the db is down
> ERROR:
> ORA-01034: ORACLE not available
> Enter user-name:
> ERROR:
> ORA-01017: invalid username/password; logon denied
>
> Or if I pass and invalid sid, (I can live with only passing
> valid sids)
> but how can I get sql+ to exit out of the error if in fact
> the db is not
> up
> I think Ive tried all variations of SQLERROR
>
> http://lina.cli.di.unipi.it:8000/WG73-doc/sqlplus/sp33/ch646.html
>
> Any ideas for the gurus???

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Feb 06 2003 - 13:37:21 CST