Fall 1993 'Select' and ora_soh

From: Jean Anderson <jean_at_seismo.CSS.GOV>
Date: 27 Jan 1994 18:28:12 GMT
Message-ID: <2i913s$rli_at_seismo.CSS.GOV>

Greetings,

The Fall 1993 issue of the International Oracle Users Group 'Select' magazine has an article "Automated State-of-Health Monitoring for UNIX Databases", which includes some sample scripts and code. The IOUG put the distribution on Compuserve and I have received requests to post it here.

So...a (shar) distribution is below and contains the following:

     ora_soh            Front-end shell script that serves as the soh driver;
                        if the check fails, email is sent to the DBA.
     sql_env            Bourne shell script that sets up environment for
                        running the database scripts.
     ora_alert          Bourne shell script that greps for errors in the 
                        Oracle alert log
     ora_cache_hit.sql  PL/SQL script that calculates the cache hit ratio
                        since instance start up and generates an alert if
                        it is below the DBA-defined threshold.
     ora_sun4disk       Bourne shell script that looks for errors in
                        /var/adm/messages (sun4 os specific)
     ersh               Maarten Litmaath's rsh front-end that returns a
                        meaningful exit status when executing remote shell
                        commands.

     ora_dead_proc.pc   PRO*C program that looks for database processes that
                        don't have a corresponding front-end.
     ora_dead_proc.h    header for ora_dead_proc.
     Makefile.33        6.0.33 Makefile for ora_dead_proc.
     Makefile.36        6.0.36 Makefile for ora_dead_proc.
     Makefile.7         7.0.9  Makefile for ora_dead_proc.

I have been running these on SUN 4.1.3 and Oracle 6.0.{33, 36}. I lightly tested some on Oracle 7.0.9 and have not tried anything on Solaris 2. So if anybody gets anything working under Solaris and could repost, I bet Solaris users in netland would appreciate it. :-)

If you would like a copy of the 'Select' issue, please contact Bill Pribyl at bill_at_datacraft.com.

This development was funded by the Nuclear Monitoring Research Office of the Advanced Research Projects Agency (ARPA).

regards,

 -jean
  jean_at_gso.saic.com

-----------------------------< ora_soh.shar >-----------------------------------

To unbundle this shar file into separate files:

1. put a copy of this file in an empty directory
2. delete all the lines from the first through the one that says cut here
3. make this file executable by typing chmod +x this_shar_filename
4. then execute the file by typing its name

-------------cut here-----------------

#! /bin/sh
echo x - Makefile.33
sed 's/^X//' >Makefile.33 << \END-of-Makefile.33

X#
X# ora_dead_proc Makefile for Oracle 6.0.33
X#
X
XPCC	=	pcc
XPCCINC	=	$(ORACLE_HOME)/c/lib
XPCCFLAGS=	include=$(PCCINC) ltype=none ireclen=160 oreclen=160 select_error=no sqlcheck=none
X
XSQLSRCS	=	ora_dead_proc.pc
XOBJS	=	$(SQLSRCS:.pc=.o)
X
XHDRS	=	$(PCCINC)/sqlca.h ora_dead_proc.h
X
XSTDLIBS	=	$(ORACLE_HOME)/rdbms/lib/libsql.a  \
X                $(ORACLE_HOME)/rdbms/lib/osntab.o \
X                $(ORACLE_HOME)/rdbms/lib/libsqlnet.a \
X                $(ORACLE_HOME)/rdbms/lib/libora.a 
X
XPROGRAM		= ora_dead_proc
X
Xora_dead_proc:	Makefile $(HDRS) $(OBJS) $(LIBS)
X		$(LD) $(LDFLAGS) -o ora_dead_proc $(OBJS) $(LIBS) $(STDLIBS)
X
XO		= -g
XINCLUDES	= -I$(PCCINC)
XCFLAGS		= $O $(INCLUDES)
XLDFLAGS		= $O 
XCC		= /usr/5bin/cc
XLD		= $(CC)
XMAKE		= make
X
Xall:		$(PROGRAM)

X

X.SUFFIXES:
X.SUFFIXES:	.o .c .c~ .pc .pc~ .h .h~
X.SUFFIXES:	.1 .1~

X
X.pc.c:
X $(PCC) $(PCCFLAGS) iname=$*.pc
X.pc.o:

X		$(PCC) $(PCCFLAGS) iname=$*.pc
X		$(CC) $(CFLAGS) -c $*.c

END-of-Makefile.33
echo x - Makefile.36
sed 's/^X//' >Makefile.36 << \END-of-Makefile.36 X# ora_dead_proc Makefile for Oracle Version 6.0.36 X#

