SUSE Linux iTOps Tube

Monday, May 14, 2012

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











No comments:

Post a Comment