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: Checking if remote database is up

RE: Checking if remote database is up

From: Smith, Ron L. <rlsmith_at_kmg.com>
Date: Tue, 26 Apr 2005 07:57:44 -0500
Message-ID: <270A0BDDFDE54E41B78F0F06D82A66B80134675E@okcexg3.kmg.com> 






Below is a script we run every 15 minutes against both local and remote
databases to make sure we can connect.  The script will either page or
email based on the DBA id given when the script is run.  This is just
one of several similar scripts we use to monitor our databases.



Ron



SAMPLE CRON ENTRY: /u001/home/oracle/dbastuff/scripts/monitoring/db.sh
mydb mydba > /dev/null 2>&1 Or


/u001/home/oracle/dbastuff/scripts/monitoring/db.sh mydb mydba_at_mycomp >
/dev/null 2>&1



>>> DB.SH script:



#! /bin/sh

#          DBA MONITORING SCRIPTS=20


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

#

#     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=20

#     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=20

#     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=20

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

#     logged on as SYSTEM.=20

#

#     The cdmonitoring script must exist in the home/oracle

#     directory.

#

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

#

#                     RUN SYNTAX


#     =20

#     db.sh (sid) (dba)     =20

#     =20

#     =20

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




# cd to the monitoring script directory



. $HOME/cdmonitoring.sh



ORACLE_SID=3D$1



export ORACLE_SID


DBA=3D$2



export DBA


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


export ATCNT


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


export SERVER



ORACLE_HOME=3D`grep "^$ORACLE_SID:" /etc/oratab | head -1 | cut -d: -f2`
export ORACLE_HOME PATH=3D$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

=20


WC=3D`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" ]=20
                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=3D`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



>>> DB.SQL:



set pause off


SET ECHO off


set verify off


set feedback off


set hea &1


define ORACLE_SID =3D &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 Mercadante, Thomas F
(LABOR)



Sent: Tuesday, April 26, 2005 6:41 AM


To: 'stephenbooth.uk_at_gmail.com'; Oracle-L (E-mail)
Subject: RE: Checking if remote database is up




Stephen,



We have a similar situation.  What we do is this:



Create a "ocella_available" table in your local database that indicates
whether the remote database is available.  Run a cron job every minute
that does the simple query you are talking about.  Depending on the
response, update the ocella_available table.  In your application, check
the ocella_available table to see if it is ok to try and retrieve a
record from the remote database.



This works for us.  The cron job will also send us email if the remote
database becomes unavailable so that we at least know about it.



Good Luck!



Tom



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


From: stephen booth [mailto:stephenbooth.uk_at_gmail.com]=20
Sent: Tuesday, April 26, 2005 6:53 AM


To: Oracle-L (E-mail)


Subject: Checking if remote database is up



One of our new systems (Documentum from EMC) uses a database link to a
remote database (Ocella from Ocella)  for some processes.  Due to
organisation politics the people managing the Ocella database don't tell
the people managing Documentum when they're taking their system down
(the joys of working in the public sector).  Documentum can do most of
it's functions when Ocella is down, it just can't do certain
transactions, unfortunately it's currently not very good at dealing with
situations where the Ocella database is down.



We're looking at some way of checking if the Ocella database is up
before trying a transaction that needs it then reporting back to the
user if it's down.  What we're currently thinking of is putting an empty
table in the Ocella database then querying that from a PL/SQL function
over the link and trapping the error.  If we get data or 'No Rows
Returned' then we know that the database is up and the link is working.
If we get an ORA-03113 then we know that the database is down or the
link isn't working for some other reason (e.g. Network broken again).
the function returns either TRUE or FALSE depending on whether the
remote dtabase is up or not.



Does anyone have experience of a similar situation?  Is there a more
elegant/reliable method?  Anything I've failed to consider that will
make this all blow up?



Thanks



Stephen



--=3D20


It's better to ask a silly question than to make a silly assumption.

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l


Important Notice!!
If you are not the intended recipient of this e-mail message, any use,
distribution or copying of the message is prohibited. Please let me know
immediately by return e-mail if you have received this message by
mistake, then delete the e-mail message. Thank you.
--
http://www.freelists.org/webpage/oracle-l


Received on Tue Apr 26 2005 - 09:02:14 CDT












Original text of this message













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