XPCC	=	proc
XPCCINC	=	$(ORACLE_HOME)/proc/lib
XPCCFLAGS=	include=$(PCCINC) ltype=none ireclen=160 oreclen=160 select_error=no sqlcheck=none
X
XSQLSRCS	=	ora_dead_proc.pc
XOBJS	=	$(SQLSRCS:.pc=.o)
X
XHDRS	=	$(PCCINC)/sqlca.h ora_dead_proc.h
X
XSTDLIBS	=	$(ORACLE_HOME)/rdbms/lib/libsql14.a  \
X                $(ORACLE_HOME)/rdbms/lib/osntab.o \
X                $(ORACLE_HOME)/rdbms/lib/libsqlnet.a \
X                $(ORACLE_HOME)/rdbms/lib/libora.a 
X
XPROGRAM		= ora_dead_proc
X
Xora_dead_proc:	Makefile $(HDRS) $(OBJS) 
X		$(LD) $(LDFLAGS) -o ora_dead_proc $(OBJS) $(STDLIBS)
X
XO		= -g
XINCLUDES	= -I$(PCCINC)
XCFLAGS		= $O $(INCLUDES)
XLDFLAGS		= $O 
XCC		= /usr/5bin/cc
XLD		= $(CC)
XMAKE		= make
X
Xall:		$(PROGRAM)

X

X.SUFFIXES:
X.SUFFIXES:	.o .c .c~ .pc .pc~ .h .h~
X.SUFFIXES:	.1 .1~

X
X.pc.c:
X $(PCC) $(PCCFLAGS) iname=$*.pc
X.pc.o:

X		$(PCC) $(PCCFLAGS) iname=$*.pc
X		$(CC) $(CFLAGS) -c $*.c

END-of-Makefile.36
echo x - Makefile.7
sed 's/^X//' >Makefile.7 << \END-of-Makefile.7

X#
X# ora_dead_proc Makefile for Oracle 7.0.9
X#
X
XPCC	=	proc
XPCCINC	=	$(ORACLE_HOME)/proc/lib
XPCCFLAGS=	include=$(PCCINC) ltype=none ireclen=160 oreclen=160 select_error=no sqlcheck=none
X
XSQLSRCS	=	ora_dead_proc.pc
XOBJS	=	$(SQLSRCS:.pc=.o)
X
XHDRS	=	$(PCCINC)/sqlca.h ora_dead_proc.h
X
XSTDLIBS	=	$(ORACLE_HOME)/lib/libsql.a  \
X                $(ORACLE_HOME)/lib/osntab.o \
X                $(ORACLE_HOME)/lib/libsqlnet.a \
X                $(ORACLE_HOME)/lib/libora.a \
X		$(ORACLE_HOME)/lib/libnetwork.a \
X		$(ORACLE_HOME)/lib/libcore.a	\
X		-lm
X
XPROGRAM		= ora_dead_proc
X
Xora_dead_proc:	Makefile $(HDRS) $(OBJS) 
X		$(LD) $(LDFLAGS) -o ora_dead_proc $(OBJS) $(STDLIBS)
X
XO		= -g
XINCLUDES	= -I$(PCCINC)
XCFLAGS		= $O $(INCLUDES)
XLDFLAGS		= $O 
XCC		= /usr/5bin/cc
XLD		= $(CC)
XMAKE		= make
X
Xall:		$(PROGRAM)

X

X.SUFFIXES:
X.SUFFIXES:	.o .c .c~ .pc .pc~ .h .h~
X.SUFFIXES:	.1 .1~

X
X.pc.c:
X $(PCC) $(PCCFLAGS) iname=$*.pc
X.pc.o:

X		$(PCC) $(PCCFLAGS) iname=$*.pc
X		$(CC) $(CFLAGS) -c $*.c

END-of-Makefile.7
echo x - ersh
sed 's/^X//' >ersh << \END-of-ersh

X    #!/bin/sh
X    # _at_(#)ersh 3.0 91/06/04 Maarten Litmaath
X    # This rsh front-end returns the exit status of the remote command,
X    # or 99 if the connection is broken prematurely.
X    # It works OK with sh/csh-compatible shells on the remote (!) side.
X    # If there is no remote command present, rlogin is invoked, which
X    # need not return a meaningful exit status.
X    # Usage: see rsh(1).

X
X RSH=/usr/ucb/rsh
X RLOGIN=/usr/ucb/rlogin
X
X hostname=
X lflag=
X nflag=
X user=
X
X case $1 in
X -l)
X ;;
X *)

X    	hostname=${1?'hostname expected'}
X    	shift

X esac
X
X case $1 in
X -l)

X    	lflag=-l
X    	shift
X    	user=${1?'username expected after -l flag'}
X    	shift

X esac
X
X case $1 in
X -n)

X    	nflag=-n
X    	shift

X esac
X
X case $hostname in
X '')

X    	hostname=${1?'hostname expected'}
X    	shift

X esac
X
X case $# in
X 0)
X exec $RLOGIN $lflag $user "$hostname" X esac
X
X id=ersh.$$.`date | awk '{ print $4; }'` X hangup=99
X
X AWK='

