Availability of the DB and listener

Under the “/export/tmscs/db_cron” folder, “db_check.sh” script seen below is created.

db_check.sh

#!/bin/sh
#
############################
# FUNCTION: IsListenerRunning
############################
#
IsListenerRunning()
{
ps -ef | egrep '/bin/tnslsnr' | egrep -v 'grep' > /dev/null 2>&1
}

############################

# FUNCTION: IsListenerUp

############################

#

IsListenerUp()

{

lsnrctl status > /dev/null 2>&1

}

############################

# MAIN PROGRAM

############################

#

CHECK_LISTENER=1

CHECK_LISTENER_IS_UP=1

SPOOLFILE=/export/tmscs/db_cron/db.log

#

# check if database is running

#

if [ `echo "select dummy||'OK' from dual;" | sqlplus -s system/manager | grep XOK` ]

then

IS_OK="XOK"

else

IS_OK="FALSE"

fi

#

# check if listener is running

#

IsListenerRunning || CHECK_LISTENER=0

#

# check if listener is up#

IsListenerUp || CHECK_LISTENER_IS_UP=0

date +"Date is %D %nTime is %T" > $SPOOLFILE



if [ $IS_OK = "XOK" ]

then

echo "SELECT 'Database has been up and running for '|| round(24*(SYSDATE-logon_time),1) || ' hour(s)' FROM sys.V_\$SESSION WHERE sid=1;" | sqlplus

-s system/manager | grep running >> $SPOOLFILE

else

echo "Database is down" >> $SPOOLFILE

fi

if [ $CHECK_LISTENER = 0 ]

then

echo "Listener is not Running" >> $SPOOLFILE

fi

if [ $CHECK_LISTENER_IS_UP = 0 ]

then

echo "Listener is down" >> $SPOOLFILE

lsnrctl start && echo "Listener is started succesfully" >> $SPOOLFILE

else

echo "Listener is up" >> $SPOOLFILE

fi
If listener is not running, script will start it. But if database is down, script will do nothing.

The crontab jobs executing this script is created under the user ROOT, and since ROOT user has already several crontab jobs, the new job line is added to the crontab file and then this file is submitted to the system (/var/spool/cron/crontabs/root);

15,30,45,01 * * * * su - oracle -c "/export/tmscs/db_cron/db_check.sh" > "/export/tmscs/db_cron/err.log"

And then to submit the file;

crontab crontab_filename

Essentially, root is the super user and can thus impersonate oracle without a password. su oracle will enable root to become oracle. The -c means 'execute the following command as oracle'. The extra '-' between su and oracle is crucial - because of this, the script is being executed as if the user oracle had logged in himself. In other words all environment variables like $ORACLE_HOME, $ORACLE_SID and $PATH are set correctly.

This job runs every fifteen minutes.

After running the script, the crontab job will produce 2 files (db.log and err.log) under the folder “/export/tmscs/db_cron”

db.log

It gives information about database and listener.

If database and listener are running;

Date is 01/07/03

Time is 10:12:57

Database has been up and running for 119.4 hour(s)

Listener is up

If database is down and listener is running;

Date is 01/07/03

Time is 13:53:43

Database is down

Listener is up

If database is up and listener is down;

Date is 01/07/03

Time is 13:54:37

Database has been up and running for 0 hour(s)

Listener is up

If both database and listener is down;

Date is 01/07/03

Time is 13:45:01

Database is down

Listener is not Running

Listener is down

err.log

$ more err.log

PATH is /usr/bin:

PATH is

/export/home/app/oracle/product/

9.2.0.1.0/bin:.:/usr/bin:.:/usr/local/bin

Sun Microsystems Inc. SunOS 5.7 Generic October 1998

You have mail.

Running Oracle .login ...

It shows the log of last process and if an error occurs, what the error is can be seen in this file;

Note that when the folder (/export/tmscs/db_cron) is changed both crontab and db_check.sh script files must be corrected.

Configuration :

• Variable SPOOLFILE in db_check.sh file must be configured (now SPOOLFILE=/export/tmscs/db_cron/db.log)
• Given Password for system user in db_check.sh file must be corrected (now system/manager)
• Given path for db_clean.sql and err.log files must be corrected (now /export/tmscs/db_cron/)
• All created scripts and folders must be owned by oracle:dba with the rights of “711”