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: SLA Trigger/Procedure

RE: SLA Trigger/Procedure

From: Mark Richard <mrichard_at_transurban.com.au>
Date: Tue, 03 Dec 2002 13:44:54 -0800
Message-ID: <F001.00511631.20021203134454@fatcity.com>


Kirti,

Nice script for checking availability. May I remind everyone that you get an additional "free" check by running this on a different physical host - you are then checking physical host availability and at least some degree of network connectivity as well.

I still think my original suggestion has some merit - Ethan was talking about some functionality to indicate service levels and track uptime/downtime, whereas we seem to have steered the discussion into a database availability monitoring tool. Still, it's been a good discussion with a lot of valid comments by everyone involved.

                                                                                                                        
                    "Deshpande, Kirti"                                                                                  
                    <kirti.deshpande_at_ve       To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    rizon.com>                cc:                                                                       
                    Sent by:                  Subject:     RE: SLA Trigger/Procedure                                    
                    root_at_fatcity.com                                                                                    
                                                                                                                        
                                                                                                                        
                    04/12/2002 03:59                                                                                    
                    Please respond to                                                                                   
                    ORACLE-L                                                                                            
                                                                                                                        
                                                                                                                        




Tool? I have a tool. Want to buy? ;)

It's a simple script that connects (actually attempts to connect with a fake id/pw) to the target DB (on Windows, HP-Alpha/VMS, UNIX) over Oracle Net. Scans for the ORA- errors and we know if the DB is accessible or not, as well as if Listener is responding. No need for any 'special' ids to run 'select stuff from dual', which in itself can be expensive (depending on the DB version).

I think I had posted this script a few times before. Here it is again. Customize it to suit you requirements to send page/emails/update table etc...:
(btw -- Steve Adams has an elaborate version that checks for a few more
things. Check his web site)

#!/usr/bin/ksh
#
# dbcheck : Script to check if database is up and accessible
#
# Author : Kirti Deshpande
#-----------------------------------------------

echo "Enter Name of the Database SID to check if it is accessible" read DB

sqlplus -s << EOF > /tmp/$$.1
whenever sqlerror exit
aaa/aaa@$DB
exit;
EOF
egrep 'ORA-121|ORA-01034' /tmp/$$.1 > /dev/null if [[ $? = 0 ]]
then

   echo "-----> '$DB' is _NOT_ Accessible\n" else

   grep 'ORA-01017' /tmp/$$.1 > /dev/null    if [[ $? = 0 ]]
   then
     echo "-----> '$DB' is UP and Accessible\n"    else
     echo "-----> '$DB' is _NOT_ Accessible\n"    fi
fi
rm /tmp/$$.1

# --- End of File

-----Original Message-----
Sent: Tuesday, December 03, 2002 4:14 AM To: Multiple recipients of list ORACLE-L

Yup, got that covered :)

If I may add another point. When dealing with SLA's, you not only have to "show" that the database has been servicing users that are connected
(showing database uptime), but also that users can also *connect* to the
database as well (the listener is servicing requests).

If you were to go about this with a trigger/procedure that inserts in to a table, then this doesn't show that the database was available to "everybody".

Typically what we do with monitoring tools is a "connect on ping", so when we are checking availability of a database we do a full connect, then "select 'PING' from dual;". If there are any errors along the way we search for the error code, and deal with the appropriate alerts (TNS = Listener "problem", ORA = Database "problem").

Of course, the problem with doing it this way, is that you are going to have
to write platform dependant scripts (batches for NT, shell type scripts for Unix), and not have a "one for all" with a database based solution.

Go buy a tool. Let it write to a table/file, alert you AND make the coffee for when you get there! ;)

Mark


 Mark Leith             | T: +44 (0)1905 330 281
 Sales & Marketing      | F: +44 (0)870 127 5283
 Cool Tools UK Ltd      | E: mark_at_cool-tools.co.uk
===================================================
           http://www.cool-tools.co.uk
       Maximising throughput & performance

-----Original Message-----
Richard
Sent: 03 December 2002 02:14
To: Multiple recipients of list ORACLE-L

Perhaps there is a "poor mans" way of doing this. The startup trigger could fire a procedure that inserts a row into a table and then sleeps for 1 minute before doing the same again. Effectively it would create a ping in the table, which you could then analyze / graph to display uptimes.

The next logical step would be to increase the intelligence of the procedure. The table storing the statistic could consist of two columns - uptime and downtime. When the startup trigger fires it creates a new row in the table with both uptime and downtime set to sysdate. It then sleeps for a minute before updating downtime for the most recent record (either remember a primary key or search for max(uptime)). This would be much easier to understand when the database was stopped / started.

Of course depending on your accuracy requirement, granularity could be changed to every 5 minutes, 10 minutes, whatever.

Hopefully that gives some ideas though. Of course the 3rd party monitors that Jared mentions are worth considering if the database is considered critical. In that case the number one requirement is probably the ability to page / SMS / email when it sees the database is down.

Regards,

     Mark.

                    Jared.Still_at_ra
                    disys.com            To:     Multiple recipients of
list
ORACLE-L <ORACLE-L_at_fatcity.com>
                    Sent by:             cc:
                    root_at_fatcity.c       Subject:     Re: SLA
Trigger/Procedure
                    om


                    03/12/2002
                    12:13

                    Please respond
                    to ORACLE-L






Ethan,

That records the startup times, but does not record the time that the database was unavailable.

What's needed is a 3rd party monitor that is not dependent on the database being up to record metrics.

Jared

"Post, Ethan" <Ethan.Post_at_ps.net>
Sent by: root_at_fatcity.com
 12/02/2002 02:33 PM
 Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc:
        Subject:        SLA Trigger/Procedure


Just a thought here for a script I think would be handy but I haven't had time to write.

It is would be a simple procedure you could call to get the service level for a particular database. I suppose you would have to have some sort of way of defining normal outage windows. Basically a startup trigger would log the times in a table. You should also check the startup time against the last startup time periodically to ensure the trigger always fires. Somehow a procedure/function should be able to use this information to report the service level for the database within the last
(week/month/quarter/year).

I suppose I will get around to it eventually but if anyone else wants to get
started on it I won't mind!

Thanks,
Ethan

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Post, Ethan
  INET: Ethan.Post_at_ps.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Jared.Still_at_radisys.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Richard INET: mrichard_at_transurban.com.au Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: mark_at_cool-tools.co.uk Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: kirti.deshpande_at_verizon.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Richard INET: mrichard_at_transurban.com.au Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Dec 03 2002 - 15:44:54 CST

Original text of this message

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