space.ksh

#!/bin/ksh
#############################################################################
#                                                                           #
# FILENAME: space.ksh [ORACLE_SID]                                          #
#                                                                           #
# Purpose: Generate a space report for an Oracle instance.                  #
#                                                                           #
#          This report will work for Versions 8i, 9i,10g or 11g of Oracle.     #
#                                                                           #
# Parameters: Passing the ORACLE_SID is optional.  If left off, it will     #
#             run the space report for the currently-defined SID, otherwise #
#             it will source in the environment for the argument passed.    #
#                                                                           #
#                      *** DO NOT HAND EDIT THIS FILE ***                   #
#      *** IF YOU NEED A CHANGE TO THIS FILE, CONTACT INFRASTRUCTURE ***    #
#############################################################################
#                                                                           #
#  Revision History:                                                        #
#                                                                           #
#  REV   DATE         BY        DESCRIPTION                                 #
#  ---  ------  --------------  ------------------------------------------- #
#  1.0  031006                  INFRASTRUCTURE Initial Release              #
#  1.1  040106                  Modify to handle Oracle 10.1                #
#       090310                 Certified for 11g                            #
#                      *** DO NOT HAND EDIT THIS FILE ***                   #
#                                                                           #
REV=1.1
#############################################################################

initialize ()
{
KEEP=30                                 # Number of log files to keep online
LOGDATE=$(date '+%m%d%Y_%H%M%S')
D=$(date '+%Y%m%d')                     # Date extension, No Minutes
DAY=$(date '+%m%d%Y_%H%M')              # Date extension

OUTPUT=$DBA/output/$DB_NAME             # Output directory
LOG=$OUTPUT/space_$LOGDATE.log          # Set up log file

if [[ ! -d $OUTPUT ]] ; then            # Test if standard output dir exists
  check_error 10
fi

if [[ -s $LOG ]] ; then                 # Log file exists
  /bin/rm -f $LOG                       # Clean up
fi

touch $LOG                              # Create the file
chmod 700 $LOG                          # Lock down permissions
}


# ----------------------------------------------------------------------------
check_newer_version()
{
# Check if Newer Version exists
if [[ ${STD:-0} = 0 ]] ; then           # If STD is not set
  check_error 20
fi

CHECK_NEWER_VERSION=$STD/check_newer_version.ksh
if [[ -s $CHECK_NEWER_VERSION ]] ; then
  $CHECK_NEWER_VERSION $1               # Check if newer file exists
else
  check_error 30
fi
}


# ---------------------------------------------------------------------------
check_error()
{
MESSAGE=$1
print " "
case $MESSAGE in
   5) # Note: cannot tee to log as log has not been created yet
      print "\nError: Invalid number of parameters."
      print "  Usage: ddl_extract.ksh <SID>" ;;

   6) # Note: cannot tee to log as log has not been created yet
      print "\nError: Oracle environment is not set."
      print "Possibly due to invalid ORACLE_SID argument passed." ;;

  10) # Note: cannot tee to log as log has not been created yet
      print "\nDirectory does not exist: $OUTPUT"
      print "Error: Oracle standard environment is not set." ;;

  20) # Note: cannot tee to log as log has not been created yet
      print "Environment variable STD... NOT defined."
      print "Please verify Oracle standard environment" ;;

  30) # Note: cannot tee to log as log has not been created yet
      print "Missing standard script - $CHECK_NEWER_VERSION"
      print "Please configure the environment using the Oracle standards" ;;

  40) print "\nError: Could not execute sqlplus" | tee -a $LOG ;;

  50) print "\nError: The file $STD/space.sql does not exist." | tee -a $LOG ;;

  85) print "\nThe environment variable ORA_VER is not set"  | tee -a $LOG
      print "Please verify Oracle standard environment"      | tee -a $LOG ;;

  90) print "\nThe database ($ORACLE_SID) is not running."   | tee -a $LOG ;;

   *) print "\nError: Unknown error code passed."            | tee -a $LOG ;;

esac
print "\nExiting..."
exit 1
}


#------------------------------------------------------------------------------
check_db()
{
# The database will be checked to see if it is running.

CHK_DB="$(ps -ef | grep -w ora_smon_$ORACLE_SID | grep -v grep)"

if [ "NULL$CHK_DB" != "NULL" ]; then      # Value of CHK_DB is set
  print "\nThe database ($ORACLE_SID) is up and running.\n" | tee -a $LOG
else
   check_error 90
fi
}


#------------------------------------------------------------------------------
execute_space()
{
# check of $ORA_VER is set
if [[ "NULL$ORA_VER" = "NULL" ]] ; then
  check_error 85
fi

ORAVERNO=$(print $ORA_VER | awk -F. '{print $1$2}')

case $ORAVERNO in
  81|90|91|92|101|102|112) # Oracle 8i, 9i, 10gR1, 10gR2, 11gR2

    # check if space sql script exists
    if [[ ! -s $STD/space.sql ]] ; then    # Check if file exists
      check_error 50
    fi

    print "Running Space Report...\n"

    sqlplus -s "/ as sysdba" <<!! | tee -a $LOG
@$STD/space.sql
!!
    SQL_STATUS=$?
    if [[ $SQL_STATUS -eq 1 ]] ; then
      check_error 40
    fi ;;

  *)
    print "\nSpace Report only supports Oracle 8i or higher\n" | tee -a $LOG
esac
}


# ------------------------------[ Main ]-------------------------------------
# ************
# Main Program
# ************

print "\nspace.ksh Rev. $REV\n"

RETCODE=0

case $1 in
  "") # Assume that current ORACLE_SID is the one that we want
      # to do our space report on
      print " " > /dev/null ;;

   *) # Read in environment
      export NEW_ORACLE_SID=$1; . $HOME/.profile > /dev/null 2>&1 ;;
esac

if [ ! -d $ORACLE_HOME ] ; then
  check_error 6
fi

check_newer_version $0                  # Check for newer version of this file
initialize $1 $2 $3 $4 $5 $6 $7 $8 $9

print "\nStarting at `date`" | tee -a $LOG

check_db                                # Check that database is up

execute_space

print "\nDone at `date`" | tee -a $LOG
print "\nLog is: $LOG"

# Purge file logs
$STD/purge_files.ksh $OUTPUT space $KEEP > /dev/null 2>&1

exit $RETCODE