#!/bin/ksh
#============================================================================
# File:         prevent1555.sh
# Type:         UNIX korn-shell script
# Author:       Tim Gorman, Evergreen Database Technologies Inc.
# Date:         06-Dec-2002
#
# Description:
#
#	This shell script is a "driver" for calling the PREVENT1555 package
#	to help prevent the ORA-01555 ("snapshot too old") error message
#	from occurring.
#
#	The script does this by placing a small "roadblock" transaction in
#	each of the online non-SYSTEM rollback segments, thus preventing the
#	overwrite of any before-image data committed subsequent.  This in
#	turn will force the rollback segments to grow (extend) instead of
#	wrapping and overwriting information useful for fulfilling consistent
#	reads.
#
#	This functionality achieves much of the same effect as the Oracle9i
#	UNDO_RETENTION parameter when used in UNDO tablespaces.  This script
#	is intended for use in databases using Oracle8i and lower, as the
#	UNDO features of Oracle9i make this script obsolete.
#
#
# Modifications:
#
#============================================================================
_Prog=prevent1555_all
#
#----------------------------------------------------------------------------
# Verify that the ORACLE_SID environment variable is set...
#----------------------------------------------------------------------------
if [[ "${ORACLE_SID}" = "" ]]
then
        echo "ORACLE_SID not set; aborting..."
        exit 1
fi
#
#----------------------------------------------------------------------------
# Obtain an Oracle account USERNAME and PASSWORD from the user...
#----------------------------------------------------------------------------
_OraAcct=""
while [[ "${_OraAcct}" = "" ]]
do
	echo "\nEnter an Oracle account username: \c"
	read _OraAcct
done
_OraPwd=""
while [[ "${_OraPwd}" = "" ]]
do
	echo "\nEnter the password for the Oracle account \"${_OraAcct}\": \c"
	read _OraPwd
done
#
#----------------------------------------------------------------------------
# Obtain the desired duration of the transactions...
#----------------------------------------------------------------------------
integer _Mins=0
while (( ${_Mins} < 1 ))
do
	echo "\nEnter the number of minutes to hold the \"dummy\" transactions: \c"
	read _Mins
done
#
#----------------------------------------------------------------------------
# Call the PREVENT1555.DISPLAY_RBS_NAMES procedure to get rollback segment
# names...
#----------------------------------------------------------------------------
_TmpFile=/tmp/${_Prog}_$$.tmp
sqlplus -s << __EOF__ > ${_TmpFile} 2>&1
${_OraAcct}/${_OraPwd}
whenever sqlerror exit failure
whenever oserror exit failure
set echo off feedback off timing off pause off pagesize 0 linesize 500 trimout on serveroutput on
exec prevent1555.display_rbs_names;
exit success
__EOF__
#
if (( $? != 0 ))
then
	echo "SQL*Plus connecting as \"${_OraAcct}\":  DISPLAY_RBS_NAMES failed..."
	echo
	cat ${_TmpFile}
	echo
	rm -f ${_TmpFile}
	exit 1
fi
#
#----------------------------------------------------------------------------
# Create a temporary SQL*Plus script to hold SQL*Plus commands...
#----------------------------------------------------------------------------
_SqlFile=/tmp/${_Prog}_$$.sql
echo "whenever sqlerror exit failure" >> ${_SqlFile}
chmod 600 ${_SqlFile}
echo "whenever oserror exit failure" >> ${_SqlFile}
echo "connect ${_OraAcct}/${_OraPwd}" >> ${_SqlFile}
echo "set echo on feedback on timing off pause off pagesize 0 linesize 500 trimout on" >> ${_SqlFile}
echo "connect ${_OraAcct}/${_OraPwd}" >> ${_SqlFile}
echo "exec prevent1555.set_roadblock(in_rbs_name=>'&1',in_mins=>${_Mins})" >> ${_SqlFile}
echo "exit success" >> ${_SqlFile}
#
#----------------------------------------------------------------------------
# For each rollback segment, initiate a "dummy" transaction to use as a
# "roadblock" to cause the rollback segments to grow, permitting rollback
# segments to grow for the requested amount of time...
#----------------------------------------------------------------------------
_OutFile="/tmp/${_Prog}_"
for _Rbs in `cat ${_TmpFile}`
do
	#
	sqlplus /nolog @${_SqlFile} ${_Rbs} > ${_OutFile}_${_Rbs}_$$.out 2>&1 &
	#
done
rm -f ${_TmpFile}
#
#----------------------------------------------------------------------------
# Wait for all of the jobs to complete...
#----------------------------------------------------------------------------
wait
rm -f ${_SqlFile}
echo
echo "Enter \"y\" to remove the SQL*Plus output files only if everything went OK..."
echo
rm -i /tmp/${_Prog}_*_$$.out
#
#----------------------------------------------------------------------------
# Finished...
#----------------------------------------------------------------------------
echo
echo "Done!"
echo
exit 0