Any better way to Monitor DB server & Listener?

Hi DBAs,

I wrote a script to monitor my DB listener(as below), if the listener is down, send an email to me.
I am going to use the same way to monitor DB server process, such as "ora_smon_dev".
Does anyone has better idea to monitor your DB server and listener?

Thanks!

Betty


Check_lsnr.sh
------------------
#!/bin/sh
cd /opt/oracle/scripts
/bin/rm -f lsnr.exist
ps -ef | grep LISTENER | grep -v grep > lsnr.exist
if [ ! -s lsnr.exist ]
then
echo "." | mailx -s "ALERT!!! Oracle listener on `hostname` is down since `date`" name@xyz.com
/opt/oracle/product/9.2.0/bin/lsnrctl start
fi

We've talked about this a couple times on this forum. IMHO, monitoring each individual process does not give you adequate enough information to determine whether your database is accessible or not. For example, you may have a pmon process but your database is only in mount mode. All your background processes may be up, but your log_archive_dest is full.

I believe (as do several others) that the best way to monitor connectivity is to actually login to the database. If you login, great. If you can't, you get an error which will indicate what you should do.

To accomplish this task for my environment, I wrote a perl script that tries to login to the database. If I login, great, I just log out and go to sleep for N seconds. If I get an error, I know what to do about it. If the connection attempt takes longer than X seconds, I timeout and know what to do about it.

Care to share this super genius script with us Mr. Wyle E. Coyote?

It's specific for my environment. Its not a general tool.

Thank you, Jeff, I will take your way.

There is a plugin for the Nagios monitoring system that will attempt a dummy login. It uses a bogus name and password and returns 'OK' if the DB errors with access denied. Any other error indicates a problem.

Here is a link to the check_oracle.sh script.

We have written an insert command with
insertion of current time into a table
and it will be called from our oem.

This script is executed every five minutes by OEM scheduling and
the same output will be mailed.

Hi huns,

Do you have features in your cellphones where the messages can be
textd or e-mailed and make your cell beep? Or you just log-in to ur
PC and check e-mail.

Thanks

HEY JHMARTIN! I RAN THAT CHECK_ORACLE.SH SCRIPT AGAINST MY PRODUCTION DATABASE AND IT CRASHED MY ENTIRE SERVER AND WE CAN'T GET IT BACK UP. IT'S COSTING US $200K PER HOUR IT'S DOWN.

I AM IN CONTACT WITH THE ADMINISTRATION OF THIS FORUM. YOUR COMPANY CAN EXPECT A PHONE CALL FROM OUR LAWYERS.

Quote:

---

Do you have features in your cellphones where the messages can be
textd or e-mailed and make your cell beep? Or you just log-in to ur
PC and check e-mail

---

Yep, we have a piece of software called Telalert that will send SMS or pages. Basically there is a standalone binary called telalertc that will send messages to the Telalert server which texts or pages. You can run telalertc from OS scripts or hook it up to Grid Control/OpenView or whatever.

Quote:

---

Originally posted by OracleDoc
HEY JHMARTIN! I RAN THAT CHECK_ORACLE.SH SCRIPT AGAINST MY PRODUCTION DATABASE AND IT CRASHED MY ENTIRE SERVER AND WE CAN'T GET IT BACK UP. IT'S COSTING US $200K PER HOUR IT'S DOWN.

I AM IN CONTACT WITH THE ADMINISTRATION OF THIS FORUM. YOUR COMPANY CAN EXPECT A PHONE CALL FROM OUR LAWYERS.

---

Hey I ran that script on my home database, and it crashed causing me an opportunity cost of $500,000 per minute, assuming that I could get a contract for that kind of money!!!

I want to sue also!!! :D :D :D

Are u serious guys? how did it crashed ur database? did u run it on
a dba priveleged user?

is the concept of April Fools celebrated in sulu?

Terriable joke! Scared me!

:D Coz i got that bad exprience b4, costs lots of downtime money.
A new dba trainee accendentally erase 1 dbf. And it cant be recovered.
We lost 400,000 payment transaction records in that dbf.
Our manager almost got sued. Lucky we used our charm to convinced the
users to re-encode again 400,000 records. We just got reprimanded by
the President, good to be in the Gov't service:p

Quote:

---

Originally posted by kris123
And it cant be recovered.

---

The Sr. DBA should have been fired on the spot.

Quote:

---

Originally posted by marist89
The Sr. DBA should have been fired on the spot.

---

Just to be clear, you say fire the Sr. DBA because the recovery procedures didn't work?
i.e. what are their backup strategy and when and how do they test it?

Maybe he believed in the power of positive thought? :rolleyes:

Quote:

---

Originally posted by kris123
:D Coz i got that bad exprience b4, costs lots of downtime money.
A new dba trainee accendentally erase 1 dbf. And it cant be recovered.
We lost 400,000 payment transaction records in that dbf.
Our manager almost got sued. Lucky we used our charm to convinced the
users to re-encode again 400,000 records. We just got reprimanded by
the President, good to be in the Gov't service:p

---

I have never been to the Phillipines, but I have been to Guam on business. They really believe in island time. We went to lunch with one of their vice presidents, she said it needed to be a quick lunch. After two hours of her telling some very funny stories, she said I feel like ice cream, so we all had ice cream. Apparently a 2.5 hour lunch is quick! :rolleyes:

Quote:

---

Originally posted by gandolf989
Just to be clear, you say fire the Sr. DBA because the recovery procedures didn't work?
i.e. what are their backup strategy and when and how do they test it?

---

Exactly.