LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Newbie (http://www.linuxquestions.org/questions/linux-newbie-8/)
-   -   check oracle tablespace script (http://www.linuxquestions.org/questions/linux-newbie-8/check-oracle-tablespace-script-944797/)

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

MensaWater 05-14-2012 08:39 AM

Your problem is with Nagios you can only have 1 output line. Nagios gets confused so you couldn't output 2 lines if 2 different tables were a problem. If 2 tables are an issue it looks a bit garbled but IS actually readable. However, what happens if you have 10 tables in that state? Your Nagios web page will look very funny indeed.

What you might want to do is adjust the script so that it only reports on one table at a time. In the event of a Nagios alert you could address that to clear the condition and see if it then reports on another OR you could run a command line script to check multiple tables after the alert so you're addressing them all at once.

Another option would to be to add separate lines in Nagios for monitoring each table you're interested in so that the Nagios display would show you a separate line for each table that was a problem. For that you could write your script to accept table name as input then pass the table name from the services.cfg (through the commands.cfg defined command using the script).


All times are GMT -5. The time now is 03:57 AM.