X    	prprev == 1 {
X    		print prev0;
X    		prprev = 0;
X    	}
X    	$1 == "'$id'" {
X    		prev0 = $0;
X    		prev2 = $2;
X    		prev3 = $3;
X    		prprev = 1;
X    		next;
X    	}
X    	{
X    		print;
X    	}
X    	END {
X    		if (prprev == 0) {
X    			exit('$hangup');
X    		}
X    		if (prev2 ~ /^[0-9]+0$/) {
X    			exit(prev2 / 10);
X    		}
X    		if (prev2 ~ /^0$/ && prev3 ~ /^[0-9]+$/) {
X    			exit(prev3);
X    		}
X    		exit('$hangup');
X    	}

X '
X
X exec 3>&1
X
X cmd="( ${*-:} ); exec sh -c 'echo $id "'"$0 $1" >&2'\'' $?0 "$status"' X
X $RSH "$hostname" $lflag $user $nflag "$cmd" 2>&1 >&3 3>&- | X awk "$AWK" >&2 3>&-
END-of-ersh
echo x - ora_alert
sed 's/^X//' >ora_alert << \END-of-ora_alert

X#!/bin/sh
X# NAME
X#	ora_alert
X#
X# SYNOPSIS
X#	ora_alert host location
X#
X# DESCRIPTION
X#	This grep's for errors in the Oracle alert log, searching for the 
X#	occurrence of "ORA-" at the beginning of a line.
X#
X#	If this script reports any errors, the DBA should take corrective
X#	action, then move the alert log to a backup so this script does not
X#	keep reporting the same error.
X# 
X#	This script should be run by user 'oracle' unless read access to the 
X#	alert log location is specifically granted to other users.
X# 
X# ARGUMENTS
X#	host		database host name
X#	location	full path location to the alert log
X#
X# DIAGNOSTICS
X#	'grep' return codes:
X#		0	grep found matches
X#		1	no matches found
X#		2	syntax error
X#
X#	'ersh' return codes:
X#		>0	return status of the command executed
X#		0	Command executed.
X#		99	Bad hostname
X#
X#	This script exits with:
X#		0	Everything OK
X#		1	Command failed
X#		2	Syntax error
X#		3	no such hostname
X#
X# FILES
X#	ersh		'rsh' returns the status of the rsh command itself.
X#			'ersh' returns the status of the command that was
X#			executed. It was posted to alt.sources by Maarten 
X#			Litmaath and is used by this script.
X# AUTHOR
X#	Jean Anderson	February, 1992
X#
X# SccsId	_at_(#)ora_alert.sh	52.1 1/8/93
X#

Xif [ "$#" -eq 2 ]
Xthen

X	hostname="$1"
X	pathname="$2"
X
X	ersh $hostname grep "^ORA-" $pathname
X	error=$?
X
X	if [ "$error" -gt 0 ]
X	then
X
X		case $error in
X			1)	exit 0 ;;
X			2)	exit 2 ;;
X			99)	exit 3 ;;
X			*)	exit 1 ;;
X		esac
X	fi
X	exit 0
Xelse
X	echo "Usage: ora_alert hostname pathname"
X	exit 2

Xfi
END-of-ora_alert
echo x - ora_cache_hit.sql
sed 's/^X//' >ora_cache_hit.sql << \END-of-ora_cache_hit.sql Xrem NAME
Xrem ora_cache_hit.sql
Xrem
Xrem DESCRIPTION

Xrem	This calculates the buffer cache hit ratio since instance startup.
Xrem	An alert is generated if it is lower than a user-specified threshold.

Xrem
Xrem USAGE

Xrem	Login to SQL*Plus and enter
Xrem		_at_ora_cache_hit error_id threshold
Xrem
Xrem	For example:
Xrem		_at_ora_cache_hit troll_database 75

Xrem
Xrem DIAGNOSTICS

Xrem     By default, the ORACLE install does not grant access to v_$ tables.
Xrem	So this script must be run from the SYS account unless access is
Xrem	granted to other accounts.
Xrem
Xrem	The plsql parser checks the PL/SQL block before it is run. A parse
Xrem	error generates "ORA-06503: PL/SQL: error" messages.  So if the account 
Xrem	does not have access to v_$statname and v_$sysstat, the ORA-00942
Xrem	error, "table or view does not exist", will not occur.  Instead, a plsql
Xrem	error will occur: "ORA-06503: PL/SQL: error 356 - 'V_$STATNAME' must
Xrem	name a table to which the user has access..."
Xrem
Xrem	Any runtime error in the PL/SQL block will be trapped by the "WHEN 
Xrem	OTHERS" exception handler, the corresponding ORACLE message saved in 
Xrem	the plmesg table, and then output at the end of this script.

Xrem
Xrem SEE ALSO
Xrem "ORACLE RDBMS Performance Tuning Guide, Version 6.0", page 3-18. Xrem

