RE: Checking if a RAC database has one or more node down

From: Mark W. Farnham <>
Date: Fri, 17 May 2019 09:16:46 -0400
Message-ID: <008401d50cb2$c7c583e0$57508ba0$>

Excellent point LS.

We can verify what is UP (rather easily).

We cannot verify the desired state without a table being instantiated that declares the desire.  

It seems to me that would be a useful system schedule table, perhaps with a calendar and notice of intended irregular maintenance outage.  

This has the potential to lower the blood pressure of operational DBAs who must baby sit “automated” database infrastructure whilst being locked out of all the goodies to observe things directly.  


From: [] On Behalf Of Ls Cheng Sent: Friday, May 17, 2019 8:53 AM
Subject: Re: Checking if a RAC database has one or more node down  


How do we know the desired count of instance?Just because someone says so or it can be obtained from somewhere? In your example of cluster_database_instances is 8 but gv$instance returns 4 how do you know 4 is the desired count?  



Virus-free. <>  

On Fri, May 17, 2019 at 2:46 PM Luis Santos <> wrote:

Thanks for all replies!  

Let's dig into my need. When I said just SQL I mean that my need is to know if a RAC node is down while connected, and just connected, to the database. No matter the node. Of course I'm aware of srvctl ou crsctl commands, but they are out of my need scope.  

And querying GV$INSTANCE alone will no work. If a node is down it will be missing from GV$INSTANCE, but how much nodes is my goal?  

Digging again: I'm talking about a simple monitoring script. With no human (or AI robot) DBA querying.  

Up to now I have been using, with relative sucess, the comparision of count(*) from GV$INSTANCE to the value of cluster_database_instances database parameter.  

But now we have a Exadata machine with 8 nodes, but with only 4 nodes actives for a specific RAC database. The count(*) from its GV$INSTANCE é 4 and the value from cluster_database_instances parameter is 8.  

So, my qustion again: is it possible to design a simple query, while connected to RAC databases, to show if one (or more) node(s) from the, let's say, desired count of instances, is missing?    



Luis Santos


Em qui, 16 de mai de 2019 às 23:22, Suresh Rajagopal <> escreveu:

crsctl stat res -w "TYPE = ora.database.type"    

On Thursday, May 16, 2019, 2:45:29 PM PDT, Mark W. Farnham <> wrote:    

select inst_id,status from gv$instance;  

From: [] On Behalf Of Luis Santos Sent: Thursday, May 16, 2019 4:51 PM
Subject: Checking if a RAC database has one or more node down  

Is there a way, using just SQL when connected to a RAC Oracle database (whatever the version), to check if all nodes are up and runing?  



Luis Santos



Virus-free. <>  

-- Received on Fri May 17 2019 - 15:16:46 CEST

Original text of this message