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

From: Adric Norris <landstander668_at_gmail.com>
Date: Fri, 17 May 2019 07:51:57 -0500
Message-ID: <CAJueESr24b0sQzVNZ_DtiUjL51KcfRj7igQ4T8yiok_gWefKqw_at_mail.gmail.com>

I'm *assuming* that you want a row included in the resultset for each offline instance. I'm not aware of any data dictionary views which provide this, but assuming the database is using a spfile you should be able to do something like this:

SQL> with all_instances as (

  2     select p.sid instance_name, p.value
  3        from v$spparameter p
  4        where p.isspecified  = 'TRUE'
  5          and p.name         = 'thread'
  6          and p.sid         != '*'

  7 )
  8 select ai.instance_name, nvl( i.status, 'OFFLINE' ) status

  9     from all_instances ai
 10        left join gv$instance i on ( i.instance_name = ai.instance_name )
 11     order by 1;

INSTANCE_NAME STATUS
--------------- ------------

TESTDR1         OPEN
TESTDR2         OPEN
TESTDR3         OFFLINE

On Thu, May 16, 2019 at 3:52 PM Luis Santos <lsantos_at_pobox.com> wrote:

> 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?
>
> *--*
> *Att*
>
>
> *Luis Santos*
>
>

-- 
"In the beginning the Universe was created. This has made a lot of people
very angry and been widely regarded as a bad move." -Douglas Adams

--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 17 2019 - 14:51:57 CEST