Oracle Tips by Burleson
A Real-Time Check for Oracle
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
* 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:
* 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
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA