Check for Oracle availability and tablespace capacity

Checking Oracle for availability requires more than just a ping or a 'ps -ef | grep oracle' command. I believe it requires a test accessing Oracle using SQL*Net to verify that the listener is also up and running -- this would be the access that a user would normally use. By actually logging on to the instance, you verify that the instance will accept logins whereas if you only do the above checks, how would you know if logins are not accepted due to archiver log waits?

Along with Oracle being alive and accessible, I also check to make sure it can be used, so I also check for tabalespace capacity.

I have written a few scripts that allows me to check:

1. if all of the Oracle instances are up

2. if any of the extents have reached their maximum limit

3. if any of the tablespaces are running short of freespace

The script can be run from crontab with any interval you feel comfortable with. This script will also page or email the support personnel when any of the above exceptions are met.

If there are any other tests that you would like to do, this script can be easily modified to include them. I use this Monitororcl script as a template and just add functionality at the end.

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle database administrator Understanding SQL string functions What is the difference between a database engineer, architect and administrator? Import on one table from dump file Error during RMAN backup Can I drop a column in SYS schema? STATSPACK tool: transaction vs. execution measurement Should I port from Microsoft Access? How can I find statistics on total memory usage and database connections? Installing multiple Oracle homes Modifying SYS password in a RAC environment Other availability strategies Creating a standby database in 10g Steps for database replication Service levels and resources: A decade of mismatch Keeping data recovery site active Data warehousing possible on a disaster recovery hot site? Replication for high availability Configure Data Guard on Windows NT Questions about partitions, indexes, and parallelism Know-IT-all Chapter Quiz #2: Backup and recovery for high availability All about partitioning

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

The query_oracle_instances.sh script that is called from crontab:

The Get_Oracle_Instance_List script:

Here is the MONITORORCL script:

For More Information

• What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB@, metadata, and datawarehousing gurus are waiting to answer your toughest questions.