To check if a databae is alive or not, we already have a way to do it. However, such script can't tell NOMOUNT, MOUNT or OPEN. That's why we need a script that can go deeper.
Moreover, for some monitoring programs, they may need to get the most recent status of your database. In this post, we introduce a script to retrieve the status from your database.
Let's see the script.
[oracle@test ~]$ vi check_database_status.sh
#!/bin/bash
. ~/.bash_profile
sqlplus -s / as sysdba << EOF > check_database_status.log
set echo off heading off feedback off pagesize 0;
select status from v\$instance;
exit;
EOF
In the above, we choose V$INSTANCE over V$DATABASE is because the later cannot be queried at NOMOUNT state.
To make it executable, we add execution permissin on user.
[oracle@test ~]$ chmod u+x check_database_status.sh
Let's test the script at various status.
NOMOUNT
[oracle@test ~]$ ./check_database_status.sh
[oracle@test ~]$ cat check_database_status.log
STARTED
MOUNT
[oracle@test ~]$ ./check_database_status.sh
[oracle@test ~]$ cat check_database_status.log
MOUNTED
OPEN
[oracle@test ~]$ ./check_database_status.sh
[oracle@test ~]$ cat check_database_status.log
OPEN
To tell what STARTED, MOUNTED, OPEN really means, you may check the link.
IDLE
[oracle@test ~]$ ./check_database_status.sh
[oracle@test ~]$ cat check_database_status.log
select status from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
At idle state, you may grep "ORA-01034" to announce the insance is down.