[Orca-users] Re: Oracle Stats
Adam Levin
alevin at audible.com
Fri Aug 23 05:43:08 PDT 2002
On Thu, 22 Aug 2002, markzander61 wrote:
> We would like to start using Orca to monitor our Oracle instances. I
> have looked through the archives here and at orca-discuss and have
> seen people asking how to do this, but haven't seen anything the
> really tells how.
Our database guy wrote a little shell script that writes out some DB
metrics to a log file. I then use that log file in a separate Orca group
called oracle. It contains stuff like user counts, requests, scans, file
i/o, blocking locks, redo waits, fetches, etc. It looks like it's a
continuous counter, but I imagine that with a little script massaging and
some state control, you could do the numbers as incremental rather than
cumulative.
Scripts are attached.
The cron call is:
## ORCA Database Metrics every 5 minutes, 7x24
0,5,10,15,20,25,30,35,40,45,50,55 * * * * /opt/oracle/admin/OASIS/unix_scripts/db_metrics.sh
The orcallator.cfg stuff is as follows:
group oracle {
find_files /opt/audible/logs/oracle/userfiles/db_graph.log.\d{4}\d{2}\d{2}
column_description first_line
date_source column_name timestamp
interval 300
reopen 1
}
#users # of users
plot {
title %g Oracle Users
source oracle
data users
legend Users
y_legend Users
}
#locks # Blocking Locks
plot {
title %g Blocking Locks
source oracle
data locks
legend Locks
y_legend Blocking Locks
}
#file_io
plot {
title %g File I/O
source oracle
data file_io
legend File I/O
y_legend File I/O
}
#redo_waits
plot {
title %g Redo Waits
source oracle
data redo_waits
legend Redo Waits
y_legend Redo Waits
}
#retries
plot {
title %g Retries
source oracle
data retries
legend Retries
y_legend Retries
}
#fetches
plot {
title %g Fetches
source oracle
data fetches
legend Fetches
y_legend Fetches
}
#scans
plot {
title %g Scans
source oracle
data scans
legend Scans
y_legend Scans
}
#cursors
plot {
title %g Cursors
source oracle
data cursors
legend Cursors
y_legend Cursors
}
#reloads
plot {
title %g Reloads
source oracle
data reloads
legend Reloads
y_legend Reloads
}
#data_dict
plot {
title %g Data Dictionary
source oracle
data data_dict
legend Data Dictionary
y_legend Data Dictionary
}
#cache_hit
plot {
title %g Cache Hit
source oracle
data cache_hit
legend Cache Hit
y_legend Cache Hit
}
#buffers
plot {
title %g Buffers
source oracle
data buffers
legend Buffers
y_legend Buffers
}
#waits
plot {
title %g Waits
source oracle
data waits
legend Waits
y_legend Waits
}
#lru_hit
plot {
title %g LRU Hits
source oracle
data lru_hit
legend LRU Hits
y_legend LRU Hits
}
#wait_get
plot {
title %g Wait Get
source oracle
data wait_get
legend Wait Get
y_legend Wait Get
}
#undo_waits
plot {
title %g Undo Waits
source oracle
data undo_waits
legend Undo Waits
y_legend Undo Waits
}
#requests
plot {
title %g Requests
source oracle
data requests
legend Requests
y_legend Requests
}
Adam Levin, Senior Unix Systems Administrator | http://www.audible.com/
Audible, Inc.
Wayne, NJ, 07470 All tribal myths are true, for a given
973-837-2797 value of "true".
-------------- next part --------------
#!/bin/ksh
# Transform Input File Column to Output File Row
# 010510 YXZ Original Script
# 010510 WRL Add Error Processing
if [[ ${#} -ne 2 ]]
then
echo "usage: ${0}: input_file output_file"
exit 1
fi
line=""
for field in `cat $1`
do
case "${field}" in
"") ;;
*) case "${line}" in
"") line="$field"
;;
*) line="$line $field"
;;
esac
;;
esac
done
# Put line to output file
echo $line > $2
-------------- next part --------------
#!/bin/ksh
# Collect and Report Database Metrics
# 010510 WRL Original script
# 010523 WRL Use four digit year
# 010523 WRL Include once a day header logic
DB_MON=db_mon/dbmon
UNIX_SCRIPTS=/opt/oracle/admin/OASIS/unix_scripts
SQL_SCRIPTS=/opt/oracle/admin/OASIS/sql_scripts
OUT_FILES=/opt/oracle/admin/OASIS/userfiles
DATE_TIME=`/usr/local/bin/date +%Y%m%d`
T_SECS=`date '+%Y-%m-%d'`
SECONDS_TIME=`/usr/local/bin/mktime -F '%t' -D ${T_SECS}`
LCK_FILE=/tmp/.$(basename $0)_LCK
trap "rm -f ${LCK_FILE}; exit 0" 1 2 15
typeset -i curr_hour
typeset -i critical_hour=0
# Once a day at midnight, create a new file with a header as the first record and cleanup old files
curr_hour=$(date +%H)
if (( $curr_hour != $critical_hour ))
then
rm -f ${LCK_FILE}
fi
if [[ ! -f ${LCK_FILE} ]]
then
if (( $curr_hour == $critical_hour ))
then
$UNIX_SCRIPTS/change.sh $OUT_FILES/db_metrics_header.txt $OUT_FILES/db_graph.log.$DATE_TIME
find $OUT_FILES -name "db_graph.log.*" -mtime +6 -exec rm -f {} \;
find $OUT_FILES -name "db_metrics.*" -mtime +0 -exec rm -f {} \;
>${LCK_FILE}
fi
fi
# Create Spool File of Database Metrics
. /opt/oracle/.profile
ORACLE_SID=OASIS
export ORACLE_SID
sqlplus << EOF >$OUT_FILES/db_metrics.out.$DATE_TIME
$DB_MON
SPOOL $OUT_FILES/db_metrics.log.$DATE_TIME
@$SQL_SCRIPTS/db_metrics.sql
SPOOL OFF
EXIT
EOF
# Transform Spool File from Column to Row
egrep -v "^SQL>" $OUT_FILES/db_metrics.log.$DATE_TIME > $OUT_FILES/db_metrics.lst.$DATE_TIME
$UNIX_SCRIPTS/change.sh $OUT_FILES/db_metrics.lst.$DATE_TIME $OUT_FILES/db_graph.log_tmp
# Prefix each line with timestamp date +%s
sed -e "s/^/${SECONDS_TIME} /" <${OUT_FILES}/db_graph.log_tmp >>${OUT_FILES}/db_graph.log.$DATE_TIME
# Cleanup old files
# cat $OUT_FILES/db_graph.log | /bin/mailx -s "Database Metrics" wleitner at audible.com
# find $OUT_FILES -name "db_graph.log.*" -mtime +7 -exec rm -f {} \;
# find $OUT_FILES -name "db_metrics.*" -mtime +1 -exec rm -f {} \;
rm -f $OUT_FILES/db_graph.log_tmp
More information about the Orca-users
mailing list