Re: Oracle Instance health

Eric wrote:
> Hello there
>
> I'm trying to figure out how to test if an Oracle instance is healthy.
> I mean, if it will respond to some basic query.
>
> If I execute one query in one of the schemas in the instance, can I be
> sure that the others schemas are in good shape? Or I need to test
> explicitly every schema (One connection to each one and one query in
> every conection?)
>
> BTW, what kind of basic query can I execute to test the instance (or
> schema)? Something that is quick enough to not stress the data base,
> but good enough to be sure that the instance is running.
>
> Well, if someone has another idea of how to test (remotely) an Oracle
> instance I'll appreciate the info.
>
> Thanks in advance.
> Eric

Your understanding of health is significantly different from mine. There is no single query or simple test that will tell whether something is or is not going to successfully handle the next query.

I would suggest you look at the OEM Grid Control rather than reinvent the wheel.

In 11g Oracle has added the new DBMS_HM (Health Management) package which moves in the direction you seem to be heading but the checks it runs include:

DB Structure Integrity Check
Data Block Integrity Check
Redo Integrity Check
Transaction Integrity Check
Undo Segment Integrity Check
Dictionary Integrity Check

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org