 |
 |
||
 |
 |
 |
|
 |
 |
 |
|||
 |
|||||
 |
|||||
 |
|||||
 |
|||||
 |
|||||
 |
|||||
 |
|||||
 |
|||||
 |
|||||
 |
|||||
|
|||||
 |
|||||
|
|||||
 |
|||||
|
|||||
 |
|||||
|
|||||
 |
|||||
|
|||||
 |
|||||
 |
|||||
 |
|
 |
|||
 |
Donald K. Burleson Oracle Tips |
A
generic Oracle alert script for table and tablespace problems
Most
folks who run Oracle database are charged with ensuring that they fix
database problems before the database crashes.
This type of proactive approach can be automated.
You can run scripts that identify and report on any conditions that
might cause Oracle to crash.
Here
are some of the things that I monitor inside Oracle:
- 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 data file
mount points in Oracle, including the UNIX Oracle home directory.
Since most databases now use AUTOEXTEND ON, the DBA must be
constantly alert for filesystem 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, and e-mail alert will be sent to the DBA.
- Object cannot extend – This report will alert the Oracle
DBA whenever an Oracle table of index does not have room to take another
extent. This alert is
obsolete if you are using tablespaces with AUTOEXTEND ON, but many DBA
still keep this alert because they want to monitor the growth of the
database tables and indexes.
- Tablespace > nn% free – This report send 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 DBA’s 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 of index exceed the number defined in
parm_num_extents.ora, an e-mail alert will be sent to the DBA.
Here is an
actual sample of the e-mail output from this script.
NON-EMERGENCY
ORACLE ALERT. Mount point /home has less than 250000 K-Bytes free.
This set of scripts can
be used to report on Oracle activity.
oracheck.run
– This is a Korn shell script that executes the oracheck.ksh script and
e-mail you if a problem is detected. The idea behind this script is to allow
the DBA to repair impending problems before that database crashes.
Note that the script below redirects the output to a file, checks the
number of lines in the output file, and e-mails an alert message if problems
are found.
#!
/bin/ksh
#
Here we write a blank line to the log file . . .
echo
`date` > /oracle/my_sid/scripts/oracheck.log
#
Now we run the check, writing errors to the oracheck.log file
/oracle/MY_SID/scripts/oracheck.ksh
>> \
/oracle/MY_SID/scripts/oracheck.log
#
If errors messages exist (2 or more lines), then go on . . .
if
[ `cat /oracle/MY_SID/scripts/oracheck.log|wc -l` -gt 1 ]
then
# Now, be sure that we don't clog the mailbox.
# the following statement checks to look for
existing mail,
# and only sends mail when mailbox is empty . . .
if [ ! -s /var/spool/mail/oramy_sid ]
then
cat /oracle/MY_SID/scripts/oracheck.log
| mail oramy_sid
fi
fi
oracheck.ksh
– This is a Korn shell script that does the queries against Oracle and the
Oracle environment.
Note that this script loops through all of the databases on the
server (using /etc/oratab) and reports any exceptional conditions for all
databases.
#!/bin/ksh
set
PATH=$PATH:/oracle/MY_SID/bin
ORACLE_SID=MY_SID;
export
ORACLE_SID;
#ORAENV_ASK=NO
#export
ORAENV_ASK;
#/oracle/MY_SID/bin/oraenv
ORACLE_BASE=/oracle/MY_SID;
export ORACLE_BASE;
ORACLE_HOME=/oracle/MY_SID;
export ORACLE_HOME;
set
$pwd=system/scion; export pwd;
ALERT_DIR=/oracle/MY_SID/saptrace/background;
export ALERT_DIR;
ALERT_DIR_MOUNTPOINT=/oracle/MY_SID;
export ALERT_DIR_MOUNTPOINT;
REDO_LOG_FILESYSTEM=/oracle/MY_SID/saparch;
export REDO_LOG_FILESYSTEM;
#**************************************************************
# Check if checknet2 deamon is running . . . .
#**************************************************************
#oracle_up=`ps
-ef|grep checknet2|grep -v grep|wc -l`;
#oracle_num=`expr
$oracle_up`
#if
[ $oracle_num -lt 1 ]
#
then echo "checknet2 deamon is NOT up."
#fi
#***********************************************************************
# Here is where we loop through each SID in /etc/oratab . . .
#***********************************************************************
#for
ORACLE_SID in `cat /etc/oratab | grep :N|cut -d":" -f1`
#do
#**************************************************************
# Check if Oracle is up . . . .
#**************************************************************
oracle_up=`ps
-ef|grep pmon_$ORACLE_SID|grep -v grep|wc -l`;
oracle_num=`expr
$oracle_up`
if
[ $oracle_num -lt 1 ]
then echo "$ORACLE_SID instance is NOT up."
fi
#***********************************************************
#
Check alert log for ORA-600
#***********************************************************
tail
-400 $ALERT_DIR/alert_$ORACLE_SID.log|grep ORA-00600
#***********************************************************
#
Check redo log file-system > 90% full
#***********************************************************
redo_log=`df
-k|grep $REDO_LOG_FILESYSTEM|awk '{print $4}'|cut -d"%" -f1`
oracle_num=`expr
$redo_log`
if
[ $oracle_num -gt 90 ]
then logger "REV_ORA_030W
$ORACLE_SID redo log file system is > 90%. Please "
echo "$ORACLE_SID redo log file system is > 90%."
fi
# Invoke SQL*Plus to generate DB space info.
/oracle/MY_SID/bin/sqlplus -s << UNTIL_DONE
$pwd
SET
ECHO OFF;
SET
TERM OFF;
SET
TIMING OFF;
SET
HEAD OFF;
SET
FEED OFF;
CREATE
TABLE oracheck_fs_temp (tablespace_name,total_bytes,free_bytes,max_chunk)
AS
SELECT tablespace_name, NVL(SUM(bytes), 1), 1, 1
FROM dba_data_files
GROUP BY tablespace_name;
UPDATE oracheck_fs_temp a
SET a.free_bytes = (SELECT NVL(SUM(b.bytes), 1)
FROM dba_free_space b
WHERE b.tablespace_name = a.tablespace_name);
COMMIT;
UPDATE oracheck_fs_temp a
SET a.max_chunk = (SELECT NVL(MAX(b.bytes), 1)
FROM dba_free_space b
WHERE b.tablespace_name = a.tablespace_name);
COMMIT;
REM
****************************************************************
REM Tablespaces more than 95% full.
REM
****************************************************************
SELECT
tablespace_name
||
' is '
||
TO_CHAR(ROUND(100-(free_bytes*100/total_bytes),
2)) ||
'% full.' T
FROM oracheck_fs_temp
WHERE 95 < 100-(free_bytes*100/total_bytes)
ORDER BY tablespace_name;
REM
****************************************************************
REM Tablespaces/objects with insufficient space for NEXT
extent.
REM
****************************************************************
SELECT
SUBSTR(a.tablespace_name,
1, 21) tablespace ,
SUBSTR(a.owner
, 1, 16) owner
,
SUBSTR(a.segment_name
, 1, 30) object_name,
SUBSTR(a.segment_type
, 1, 8 ) what
FROM dba_segments a
WHERE a.segment_type IN ('TABLE', 'INDEX', 'ROLLBACK')
AND NVL(a.next_extent,
1) > (SELECT b.max_chunk
FROM
oracheck_fs_temp b
WHERE b.tablespace_name = a.tablespace_name)
ORDER BY 1,2,4,3;
DROP TABLE oracheck_fs_temp;
REM
*****************************************************************
REM Objects with more than 600 extents.
REM
*****************************************************************
REM Applied NVL function purposely to extents column.
SELECT SUBSTR(owner
, 1, 22) owner
,
SUBSTR(segment_name,
1, 30) object_name,
SUBSTR(segment_type,
1, 8 ) type ,
extents
FROM dba_segments
WHERE 600 < NVL(extents, 1)
AND segment_type
IN ('TABLE', 'INDEX', 'ROLLBACK')
ORDER BY 1,3,4 DESC,2;
EXIT;
UNTIL_DONE
#done
If
you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress. It’s only
$41.95(I don’t think it is right to charge a fortune for books!) and you
can buy it right now at this link:
http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm
|
|
 |
E-mail us for BC Oracle support: 
Copyright © 1996 - 2009
by Burleson Enterprises, Inc. All rights reserved. Oracle® is the registered trademark of Oracle Corporation.
|
|