Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Oracle Messurements in HP OV 1
- Thread starter appi
- Start date
bluedragon2
IS-IT--Management
Not sure if this is what your looking for, but take a look at MRTG. It is a graphing app that has 1 hour/1 day/30 day/1 year graphs. It is designed for routers, but I customized it for my needs. Just do a web search for MRTG and you should find info on it.
Blue
Blue
- Thread starter
- #3
Hi Blue,
what we want is to measure some internal Oracle parameters. We could read them out with SQL, but there is no way to display them in HP-OV. Only setting up events for notification. But we want to display them in our OV mostly graphical.
I don't know if MRTG is also for measuring SQL-Databases ?
rgds
Uwe
what we want is to measure some internal Oracle parameters. We could read them out with SQL, but there is no way to display them in HP-OV. Only setting up events for notification. But we want to display them in our OV mostly graphical.
I don't know if MRTG is also for measuring SQL-Databases ?
rgds
Uwe
bluedragon2
IS-IT--Management
No, I think MRTG only works with MIB's
Sorry I couldn't be more help,
Blue
Sorry I couldn't be more help,
Blue
-
1
- #5
Hi appi, well if you are looking for something to generate events based on tablespace util, listener up/down, etc...I have a script to take care of that, displaying messages through "opcmsg" in the OV's message browser.
But if you are looking for some reports, graphs,etc...you may use MRTG , NRG, whatever, but, making sure you have Oracle's snmp daemon properly configured and running of course.
Hope this helps, let me know if you want me to post the script, ok?
Regards,
vlan52
![[sunshine]](/data/assets/smilies/sunshine.gif "[sunshine] [sunshine]")
vlan52
The end of wisdom is freedom. The end of culture is perfection. The end of
education is character. The end of knowledge is love.
But if you are looking for some reports, graphs,etc...you may use MRTG , NRG, whatever, but, making sure you have Oracle's snmp daemon properly configured and running of course.
Hope this helps, let me know if you want me to post the script, ok?
Regards,
vlan52
vlan52
The end of wisdom is freedom. The end of culture is perfection. The end of
education is character. The end of knowledge is love.
- Thread starter
- #6
The best thing I could reccommend would be a DataBase HP Smart Plug In (DBSPI)
There's a DBSPI for Oracle and it does exactly what you need, probably a bit more as well.
There's nothing in there you couldn't duplicate yourself but doing so would take you a considerable time, months I would think.
Mike
Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884
It's like this; even samurai have teddy bears, and even teddy bears get drunk.
There's a DBSPI for Oracle and it does exactly what you need, probably a bit more as well.
There's nothing in there you couldn't duplicate yourself but doing so would take you a considerable time, months I would think.
Mike
Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884
It's like this; even samurai have teddy bears, and even teddy bears get drunk.
- Thread starter
- #8
Hi Mike,
that's right. We use DBSPI for logfilies and alarming, but we hope to find some informations how we can make historical views for fillments or memory-usage.
So I hope taht the script from vlan will help us straight foreward
rgds
Uwe
that's right. We use DBSPI for logfilies and alarming, but we hope to find some informations how we can make historical views for fillments or memory-usage.
So I hope taht the script from vlan will help us straight foreward
rgds
Uwe
bluedragon2
IS-IT--Management
Question about DBSPI:
I have SNMP alarms coming in and they are stored in an oracle DB. I have one alarm that is a minor alarm, but I would like to change it to critical if it starts coming in at so many per minute. I don't think it is possible with NNM. Is DBSPI capable of sending an alert if the oracle DB is populated with this alarm at a threshold?
Thanks,
Blue
I have SNMP alarms coming in and they are stored in an oracle DB. I have one alarm that is a minor alarm, but I would like to change it to critical if it starts coming in at so many per minute. I don't think it is possible with NNM. Is DBSPI capable of sending an alert if the oracle DB is populated with this alarm at a threshold?
Thanks,
Blue
No problem Appi, here it goes, there is a script and a conf file for it.
The DBSPI solution is great, but, is kind of expensive, at least for our budget right now.
Hope this helps,
vlan52
-----------------------------------------
#!/bin/ksh
###############################################################
#
# File: dbmon
# Propose: database monitor for ito
# Dependencies: dbmon.conf
# Created: 26/May/00 - Marcio Tadashi Sano
# Last review: 01/Jun/00 - cgarcia@optiglobe.com.ar
#
# Usage: dbmon -l|-d key
# -l Log file monitoring
# -d database objects monitoring
#
###############################################################
#
# metrics
#
# . Tablespaces with used space more than n% of the total
# . Segments with used extents more than n% of the max_extents
###############################################################
#
# variables
#
typeset MonName=`basename $0`
OPC_MON=/opt/OV/bin/OpC/opcmon
OPC_MSG=/opt/OV/bin/OpC/opcmsg
#ORATAB=/var/opt/oracle/oratab
ORATAB=/etc/oratab
#CONFILE=/export/home/oracle/scripts/dbmon.conf
CONFILE=/var/opt/OV/conf/OpC/dbmon.conf
###############################################################
#
# Send alert to ITO
#
function Message # (severity msggrp process message)
{
typeset severity=$1; shift
typeset msggrp=$1; shift
typeset process=$1; shift
${OPC_MSG} application=Oracle object=$process msg_grp=$msggrp severity=$severity msg_text="$*"
}
function build_message # (type Message)
{
M_TYPE=$1; shift
case $M_TYPE in
ts_used_space)
while [ $# != 0 ]
do
typeset TS=$1; shift
typeset USED=$1; shift
typeset TS_SEV=$1; shift
typeset TS_DB=$1; shift
Message $TS_SEV "Database" "Tablespace" "Oracle: Tablespace $TS has $USED% of used space in database $TS_DB"
done
;;
extent_used)
while [ $# != 0 ]
do
typeset SEG=$1; shift
typeset TP=$1; shift
typeset USED=$1; shift
typeset ET_SEV=$1; shift
typeset ET_DB=$1; shift
Message $ET_SEV "Database" "Segment" "Oracle: Segment $SEG($TP) has $USED% of used extents in database $ET_DB"
done
;;
log_space)
while [ $# != 0 ]
do
typeset LOG=$1; shift
typeset DEV=$1; shift
typeset USED=$1; shift
typeset LOG_SEV=$1; shift
typeset LOG_DB=$1; shift
Message $LOG_SEV "Database" "Diskspace" "Oracle: The $LOG directory of database $LOG_DB is in filesystem $DEV with $USED% of used space"
done
;;
lock_pending)
while [ $# != 0 ]
do
typeset LOCK=$1; shift
typeset LVAL=$1; shift
typeset LOCK_SEV=$1; shift
typeset LOCK_DB=$1; shift
Message $LOCK_SEV "Database" "Lock" "Oracle: The database $LOCK_DB has $LVAL% of locked sessions"
done
;;
roll_contention)
while [ $# != 0 ]
do
typeset ROLL=$1; shift
typeset VAL=$1; shift
typeset ROLL_SEV=$1; shift
typeset ROLL_DB=$1; shift
Message $ROLL_SEV "Database" "Rollback" "Oracle: The $ROLL rollback segment of database $ROLL_DB has $VAL% of contention"
done
;;
esac
}
function check_ts_used_space # (threshold)
{
typeset THRESHOLD=$1
pLinha=`svrmgrl <<EOF | grep $DB_NAME
connect $p_v1/$p_v2
select a.tablespace_name Tablespace,
to_char((1-sum(a.bytes/b.bytes)) * 100, '99' ) Used,
'$SEV',
'$DB_NAME'
from dba_free_space a,
dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name having (1-sum(a.bytes/b.bytes)) * 100 > $THRESHOLD
/
EOF
`
}
function check_extents_used # (threshold)
{
typeset THRESHOLD=$1
pLinha=`svrmgrl <<EOF | grep $DB_NAME
connect $p_v1/$p_v2
select SEGMENT_NAME Segment,
SEGMENT_TYPE type,
to_char((EXTENTS/MAX_EXTENTS *100), '99' ) Used,
'$SEV',
'$DB_NAME'
from DBA_SEGMENTS
where (EXTENTS/MAX_EXTENTS *100) > $THRESHOLD
and SEGMENT_TYPE <> 'CACHE'
/
EOF
`
}
function check_exception # (exception_type exception_name exception_value exception_sev pMessage)
{
typeset E_TYPE=$1; shift
EX_NAME=$1; shift
EX_VAL=$1; shift
EX_SEV=$1; shift
pMessage=""
case $E_TYPE in
ts_used_space)
while [ $# != 0 ]
do
typeset OBJ_NAME=$1; shift
typeset OBJ_VAL=$1; shift
typeset OBJ_SEV=$1; shift
typeset OBJ_DB=$1; shift
if [ $EX_NAME = $OBJ_NAME ]; then
if [ $OBJ_VAL -gt $EX_VAL ]; then
pMessage="$pMessage $OBJ_NAME $OBJ_VAL $EX_SEV $OBJ_DB"
fi
else
pMessage="$pMessage $OBJ_NAME $OBJ_VAL $OBJ_SEV $OBJ_DB"
fi
done
;;
extent_used)
while [ $# != 0 ]
do
typeset OBJ_NAME=$1; shift
typeset OBJ_TYPE=$1; shift
typeset OBJ_VAL=$1; shift
typeset OBJ_SEV=$1; shift
typeset OBJ_DB=$1; shift
if [ $EX_NAME = $OBJ_NAME ]; then
if [ $OBJ_VAL -gt $EX_VAL ]; then
pMessage="$pMessage $OBJ_NAME $OBJ_TYPE $OBJ_VAL $EX_SEV $OBJ_DB"
fi
else
pMessage="$pMessage $OBJ_NAME $OBJ_TYPE $OBJ_VAL $OBJ_SEV $OBJ_DB"
fi
done
;;
log_space)
while [ $# != 0 ]
do
typeset OBJ_NAME=$1; shift
typeset OBJ_DEV=$1; shift
typeset OBJ_VAL=$1; shift
typeset OBJ_SEV=$1; shift
typeset OBJ_DB=$1; shift
if [ $EX_NAME = $OBJ_NAME ]; then
if [ $OBJ_VAL -gt $EX_VAL ]; then
pMessage="$pMessage $OBJ_NAME $OBJ_DEV $OBJ_VAL $EX_SEV $OBJ_DB"
fi
else
pMessage="$pMessage $OBJ_NAME $OBJ_DEV $OBJ_VAL $OBJ_SEV $OBJ_DB"
fi
done
;;
esac
}
function get_path # (log db_name )
{
LOG_TYPE=$1; shift
DB_NAME=$1
LOGPATH="Not found"
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
if [ "`echo $LINE | awk -F: '{print $1}' -`" = $DB_NAME ] ; then
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
ORACLE_SID=""
fi
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
case $LOG_TYPE in
alert|bdump)
DFILE=background_dump_dest
;;
cdump)
DFILE=core_dump_dest
;;
udump)
DFILE=user_dump_dest
;;
arch)
DFILE=log_archive_dest
;;
esac
LOGPATH="`grep $DFILE $PFILE | awk -F= '{print $2}' -`"
if [ "$LOGPATH" = "" ] ; then
grep -v ^# ${PFILE} | grep -i ifile | while read par1
do
iFile=`echo $par1 | awk -F= '{print $2}'`
LOGPATH="`grep -v ^# $iFile | grep $DFILE | awk -F= '{print $2}' -`"
done
elif [ "$LOGPATH" = "" ] ; then
LOGPATH="${ORACLE_HOME}/rdbms/log" #default
fi
if [ "$LOG_TYPE" = "alert" ] ; then
LOGPATH="${LOGPATH}/alert_${ORACLE_SID}.log"
fi
fi
;;
esac
done
}
function check_disk_space # (db_name value severity)
{
DB_NAME=$1; shift
DF_VAL=$1; shift
DF_SEV=$1;
# Check filesystem space where bdump is located
get_path bdump $DB_NAME
if [ "$LOGPATH" != "Not found" ] ; then
df -k ${LOGPATH} 2>/dev/null | grep -v Filesystem | read FSD KB USD AVA UTIL FS
UTIL=`echo $UTIL | cut -d% -f1`
if [ $UTIL -gt $DF_VAL ] ; then
pLinha="BDUMP $FS $UTIL $DF_SEV $DB_NAME"
fi
else
pLinha=""
fi
# Check filesystem space where cdump is located
get_path cdump $DB_NAME
if [ "$LOGPATH" != "Not found" ] ; then
df -k ${LOGPATH} 2>/dev/null | grep -v Filesystem | read FSD KB USD AVA UTIL FS
UTIL=`echo $UTIL | cut -d% -f1`
if [ $UTIL -gt $DF_VAL ] ; then
pLinha="$pLinha CDUMP $FS $UTIL $DF_SEV $DB_NAME"
fi
else
pLinha=""
fi
# Check filesystem space where cdump is located
get_path udump $DB_NAME
if [ "$LOGPATH" != "Not found" ] ; then
df -k ${LOGPATH} 2>/dev/null | grep -v Filesystem | read FSD KB USD AVA UTIL FS
UTIL=`echo $UTIL | cut -d% -f1`
if [ $UTIL -gt $DF_VAL ] ; then
pLinha="$pLinha UDUMP $FS $UTIL $DF_SEV $DB_NAME"
fi
else
pLinha=""
fi
# Check filesystem space where cdump is located
get_path arch $DB_NAME
if [ "$LOGPATH" != "Not found" ] ; then
df -k ${LOGPATH} 2>/dev/null | grep -v Filesystem | read FSD KB USD AVA UTIL FS
UTIL=`echo $UTIL | cut -d% -f1`
if [ "$UTIL" -gt $DF_VAL ] ; then
pLinha="$pLinha ARCHIVE $FS $UTIL $DF_SEV $DB_NAME"
fi
else
pLinha=""
fi
}
function check_listener_status # (db_name)
{
v_count=`lsnrctl status | grep $DB_NAME | grep -c "service handler(s)"`
if [ $v_count -eq 0 ]; then
Message "Critical" "Database" "Listener" "Listener of database $DB_NAME is not running"
fi
}
function check_db_connectivity # (db_name)
{
svrmgrl <<EOF | grep OPEN | read pStatus
connect $p_v1/$p_v2
select status from v\$instance
/
EOF
if [ "$pStatus" != "OPEN" ]
then
Message "Critical" "Database" "Instance" "Connection failed with instance $DB_NAME and $p_v1/*******"
fi
}
function check_lock_pending # (threshold)
{
THRESHOLD=$1
svrmgrl <<EOF | grep $DB_NAME | read v_session DB
connect $p_v1/$p_v2
select count(*) ,
'$DB_NAME'
from v\$session
where username is not null
/
EOF
svrmgrl <<EOF | grep $DB_NAME | read LVAL DB
connect $p_v1/$p_v2
select to_char((count(*)/10) * 100, '99' ) ,
'$DB_NAME'
from v\$session
where lockwait is not null
/
EOF
if [ $LVAL -gt $THRESHOLD ] ; then
pLinha="Lock $LVAL $SEV $DB_NAME"
else
pLinha=""
fi
}
function check_rollback_contention # (threshold)
{
THRESHOLD=$1
pLinha=`svrmgrl <<EOF | grep $DB_NAME
connect $p_v1/$p_v2
select name,
to_char( (waits/gets)*100 , '90.99' ) ,
'$SEV',
'$DB_NAME'
from v\\$rollstat a, v\\$rollname b
where a.usn = b.usn
and ((waits/gets) *100) > $THRESHOLD
/
EOF
`
}
function send_message
{
if [ "$pTS_Message" != "" ] ; then
build_message ts_used_space $pTS_Message
fi
if [ "$pET_Message" != "" ] ; then
build_message extent_used $pET_Message
fi
if [ "$pLOG_Message" != "" ] ; then
build_message log_space $pLOG_Message
fi
if [ "$pLOCK_Message" != "" ] ; then
build_message lock_pending $pLOCK_Message
fi
if [ "$pROLL_Message" != "" ] ; then
build_message roll_contention $pROLL_Message
fi
}
###############################################################
#
# Main #
if [ $# -eq 1 ] && [ $1 = '-l' ]; then
p_type=$1
elif [ $# -eq 2 ] && [ $1 = '-d' ]; then
p_type=$1
p_key=$2
else
echo "Execution failure - Usage: dbmon -l| -d key"
exit
fi
if [ ! -r $CONFILE ]
then
Message "warning" "ITO" "dbmon" "$MonName::get_conf_info() The configuration file $CONFILE was not found or is unreadable"
elif [ ! -f /tmp/dbmon.lock ]
then
pTS_Message=""; pET_Message=""; pLOG_Message=""; pLOCK_Message=""; pROLL_Message=""
case $p_type in
-d)
#-- Monitor objects for databases in config file
grep -v "^#" ${CONFILE} | while read par1 par2
do
pLinha=""
case $par1 in
DATABASE)
send_message pTS_Message=""; pET_Message=""; pLOG_Message=""; pLOCK_Message=""; pROLL_Message="" DB_NAME=$par2
p_v1=`crypt $p_key < ${HOME}/.dbx | grep $DB_NAME | cut -f2`
p_v2=`crypt $p_key < ${HOME}/.dbx | grep $DB_NAME | cut -f3`
ORACLE_SID=$DB_NAME; export ORACLE_SID
check_db_connectivity $DB_NAME
check_listener_status $DB_NAME
;;
TABLESPACE)
echo $par2 | read TYPE NAME VAL SEV
if [ "$NAME" = "DEFAULT" ] ; then
check_ts_used_space $VAL
pTS_Message=$pLinha
#debug
#echo ---------------------------------------------
#build_message ts_used_space $pTS_Message
#echo ---------------------------------------------
else
check_exception ts_used_space $NAME $VAL $SEV $pTS_Message
pTS_Message=$pMessage
fi
;;
SEGMENT)
echo $par2 | read TYPE NAME VAL SEV
if [ "$NAME" = "DEFAULT" ] ; then
check_extents_used $VAL
pET_Message=$pLinha
#debug
#echo ---------------------------------------------
#build_message extent_used $pET_Message
#echo ---------------------------------------------
else
check_exception extent_used $NAME $VAL $SEV $pET_Message
pET_Message=$pMessage
fi
;;
LOG)
echo $par2 | read TYPE NAME VAL SEV
if [ "$NAME" = "DEFAULT" ] ; then
check_disk_space $DB_NAME $VAL $SEV
pLOG_Message=$pLinha
#debug
#echo ---------------------------------------------
#build_message log_space $pLOG_Message
#echo ---------------------------------------------
else
check_exception log_space $NAME $VAL $SEV $pLOG_Message
pLOG_Message=$pMessage
fi
;;
LOCK)
echo $par2 | read TYPE NAME VAL SEV
if [ "$NAME" = "DEFAULT" ] ; then
check_lock_pending $VAL
pLOCK_Message=$pLinha
#debug
#echo ---------------------------------------------
#build_message lock_pending $pLOCK_Message
#echo ---------------------------------------------
fi
;;
ROLLBACK)
echo $par2 | read TYPE NAME VAL SEV
if [ "$NAME" = "DEFAULT" ] ; then
check_rollback_contention $VAL
pROLL_Message=$pLinha
#debug
#echo ---------------------------------------------
#build_message roll_contention $pROLL_Message
#echo ---------------------------------------------
fi
;;
esac
done
send_message
;;
-l)
#-- Find the alert.log path for databases in config file
ALERTLOG=""
LOGPATH=""
grep -v "^#" ${CONFILE} | while read par1 par2
do
if [ $par1 = "DATABASE" ] ; then
LOGPATH=""
DB_NAME=$par2
get_path alert $DB_NAME
if [ "$LOGPATH" != "Not found" ] ; then
ALERTLOG="${ALERTLOG} ${LOGPATH}"
fi
fi
done
#return the logs path/name
echo $ALERTLOG
;;
esac
fi
exit 0
vlan52
The end of wisdom is freedom. The end of culture is perfection. The end of
education is character. The end of knowledge is love.
The DBSPI solution is great, but, is kind of expensive, at least for our budget right now.
Hope this helps,
vlan52
-----------------------------------------
#!/bin/ksh
###############################################################
#
# File: dbmon
# Propose: database monitor for ito
# Dependencies: dbmon.conf
# Created: 26/May/00 - Marcio Tadashi Sano
# Last review: 01/Jun/00 - cgarcia@optiglobe.com.ar
#
# Usage: dbmon -l|-d key
# -l Log file monitoring
# -d database objects monitoring
#
###############################################################
#
# metrics
#
# . Tablespaces with used space more than n% of the total
# . Segments with used extents more than n% of the max_extents
###############################################################
#
# variables
#
typeset MonName=`basename $0`
OPC_MON=/opt/OV/bin/OpC/opcmon
OPC_MSG=/opt/OV/bin/OpC/opcmsg
#ORATAB=/var/opt/oracle/oratab
ORATAB=/etc/oratab
#CONFILE=/export/home/oracle/scripts/dbmon.conf
CONFILE=/var/opt/OV/conf/OpC/dbmon.conf
###############################################################
#
# Send alert to ITO
#
function Message # (severity msggrp process message)
{
typeset severity=$1; shift
typeset msggrp=$1; shift
typeset process=$1; shift
${OPC_MSG} application=Oracle object=$process msg_grp=$msggrp severity=$severity msg_text="$*"
}
function build_message # (type Message)
{
M_TYPE=$1; shift
case $M_TYPE in
ts_used_space)
while [ $# != 0 ]
do
typeset TS=$1; shift
typeset USED=$1; shift
typeset TS_SEV=$1; shift
typeset TS_DB=$1; shift
Message $TS_SEV "Database" "Tablespace" "Oracle: Tablespace $TS has $USED% of used space in database $TS_DB"
done
;;
extent_used)
while [ $# != 0 ]
do
typeset SEG=$1; shift
typeset TP=$1; shift
typeset USED=$1; shift
typeset ET_SEV=$1; shift
typeset ET_DB=$1; shift
Message $ET_SEV "Database" "Segment" "Oracle: Segment $SEG($TP) has $USED% of used extents in database $ET_DB"
done
;;
log_space)
while [ $# != 0 ]
do
typeset LOG=$1; shift
typeset DEV=$1; shift
typeset USED=$1; shift
typeset LOG_SEV=$1; shift
typeset LOG_DB=$1; shift
Message $LOG_SEV "Database" "Diskspace" "Oracle: The $LOG directory of database $LOG_DB is in filesystem $DEV with $USED% of used space"
done
;;
lock_pending)
while [ $# != 0 ]
do
typeset LOCK=$1; shift
typeset LVAL=$1; shift
typeset LOCK_SEV=$1; shift
typeset LOCK_DB=$1; shift
Message $LOCK_SEV "Database" "Lock" "Oracle: The database $LOCK_DB has $LVAL% of locked sessions"
done
;;
roll_contention)
while [ $# != 0 ]
do
typeset ROLL=$1; shift
typeset VAL=$1; shift
typeset ROLL_SEV=$1; shift
typeset ROLL_DB=$1; shift
Message $ROLL_SEV "Database" "Rollback" "Oracle: The $ROLL rollback segment of database $ROLL_DB has $VAL% of contention"
done
;;
esac
}
function check_ts_used_space # (threshold)
{
typeset THRESHOLD=$1
pLinha=`svrmgrl <<EOF | grep $DB_NAME
connect $p_v1/$p_v2
select a.tablespace_name Tablespace,
to_char((1-sum(a.bytes/b.bytes)) * 100, '99' ) Used,
'$SEV',
'$DB_NAME'
from dba_free_space a,
dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name having (1-sum(a.bytes/b.bytes)) * 100 > $THRESHOLD
/
EOF
`
}
function check_extents_used # (threshold)
{
typeset THRESHOLD=$1
pLinha=`svrmgrl <<EOF | grep $DB_NAME
connect $p_v1/$p_v2
select SEGMENT_NAME Segment,
SEGMENT_TYPE type,
to_char((EXTENTS/MAX_EXTENTS *100), '99' ) Used,
'$SEV',
'$DB_NAME'
from DBA_SEGMENTS
where (EXTENTS/MAX_EXTENTS *100) > $THRESHOLD
and SEGMENT_TYPE <> 'CACHE'
/
EOF
`
}
function check_exception # (exception_type exception_name exception_value exception_sev pMessage)
{
typeset E_TYPE=$1; shift
EX_NAME=$1; shift
EX_VAL=$1; shift
EX_SEV=$1; shift
pMessage=""
case $E_TYPE in
ts_used_space)
while [ $# != 0 ]
do
typeset OBJ_NAME=$1; shift
typeset OBJ_VAL=$1; shift
typeset OBJ_SEV=$1; shift
typeset OBJ_DB=$1; shift
if [ $EX_NAME = $OBJ_NAME ]; then
if [ $OBJ_VAL -gt $EX_VAL ]; then
pMessage="$pMessage $OBJ_NAME $OBJ_VAL $EX_SEV $OBJ_DB"
fi
else
pMessage="$pMessage $OBJ_NAME $OBJ_VAL $OBJ_SEV $OBJ_DB"
fi
done
;;
extent_used)
while [ $# != 0 ]
do
typeset OBJ_NAME=$1; shift
typeset OBJ_TYPE=$1; shift
typeset OBJ_VAL=$1; shift
typeset OBJ_SEV=$1; shift
typeset OBJ_DB=$1; shift
if [ $EX_NAME = $OBJ_NAME ]; then
if [ $OBJ_VAL -gt $EX_VAL ]; then
pMessage="$pMessage $OBJ_NAME $OBJ_TYPE $OBJ_VAL $EX_SEV $OBJ_DB"
fi
else
pMessage="$pMessage $OBJ_NAME $OBJ_TYPE $OBJ_VAL $OBJ_SEV $OBJ_DB"
fi
done
;;
log_space)
while [ $# != 0 ]
do
typeset OBJ_NAME=$1; shift
typeset OBJ_DEV=$1; shift
typeset OBJ_VAL=$1; shift
typeset OBJ_SEV=$1; shift
typeset OBJ_DB=$1; shift
if [ $EX_NAME = $OBJ_NAME ]; then
if [ $OBJ_VAL -gt $EX_VAL ]; then
pMessage="$pMessage $OBJ_NAME $OBJ_DEV $OBJ_VAL $EX_SEV $OBJ_DB"
fi
else
pMessage="$pMessage $OBJ_NAME $OBJ_DEV $OBJ_VAL $OBJ_SEV $OBJ_DB"
fi
done
;;
esac
}
function get_path # (log db_name )
{
LOG_TYPE=$1; shift
DB_NAME=$1
LOGPATH="Not found"
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
if [ "`echo $LINE | awk -F: '{print $1}' -`" = $DB_NAME ] ; then
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
ORACLE_SID=""
fi
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
case $LOG_TYPE in
alert|bdump)
DFILE=background_dump_dest
;;
cdump)
DFILE=core_dump_dest
;;
udump)
DFILE=user_dump_dest
;;
arch)
DFILE=log_archive_dest
;;
esac
LOGPATH="`grep $DFILE $PFILE | awk -F= '{print $2}' -`"
if [ "$LOGPATH" = "" ] ; then
grep -v ^# ${PFILE} | grep -i ifile | while read par1
do
iFile=`echo $par1 | awk -F= '{print $2}'`
LOGPATH="`grep -v ^# $iFile | grep $DFILE | awk -F= '{print $2}' -`"
done
elif [ "$LOGPATH" = "" ] ; then
LOGPATH="${ORACLE_HOME}/rdbms/log" #default
fi
if [ "$LOG_TYPE" = "alert" ] ; then
LOGPATH="${LOGPATH}/alert_${ORACLE_SID}.log"
fi
fi
;;
esac
done
}
function check_disk_space # (db_name value severity)
{
DB_NAME=$1; shift
DF_VAL=$1; shift
DF_SEV=$1;
# Check filesystem space where bdump is located
get_path bdump $DB_NAME
if [ "$LOGPATH" != "Not found" ] ; then
df -k ${LOGPATH} 2>/dev/null | grep -v Filesystem | read FSD KB USD AVA UTIL FS
UTIL=`echo $UTIL | cut -d% -f1`
if [ $UTIL -gt $DF_VAL ] ; then
pLinha="BDUMP $FS $UTIL $DF_SEV $DB_NAME"
fi
else
pLinha=""
fi
# Check filesystem space where cdump is located
get_path cdump $DB_NAME
if [ "$LOGPATH" != "Not found" ] ; then
df -k ${LOGPATH} 2>/dev/null | grep -v Filesystem | read FSD KB USD AVA UTIL FS
UTIL=`echo $UTIL | cut -d% -f1`
if [ $UTIL -gt $DF_VAL ] ; then
pLinha="$pLinha CDUMP $FS $UTIL $DF_SEV $DB_NAME"
fi
else
pLinha=""
fi
# Check filesystem space where cdump is located
get_path udump $DB_NAME
if [ "$LOGPATH" != "Not found" ] ; then
df -k ${LOGPATH} 2>/dev/null | grep -v Filesystem | read FSD KB USD AVA UTIL FS
UTIL=`echo $UTIL | cut -d% -f1`
if [ $UTIL -gt $DF_VAL ] ; then
pLinha="$pLinha UDUMP $FS $UTIL $DF_SEV $DB_NAME"
fi
else
pLinha=""
fi
# Check filesystem space where cdump is located
get_path arch $DB_NAME
if [ "$LOGPATH" != "Not found" ] ; then
df -k ${LOGPATH} 2>/dev/null | grep -v Filesystem | read FSD KB USD AVA UTIL FS
UTIL=`echo $UTIL | cut -d% -f1`
if [ "$UTIL" -gt $DF_VAL ] ; then
pLinha="$pLinha ARCHIVE $FS $UTIL $DF_SEV $DB_NAME"
fi
else
pLinha=""
fi
}
function check_listener_status # (db_name)
{
v_count=`lsnrctl status | grep $DB_NAME | grep -c "service handler(s)"`
if [ $v_count -eq 0 ]; then
Message "Critical" "Database" "Listener" "Listener of database $DB_NAME is not running"
fi
}
function check_db_connectivity # (db_name)
{
svrmgrl <<EOF | grep OPEN | read pStatus
connect $p_v1/$p_v2
select status from v\$instance
/
EOF
if [ "$pStatus" != "OPEN" ]
then
Message "Critical" "Database" "Instance" "Connection failed with instance $DB_NAME and $p_v1/*******"
fi
}
function check_lock_pending # (threshold)
{
THRESHOLD=$1
svrmgrl <<EOF | grep $DB_NAME | read v_session DB
connect $p_v1/$p_v2
select count(*) ,
'$DB_NAME'
from v\$session
where username is not null
/
EOF
svrmgrl <<EOF | grep $DB_NAME | read LVAL DB
connect $p_v1/$p_v2
select to_char((count(*)/10) * 100, '99' ) ,
'$DB_NAME'
from v\$session
where lockwait is not null
/
EOF
if [ $LVAL -gt $THRESHOLD ] ; then
pLinha="Lock $LVAL $SEV $DB_NAME"
else
pLinha=""
fi
}
function check_rollback_contention # (threshold)
{
THRESHOLD=$1
pLinha=`svrmgrl <<EOF | grep $DB_NAME
connect $p_v1/$p_v2
select name,
to_char( (waits/gets)*100 , '90.99' ) ,
'$SEV',
'$DB_NAME'
from v\\$rollstat a, v\\$rollname b
where a.usn = b.usn
and ((waits/gets) *100) > $THRESHOLD
/
EOF
`
}
function send_message
{
if [ "$pTS_Message" != "" ] ; then
build_message ts_used_space $pTS_Message
fi
if [ "$pET_Message" != "" ] ; then
build_message extent_used $pET_Message
fi
if [ "$pLOG_Message" != "" ] ; then
build_message log_space $pLOG_Message
fi
if [ "$pLOCK_Message" != "" ] ; then
build_message lock_pending $pLOCK_Message
fi
if [ "$pROLL_Message" != "" ] ; then
build_message roll_contention $pROLL_Message
fi
}
###############################################################
#
# Main #
if [ $# -eq 1 ] && [ $1 = '-l' ]; then
p_type=$1
elif [ $# -eq 2 ] && [ $1 = '-d' ]; then
p_type=$1
p_key=$2
else
echo "Execution failure - Usage: dbmon -l| -d key"
exit
fi
if [ ! -r $CONFILE ]
then
Message "warning" "ITO" "dbmon" "$MonName::get_conf_info() The configuration file $CONFILE was not found or is unreadable"
elif [ ! -f /tmp/dbmon.lock ]
then
pTS_Message=""; pET_Message=""; pLOG_Message=""; pLOCK_Message=""; pROLL_Message=""
case $p_type in
-d)
#-- Monitor objects for databases in config file
grep -v "^#" ${CONFILE} | while read par1 par2
do
pLinha=""
case $par1 in
DATABASE)
send_message pTS_Message=""; pET_Message=""; pLOG_Message=""; pLOCK_Message=""; pROLL_Message="" DB_NAME=$par2
p_v1=`crypt $p_key < ${HOME}/.dbx | grep $DB_NAME | cut -f2`
p_v2=`crypt $p_key < ${HOME}/.dbx | grep $DB_NAME | cut -f3`
ORACLE_SID=$DB_NAME; export ORACLE_SID
check_db_connectivity $DB_NAME
check_listener_status $DB_NAME
;;
TABLESPACE)
echo $par2 | read TYPE NAME VAL SEV
if [ "$NAME" = "DEFAULT" ] ; then
check_ts_used_space $VAL
pTS_Message=$pLinha
#debug
#echo ---------------------------------------------
#build_message ts_used_space $pTS_Message
#echo ---------------------------------------------
else
check_exception ts_used_space $NAME $VAL $SEV $pTS_Message
pTS_Message=$pMessage
fi
;;
SEGMENT)
echo $par2 | read TYPE NAME VAL SEV
if [ "$NAME" = "DEFAULT" ] ; then
check_extents_used $VAL
pET_Message=$pLinha
#debug
#echo ---------------------------------------------
#build_message extent_used $pET_Message
#echo ---------------------------------------------
else
check_exception extent_used $NAME $VAL $SEV $pET_Message
pET_Message=$pMessage
fi
;;
LOG)
echo $par2 | read TYPE NAME VAL SEV
if [ "$NAME" = "DEFAULT" ] ; then
check_disk_space $DB_NAME $VAL $SEV
pLOG_Message=$pLinha
#debug
#echo ---------------------------------------------
#build_message log_space $pLOG_Message
#echo ---------------------------------------------
else
check_exception log_space $NAME $VAL $SEV $pLOG_Message
pLOG_Message=$pMessage
fi
;;
LOCK)
echo $par2 | read TYPE NAME VAL SEV
if [ "$NAME" = "DEFAULT" ] ; then
check_lock_pending $VAL
pLOCK_Message=$pLinha
#debug
#echo ---------------------------------------------
#build_message lock_pending $pLOCK_Message
#echo ---------------------------------------------
fi
;;
ROLLBACK)
echo $par2 | read TYPE NAME VAL SEV
if [ "$NAME" = "DEFAULT" ] ; then
check_rollback_contention $VAL
pROLL_Message=$pLinha
#debug
#echo ---------------------------------------------
#build_message roll_contention $pROLL_Message
#echo ---------------------------------------------
fi
;;
esac
done
send_message
;;
-l)
#-- Find the alert.log path for databases in config file
ALERTLOG=""
LOGPATH=""
grep -v "^#" ${CONFILE} | while read par1 par2
do
if [ $par1 = "DATABASE" ] ; then
LOGPATH=""
DB_NAME=$par2
get_path alert $DB_NAME
if [ "$LOGPATH" != "Not found" ] ; then
ALERTLOG="${ALERTLOG} ${LOGPATH}"
fi
fi
done
#return the logs path/name
echo $ALERTLOG
;;
esac
fi
exit 0
vlan52
The end of wisdom is freedom. The end of culture is perfection. The end of
education is character. The end of knowledge is love.
And here goes the conf file:
###############################################################
#
# File: dbmon.conf
# Propose: Config file for dbmon monitor
# Created: 26/05/00 - Marcio
# Last review: 30/05/00 - cgarcia@optiglobe.com.ar
#
#
#--------------- Database Name --------------------------------
# Must have the same name in oratab
DATABASE optg01
#
#-- Tablespaces: used space thresholds
#OBJECT TYPE NAME VALUE(%) SEVER.
#
TABLESPACE USED DEFAULT 90 Major
#TABLESPACE USED OPC_4 92 Critical
#TABLESPACE USED OPC_INDEX2 91 Minor
#TABLESPACE USED RBS1 94 Normal
#TABLESPACE USED TEMP 91 Warning
#
#
#-- Segments: used extents thresholds
#OBJECT TYPE NAME VALUE(%) SEVER.
#
SEGMENT USED DEFAULT 80 Major
#SEGMENT USED R0 9 Major
#SEGMENT USED R01 3 Warning
#SEGMENT USED R02 2 Warning
#
#
#-- Logs: used disk space in logs file systems
#OBJECT TYPE NAME VALUE(%) SEVER.
#
LOG USED DEFAULT 80 Warning
#LOG USED BDUMP 90 Normal
#LOG USED ARCHIVE 20 Critical
#
#
#-- Lock: Session waiting for release of a lock
#OBJECT TYPE NAME VALUE(%) SEVER.
#
LOCK PENDING DEFAULT 5 Warning
#
#
#-- Rollback: Rollback segments waits to gets
#OBJECT TYPE NAME VALUE(%) SEVER.
#
ROLLBACK WAITS DEFAULT 0.7 Warning
#
#
#
#
#----------- Database Name ----------------------------------
# Must have the same name in oratab
#DATABASE rmdy1
#
#-- Tablespaces: used space thresholds
#OBJECT TYPE NAME VALUE(%) SEVER.
#
#TABLESPACE USED DEFAULT 90 Major
#
#-- Segments: used extents thresholds
#OBJECT TYPE NAME VALUE(%) SEVER.
#
#SEGMENT USED DEFAULT 90 Major
#
#-- Logs: used disk space in logs file systems
#OBJECT TYPE NAME VALUE(%) SEVER.
#
#LOG USED DEFAULT 90 Warning
vlan52
The end of wisdom is freedom. The end of culture is perfection. The end of
education is character. The end of knowledge is love.
###############################################################
#
# File: dbmon.conf
# Propose: Config file for dbmon monitor
# Created: 26/05/00 - Marcio
# Last review: 30/05/00 - cgarcia@optiglobe.com.ar
#
#
#--------------- Database Name --------------------------------
# Must have the same name in oratab
DATABASE optg01
#
#-- Tablespaces: used space thresholds
#OBJECT TYPE NAME VALUE(%) SEVER.
#
TABLESPACE USED DEFAULT 90 Major
#TABLESPACE USED OPC_4 92 Critical
#TABLESPACE USED OPC_INDEX2 91 Minor
#TABLESPACE USED RBS1 94 Normal
#TABLESPACE USED TEMP 91 Warning
#
#
#-- Segments: used extents thresholds
#OBJECT TYPE NAME VALUE(%) SEVER.
#
SEGMENT USED DEFAULT 80 Major
#SEGMENT USED R0 9 Major
#SEGMENT USED R01 3 Warning
#SEGMENT USED R02 2 Warning
#
#
#-- Logs: used disk space in logs file systems
#OBJECT TYPE NAME VALUE(%) SEVER.
#
LOG USED DEFAULT 80 Warning
#LOG USED BDUMP 90 Normal
#LOG USED ARCHIVE 20 Critical
#
#
#-- Lock: Session waiting for release of a lock
#OBJECT TYPE NAME VALUE(%) SEVER.
#
LOCK PENDING DEFAULT 5 Warning
#
#
#-- Rollback: Rollback segments waits to gets
#OBJECT TYPE NAME VALUE(%) SEVER.
#
ROLLBACK WAITS DEFAULT 0.7 Warning
#
#
#
#
#----------- Database Name ----------------------------------
# Must have the same name in oratab
#DATABASE rmdy1
#
#-- Tablespaces: used space thresholds
#OBJECT TYPE NAME VALUE(%) SEVER.
#
#TABLESPACE USED DEFAULT 90 Major
#
#-- Segments: used extents thresholds
#OBJECT TYPE NAME VALUE(%) SEVER.
#
#SEGMENT USED DEFAULT 90 Major
#
#-- Logs: used disk space in logs file systems
#OBJECT TYPE NAME VALUE(%) SEVER.
#
#LOG USED DEFAULT 90 Warning
vlan52
The end of wisdom is freedom. The end of culture is perfection. The end of
education is character. The end of knowledge is love.
- Thread starter
- #12
- Status
Similar threads
- Locked
- Question
- Locked
- Question