|
|
|
Here is an utility to capture the Oracle database up time statistics. The database shutdown and startup information are extracted from the alert log files and saved in an oracle table. The scripts provided here will read the alert log file and load information to an Oracle table. You may have many different reports generated using the information from the Oracle table. If the server crashes due to some reason, there will not be any entry in the alert log file. In such cases, you will find two database startup times appearing together. The DBA may need to insert appropriate values into the table for proper reporting. The scripts are tested on Oracle 7.3.3 database alert log files. Modifications required for 7.2.x database. On SUN platforms replace /etc/oratab with /var/opt/oracle/oratab. If you are not following OFA standard, the alert log file locations may be different and such modifications should be applied to the script. Here is how the scripts work. 1. Create Oracle table to save information in database. Identify a database and tablespace to create the table. The column 'down_reason' may be suitably updated by the reason for taking the database down after loading the data. drop table db_down_up_stat; create table db_down_up_stat ( mon_date date, db_name varchar2 (10), host_name varchar2 (10), operation varchar2 (3), omode varchar2 (10), down_reason varchar2 (40), constraint pk_db_down_up_stat primary key (mon_date, db_name) using index tablespace stats_data) tablespace stats_data storage (initial 2M next 2M pctincrease 0 maxextents 249) / 2. updownstat.sh - This script reads the alert log file and looks for the words "Starting" and "Shutting" and extracts the time stamp for the start and shut operation. #!/bin/ksh
#
# Database Down Time Monitor
# Get info from alert.log file
#
# Biju Thomas 07/13/98
#
woutfile=/tmp/statfile1.stat
rm -f $woutfile 2>/dev/null
grep -v "^#" /etc/oratab | cut -f1 -d: | while read SIDNAME
do
for FILENAME in `ls /u00/app/oracle/admin/${SIDNAME}/bdump/alert_${SIDNAME}.*`
do
PREVLINE=`egrep "^Sun|^Mon|^Tue|^Wed|^Thu|^Fri|^Sat" $FILENAME | head -1`
egrep "^Sun|^Mon|^Tue|^Wed|^Thu|^Fri|^Sat|^Starting ORACLE|^Shutting down" $FILENAME | while read CURRLINE
do
case $CURRLINE in
Starting*)
echo "${PREVLINE}|SUP|${SIDNAME}|"`hostname`"|"`echo $CURRLINE | cut -f 2 -d \( | tr -d \)` >> $woutfile
;;
Shutting*)
echo "${PREVLINE}|SDN|${SIDNAME}|"`hostname`"|"`echo $CURRLINE | cut -f 2 -d \( | tr -d \)` >> $woutfile
;;
*) PREVLINE=$CURRLINE
;;
esac
done
done
done
3. allupdown.sh - This script is used to load the statistics collected from various servers into the database. It is assumed that all the statistics files will have an extension .stat. The control file under (4) is used to load the data. #!/bin/ksh
#
# Database Down Time Monitor
# Load Info from various stat files to database
#
# Biju Thomas 07/13/98
#
. /etc/profile
#
# Consolidate all stat files to one data file
woutdir=/tmp/stat
wdatfile=/tmp/updownstat.dat
rm -f $wdatfile 2>/dev/null
for FILENAME in `ls ${woutdir}/*.stat`
do
cat ${FILENAME} >> $wdatfile
done
#
# Load the data file to database
# Load data to MYORACLE database
ORACLE_SID=MYORACLE ## Name of oracle instance to load data here
$ORACLE_HOME/bin/sqlload / control=/scriptdir/updownstat.ctl errors=2000
#
# End of Script
4. updownstat.ctl - The loader control file used to load data. load data infile '/tmp/updownstat.dat' append into table dbstats.db_down_up_stat fields terminated by '|' (mon_date date(24) "Dy Mon DD HH24:MI:SS YYYY", operation, db_name, host_name, omode ) These scripts are provided to give you an idea of how the statistics may be collected by reading the alert log file. Many third party tools are available in the market to capture various performance statistics. This is for the Database Administrators who do not have any such tools.... Let me know if this help you!
|
