LinuxQuestions.org
LinuxAnswers - the LQ Linux tutorial section.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices

Reply
 
Search this Thread
Old 05-14-2012, 12:01 AM   #1
bluethundr
Member
 
Registered: Jun 2003
Location: Summit, NJ
Distribution: CentOS 5.4
Posts: 122

Rep: Reputation: 15
Post 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

Last edited by bluethundr; 05-14-2012 at 12:08 AM. Reason: obscured some detail that should be private
 
Old 05-14-2012, 09:39 AM   #2
MensaWater
Guru
 
Registered: May 2005
Location: Atlanta Georgia USA
Distribution: Redhat (RHEL), CentOS, Fedora, Debian, FreeBSD, HP-UX, Solaris, SCO
Posts: 6,028
Blog Entries: 5

Rep: Reputation: 790Reputation: 790Reputation: 790Reputation: 790Reputation: 790Reputation: 790Reputation: 790
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).
 
  


Reply

Tags
bash scripting, oracle


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Check an Oracle database brownie_cookie Linux - Newbie 37 05-05-2011 02:51 AM
How to check that my script can check if it has a specific range of charcters. shirlcurl20 Linux - Newbie 2 11-17-2010 12:15 AM
oracle query script investmentbnker75 Programming 1 11-15-2008 10:41 PM
Oracle Start Up Script linux_pioneer Linux - Software 1 09-16-2003 06:44 AM
Oracle Startup Script linux_pioneer Linux - Software 0 09-15-2003 08:55 PM


All times are GMT -5. The time now is 12:49 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration