| bluethundr |
05-13-2012 11:01 PM |
check oracle tablespace script
hello all,
I am attempting to develop a bash script that checks the tablespaces sizes of an oracle database and reports the ones that rise above a certain threshold.
So far the script works fine if only one tablespace is above the threshold. However, the formatting falls completely apart if more than one tablespace is too large.
Here's an example that shows it attempting to print out two tablespaces:
Code:
[db07:~] root% /opt/nagios/libexec/check_qa_db_tablespace.sh
NOK BAM_USER_INDX_LG MLBDATASM is at 97.50% 95.85% 820 340/32764 8191
But when only one tablespace is over the size threshold the output is perfectly readable and makes sense:
Code:
[db05:~] root% /opt/nagios/libexec/check_prod_tablespace.sh
NOK MART_INDX_LG is at 95.92% 1420/34768
Here's what the code looks like so far:
Code:
#!/bin/bash
# exit codes
CRED_ERR=1 # if the credentials are not valid
NOARGS=2 # if the required parameters were not supplied
# credentials / environment variables
ORACLE_HOME="/u01/app/oracle/product/10.2.0.4"
ORACLE_SID=qa_db
sqlplus="/u01/app/oracle/product/10.2.0.4/bin/sqlplus"
USERNAME=user
PASS=secret
SID=${ORACLE_SID}
if [ -z "${USERNAME}" ] || [ -z "${PASS}" ]; # Exit if no arguments were given.
then
echo "Error: Username or Password are empty"
exit $NOARGS
fi ;
PATH=$PATH:$ORACLE_HOME/bin
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export ORACLE_HOME PATH LD_LIBRARY_PATH
while i=$($sqlplus -s -l $USERNAME/$PASS@$SID @/opt/bin/ops/company_tablespace.sql | awk '{print $1}') j=$($sqlplus -s -l $USERNAME/$PASS@$SID @/opt/bin/ops/company_tablespace.sql | awk '{print $3}') k=$($sqlplus -s -l $USERNAME/$PASS@$SID @/opt/bin/ops/company_tablespace.sql | awk '{print $2}') l=$($sqlplus -s -l $USERNAME/$PASS@$SID @/opt/bin/ops/company_tablespace.sql | awk '{print $4}')
do
TBLSPACE=$i
PCT=$j
FREE=$k
TOTAL=$l
if [ "$TBLSPACE" ]
then
echo "NOK" $TBLSPACE " is at " $PCT $FREE/$TOTAL
exit 2
else
echo "All OK"
exit 0
fi
done
errorCode=$? # checks if the last operation (sqlplus) was completed successfully or not
if [ ${errorCode} -ne 0 ]
then
echo "Running sqlplus FAILED"
exit ${CRED_ERR}
echo
fi
As you can probably tell, all it does is execute a few sqlplus commands (with the SQL contained in a separate file) and assign them to a few variables.
I was thinking that the best way to approach this problem is to assign the output of the sql commands to an array and iterate through that. But I'm having a little trouble with the syntax and I was hoping that someone might not mind sharing a suggestion on how to handle this.
I took a stab at the array, but sadly missed the mark
Code:
while declare -a TSPACES=($sqlplus -s -l $USERNAME/$PASS@$SID @/opt/bin/ops/company_tablespace.sql | awk '{print $1}'), $($sqlplus -s -l $USERNAME/$PASS@$SID @/opt/bin/ops/company_tablespace.sql | awk '{print $3}'), $($sqlplus -s -l $USERNAME/$PASS@$SID @/opt/bin/ops/company_tablespace.sql | awk '{print $2}'), $($sqlplus -s -l $USERNAME/$PASS@$SID @/opt/bin/ops/company_tablespace.sql | awk '{print $4}')
do
TBLSPACE=$TSPACES[0]
PCT=$TSPACES[1]
FREE=$TSPACES[1]
TOTAL=$TSPACES[2]
if [ "$TBLSPACE" ]
then
echo "NOK" $TBLSPACE " is at " $PCT $FREE/$TOTAL
exit 2
else
echo "All OK"
exit 0
fi
done
Results in this rather ugly error
Code:
[db07-dc2:~] root% ./check_qa_db_tablespace.sh
./check_qa_db_tablespace.sh: line 26: syntax error near unexpected token `|'
./check_qa_db_tablespace.sh: line 26: `le declare -a TSPACES=($sqlplus -s -l $USERNAME/$PASS@$SID @/opt/bin/ops/company_tablespace.sql | awk '{print $1}'), $($sqlplus -s -l $USERNAME/$PASS@$SID @/opt/bin/ops/company_tablespace.sql | awk '{print $3}'), $($sqlplus -s -l $USERNAME/$PASS@$SID @/opt/bin/ops/company_tablespace.sql | awk '{print $2}'), $($sqlplus -s -l $USERNAME/$PASS@$SID @/opt/bin/ops/company_tablespace.sql | awk '{print $4}') '
./check_qa_db_tablespace.sh: line 27: syntax error near unexpected token `do'
./check_qa_db_tablespace.sh: line 27: `do'
The ultimate purpose of this script is to become a nagios check.
In case you're curious this is the rather well written SQL that the script calls
Code:
--###########################################################################
--### THIS IS FOR TABLESPACE MONITORING with exclusion of TEMP and UNDO
--## Tablespace Alert - A tablespace has reached a crital state! #
--### Checks for different pecentage thresholds by total size of the TS. #
--### Alert the DBA Group - Page - Phone Service #
--###########################################################################
set feedback off
set pagesize 0
set trimspool on
SELECT d.tablespace_name "NAME",
ROUND(NVL(f.bytes, 0)/1024/1024) "FREE(M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),'990.00')||'%' "USED %",
ROUND(NVL(a.bytes, 0)/1024/1024) "TOTAL(M)"
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, sum(bytes) bytes
FROM dba_data_files group by tablespace_name) a,
(SELECT tablespace_name, sum(bytes) bytes
FROM dba_free_space group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name != (select VALUE from v$parameter where name
='undo_tablespace')
AND round((a.bytes - F.bytes)/a.bytes * 100) >=
CASE
WHEN a.bytes < 10737418240 THEN 90
WHEN a.bytes >= 10737418240 AND a.bytes < 21474836480 THEN 92
WHEN a.bytes >= 21474836480 AND a.bytes < 32212254720 THEN 94
WHEN a.bytes >= 32212254720 AND a.bytes < 42949672960 THEN 96
WHEN a.bytes >= 42949672960 AND a.bytes < 64424509440 THEN 97
WHEN a.bytes >= 64424509440 AND a.bytes < 118111600640 THEN 98
ELSE 99
END/* */
ORDER BY 4 desc
/
exit
Not written by me obviously I am not a DBA.. though I would rather well like to be one day! My script disregards the thresholds set in the SQL in favor of setting it's own levels.
Here's the output of running the sql code directly on the command line
Code:
db07: /u01/app/oracle>sqlplus -s -l user/secret@qa_db @/opt/bin/ops/qa_db_tablespace.sql
USER_INDX_LG 820 97.50% 32764
DATASM 340 95.85% 8191
Thanks in advance |