Xrem	ORACLE Tech Bulletin #99979.343, "DESCRIPTION OF V$SYSSTAT AND 
Xrem	V$SESSTAT VIEWS"

Xrem
Xrem MODIFICATION HISTORY

Xrem	Jean Anderson	Apr, 1991	First installation & distribution
Xrem	J. Anderson	May, 1991	Converted to PL/SQL
Xrem	J. Anderson	Nov, 1991	Added error return

Xrem
Xrem SccsID
Xrem _at_(#)ora_cache_hit.sql 52.1 1/8/93 X

Xdefine	error_id=&1
Xdefine	threshold=&2
Xrem	Some Oracle installations have pause set for interactive work
Xset	pause off
X
Xset	echo off
Xset	feedback off
Xset	termout off
Xset	verify off
X
Xrem	SESSIONID, the user's auditing identifier, is used for creating unique
Xrem	names for the intermediate sql script, the results table, and the index.

X
Xcolumn uniq_id new_value sid noprint
Xcolumn tmesg new_value plmesg noprint X
Xselect userenv('SESSIONID') uniq_id Xfrom dual
X/
X
Xselect 'plmesg'||&sid tmesg
Xfrom dual
X/
X
XWHENEVER SQLERROR EXIT FAILURE
X
Xcreate table &plmesg (

X	mesgtext	varchar(255),
X	ratio		number,
X	lddate		date)

X/
X
Xset termout on
X
XDECLARE

X	msg		&plmesg..mesgtext%TYPE;
X	db_block_gets	number;			-- stored in v_$sysstat
X	consistent_gets	number;			-- stored in v_$sysstat
X	physical_reads	number;			-- stored in v_$sysstat
X	logical_reads	number;			-- db_block_gets+consistent_gets
X	cache_hit_ratio	&plmesg..ratio%TYPE;	-- final calculation

XBEGIN
X

X	SELECT	s.value a
X	INTO	db_block_gets
X	FROM	sys.v_$statname n ,  sys.v_$sysstat s
X	WHERE	n.statistic# = s.statistic#
X	AND	n.name='db block gets';
X
X	SELECT	s.value b
X	INTO	consistent_gets
X	FROM	sys.v_$statname n ,  sys.v_$sysstat s
X	WHERE	n.statistic# = s.statistic#
X	AND	n.name='consistent gets';
X
X	SELECT	s.value c
X	INTO	physical_reads
X	FROM	sys.v_$statname n ,  sys.v_$sysstat s
X	WHERE	n.statistic# = s.statistic#
X	AND	n.name='physical reads';
X
X	logical_reads := db_block_gets + consistent_gets;
X
X	cache_hit_ratio := ((logical_reads - physical_reads) / logical_reads);
X	cache_hit_ratio := floor(cache_hit_ratio*100);
X
X	msg := '&error_id: The cache hit ratio since instance startup is ';
X	msg := msg || to_char(cache_hit_ratio) || '%';
X	insert into &plmesg(mesgtext, ratio, lddate)
X		values (msg, cache_hit_ratio, sysdate);
X
X	commit;
XEXCEPTION
X	WHEN NO_DATA_FOUND THEN
X		msg := 'Could not compute cache hit ratio because a row was ';
X		msg := msg ||'missing for an expected value in sys.v_$sysstat.';
X		msg := msg ||' Manually check WHERE clauses in PL/SQL block.';
X		insert into &plmesg(mesgtext, lddate) values (msg, sysdate);
X		commit;
X	WHEN OTHERS THEN
X		msg := substr(SQLERRM, 1, 70);
X		insert into &plmesg(mesgtext, lddate) values (msg, sysdate);
X		commit;

XEND;
X/
X
Xrem Now print out messages from plmesg table. X

Xset	heading off
Xcolumn	mesgtext format A60 WORD_WRAPPED
Xcolumn	lddate format A18
Xselect	mesgtext, ratio, to_char(lddate,'DD-Mon-YY HH24:mi:ss') lddate
Xfrom	&plmesg
Xwhere	ratio < &threshold

X/
X
Xdrop table &plmesg
X/
Xexit SUCCESS
END-of-ora_cache_hit.sql
echo x - ora_dead_proc.h
sed 's/^X//' >ora_dead_proc.h << \END-of-ora_dead_proc.h

X/* 
X * NAME
X *	ora_dead_proc.h
X *
X * DESCRIPTION
X *	Contains defines and function prototypes for ora_dead_proc.
X *
X * AUTHOR
X *	Jean Anderson, SAIC Open Systems Division
X *
X * SccsId = _at_(#)ora_dead_proc.h	52.1 1/8/93
X */

X
X#ifndef _ORA_DEAD_PROC_H_
X#define _ORA_DEAD_PROC_H_
X

X#ifndef TRUE
X#define TRUE	1
X#define FALSE	0
X#endif

X
X/* These are the return codes expected by ora_soh */

X#define SQL_OK		0
X#define BAD_SYNTAX	1
X#define COMMAND_FAILED	2
X
X#define NOTFOUND	1403
X#define MAX_BUFFER	132

X

Xextern  char *getenv();
Xextern  char *strtok();
Xextern  char *strchr();
Xextern  int     atol();

Xextern void dbclose();
X
X#endif _ORA_DEAD_PROC_H_
END-of-ora_dead_proc.h
echo x - ora_dead_proc.pc
sed 's/^X//' >ora_dead_proc.pc << \END-of-ora_dead_proc.pc

X/*
X * NAME
X *	ora_dead_proc
X *
X * SYNOPSIS (C SHELL)
X *	setenv CONNECT_STRING sys/xxx_at_t:host:sid
X *	ora_dead_proc error_identifier [debug]
X *
X * SYNOPSIS (BOURNE SHELL)
X *	CONNECT_STRING=sys/xxx_at_t:host:sid ora_dead_proc error_identifier [debug]
X *
X * SYNOPSIS (ORA_SOH)
X *	(CONNECT_STRING=sys/xxx_at_t:host:sid  \
X *	 SQL_NOTIFY="name" \
X *	 ora_dead_proc error_identifier [debug])
X *
X * DESCRIPTION
X *	This makes sure that backend processes have a corresponding frontend
X *	process.
X *
X * OPTIONS
X *	debug	The existence of an option reports live connections as well.
X *
X * ENVIRONMENT
X *	The environmental variable CONNECT_STRING stores the entire database 
X *	connect string to hide dba passwords from ps listings.
X *
X * FILES
X *	ora_dead_proc.pc
X *	ora_dead_proc.h
X *
X * SEE ALSO
X *	ora_soh(1)
X *
X * AUTHOR
X *	Jean Anderson, SAIC Open Systems Division
X */

X
X#ifndef lint
Xstatic char SccsId[] = "_at_(#)ora_dead_proc.pc 52.1 1/8/93"; X#endif
X
X#include <stdio.h>
X#include "ora_dead_proc.h"
X
XEXEC SQL INCLUDE SQLCA.H;
X
XEXEC SQL BEGIN DECLARE SECTION;
XEXEC SQL END DECLARE SECTION;
X

Xint	debug=FALSE;
Xchar	error_identifier[30];

X
Xint
Xmain
X(argc, argv)

Xint	argc;
Xchar	**argv;
X{
X	char	*connect_string;
X
X	if (argc < 2)
X	{
X		fprintf(stdout,
X			"Usage: ora_dead_proc error_identifier [debug]\n");
X		exit(BAD_SYNTAX);
X	}
X
X		/* Existence of additional arguments turns on debug */
X	if (argc > 2)
X		debug=TRUE;
X
X	(void) strcpy(error_identifier, argv[1]);
X
X	connect_string=getenv("CONNECT_STRING");
X	if(connect_string==NULL)
X	{
X		fprintf(stdout, "CONNECT_STRING env variable must be set\n");
X		exit(BAD_SYNTAX);
X	}
X
X	if( dbopen (connect_string) != SQL_OK )
X		exit (COMMAND_FAILED);
X
X	if( rogue_proc() != SQL_OK )
X	{
X		dbclose();
X		exit (COMMAND_FAILED);
X	}
X	else
X	{
X		dbclose();
X		exit(SQL_OK);
X	}

X}
X
X
Xstatic int
Xrogue_proc()
X{

X	char	*p;			/* searches for an '_at_' in 'program' */
X	char	*hostname;		/* gets extracted from 'program' */
X	char	progname[50];
X	char	ps_command[MAX_BUFFER];	/* command is handed off to ps_count */
X	int	proc_count;		/* return value from ps_count */
X
X	EXEC SQL BEGIN DECLARE SECTION;
X		long	pid;		/* Oracle process id */
X		long	spid;		/* Operating system process id */
X		varchar	username[10];
X		varchar	program[48];
X	EXEC SQL END DECLARE SECTION;
X
X		/* Reason for NOT NULL check on spid: Oracle 7, at least in
X		 * the developers release, has a program named 'PSEUDO' with
X		 * a NULL spid, which obviously cannot be verified.
X		 */
X	EXEC SQL DECLARE proc_csr CURSOR FOR
X	SELECT	pid, to_number(spid), username, program
X	FROM	sys.v$process
X	WHERE	spid IS NOT NULL;
X
X	if(check_error("declare proc_csr") != SQL_OK)
X		return(COMMAND_FAILED);
X
X	EXEC SQL OPEN proc_csr;
X
X	if(check_error("open proc_csr") != SQL_OK)
X		return(COMMAND_FAILED);
X
X	while (sqlca.sqlcode != NOTFOUND)
X	{
X		EXEC SQL FETCH proc_csr
X		INTO	:pid, :spid, :username, :program;
X
X		if(check_error("fetch proc_csr") != SQL_OK)
X			return(COMMAND_FAILED);
X
X			/* This check avoids processing the last row twice */
X		if (sqlca.sqlcode != NOTFOUND)
X		{
X			strncpy(progname, program.arr, program.len);
X			progname[sizeof(progname)] = '\0';
X
X				/* Extract hostname from program */
X			p=strchr(program.arr, '_at_');
X			p++;
X			hostname=strtok(p, " ");	/* read up to space */
X
X				/* If ps is run on the localhost, it'll see the
X				 * "grep" line. Piping it to grep -v will 
X				 * eliminate it from the count.
X				 */
X			sprintf (ps_command,
X			"rsh %s ps aux |egrep %d |egrep -v 'grep' |egrep -c %d",
X				hostname, spid, spid);
X
X				/* ps_count returns the number of processes it
X				 * found, or a -1 if the command failed.
X				 */
X			if((proc_count=ps_count(ps_command)) == -1)
X			{
X				fprintf(stdout, "%s: command %s failed\n",
X					error_identifier, ps_command);
X				return(COMMAND_FAILED);
X			}
X			else if(proc_count == 0)
X			{
X				fprintf(stdout,
X				   "ERROR %s:\n%.*s's (oracle pid %d) frontend",
X					error_identifier,
X					username.len,
X					username.arr, pid);
X				fprintf(stdout,
X					" on %s (unix pid %d) is dead.\n",
X					hostname, spid);
X				fprintf(stdout,
X					"Oracle program entry: %s\n", 
X					progname);
X			}
X
X			if(debug)
X			{
X				fprintf(stdout,
X				   "DEBUG %s:\n%.*s's (oracle pid %d) frontend",
X					error_identifier,
X					username.len,
X					username.arr, pid);
X				fprintf(stdout,
X					" on %s (unix pid %d) is live.\n",
X					hostname, spid);
X				fprintf(stdout,
X					"Oracle program entry: %s\n", 
X					progname);
X			}
X
X		}	/* END check for NOTFOUND */
X
X	}	/* END while loop */
X
X	EXEC SQL CLOSE proc_csr;
X
X	return(SQL_OK);

X}
X
Xstatic int
Xdbopen(uid)
Xchar *uid;
X{

X	EXEC SQL BEGIN DECLARE SECTION;
X		varchar	userid[80];
X	EXEC SQL END DECLARE SECTION;
X
X	strncpy(userid.arr, uid, sizeof(userid.arr));
X	userid.len=strlen(userid.arr);
X
X	EXEC SQL CONNECT :userid;
X	if(check_error("open database") != SQL_OK)
X		return(COMMAND_FAILED);
X
X	return(SQL_OK);

X}
X
Xstatic void
Xdbclose()
X{

X	EXEC SQL BEGIN DECLARE SECTION;
X	EXEC SQL END DECLARE SECTION;
X
X	EXEC SQL ROLLBACK WORK RELEASE;
X	return;		/* ignore error */

X}
X
Xstatic int
Xcheck_error(str)
Xchar *str;
X{

X	EXEC SQL BEGIN DECLARE SECTION;
X	EXEC SQL END DECLARE SECTION;
X
X	if(sqlca.sqlcode < SQL_OK)
X	{
X		fprintf(stdout, "%s: %.70s(%d)\n",
X			str, sqlca.sqlerrm.sqlerrmc,-sqlca.sqlcode);
X		return(COMMAND_FAILED);
X        }
X	else
X		return(SQL_OK);

X}
X
X
Xstatic int
Xps_count(pscommand)
Xchar *pscommand; /* (i) 'ps' command to run */ X{

X	FILE    *strm, *popen();                /* stream opened for ps */
X	char    result[MAX_BUFFER + 1];         /* from fgets(strm) */
X	int     ret_code;                       /* from atoi(result) */
X
X		/* Processes have no direct access to process and load info.
X		 * 'ps' reads the kernal image from /dev/kmem to get pointers
X		 * to the process table.  So 'ps' is a setgid process.  The
X		 * upshot is the only way to obtain 'ps' info is to run ps.
X		 * popen(), normally a no-no,  is used here so the results 
X		 * can be fed directly back to the program.
X		 *
X		 * This will change in SysV R4 where processes become files.
X		 */
X	if((strm = popen(pscommand, "r")) == NULL)
X	{	perror("popen failed in db_ps_count");
X		return(-1);
X	}
X
X	if(fgets(result, sizeof(result), strm) == NULL)
X	{	perror("fgets failed in db_ps_count");
X		return(-1);
X	}
X
X	pclose(strm);
X
X		/* Get rid of the newline, if it exists */
X	if(result[strlen(result)-1] == '\n')
X	result[strlen(result)-1] = '\0';
X
X		/* Convert the result to an integer to be returned to caller */
X	ret_code=0;
X	ret_code = atoi(result);
X	return(ret_code);

X}
END-of-ora_dead_proc.pc
echo x - ora_soh
sed 's/^X//' >ora_soh << \END-of-ora_soh

X#!/bin/sh
X# NAME
X#	db_soh
X#
X# FILE
X#	db_soh.sh
X#
X# SYNOPSIS
X#	db_soh [db_check] [db_param]...
X#
X# DESCRIPTION
X#	This script is a front end driver to execute another script that 
X#	performs a state of health check on the database.
X# 
X# ARGUMENTS
X#	db_check	Name of a state-of-health program or script.
X#	db_param	All parameters required by the db_check.
X#
X# FILES
X#	sql_env.sh	Contains default environmental variables for the
X#			database host this script is run on.  Variables used
X#			either explicitely or implicitely in this script are:
X#
X#				PATH        UNIX path for scripts and programs.
X#				SQL_NOTIFY  email notification list
X#				SQL_MAIL    mailer (should be /usr/ucb/Mail)
X#				TMP_DIR     location for logfile output
X#				SQLPATH     location of SQL*Plus scripts 
X#
X#	db_check	The script or program performing the check. Any output
X#			should be to stdout or stderr and indicates an error.
X#			The output is emailed to those designated in SQL_NOTIFY.
X#			The script should return one of the following:
X#
X#				0	No problem.
X#				1	Command failed to execute.
X#				2	Bad syntax.
X#				3	Bad hostname.
X#
X# ENVIRONMENT
X#	See discussion in FILES section above on sql_env.sh.
X#
X#	The PATH variable can introduce some problems when this is run out
X#	of cron since PATH is part of the environment set up by sql_env.
X#	To avoid a 'chicken & egg' problem, resolve the PATH up front when
X#	db_soh is called; otherwise, the full path to sql_env will have to be
X#	hard coded in db_soh.
X#
X#	Instead of this:
X#		/nmrd/dev/bin/db_soh <db_check> <db_param...>
X#	Do this:
X#		env PATH=$PATH:/nmrd/dev/bin db_soh <db_check> <db_param...>
X#		^^^^^^^^^^^^^^^^^^^^^^^^^^^^
X# AUTHOR
X#	Jean Anderson	February, 1992
X#
X# SccsId	_at_(#)ora_soh.sh	52.1 1/8/93
X#

Xtrap "echo script aborted; rm -f $LOG; exit" 1 2 15

X# 
X#-------------------- 1. Get Command line argument(s) -------------------------
X#

Xif [ "$#" -gt 0 ]
Xthen

X	db_check="$1"
X	db_command=$*
Xelse
X	echo "Usage: db_soh db_check [db_param]... "
X	exit 2

Xfi
X

X# 
X#-------------------- 2. Source Database Environment File --------------------
X# 

X. sql_env
X

X#-------------------- 3. Set up log file(s) -----------------------------------

XLOG="$TMP_DIR/$$-DbSOH"
X

X#-------------------- 4. Perform the check -----------------------------------

X# Log the entire command to the log file. Xecho Command: $db_command > $LOG
X
X( $db_command ) >> $LOG 2>&1
Xerror=$?
X
Xif [ "$error" -gt 0 ]
Xthen

X	case $error in
X	   1) $SQL_MAIL -s "$db_check: command failed" $SQL_NOTIFY < $LOG ;;
X	   2) $SQL_MAIL -s "$db_check: bad syntax" $SQL_NOTIFY < $LOG ;;
X	   3) $SQL_MAIL -s "$db_check: bad hostname" $SQL_NOTIFY < $LOG ;;
X	   *) $SQL_MAIL -s "$db_check: unhandled failure" $SQL_NOTIFY < $LOG ;;
X	esac
X
X	rm -f $LOG
X	exit

