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”
Posts
