ss

Oracle Tips by Burleson

A Real-Time Check for Oracle Problems

The oracheck.run script is usually scheduled to run hourly in a production environment to report on any exception condition that may jeopardize the Oracle database. This script is quite sophisticated and contains four parameter files that control the level of reporting. The parameter files for this script include:

* parm_mount_point_kb_free.ora?This file contains the threshold for any Oracle mount point. If you are using tablespaces with AUTOEXTEND ON, you must constantly monitor the UNIX mount points to prevent Oracle from hanging on a failure to extend problem.

* parm_ts_free.ora?This file contains the threshold for reporting on full tablespaces.

* parm_num_extents.ora?This file contains the number by which a table or index’s extents cannot exceed. For example, placing 600 in this file will cause the DBA to be e-mailed when any object exceeds 600 extents.

* parm_alert_log.ora?This file contain alert log messages that should be reported to the DBA. Below is a common list for this file:

>cat parm_alert_log.ora

ORA-00600

ORA-1631

ORA-1650

ORA-1652

ORA-1653

ORA-00447

ORA-00603

ORA-01092

ORA-02050

ORA-1535

* oracheck.run?This is a Korn shell script that reports on anything that might cause the database to hang up or crash. The idea behind this script is to allow the DBA to repair impending problems before that database crashes. Here are the checks that are performed by this script:

* Alert log messages?This script e-mails any alert log messages that are found in the alert log. The parameter file parm_alert_log.ora contains a list of alert log messages to be reported.

* Low free space in archived redo log directory?If the archived redo log directory become full, our Oracle database will hang up. This alert allows the Oracle DBA to add space before the database hangs.

* UNIX mount point space alert?The script checks all datafile mount points in Oracle, including the UNIX Oracle home directory. Since most databases now use AUTOEXTEND ON, the DBA must be constantly alert for file systems that may not be able to extend. If the free space in any mount point is less than specified in parm_mount_point_kb_free.ora, an e-mail alert will be sent to the DBA.

* Object cannot extend?This report will alert the Oracle DBA whenever an Oracle table or index does not have room to take another extent. This alert is obsolete if you are using tablespaces with AUTOEXTEND ON, but many DBAs still keep this alert because they want to monitor the growth of the database tables and indexes.

* Tablespace > nn% free?This report sends an e-mail alert whenever any tablespaces contain less space than specified by parm_ts_free.ora. Again, this alert is obsolete when using AUTOEXTEND ON, but many DBAs still want to see the available space within each tablespace.

* Object > nnn extents?This report is very useful for reporting tables and indexes that experience unexpected growth. Whenever a table or index exceeds the number defined in parm_num_extents.ora, an e-mail alert will be sent to the DBA.

The above is an excerpt from the "Oracle9i UNIX Administration Handbook" by Oracle press, authored by Donald K. Burleson.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA