Skip to content
Home » Oracle Database » How to Check DB Status by Script

How to Check DB Status by Script

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.

Leave a Reply

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