RE: SLA Trigger/Procedure
Mark, I didn't mean to turn this in to a tool discussion, I merely tried to point out the down side to the original idea, due to not *fully* checking availability, by showing what tools typically do. Sorry Mark -Original Message- Richard Sent: 03 December 2002 21:45 To: Multiple recipients of list ORACLE-L 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@ve To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] rizon.comcc: Sent by: Subject: RE: SLA Trigger/Procedure [EMAIL PROTECTED] 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 - Kirti -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: [EMAIL PROTECTED] === 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
Re: SLA Trigger/Procedure
So do a select from a view based on sys.x$dual. That does not have cause LIO. I checked a few months ago. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 03, 2002 4:32 PM and that select 'PING' from dual is very costly. I believe Gaja, Cary, Anjo, and a few others (who were nice enough to include me on the thread) determined that before 9i Oracle takes about 5 LIOs to do a select anything from dual. I think it went down to either 3 or 4 in 9i but that's still VERY costly. Dan Fink has that discussion summarized on his site: http://www.optimaldba.com/internals/oraint_dual.html Rachel --- Mark Leith [EMAIL PROTECTED] wrote: 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: [EMAIL PROTECTED] === 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@ra disys.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/02/2002 02:33 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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
RE: SLA Trigger/Procedure
Don't forget to handle connection attempts that hang and never return. Jared Post, Ethan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/03/2002 02:04 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: SLA Trigger/Procedure I agree what is needed is something very generic that could monitor the uptime of pretty much any process. After giving this more thought and always liking to keep things simple here is the direction I might eventually head. The logic below would result in a log file that shows all unscheduled outage periods. You would have to ensure it runs at regular intervals. (NOT VERY PRETTY, BUT SIMPLE ENOUGH) Files Required: * Control file that has crontab like entries which define periods that are either outages or times when database is expected to be running. Script Workings: * Run command/script which returns 0 or 1 (0=thingy not running, 1=thingy running) use separate command/script to log into database, grep for process etc... If (( 0 )) check control file to see if thingy should be running if suppose to be running store time trigger flag (cat time to flag file) if not suppose to be running if flag unscheduled outage was triggered but has rolled into an scheduled outage period log start and end time for outage reset flag fi fi else if flag thingy is back up log start and end time for outage reset flag fi fi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: SLA Trigger/Procedure
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: [EMAIL PROTECTED] === 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@ra disys.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/02/2002 02:33 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
RE: SLA Trigger/Procedure
For reasons too long to go in to, go buy a tool is not a good option for my purposes. Besides, why buy what I can build? These are exactly the sort of challenges that make my job worth coming to. Thanks, Ethan -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: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance 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: Post, Ethan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: SLA Trigger/Procedure
Title: RE: SLA Trigger/Procedure Go buy a tool? Here is an idea instead ... Write a small Perl (pick your favorite language here) script that looks at /etc/oratab followed by a check to see if each of the instances listed in /etc/oratab is up on the side where it is checking. It then log in as a schema (we call it) called heartbeat, insert a row into a table with sysdate. Wait 3 seconds, retrieve the row and checks the time difference. If it is more than 5 seconds, you have a potential delay somewhere and have it alert appropriate people. (If it is night, page your VP with This is your heartbeat calling ... we have a problem ...) Life is great with cron ... HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: SLA Trigger/Procedure
and that select 'PING' from dual is very costly. I believe Gaja, Cary, Anjo, and a few others (who were nice enough to include me on the thread) determined that before 9i Oracle takes about 5 LIOs to do a select anything from dual. I think it went down to either 3 or 4 in 9i but that's still VERY costly. Dan Fink has that discussion summarized on his site: http://www.optimaldba.com/internals/oraint_dual.html Rachel --- Mark Leith [EMAIL PROTECTED] wrote: 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: [EMAIL PROTECTED] === 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@ra disys.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/02/2002 02:33 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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
RE: SLA Trigger/Procedure
Title: RE: SLA Trigger/Procedure Dang, I just new that would cause a stir! ;P Mark Running away now Leith -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jamadagni, RajendraSent: 03 December 2002 14:32To: Multiple recipients of list ORACLE-LSubject: RE: SLA Trigger/Procedure Go buy a tool? Here is an idea instead ... Write a small Perl (pick your favorite language here) script that looks at /etc/oratab followed by a check to see if each of the instances listed in /etc/oratab is up on the side where it is checking. It then log in as a schema (we call it) called heartbeat, insert a row into a table with sysdate. Wait 3 seconds, retrieve the row and checks the time difference. If it is more than 5 seconds, you have a potential delay somewhere and have it alert appropriate people. (If it is night, page your VP with "This is your heartbeat calling ... we have a problem" ...) Life is great with "cron" ... HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art!
RE: SLA Trigger/Procedure
Ahem... OK, I can not resist any longer. The tools exist: http://www.amazon.com/exec/obidos/tg/detail/-/0596002106 The 'dbup' script logs all connections attempts. Use sed, perl, or whatever to parse the logfile, or modify to create logs more to your liking. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/03/2002 06:32 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: SLA Trigger/Procedure Go buy a tool? Here is an idea instead ... Write a small Perl (pick your favorite language here) script that looks at /etc/oratab followed by a check to see if each of the instances listed in /etc/oratab is up on the side where it is checking. It then log in as a schema (we call it) called heartbeat, insert a row into a table with sysdate. Wait 3 seconds, retrieve the row and checks the time difference. If it is more than 5 seconds, you have a potential delay somewhere and have it alert appropriate people. (If it is night, page your VP with This is your heartbeat calling ... we have a problem ...) Life is great with cron ... HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: SLA Trigger/Procedure
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 - Kirti -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: [EMAIL PROTECTED] === 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@ra disys.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/02/2002 02:33 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L
RE: SLA Trigger/Procedure
To be fair, there is some disagreement about adding an index on DUAL. I have not duplicated the scenario that Cary presents and I have been too distracted (writing a paper/presentation for Rachel) to get back to my testing. So take with a small grain of salt. Dan -Original Message- Sent: Tuesday, December 03, 2002 7:32 AM To: Multiple recipients of list ORACLE-L and that select 'PING' from dual is very costly. I believe Gaja, Cary, Anjo, and a few others (who were nice enough to include me on the thread) determined that before 9i Oracle takes about 5 LIOs to do a select anything from dual. I think it went down to either 3 or 4 in 9i but that's still VERY costly. Dan Fink has that discussion summarized on his site: http://www.optimaldba.com/internals/oraint_dual.html Rachel --- Mark Leith [EMAIL PROTECTED] wrote: 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: [EMAIL PROTECTED] === 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@ra disys.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/02/2002 02:33 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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
RE: SLA Trigger/Procedure
Yes, but can I get it signed... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan Jared.Still@radis ys.com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: SLA Trigger/Procedure 12/03/2002 10:34 AM Please respond to ORACLE-L Ahem... OK, I can not resist any longer. The tools exist: http://www.amazon.com/exec/obidos/tg/detail/-/0596002106 The 'dbup' script logs all connections attempts. Use sed, perl, or whatever to parse the logfile, or modify to create logs more to your liking. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/03/2002 06:32 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: SLA Trigger/Procedure Go buy a tool? Here is an idea instead ... Write a small Perl (pick your favorite language here) script that looks at /etc/oratab followed by a check to see if each of the instances listed in /etc/oratab is up on the side where it is checking. It then log in as a schema (we call it) called heartbeat, insert a row into a table with sysdate. Wait 3 seconds, retrieve the row and checks the time difference. If it is more than 5 seconds, you have a potential delay somewhere and have it alert appropriate people. (If it is night, page your VP with This is your heartbeat calling ... we have a problem ...) Life is great with cron ... HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! (See attached file: ESPN_Disclaimer.txt) ESPN_Disclaimer.txt Description: Binary data
Re: SLA Trigger/Procedure
[EMAIL PROTECTED] wrote: 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 Jared, I am far from having given much thought to this interesting idea but you can possibly work around this by using utl_file to check background_dump_dest. If it's not recorded in the alert.log, you probably have a .trc file hanging around. This optimistically assumes of course that you do not let you alert.log file grow out of control, because the idea of reading with UTL_FILE a 300M+ text file is not properly exciting. In fact the solution might be some external procedure to do in C directory operations, stat() calls and the like. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: SLA Trigger/Procedure
Title: RE: SLA Trigger/Procedure you still have to *buy* the tool right? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Subject: RE: SLA Trigger/Procedure Ahem... OK, I can not resist any longer. The tools exist: http://www.amazon.com/exec/obidos/tg/detail/-/0596002106 The 'dbup' script logs all connections attempts. Use sed, perl, or whatever to parse the logfile, or modify to create logs more to your liking. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/03/2002 06:32 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: RE: SLA Trigger/Procedure Go buy a tool? Here is an idea instead ... Write a small Perl (pick your favorite language here) script that looks at /etc/oratab followed by a check to see if each of the instances listed in /etc/oratab is up on the side where it is checking. It then log in as a schema (we call it) called heartbeat, insert a row into a table with sysdate. Wait 3 seconds, retrieve the row and checks the time difference. If it is more than 5 seconds, you have a potential delay somewhere and have it alert appropriate people. (If it is night, page your VP with This is your heartbeat calling ... we have a problem ...) Life is great with cron ... HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: SLA Trigger/Procedure
Mark - Well, speaking for myself, I appreciate your openness in pointing out the major issues one would have to deal with if they were going to write their own. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 03, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Dang, I just new that would cause a stir! ;P Mark Running away now Leith -Original Message- Rajendra Sent: 03 December 2002 14:32 To: Multiple recipients of list ORACLE-L Go buy a tool? Here is an idea instead ... Write a small Perl (pick your favorite language here) script that looks at /etc/oratab followed by a check to see if each of the instances listed in /etc/oratab is up on the side where it is checking. It then log in as a schema (we call it) called heartbeat, insert a row into a table with sysdate. Wait 3 seconds, retrieve the row and checks the time difference. If it is more than 5 seconds, you have a potential delay somewhere and have it alert appropriate people. (If it is night, page your VP with This is your heartbeat calling ... we have a problem ...) Life is great with cron ... HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: SLA Trigger/Procedure
Stephan, There will be no relevant info in the alert.log about the time when database was unavailable, if database was not shut down properly (let say, the plug was pulled). Or, may be I misunderstood you? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 03, 2002 2:10 PM [EMAIL PROTECTED] wrote: 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 Jared, I am far from having given much thought to this interesting idea but you can possibly work around this by using utl_file to check background_dump_dest. If it's not recorded in the alert.log, you probably have a .trc file hanging around. This optimistically assumes of course that you do not let you alert.log file grow out of control, because the idea of reading with UTL_FILE a 300M+ text file is not properly exciting. In fact the solution might be some external procedure to do in C directory operations, stat() calls and the like. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Igor Neyman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: SLA Trigger/Procedure
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@ve To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] rizon.comcc: Sent by: Subject: RE: SLA Trigger/Procedure [EMAIL PROTECTED] 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 - Kirti -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: [EMAIL PROTECTED] === http://www.cool-tools.co.uk
RE: SLA Trigger/Procedure
I agree what is needed is something very generic that could monitor the uptime of pretty much any process. After giving this more thought and always liking to keep things simple here is the direction I might eventually head. The logic below would result in a log file that shows all unscheduled outage periods. You would have to ensure it runs at regular intervals. (NOT VERY PRETTY, BUT SIMPLE ENOUGH) Files Required: * Control file that has crontab like entries which define periods that are either outages or times when database is expected to be running. Script Workings: * Run command/script which returns 0 or 1 (0=thingy not running, 1=thingy running) use separate command/script to log into database, grep for process etc... If (( 0 )) check control file to see if thingy should be running if suppose to be running store time trigger flag (cat time to flag file) if not suppose to be running if flag unscheduled outage was triggered but has rolled into an scheduled outage period log start and end time for outage reset flag fi fi else if flag thingy is back up log start and end time for outage reset flag fi fi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: SLA Trigger/Procedure
Mark, Thanks. I was just picking on Mark The Cool Tool Guy Leith ;) Hope he doesn't mind ;) Sure, your suggestion still stands and, you are right, we digressed.. - Kirti -Original Message- Sent: Tuesday, December 03, 2002 3:45 PM To: Multiple recipients of list ORACLE-L 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@ve To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] rizon.comcc: Sent by: Subject: RE: SLA Trigger/Procedure [EMAIL PROTECTED] 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 - Kirti -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
Re: SLA Trigger/Procedure
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 [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/02/2002 02:33 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: SLA Trigger/Procedure
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@ra disys.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/02/2002 02:33 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
