V$INSTANCE: Display Oracle Database & Instance State

V$INSTANCE displays the state of the current instance and database.

SQL> select * from v$instance;

INSTANCE_NUMBER  INSTANCE_NAME  HOST_NAME  VERSION
---------------  -------------  ---------  -------
1                DEV            itsiti     10.2.0.6.0

STARTUP_TIME  STATUS  PARALLEL  THREAD#  ARCHIVER
------------  ------  --------  -------  --------
15-APR-11     OPEN    NO        1        STARTED

LOG_SWITCH_WAIT  LOGINS  SHUTDOWN_PENDING   DATABASE_STATUS
---------------  ------  -----------------  ---------------
ALLOWED NO            ACTIVE

INSTANCE_ROLE    ACTIVE_STATE
-------------    ------------
PRIMARY_INSTANCE NORMAL

INSTANCE_NUMBER

Instance number used for instance registration (corresponds to the INSTANCE_NUMBER initialization parameter)

SQL> select INSTANCE_NUMBER from v$instance;

INSTANCE_NUMBER
---------------
1

INSTANCE_NAME

Name of the instance

SQL> select INSTANCE_NAME from v$instance;

INSTANCE_NAME
----------------
DEV

HOST_NAME

Name of the host machine

SQL> select HOST_NAME from v$instance;

HOST_NAME
-----------------------------------------------------
itsiti

SQL>

VERSION

Database version

SQL> select VERSION from v$instance;

VERSION
-----------------
10.2.0.6.0

STARTUP_TIME

Time when the instance was started

SQL> select STARTUP_TIME from v$instance;

STARTUP_TIME
---------------
15-APR-11

STATUS

Status of the instance:
• STARTED – After STARTUP NOMOUNT
• MOUNTED – After STARTUP MOUNT or ALTER DATABASE CLOSE
• OPEN – After STARTUP or ALTER DATABASE OPEN
• OPEN MIGRATE – After ALTER DATABASE OPEN { UPGRADE | DOWNGRADE }

SQL> select STATUS from v$instance;

STATUS
------------
OPEN

PARALLEL

Indicates whether the instance is mounted in cluster database mode (YES) or not (NO)

SQL> select PARALLEL from v$instance;

PAR
---
NO

THREAD#

Redo thread opened by the instance

SQL> select THREAD# from v$instance;

THREAD#
----------
1

ARCHIVER

Automatic archiving status:
• STOPPED
• STARTED
• FAILED – Archiver failed to archive a log last time but will try again within 5 minutes

SQL> select ARCHIVER from v$instance;

ARCHIVE
-------
STARTED

LOG_SWITCH_WAIT

Event that log switching is waiting for:
• ARCHIVE LOG
• CLEAR LOG
• CHECKPOINT
• NULL – ALTER SYSTEM SWITCH LOGFILE is hung but there is room in the current online redo log

SQL> select LOG_SWITCH_WAIT from v$instance;

LOG_SWITCH_
-----------

LOGINS

Indicates whether the instance is in unrestricted mode, allowing logins by all users (ALLOWED, or in restricted mode, allowing logins by database administrators only (RESTRICTED)

SQL> select LOGINS from v$instance;

LOGINS
----------
ALLOWED

SHUTDOWN_PENDING

Indicates whether a shutdown is pending (YES) or not (NO)

SQL> select SHUTDOWN_PENDING from v$instance;

SHU
---
NO

DATABASE_STATUS

Status of the database:
• ACTIVE
• SUSPENDED
• INSTANCE RECOVERY

SQL> select DATABASE_STATUS from v$instance;

DATABASE_STATUS
-----------------
ACTIVE

INSTANCE_ROLE

Indicates whether the instance is an active instance (PRIMARY_INSTANCE) or an inactive secondary instance (SECONDARY_INSTANCE), or UNKNOWN if the instance has been started but not mounted

SQL> select INSTANCE_ROLE from v$instance;

INSTANCE_ROLE
------------------
PRIMARY_INSTANCE

ACTIVE_STATE

Quiesce state of the instance:
• NORMAL – Database is in a normal state.
• QUIESCING – ALTER SYSTEM QUIESCE RESTRICTED has been issued: no new user transactions, queries, or PL/SQL statements are processed in this instance. User transactions, queries, or PL/SQL statements issued before the ALTER SYSTEM QUIESCE RESTRICTED statement are unaffected. DBA transactions, queries, or PL/SQL statements are also unaffected.
• QUIESCED – ALTER SYSTEM QUIESCE RESTRICTED has been issued: no user transactions, queries, or PL/SQL statements are processed. DBA transactions, queries, or PL/SQL statements are unaffected. User transactions, queries, or PL/ SQL statements issued after the ALTER SYSTEM QUIESCE RESTRICTED statement are not processed.

A single ALTER SYSTEM QUIESCE RESTRICTED statement quiesces all instances in an Oracle RAC environment. After this statement has been issued, some instances may enter into a quiesced state before other instances; the system is quiesced when all instances enter the quiesced state.

SQL> select ACTIVE_STATE from v$instance;

ACTIVE_ST
---------
NORMAL

BLOCKED
Indicates whether all services are blocked (YES) or not (NO)