Skip to content
Oracle DBA Scripts & Database Utilities

RAC Instance Status (gvinst.sql)

What This Script Does

Section titled “What This Script Does”

Displays detailed status information for all instances in a RAC cluster, including uptime, version, and operational status.

The Script

Section titled “The Script”
rem  vinst.sql
rem
ttitle 'Instance Status'
rem
set lines 160
rem
clear col
col name format a9 heading 'NAME'
col host_name format a22 head 'HOST'
col instance_number format 9999 heading 'INST#'
col thread# format 9999
col days format 990.0
col shutdown_pending format a8 heading 'SHUTDOWN|PENDING'
rem
select
       instance_number,
       instance_name name,
       host_name,
       version,
       to_char(startup_time, 'mm/dd hh24:mi') startup_time,
       round(sysdate - startup_time,1) days,
       status,
       parallel,
       thread#,
       archiver,
       log_switch_wait,
       logins,
       shutdown_pending
  from gv$instance
 order by instance_number;

Usage

Section titled “Usage”
-- Basic usage
@gvinst.sql

Parameters

Section titled “Parameters”

The script prompts for:

  • None - this script requires no parameters

Required Privileges

Section titled “Required Privileges”
SELECT ANY DICTIONARY
-- OR --
SELECT ON GV$INSTANCE

Sample Output

Section titled “Sample Output”
                                     Instance Status


INST# NAME      HOST                 VERSION          STARTUP_TIME    DAYS STATUS   PARALLEL THREAD# ARCHIVER LOG_SWITCH_WAIT LOGINS   SHUTDOWN
                                                                                                                                        PENDING
----- --------- -------------------- ---------------- ------------- ------ -------- -------- ------- -------- --------------- -------- --------
    1 ORCL1     rac-node-01          19.0.0.0.0       05/30 08:15    1.7   OPEN     YES          1    STARTED  ACTIVE          ALLOWED  NO
    2 ORCL2     rac-node-02          19.0.0.0.0       05/30 08:16    1.7   OPEN     YES          2    STARTED  ACTIVE          ALLOWED  NO

Key Output Columns

Section titled “Key Output Columns”
  • INST#: Instance number in the cluster
  • NAME: Instance name
  • HOST: Host/server name where instance is running
  • VERSION: Oracle database version
  • STARTUP_TIME: When the instance was started (MM/DD HH24:MI)
  • DAYS: Number of days since startup
  • STATUS: Current instance status (OPEN, MOUNTED, etc.)
  • PARALLEL: RAC parallel processing enabled (YES/NO)
  • THREAD#: Redo thread number for this instance
  • ARCHIVER: Archive log process status
  • LOG_SWITCH_WAIT: Redo log switch wait status
  • LOGINS: Login restriction status
  • SHUTDOWN PENDING: Whether shutdown is in progress

Common Use Cases

Section titled “Common Use Cases”

RAC Health Check

-- Quick overview of all cluster instances and their status
@gvinst.sql

Load Balancing

-- Identify which instances are available for connections
@gvinst.sql
Section titled “Related Scripts”