#!/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,11g,12c 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 #
# --- ------ -------------- ------------------------------------------- #
# #
REV=1.2 #
#############################################################################
#############################################################################################################################################################################################
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
}
#-----------------------------------------------------------------------------
get_db_type()
{
# Get DB type i.e. CDB or a non-CDB
if [[ "$ORA_VER" == 12* ]]; then
OUT=`$ORACLE_HOME/bin/sqlplus -s / as sysdba <<-EOF
set time off veri off head off feed off
select 'CDB='||cdb from v\\$database;
EOF
`
CDB=$(print $OUT | grep CDB= | cut -d= -f2)
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|111|112|121|122) # Oracle 8i, 9i, 10gR1, 10gR2, 11gR2, 12cR1, 12cR2
# 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"
CDB=NO
get_db_type
if [[ "$CDB" == "YES" ]]; then
sqlplus -s "/ as sysdba" <<!! | tee -a $LOG
@$STD/cdb_space.sql
!!
else
sqlplus -s "/ as sysdba" <<!! | tee -a $LOG
@$STD/space.sql
!!
fi
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