For most organizations using Oracle Database the de facto tool for monitoring instance health is Oracle Enterprise Manager Cloud Control. While Cloud Control is great at what it does, there are times when the Oracle Management Server (OMS), the Oracle Management Agent, or other components of Cloud Control are down. This could be during maintenance windows or unplanned events that lead to your Oracle instances going unmonitored.

It never hurts to have more monitoring scripts than necessary. In fact, there are many times when a custom monitoring script will alert you to issues far before any notification is sent via Cloud Control. Any DBA worth their salt should be familiar will shell scripting for the native shell (bash, ksh, csh, etc.) of the host that the database instances are running on. There are always business-specific cases that must be checked for on top of the default monitoring templates provided by OEM.

The most basic check for instance availability is to check for the Oracle operating system process PMON. If PMON is not active, it’s certain that your instance is not running. As proven by several benchmarks over the years, Oracle Database runs best on *nix environments. If you’re lucky enough to administer Oracle in one of these environments, the below shell script can provide an additional check on top of what OEM already provides.

The script makes use of the /etc/oratab file that gets populated (by execution of the root scripts during database creation via DBCA) with each database’s SID, Oracle home, and whether or not Oracle Restart should restart the instance automatically (Y or N). The below script parses each entry in /etc/oratab to obtain the database’s SID and to determine whether or not the instance should be automatically restarted by Oracle utilities. If the instance’s PMON process is not running, and if the database is marked as to be restarted automatically, the mail command is invoked to send an alert (e-mail and SMS message) to the DBA alerting them to the problem. The script ignores database instances not marked for automatic restart.

As always, please ensure that you understand exactly what the script does before scheduling it to execute in a production environment. It can be executed from the crontab of the Oracle software owner, or any other operating system user that can read /etc/oratab. The script has been tested on Oracle Linux 8.8 using both Bash and Korn shells.

#!/bin/ksh
## File Name: alert_instance_down.sh
## Purpose: Used to check if database instances are down.

ORATAB_FILE=/etc/oratab
if [ ! -f "$ORATAB_FILE" ]; then
    echo "Error, file $ORATAB_FILE not found."
    exit 99
fi

# Loop through each line in the oratab file
while read -r line; do
    # Skip comments and empty lines
    if [[ "$line" =~ ^#|^$ ]]; then
        continue
    fi

    # Obtain the database SID and whether it should be automatically restarted
    SID=$(echo "$line" | cut -d: -f1)
    DO_RESTART=$(echo "$line" | cut -d: -f3)

    # Check if the instance is marked for automatic startup
    if [ "$DO_RESTART" = "Y" ]; then
        # Check if the Oracle instance is up
        if ps -ef | grep -q "[o]ra_pmon_$SID"; then
            echo "Oracle instance $SID is running."
        else
	    echo "Oracle instance $SID is down"
	    echo | mail -s "Oracle instance $SID is down" mail@example.com
            echo | mail -s "$SID is down" sms@example.com
        fi
    else
        echo "Skipping $SID as it is not marked for restart in $ORATAB_FILE."
    fi
done < "$ORATAB_FILE"

Please feel free to modify this script to suit any additional requirements that you may have.