0

Oracle Linux 5.10

I can manually run the script "tblspc_usage.sh" successfully as the oracle user. I know this because it emails me a report.

But it not run at all from cron. It doesn't generate a log file.

[oracle@dub-ImrORA3 scripts]$ crontab -l
# user /bin/sh
SHELL=/bin/sh
# mail to oracle user
MAILTO=oracle
# run at 9:45 AM monday thur friday
45 09 * * 1-5 /home/oracle/scripts/tblspc_usage.sh 2>&1 /home/oracle/scripts/tblspc_usage.log

[oracle@dub-ImrORA3 scripts]$ ls -al tblspc_usage.sh
-rwxrwxr-- 1 oracle oinstall 2013 Jan 20 09:12 tblspc_usage.sh

Okay, here is the email in /var/mail/oracle /home/oracle/scripts/tblspc_usage.sh: line 15: sqlplus: command not found grep: body.log: No such file or directory

Here is my shell script:

#!/bin/sh
#
# tblspc_usage.sh
#=======================================
#
# Checks for tablespace usage exceeding 90% and email the details
# does not check undo or temp tablespaces
#=======================================
#
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 ; export ORACLE_HOME
ORACLE_SID=IMR1 ; export ORACLE_SID
user="system"
pass="letmein" # bogus passwd

sqlplus -S $user/$pass <<EOF
   column "TOTAL ALLOC (MB)"      format 9,999,990.00
   column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
   column "USED (MB)"             format 9,999,990.00
   column "FREE (MB)"             format 9,999,990.00
   column "% USED"                format 990.00
   set echo off
   spool body.log
   select 
       a.tablespace_name,
       a.bytes_alloc/(1024*1024)               "TOTAL ALLOC (MB)",
       a.physical_bytes/(1024*1024)            "TOTAL PHYS ALLOC (MB)",
       nvl(b.tot_used,0)/(1024*1024)           "USED (MB)",
       (nvl(b.tot_used,0)/a.bytes_alloc)*100   "USED %"
   from 
       (select 
            tablespace_name,
            sum(bytes) physical_bytes,
            sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
        from 
            dba_data_files
        group by 
            tablespace_name ) a,
   (select 
        tablespace_name, 
        sum(bytes) tot_used
    from 
        dba_segments
    group by 
        tablespace_name ) b
  where 
        a.tablespace_name = b.tablespace_name (+)
  and 
        a.tablespace_name not in 
           (select distinct 
                   tablespace_name 
            from 
                   dba_temp_files
           )
  and      
        a.tablespace_name not like 'UNDO%'
  and
       ( nvl(b.tot_used,0)/a.bytes_alloc)*100 >= 90.00
  order by 1;

    spool off
    exit
EOF

   # if the word "TABLESPACE" exists in the spool file 
   # then at least one tablespace has usage over 90%
   if grep -q TABLESPACE "body.log";
   then
        cat /home/oracle/scripts/body.log | mailx -s "ORA3 - Tablespace(s) 90% Full" \
        stringer.bell@xxx.com
   fi
  • I know this might be a stupid question but is crond started ? you can check by using service crond status. – Digisec Jan 20 '15 at 18:30
  • Yes, crond is running: [root@dub-ImrORA3 ~]# service crond status crond (pid 3933) is running... – Stringer Jan 20 '15 at 18:33
1

The message sqlplus: command not found gives the answer. Since you have an environment variable ORACLE_HOME set, put this line in your script after exporting ORACLE_HOME:

export PATH=$PATH:$ORACLE_HOME/bin

I also have a superstition of exporting TNS_ADMIN:

export TNS_ADMIN=$ORACLE_HOME/network/admin

I don't know if that's strictly necessary.

  • adding the EXPORT statement worked. I also had to specify the full path to the Oracle spool file. Thanks! – Stringer Jan 20 '15 at 18:57
1

Seems like you updated your post with more information, like Bruce said, that error tells you that ORACLE_HOME path is missing. Update your script to include:

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=IMR1

I would still fix this in your cron:

# run at 9:45 AM monday thur friday
45 09 * * 1-5 /home/oracle/scripts/tblspc_usage.sh >> /home/oracle/scripts/tblspc_usage.log 2>&1
  • Append to log: >>
  • Redirect stderr to log file (in this case same as stdout): 2>&1
  • Correct - log file was not being created until I made your suggested change. – Stringer Jan 20 '15 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.