Xfi
X
X# If log has more than one line (first line is the command), errors occurred. Xnum_errors=0
Xnum_errors=`wc -l $LOG | awk '{print $1}'` Xif [ "$num_errors" -gt 1 ]
Xthen

X	$SQL_MAIL -s "$db_check: ALERT" $SQL_NOTIFY < $LOG
X	rm -f $LOG 
X	exit

Xfi

X#
X#-------------------- 5. Clean up and exit -----------------------------------
X#

Xrm -f $LOG
Xexit
END-of-ora_soh
echo x - ora_sun4disk
sed 's/^X//' >ora_sun4disk << \END-of-ora_sun4disk

X#!/bin/sh
X# NAME
X#	ora_sun4disk
X#
X# FILE
X#	ora_sun4disk.sh
X#
X# SYNOPSIS
X#	ora_sun4disk host drive
X#
X# DESCRIPTION
X#	This looks for errors in /var/adm/messages by grep'ing for entries 
X#	for the database drive.  This check is SUN 4 specific.
X#
X#	This script may be run by any user with access to /var/adm/messages.
X#
X# SAMPLE USAGE
X#	ora_sun4disk troll sd2g
X#
X# ARGUMENTS
X#	host		Name of the host to check.
X#	drive		Drive database files are on.
X#	filename	An alternate to /var/adm/messages for test purposes.
X#
X# FILES
X#	ersh		If 'rsh' is used to execute a remote command, a 'test'
X#			only returns the status of the rsh command itself. You
X#			cannot determine if the remote command itself succeeded.
X#			'ersh' was posted to alt.sources by Maarten Litmaath and
X#			is used by this script.
X# # DIAGNOSTICS
X#       'grep' return codes:
X#               0       grep found matches
X#               1       no matches found
X#               2       syntax error
X#
X#       'ersh' return codes:
X#               >0      return status of the command executed
X#               0       Command executed.
X#               99      Bad hostname
X#
X#       This script exits with:
X#               0       Everything OK
X#               1       Command failed
X#               2       Syntax error
X#               3       no such hostname
X#
X# AUTHOR
X#	Jean Anderson	February, 1992
X#
X# SccsId	_at_(#)ora_sun4disk.sh	52.1 1/8/93
X#

