Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Unix script to check database status

RE: Unix script to check database status

From: Smith, Ron L. <rlsmith_at_kmg.com>
Date: Tue, 9 Mar 2004 15:18:18 -0600
Message-ID: <270A0BDDFDE54E41B78F0F06D82A66B850862B@okcexg3.kmg.com> 





Below is a script we are using to monitor over 100 databases on Unix and
NT.


The script is part of a group of scripts we created to do database
monitoring.


The scripts run every 15 minutes throughout the day.
The script will either page or email, depending on the DBA name
provided.


It looks much more complicated than it is.
Basically all it does is try to connect to the database and return the
database name.


It it is successful, it indicates the database is up, accepting
commands, and the listener is up.



Ron Smith


Kerr-McGee Corp.



#! /bin/sh

#          DBA MONITORING SCRIPTS 


# ******************************************************************

#

#     Author:         Ron Smith

#     Date:           12/18/00

#     Funtion:        Checks to make sure in instance is up and

#                     responding.

#

# ******************************************************************

#

#                     CHANGE HISTORY


#

#     DATE        WHO             Reason for Change

#

#     12/19/00    Ron Smith       New Prog

#

#

# ******************************************************************

#

#                     FUNCTION





#

#     This script calls db.sql.

#

#     The function of this script is to try to connect to a SID and

#     return the value of the name field in the V$database view.  If

#     this fails, the listener and the database should be checked.

#

#     (The following paragraph may not be true.  The check for the

#     error file may have been commented out)

#     If an error file already exists, the script exits without any 

#     action.  The DBA should delete the error file when the problem

#     is resolved.  Another script should be scheduled to run daily

#     to delete the error file so the DBA is paged at least once a

#     day if the condition continues.

#

#     If the id of the DBA is a Zid, a page will be sent.  If the 

#     id of the DBA is an email address (determined by looking for

#     an "@" ) , an EMAIL will be sent.

#

# ******************************************************************

#

#                     PREREQUISITES


#

#     The OPS$ORACLE user must exist in the instance.  This can be 

#     created by running the opsuer.sql script in SQLPLUS while

#     logged on as SYSTEM. 

#

#     The cdmonitoring script must exist in the home/oracle

#     directory.

#

# ******************************************************************

#

#                     RUN SYNTAX


#      

#     db.sh (sid) (dba)      

#      

#      

# ******************************************************************




# cd to the monitoring script directory



. $HOME/cdmonitoring.sh



ORACLE_SID=$1



export ORACLE_SID


DBA=$2



export DBA


ATCNT=`echo $DBA | grep @ |  wc -l`


export ATCNT


SERVER=`uname -a | cut -d " " -f2`


export SERVER



ORACLE_HOME=`grep "^$ORACLE_SID:" /etc/oratab | head -1 | cut -d: -f2`
export ORACLE_HOME


PATH=$ORACLE_HOME/bin:/usr/local/bin:$PATH:.
export PATH



# Delete the old list file if it exists




if [ -f db_$ORACLE_SID.lst ]


then rm db_$ORACLE_SID.lst


fi



# Check to see if an error file exists.  If it does get out.




#if [ -f db_$ORACLE_SID.err ]

#then echo "Error file db_$ORACLE_SID.err exists - will exit now"

# exit

#fi




# If sending to EMAIL address, run sql with headings on




if [ "$ATCNT" -gt "0" ]

        then
        sqlplus / @db.sql on $ORACLE_SID
        else
        sqlplus / @db.sql off $ORACLE_SID


fi



# If there is anything in the lst file then send a message

 


WC=`grep -i $ORACLE_SID db_$ORACLE_SID.lst | wc -l`



if [ "$WC" -lt "1" ]


        then echo "-DBA- Could not connect to $ORACLE_SID on server
$SERVER " > db_$ORACLE_SID.err

        if [ "$ATCNT" -gt "0" ] 
                then
                echo "email sent"
                cat db_$ORACLE_SID.lst >> db_$ORACLE_SID.err
                elm -s "-DBA- Could not connect to $ORACLE_SID on server
$SERVER" $DBA < db_$ORACLE_SID.err
                else
                LC=`cat db_$ORACLE_SID.lst | sed -e 's/  */ /g' | wc -c`
                echo $LC
                if [ "$LC" -gt "160" ]
                        then echo "Too many errors to send. Check
db_$ORACLE_SID.lst" >> db_$ORACLE_SID.err
                        else
                        cat db_$ORACLE_SID.lst >> db_$ORACLE_SID.err
                fi
                echo "page sent"
                pager $DBA "`cat db_$ORACLE_SID.err`"
        fi




Fi




oracle_at_typeMENU:> more db.sql



set pause off


SET ECHO off


set verify off


set feedback off


set hea &1


col type format a10 


col name format a20


col exts format 9,999


col maxe format 9,999


define ORACLE_SID = &2


spool db_$ORACLE_SID.lst


select name from v$database


/


spool off;


exit







-----Original Message-----


From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala
Sent: Tuesday, March 09, 2004 2:59 PM


To: oracle-l_at_freelists.org


Subject: Re: Unix script to check database status




Korn shell? Whaddya mean by "Korn shell"? Civilized people use perl for
things like that.



On 03/09/2004 03:29:38 PM, Michael Milligan wrote:

> Hi,
> 
> I have to write an HP-UX Korn shell script to check the status of 20 
> databases - up or down - and send an email if any are down. I'm a 
> relative newbie at Unix. I'm not asking anyone to "do it for me", but 
> does anyone know of any scripts to check database status by greping or

> otherwise looking at the oratab or other file and checking the status?
> 
> Thanks,
> 
> Mike
> 
> 
> This e-mail, including attachments, may include confidential and/or 
> proprietary information, and may be used only by the person or entity 
> to which it is addressed. If the reader of this e-mail is not the 
> intended recipient or his or her authorized agent, the reader is 
> hereby notified that any dissemination, distribution or copying of 
> this e-mail is prohibited. If you have received this e-mail in error, 
> please notify the sender by replying to this message and delete this 
> e-mail immediately.
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------


> 




Please see the official ORACLE-L FAQ: http://www.orafaq.com




To unsubscribe send email to:  oracle-l-request_at_freelists.org put
'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


Received on Tue Mar 09 2004 - 15:16:53 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US