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.
RMAN
We can connect to the instance by RMAN to learn current state.
Idle
[oracle@test ~]$ rman target /
...
connected to target database (not started)
not started means the instance is idle.
Nomount
[oracle@test ~]$ rman target /
...
connected to target database: ORCLCDB (not mounted)
not mounted means NOMOUNT, very easy to understand.
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.
Open
[oracle@ora19c1 ~]$ rman target /
...
connected to target database: ORA19C1 (DBID=3411734329)
We see only DBID of the fully opened database.
SQL*Plus
We use SQL*Plus to query some dynamic views.
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 why.
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.
Mount
SQL> select status from v$instance;
STATUS
------------
MOUNTED
The database is MOUNTED.
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.