Hello,

I want to test in an application if an Oracle DB is available and a
login is successfull.
Now I'm looking for a batch script which I can run under WinNT. It
should proceed a logon at the database (which is installed on the same
system) and logoff again and then give back a return value which
indicates if the process was successfull.

I'm not very familiar with Oracle and its build-in tools so please
excuse if this question seems stupid.

Can anybody give me a hint how to realize this?

Thanks,
Jens

Jens Riedel <JensRie (AT) gmx (DOT) de> wrote

Oracle Enterprise Manager (comes free of charge with Oracle) has tools
to check for database availability. The overhead for your proposed
solution is usually very high.

Sybrand Bakker
Senior Oracle DBA

sybrandb (AT) yahoo (DOT) com wrote:
alternatively, look into LOGON/LOGOFF triggers

http://download-west.oracle.com/docs...ements_76a.htm
or check asktom.oracle.com

Jens Riedel wrote:

Rather than testing from a specific application, an overall monitoring
mechanism for your database(s) is a more robust approach. After all,
your application no doubt already handles errors from the database, but
it's not likely your application users are in a position to fix
anything. Plus, your database could be unavailable for a long time
before someone finds out by running the application.

OEM is the Oracle tool for monitoring your databases, but it involves
quite a bit more than a batch script. A system such as Big Brother (or
any of many other monitoring packages on the market) also has some
Oracle-specific testing ability.

One simple approach that works well is a Perl script that not only logs
in (using DBD::Oracle module) but attempts to commit an update to a test
table. After all, your database could allow successful logins but still
not be able to complete typical transactions.

Most importantly, the actual login test needs to be forked off as a
child process, and then checked from the parent script after a
reasonable time, say a few seconds. This is because your login session
or transaction commit could simply "hang" and never come back on its own.

Lastly, you might as well perform the check from a remote host to test
your network as well - thus making a pretty complete end-to-end test,
and also providing functionality when the system running the database is
itself completely hosed.

Software such as Veritas Cluster Server (or HP Service Guard) include
agents (based on Perl scripts, the last time I checked) which do pretty
much the same thing in order to determine whether or not it's time to
try switching the database resource to another node in the VCS cluster.

-Mark Bole

Jens Riedel <JensRie (AT) gmx (DOT) de> wrote

If you understand DOS then you can do this,

C:\> echo exit|sqlplus username/password@database|find /i "connected to:" >NUL
If return errorlevel is 0 the logon OK else not.

You can easily incorporate this into a batch script.

Regards
/Rauf