OraMatt: YABAOracle

#!/bin/bash
   #===============================================================================================================
   #                                                                                                                                              
   #         FILE: quick_diag.sh
   #
   #        USAGE: run it
   #
   #  DESCRIPTION: try to diag system
   #      OPTIONS:  
   # REQUIREMENTS: 
   #       AUTHOR: Matt D
   #      CREATED: 11.27.2012
   #      VERSION: 1.0
   #      EUL    : 	THIS CODE IS OFFERED ON AN “AS-IS” BASIS AND NO WARRANTY, EITHER EXPRESSED OR IMPLIED, IS GIVEN. 
   #				THE AUTHOR EXPRESSLY DISCLAIMS ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED.
   #				YOU ASSUME ALL RISK ASSOCIATED WITH THE QUALITY, PERFORMANCE, INSTALLATION AND USE OF THE SOFTWARE INCLUDING, 
   #				BUT NOT LIMITED TO, THE RISKS OF PROGRAM ERRORS, DAMAGE TO EQUIPMENT, LOSS OF DATA OR SOFTWARE PROGRAMS, 
   #				OR UNAVAILABILITY OR INTERRUPTION OF OPERATIONS. 
   #				YOU ARE SOLELY RESPONSIBLE FOR DETERMINING THE APPROPRIATENESS OF USE THE SOFTWARE AND ASSUME ALL RISKS ASSOCIATED WITH ITS USE.
   #
   #
   #
   #
   #
   #
   #===============================================================================================================
clear screen

# Set the run timestamp
export RUNTIME=`date +%m_%d_%y_%H%M`

# Inform the enduser & ask for permission to run
	echo "##################################################"
	echo "This script will attempt to collect the following"
	echo "OS Memory & CPU information"
	echo "Oracle Database Tablespace Usage"
	echo "Would you like to continue? "
	echo "Enter yes or no"
	echo "###################################################"
	read DOWHAT
	if [[ $DOWHAT = yes ]]; then
		echo "Yes"
		clear screen
	else
		echo "No"
		exit 1
	fi
	
	

# Make sure ORACLE_HOME is set
 if test "${ORACLE_HOME+set}" != set ; then
        echo "ORACLE_HOME is not set"
        echo "Please set your ORACLE_HOME and rerun"
        exit 1
    else
    	echo "---------------------------------------"
        echo "ORACLE_HOME is set to ${ORACLE_HOME}"
    fi 

# Get the ORACLE_SID from ps
export ORACLE_SID=`ps -ef | grep -v grep | grep -v ASM | grep pmon | cut -d _ -f3`
if [ -z $ORACLE_SID ]
	then echo "ORACLE_SID not set...perhaps not running?" >/tmp/Oracle_Not_Running_${RUNTIME}.log
	ps -ef >> /tmp/Oracle_Not_Running_${RUNTIME}.log
	else echo "ORACLE_SID is ${ORACLE_SID}" >/tmp/Oracle_Running_${RUNTIME}.log
fi


# Ask for the Oracle DB user & password
echo "---------------------------------------"
echo "Please enter the SYSDBA Username: "
echo "---------------------------------------"
read ORAUSER
echo "---------------------------------------"
echo "Please enter the SYSDBA Password: "
read ORAPASS
echo "---------------------------------------"
    
# Put $ORACLE_HOME in PATH
export PATH=$PATH:$ORACLE_HOME/bin

# Collect Database Information
function get_db_info()
{
sqlplus $ORAUSER/$ORAPASS@localhost:1521/${ORACLE_SID} as sysdba <<EOF
spool /tmp/db_info_${RUNTIME}.log

select (sum(value)/1024/1024/1024) SGA_Size_GB from v\$sga;

col file_name for a45
col tablespace_name for a30
select file_name, tablespace_name from dba_data_files;

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
exit
EOF
}

# Collect Linux System Info
function get_linux_info()
{
	cat /proc/meminfo 	>/tmp/os_info_${RUNTIME}.log
	cat /proc/cpuinfo 	>>/tmp/os_info_${RUNTIME}.log
	free -m 			>>/tmp/os_info_${RUNTIME}.log
}

# Collect Solaris System Info
function get_solaris_info()
{
	prtconf | grep Mem		>/tmp/os_info_${RUNTIME}.log
	id -p 					>>/tmp/os_info_${RUNTIME}.log
	export PROJECT_ID=`id -p | cut -d '=' -f 4 | cut -d '(' -f 1`
	prctl -n project.max-shm-memory -i project ${PROJECT_ID} >>/tmp/os_info_${RUNTIME}.log
}


# Inform the user of the log files
function show_log_files()
{
clear screen
#cat /tmp/Oracle_Not_Running_${RUNTIME}.log | cat /tmp/Oracle_Running_${RUNTIME}.log | cat /tmp/os_info_${RUNTIME}.log | cat /tmp/db_info_${RUNTIME}.log >/tmp/ALL_Info_${RUNTIME}.log
echo "The following log file was created: "
echo "---------------------------------------"
ls /tmp/Oracle_Not_Running_${RUNTIME}.log
ls /tmp/Oracle_Running_${RUNTIME}.log 
ls /tmp/os_info_${RUNTIME}.log
ls /tmp/db_info_${RUNTIME}.log
}



# See what OS we are running & collect data
export OS=`uname`
case $OS in
	Linux)
	echo "Linux"
	get_linux_info
	get_db_info
	show_log_files
	;;
	SunOS)
	echo "Solaris"
	get_solaris_info
	get_db_info
	show_log_files
	;;
esac