 |
|
Real-Time Check for
Oracle Problems
Oracle Tips by Burleson Consulting
|
The oracheck.run script is usually
scheduled to run hourly in a production environment to report on any
exception condition that may jeopardize the database. The script is
quite sophisticated and contains four parameter files that control the
level of reporting.
These are the parameter files for the script:
-
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 that the extents for a table or index may not
exceed. For example, placing 600 in this file causes the Remote DBA to
receive an e-mail message when any object exceeds 600 extents.
-
parm_alert_log.ora This
file contains alert log messages that should be reported to the Remote DBA.
A common list for the file looks like this:
>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
In addition to the parameter files, we have a
Korn shell script called oracheck.run that controls the overall
execution. The Korn shell script reports on anything that might cause
the database to hang up or crash. The idea behind the script is to
allow the Remote DBA to repair impending problems before the database
crashes. Here are the checks performed by the script:
Alert log messages The 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 becomes full, the Oracle database will hang up.
This alert allows the Oracle Remote 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.
Because most databases now use AUTOEXTEND ON, the Remote DBA must constantly
be alert for file systems that may not be able to extend. If the free
space in any mount point is less than that specified in
parm_mount_point_kb_free.ora, an e-mail alert will be sent to the
Remote DBA.
Object cannot extend This report alerts the Oracle Remote DBA whenever
an Oracle table or index lacks room to take another extent. The alert
is obsolete if you are using tablespaces with AUTOEXTEND ON, but many
Remote 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 Remote 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 is sent to the
Remote DBA.
Here is an actual sample of the e-mail output
from the oracheck.run script:
NON-EMERGENCY ORACLE ALERT. Mount point /home
has less than 250000 K-Bytes free.
Next, let’s look at a weekly object report that
can tell the Remote DBA of the changes to the database in the past week.
Weekly Object Growth Report
The Weekly Object Growth report uses the
STATSPACK extension tables for objects to prepare weekly growth
reports. For details on collecting the statistics, see Chapter 10.
The rpt_object_stats.sql script is a
very useful report that approximates the overall growth of the
database over the past week. The Remote DBA can quickly compare table and
index counts, and see the total growth for table and indexes over the
past week. The report is often e-mailed to MIS managers. Let’s take a
closer look at each section.
Elapsed-Time Section
The first section of the report identifies the
snapshots that are used in the comparison. The script identifies the
most recent snapshot and compares it to the n – 1 snapshot.
SQL> @/export/home/oracle/obj_stat/rpt_object_stats
Connected.
'*********************************************'
Mon Jan 22
page 1
Object growth
Comparing last two snapshots
Most recent date 2001-01-22
Mon Jan 22
page 1
Object growth
Comparing last two snapshots
Older date 2001-01-08
'*********************************************'
The next section shows the total counts of
tables and indexes in the database. This is a very useful report for
the Remote DBA to ensure that no new objects have migrated into the
production environment. We also see the total bytes for all tables and
indexes and the size change over the past week.
This report shows the total growth of tables
and indexes for the past week.
Mon Jan 22
page 1
Most recent database object counts and sizes
DB_NAME TAB_COUNT IDX_COUNT TAB_BYTES IDX_BYTES
---------------- --------- ----------------
----------------
prodb12 451 674 330,219,520
242,204,672
-------- --------- ----------------
----------------
Total 451 674 330,219,520
242,204,672
Mon Jan
22
page 1
Database size change
comparing the most recent snapshot dates
DB_NAME OLD_BYTES NEW_BYTES
CHANGE
--------- ------------- ----------------
----------------
prodb12 467,419,136 572,424,192
105,005,056
------------- ----------------
----------------
Total 467,419,136 572,424,192
105,005,056
The next section is primarily for the Remote DBA. It
reports on any tables that contain excessive chained rows, where the
table has no RAW, LONG RAW, or BLOB type columns. These row chaining
problems can generally be resolved by a table reorganization.
Mon Jan
22
page 1
comparing the most recent snapshot dates
Tables with > 10% chained rows and no LONG columns.
Owner Table PCTFREE PCTUSED avg row Rows Chains
Pct
--------- ------------ ------- ------- ------- ------- -------
----
OE SO_OBJECTS 10 70 1,858 87 64
.74
INV MTL_ABC_COMP 10 70 73 367 44
.12
APPLSYS FND_PERFORMA 10 40 27,152 20 19
.99
Next we see the chained rows for tables with
LONG type column values. These tables cannot be repaired with
reorganization, because the row length often exceeds the database
block size. This is especially true when RAW data columns are stored
inline, inside the actual table rows.
Mon Jan
22
page 1
comparing the most recent snapshot dates
Tables with > 10% chained rows that contain LONG
columns.
Owner Table PCTFREE PCTUSED avg row Rows Chains
Pct
--------- ------------ ------- ------- ------- ------ ------------
----
EUL_MWC DIS_DOCS 10 40 23,912 9 9
100
Next we see a report showing all tables with
more than 200 extents, or tables that have extended over the past
week. While excessive extents for a table is not a cause for concern,
the report tells the Remote DBA about critical tables that are growing. The
report helps the Remote DBA plan for tablespace growth.
Mon Jan
22
page 1
Table extents report
Where extents > 200 or table extent changed
comparing most recent snapshots
DB OWNER TAB_NAME OLD_EXT
NEW_EXT
---------- ---------- ------------------------- ----------
----------
prodb1 WOMP REFERER_LOG 2
1
WOMP JANET_SITE_STATISTICS 1
2
WOMP EC_CUSTOMER_SERVICE_ACTIONS 1
3
ORACLE TOM_ENTRY 2
5
WOMP SEC_BROWSER_PROPERTIES 1
8
WOMP SEC_SESSIONS 1
10
WOMP EC_USER_SESSIONS 1
12
ORACLE PAGE_IM3 6
12
WOMP EC_PRODUCTS_AUDIT 5
14
ORACLE SQLTEMPO 1
17
ORACLE PAGE_IMORP 66
125
Mon Jan
22
page 1
Index extents report
Where extents > 200 or index extent changed
Comparing last two snapshots
DB OWNER IDX_NAME OLD_EXT
NEW_EXT
---------- ---------- ------------------------- ----------
----------
prodb1 ORACLE ISBN_TOC_SEQ_IDX 1
2
WOMP ROV_STAT_PAGE_TYPE_IDX 1
2
WOMP SYS_C006210 1
3
ORACLE SYS_IL0000005970C00007$$ 1
3
ORACLE SEQ_KEY_IDX 3
5
Next, let’s look at other miscellaneous reports
that are helpful for the Remote DBA.
Trace Alert Report
Trace Alert is a great script for instantly
notifying the Remote DBA and developers of the presence of trace files. In a
production environment, the script can be used to alert the Remote DBA to
production aborts, and it is also useful in development environments,
where developers can be e-mailed their trace file dumps when a program
aborts. This script is generally executed every five minutes.
The trace_alert.ksh script interrogates
the Oracle datafile systems to find the locations of all trace and
dump files. It then checks those directories and e-mails any trace
files to the appropriate staff member. Let’s take a close look at the
steps in the script.
Set the Environment
The first part of the script ensures that a
valid ORACLE_SID is passed to the script:
#!/bin/ksh
#******************************************************
# Exit if no first parameter $1 is passed to script
#******************************************************
if [ -z "$1" ]
then
echo "Usage: trace_alert.ksh <ORACLE_SID>"
exit 99
fi
#******************************************************
# First, we must set the environment . . . .
#******************************************************
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID:|cut -f2
-d':'`
export ORACLE_HOME
ORACLE_BASE=`echo $ORACLE_HOME | sed -e 's:/product/.*::g'`
export ORACLE_BASE
export Remote DBA=$ORACLE_BASE/admin;
export Remote DBA
PATH=$ORACLE_HOME/bin:$PATH
export PATH
MON=`echo ~oracle/mon`
export MON
Get Environment Information
Next, we get the name of the database server
and the current date:
#******************************************************
# Get the server name & date for the e-mail message
#******************************************************
SERVER=`uname -a|awk '{print $2}'`
MYDATE=`date +"%m/%d %H:%M"`
#******************************************************
# Remove the old file list
#******************************************************
rm -f /tmp/trace_list.lst
touch /tmp/trace_list.lst
Get the Names of Any Recent Trace or Dump Files
This section issues the UNIX find
command to locate any Oracle trace or dump files that were created in
the past day:
#******************************************************
# list the full-names of all possible dump files . . . .
#******************************************************
find $Remote DBA/$ORACLE_SID/bdump/*.trc -mtime -1 -print >> /tmp/trace_list.lst
find $Remote DBA/$ORACLE_SID/udump/*.trc -mtime -1 -print >> /tmp/trace_list.lst
find $ORACLE_HOME/rdbms/log/*.trc -mtime -1 -print >> /tmp/trace_list.lst
Exit Immediately If No Files Found
This section exits right away if there are no
files to e-mail to the Remote DBA and developers:
#******************************************************
# Exit if no trace files are found
#******************************************************
NUM_TRACE=`cat /tmp/trace_list.lst|wc -l`
oracle_num=`expr $NUM_TRACE`
if [ $oracle_num -lt 1 ]
then
exit 0
fi
#echo $NUM_TRACE files found
#cat /tmp/trace_list.lst
E-mail the Trace Files
This section of the code extracts the first 100
lines of each trace and dump file and e-mails them to the Remote DBA and
developer staff.
#******************************************************
# for each trace file found, send Remote DBA an e-mail message
# and move the trace file to the /tmp directory
#******************************************************
cat /tmp/trace_list.lst|while read TRACE_FILE
do
#***************************************************
# This gets the short file name at the end of the full path
#***************************************************
SHORT_TRACE_FILE_NAME=`echo $TRACE_FILE|awk -F"/" '{ print $NF
}'`
#***************************************************
# This gets the file location (bdump, udump, log)
#***************************************************
DUMP_LOC=`echo $TRACE_FILE|awk -F"/" '{ print $(NF-1) }'`
#***************************************************
# send an e-mail to the administrator
#***************************************************
head -100 $TRACE_FILE|\
mailx -s "$ORACLE_SID Oracle trace file at $MYDATE."\
don@remote-Remote DBA.net\
terry@oracle.net\
tzu@oracle.com
Move the Trace File
The final step is to move the trace or dump
file from its current location to the UNIX /tmp directory. This keeps
the dump file locations from getting clogged and ensures that the
trace file is periodically deleted. This is because most UNIX
administrators remove files from the /tmp directory after they are
seven days old.
#***************************************************
# Move the trace file to the /tmp directory
# This prevents multiple messages to the developers
# and allows the script to run every minute
#***************************************************
cp $TRACE_FILE /tmp/${DUMP_LOC}_${SHORT_TRACE_FILE_NAME}
rm –f $TRACE_FILE
Next, let’s look at a generic alert script that
can be used on nondatabase servers to e-mail alerts when a program on
a web server aborts.
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