Xif [ "$#" -ge 2 ]
Xthen

X	hostname="$1"
X	drive="$2"
X
X	if [ "$#" -eq 3 ]
X	then
X		filename="$3"
X	else
X		filename="/var/adm/messages"
X	fi
X
X	# This is a simple string match, use f(ast)grep.
X
X	ersh $hostname fgrep $drive $filename
X        error=$?
X
X	if [ "$error" -gt 0 ]
X	then
X
X		case $error in
X			1)	exit 0 ;;
X			2)	exit 2 ;;
X			99)	exit 3 ;;
X			*)	exit 1 ;;
X		esac
X	fi
X        exit 0
Xelse
X	echo "Usage: ora_sun4disk hostname drive"
X	exit 2

Xfi
END-of-ora_sun4disk
echo x - sql_env
sed 's/^X//' >sql_env << \END-of-sql_env X

X# NAME
X#	sql_env
X#
X# FILE
X#	sql_env		Bourne Shell script to set up environment for 
X#			running database scripts.
X# SYNOPSIS
X#	. sql_env
X#
X# DESCRIPTION
X#	This sets up the environment for a 'driver' script that in turn
X#	runs an ORACLE script.  So far the 'driver' scripts include
X#	'sql_job' that runs SQL*Plus reports and 'ora_soh' that runs a database
X#	state-of-health check.
X#
X#	Custom tailoring for a specific database host should be performed
X#	in this file.  This file must be reviewed carefully before installed.
X#	Defaults are currently set to the NMRD development database in San
X#	Diego.
X#
X# ENVIRONMENT
X#	A default value for each environmental variable may be set below.
X#
X#	ORACLE_HOME	This is the home location for the ORACLE instance.
X#			Among other things, ORACLE error messages are stored
X#			in $ORACLE_HOME/rdbms/mesg.
X#	PATH		$ORACLE_HOME/bin must be in the PATH because sqlplus is
X#			used by some scripts.
X#	SQLPATH		Location of SQL*Plus scripts.
X#	ORACLE_SID	This must be set if the scripts are run locally and
X#			TWO_TASK information is not included in the user/passwd
X#			string.
X#	SQL_NOTIFY	This is a list of people to receive email notification.
X#	SQL_MAIL	Mail notification uses UCB Mail parameters.  If a
X#			different mailer is used, all driver scripts that call
X#			this one should be modified appropriately.
X#	TMP_DIR		Directory location for error logs.
X#
X# AUTHOR
X#	Jean Anderson	November 1991
X#

X
X# If ORACLE_HOME is NULL, set it to a default. Xif test -z "$ORACLE_HOME"
Xthen

X	ORACLE_HOME=/home/troll/oradata/6033
X	export ORACLE_HOME

Xfi
X
Xif test -z "$SQLPATH"
Xthen

X	SQLPATH=/nmrd/dev/sql
X	export SQLPATH

Xfi
X
Xif test -z "$ORACLE_SID"
Xthen

X	ORACLE_SID=dev
X	export ORACLE_SID

Xfi
X
Xif test -z "$SQL_NOTIFY"
Xthen
X SQL_NOTIFY="oracle"
Xfi
X
Xif test -z "$SQL_MAIL"
Xthen
X SQL_MAIL="/usr/ucb/Mail"
Xfi
X
Xif test -z "$TMP_DIR"
Xthen

X	TMP_DIR=/tmp
X	export TMP_DIR

Xfi
X
X
X# Make sure ORACLE's bin is in the PATH. XPATH=$PATH:$ORACLE_HOME/bin
END-of-sql_env
exit Received on Thu Jan 27 1994 - 19:28:12 CET