Database Down Time Monitor

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!

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.