Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Command line script to check Oracle login and logoff

Re: Command line script to check Oracle login and logoff

From: Mark Bole <>
Date: Tue, 30 Nov 2004 16:19:25 GMT
Message-ID: <h41rd.52031$>

Jens Riedel wrote:

> 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

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 Received on Tue Nov 30 2004 - 10:19:25 CST

Original text of this message