How to Tell What State of Database

  • by

We have already known that there're 4 states in life cycle of an instance. They are idle, nomount, mount and open. In this post, we will talk about how to tell what current state of the database by several ways.

  1. RMAN
  2. SQL*Plus

A. RMAN

We can connect to the instance by RMAN to learn current state.

1. Idle

[oracle@test ~]$ rman target /
...
connected to target database (not started)

not started means the instance is idle.

2. Nomount

[oracle@test ~]$ rman target /
...
connected to target database: ORCLCDB (not mounted)

not mounted means NOMOUNT, very easy to understand.

3. Mount

[oracle@test ~]$ rman target /
...
connected to target database: ORCLCDB (DBID=2824835543, not open)

Although we can see Database Identifier (DBID), but the state is not open which means MOUNT state.

4. Open

[oracle@ora19c1 ~]$ rman target /
...
connected to target database: ORA19C1 (DBID=3411734329)

We see only DBID of the fully opened database.

B. SQL*Plus

We use SQL*Plus to query some dynamic views.

1. Idle

[oracle@test ~]$ sqlplus / as sysdba
...
Connected to an idle instance.

Sometime, an idle state for an instance is not normal, you have to find out.

2. Nomount

If you didn't see idle when you login, you can query V$INSTANCE to learn the state.

SQL> select status from v$instance;

STATUS
------------
STARTED

The instance is STARTED which means NOMOUNT.

3. Mount

SQL> select status from v$instance;

STATUS
------------
MOUNTED

The database is MOUNTED.

4. Open

SQL> select status from v$instance;

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

The database is OPEN, but we can't tell what open mode we have. So let's continue.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Oh, it's READ WRITE.

Leave a Reply

Your email address will not be published. Required fields are marked *