Oracle check database status

In this Oracle tutorial, we will learn how to check database status in oracle databases 19c & 21c. Also, we will cover these topics.

• Oracle check database status
• Oracle check database status windows
• Check Oracle database status shell script
• How to check oracle database status in Unix
• Oracle check pluggable database status
• Oracle check standby database status

Oracle check database status

Before you start working with the database in oracle it is important that the database is in an active state. Oracle object V$INSTANCE keeps the record of the status of the database.

Use the below command to check the status and other names of the oracle databases 19c and 21c.

SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS 
FROM V$INSTANCE;

In the below output, you can see that database status is active which means the database is ready to use.

Also, check: Oracle get database name

Oracle check database status windows

In windows, you can check the status of the oracle database either by using sqlplus or by SQL developer tool. The database status information is stored inside the instance object of oracle.

Using v$instance object you can fetch the relevant values from the oracle database. The v$instance object has other information as well like, Instance name, status, database status.

Use the below code, to fetch database status information on the windows operating system:

SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS 
FROM V$INSTANCE;

The above code is executed on the SQL developer tool on windows and the below output shoes database_status as active.

Read: Oracle Add a Column to Table

Check Oracle database status shell script

Shell scripts are the Linux-based set of instructions that performs a sequence of actions to fulfill the requirement. In this case, we will write a shell script that will check the status of oracle databases on the Linux operating system.

Oracle stores database information in the Instance object which can be viewed using select status from v$instance a statement.

below is the shell script to check oracle database status in Unix or Linux operating systems. Save the file with the .sh extension and then execute it through a terminal.

ORATAB=/var/opt/oracle/oratab

echo "`date`   "
echo  "Oracle Database(s) Status `hostname` :\n"
db=`egrep -i ":Y|:N" $ORATAB | cut -d":" -f1 | grep -v "\#" | grep -v "\*"`
processlist="`ps -ef | grep pmon`"
for i in $db ; do
echo  "$processlist" | grep  "ora_pmon_$i"  > /dev/null 2>$1
if (( $? )); then
echo "Oracle Instance - $i:       Down"
else
echo "Oracle Instance - $i:       Up"
fi
done

Read: Oracle to_char Date Format

Oracle check pluggable database status

Pluggable database(s) is the portable database of schemas, schema objects, and non-schema objects. Pluggable database objects appear as non-cdb (container database) to the clients.

The oracle stores all the information related to PDBs into the DBA_PDBS object. You can fetch the PDB name, creation time, and status information from the dba_pdbs.

The below code shows how to check pluggable database status:

SELECT PDB_NAME,CREATION_TIME,STATUS 
FROM DBA_PDBS;

In this output, the status of pdb shows Normal which means pdbs are ready to use.

You may also like to read the following Oracle tutorials.

• Oracle Create User – Detailed Guide
• Oracle Create Sequence Tutorial
• Oracle to_date Format [Complete Guide]
• Convert rows into columns in Oracle
• How to create table in Oracle
• Oracle how to copy a table
• Alter Table Add Column Oracle
• Number Datatype in Oracle

In this tutorial, we have learned how to check database status in oracle databases 19c & 21c. Also, we have covered these topics.

• Oracle check database status
• Oracle check database status windows
• Check Oracle database status shell script
• How to check oracle database status in unix
• Oracle check pluggable database status
• Oracle check standby database status