script required for Flash back area

tspoon · #1 (**permalink**) 06-08-11, 09:32

Hello,

on a daily / hourly basis I need need to check the size for the Flash Recovery Area (v$flash_recovery_area_usage)

I was not in work yesterday and when I arrived today I checked the alert log only to find that the usage was 100%. This means the overnight backups will not run.

I was wondering if someone could provide a script that gives a warning at 80%

The SQL that I use to determine the ASM Flash Recovery Usage is -->

SQL> COL % FORMAT 99.0
SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 "%" FROM v$asm_diskgroup;

NAME FREE_MB TOTAL_MB %
------------------------------ ---------- ---------- ----------
DATA 36054 40960 88.0224609
REDOLOG 40859 40960 99.753418
BACKUP 17885 20480 87.3291016
OCRVOTE 10145 10240 99.0722656

At the moment the usage is fine, but I'd like a shell script that I can schedule via cron, on an hourly basis and send an email, warning me that any of the ASM disks usage has gone above 80%

I found this script, which should make things easier - but as a novice shell programmer - unclear to me

#!/bin/bash

#asmfreespacecheck.sh

echo $1=lower limit of freespace below which we need to raise and alert
echo $2=list of email addresses to send alert to (delimited)

echo may need to cd to the location of your scripts here...
$ORACLE_HOME/bin/sqlplus -s -l /nolog <<EOF |awk '(NF>8&&$9=="ERROR") {print "\"",$1,$8,$9,"\""}'|xargs -rt -l1 mail $2 -s
connect / AS SYSDBA
@asmfreespace.sql $1
exit

Thanks in advance

kitaman · #2 (**permalink**) 06-08-11, 19:35

Can you post the output of just the following

Code:

#/bin/bash
echo input is $1
$ORACLE_HOME/bin/sqlplus -s -l /nolog <<EOF
connect / AS SYSDBA
@asmfreespace.sql $1
exit
EOF

The script that you posted, does not match the output that you show.
Do you also have the asmfreespace.sql script?

tspoon · #3 (**permalink**) 06-10-11, 03:12

script for asmfreespace

REM

PROMPT ErrorThreholdPCT represents the precentage of free disk space below which an error condition should be noted.
DEF ErrorThresholdPct=&&1
SET ECHO off VER off FEEDBACK off LINES 128

COLUMN DGName FORMAT A32 HEADING "ASM Disk Group"
COLUMN TotalMB FORMAT 9,999,999,999 HEADING "Total Space (MB)"
COLUMN UsedMB FORMAT 9,999,999,999 HEADING "Used Space (MB)"
COLUMN FreeMB FORMAT 9,999,999,999 HEADING "|Free Space (MB)"
COLUMN UsableMB FORMAT 9,999,999,999 HEADING "Usable|Free Space (MB)"
COLUMN UsedPct FORMAT 999.99 HEADING "Used%"
COLUMN FreePct FORMAT 999.99 HEADING "Free%"
COLUMN UsablePct FORMAT 999.99 HEADING "Usable|Free%"
COLUMN Msg FORMAT A5 HEADING "State"
SELECT name DGName
,total_mb TotalMB
,total_mb-free_mb UsedMB
,free_mb FreeMb
,usable_file_mb UsableMB
,ROUND(((total_mb-free_mb)/total_mb)*100,2) UsedPct
,ROUND((free_mb/total_mb)*100,2) FreePct
,ROUND((usable_file_mb/total_mb)*100,2) UsablePct
,CASE WHEN CEIL((usable_file_mb/total_mb)*100) < &&ErrorThresholdPct THEN 'ERROR' ELSE 'OK' END Msg
FROM v$asm_diskgroup
ORDER BY 8 ASC;
REM

---- apologies and thanks

kitaman · #4 (**permalink**) 06-10-11, 10:13

Code:

sampl output
ASM DISK GROUP   TOTAL SPACE USED SPACE FREE SPACE USED% FREE% STATE
diskname              99999            66666    33333              66         33      OK

Code:

#!/bin/ksh
read heading
while read dgname totalmb usedmb freemb pctused pctfree status
do
#possible addtion
if [ "$status" != "OK" ]
then
  mail -s "Trouble $dgname $status" $mailinglist<boilerplate.txt
fi
#
if [ $pctused -gt 80 ]
then
   mail -s "$dgname is at $pctused" $mailinglist <boilerplate2.txt
fi
done

Then pipe the output of the sql to this.
There is one caveat, if DGNAME has embedded spaces this will not work. as the script uses spaces as field separators.
Read how to use "export" to convert $2 to $maillist and have it available to all scripts in the session.

tspoon · #5 (**permalink**) 06-13-11, 05:06

Hello,

thanks for your reply, but I do not understand.

I can run the asmfreespace sql script fine - no issues, however it does prompt me for a value, which I do not want - as if this is running via cron, I will be unable to do this

Your piece of code - do I create a shell script and include your piece of code?

Can you give an example of what you mean

The output of the SQL script does exactly what I need

SQL> @asmfreespace
ErrorThreholdPCT represents the precentage of free disk space below which an error condition should be noted.
Enter value for 1: 15

ASM Disk Group Total Space (MB) Used Space (MB) Free Space (MB) Free Space (MB) Used% Free% Free% State
-------------------------------- ---------------- --------------- --------------- --------------- ------- ------- ------- -----
BACKUP 20,480 5,563 14,917 14,917 27.16 72.84 72.84 OK
DATA 40,960 4,916 36,044 36,044 12.00 88.00 88.00 OK
OCRVOTE 10,240 95 10,145 10,145 .93 99.07 99.07 OK
REDOLOG 40,960 101 40,859 40,859 .25 99.75 99.75 OK
SQL>

I now need your script to read the "% used" field and if it is above 85% then it should send an email to me.

I will test the script and if all is working well, will schedule this on a daily basis via cron.

Thanks again and apologies for my misunderstanding

kitaman · #6 (**permalink**) 06-13-11, 09:52

Lets suppose that the script that I wrote is called "notify.ksh".
Then replace

Code:

$ORACLE_HOME/bin/sqlplus -s -l /nolog <<EOF |awk '(NF>8&&$9=="ERROR") {print "\"",$1,$8,$9,"\""}'|xargs -rt -l1 mail $2 -s

with

Code:

$ORACLE_HOME/bin/sqlplus -s -l /nolog <<EOF |notify.ksh $1 $2

Now the output of asmfreespace.sql is used as input to notify.ksh (make sure that notify.ksh is in your $PATH, or add the path to the statement.)
Set mailing list = $2, and set threshold=$1.
Then replace the following line

Code:

if [ $pctused -gt 80 ]

with

Code:

if [ $pctused -gt $threshold ]

tspoon · #7 (**permalink**) 06-16-11, 05:04

Hi,

I am now testing your procedure - with not much joy, but I think it's me and my lack of scripting knowledge

As you can see I have 2 scripts

rw-r--r-- 1 oracle oinstall 1068 Jun 13 09:55 asmfreespace.sql
-rwxrwxrwx 1 oracle oinstall 139 Jun 16 09:49 notify.sh

The first script - notify.sh does the following:-

Code:

#/bin/bash
echo input is $1
$ORACLE_HOME/bin/sqlplus -s -l /nolog <<EOF |notify.sh $1 $2
connect / AS SYSDBA
@asmfreespace.sql $1
exit
EOF

then asmfreespace.sql does the following:-

Code:

REM

PROMPT ErrorThreholdPCT represents the precentage of free disk space below which an error condition should be noted.
DEF ErrorThresholdPct=&&1
SET ECHO off VER off FEEDBACK off LINES 128

COLUMN DGName FORMAT A32 HEADING "ASM Disk Group"
COLUMN TotalMB FORMAT 9,999,999,999 HEADING "Total Space (MB)"
COLUMN UsedMB FORMAT 9,999,999,999 HEADING "Used Space (MB)"
COLUMN FreeMB FORMAT 9,999,999,999 HEADING "|Free Space (MB)"
COLUMN UsableMB FORMAT 9,999,999,999 HEADING "Usable|Free Space (MB)"
COLUMN UsedPct FORMAT 999.99 HEADING "Used%"
COLUMN FreePct FORMAT 999.99 HEADING "Free%"
COLUMN UsablePct FORMAT 999.99 HEADING "Usable|Free%"
COLUMN Msg FORMAT A5 HEADING "State"
SELECT name DGName
,total_mb TotalMB
,total_mb-free_mb UsedMB
,free_mb FreeMb
,usable_file_mb UsableMB
,ROUND(((total_mb-free_mb)/total_mb)*100,2) UsedPct
,ROUND((free_mb/total_mb)*100,2) FreePct
,ROUND((usable_file_mb/total_mb)*100,2) UsablePct
,CASE WHEN CEIL((usable_file_mb/total_mb)*100) < &&ErrorThresholdPct THEN 'ERROR' ELSE 'OK' END Msg
FROM v$asm_diskgroup
ORDER BY 8 ASC;
REM

However when I run notify.sh - it displays the following errors

Code:

[oracle@ukedxdtmtdbs01a scripts]$ ./notify.sh
input is
./notify.sh: line 3: notify.sh: command not found

[oracle@ukedxdtmtdbs01a scripts]$ ls -ltr $ORACLE_HOME/bin/sqlplus
-rwxr-x--x 1 oracle oinstall 9197 May 19 18:41 /u01/app/oracle/product/11.2.0/db_1/bin/sqlplus
[oracle@ukedxdtmtdbs01a scripts]$

here is my path variable - what do I need to do to the path variable to get this procedure to work

Code:

PATH=/u01/app/oracle/product/11.2.0/db_1/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin

Do I need to pass a parameter(s) when I execute the procedure . i.e.

./notify.sh 15 name@company.com for example

Thanks again

kitaman · #8 (**permalink**) 06-16-11, 06:03

You need two scripts, asmfreespacecheck.sh, and notify.sh
asmfreespace:

Code:

#!/bin/bash

#asmfreespacecheck.sh

echo $1=lower limit of freespace below which we need to raise and alert
echo $2=list of email addresses to send alert to (delimited)

echo may need to cd to the location of your scripts here...
$ORACLE_HOME/bin/sqlplus -s -l /nolog <<EOF |notify.sh $1 $2
connect / AS SYSDBA
@asmfreespace.sql $1
exit

and notify.sh

Code:

#!/bin/ksh
read heading
while read dgname totalmb usedmb freemb pctused pctfree status
do
#possible addtion
if [ "$status" != "OK" ]
then
  mail -s "Trouble $dgname $status" $mailinglist<boilerplate.txt
fi
#
if [ $pctused -gt 80 ]
then
   mail -s "$dgname is at $pctused" $mailinglist <boilerplate2.txt
fi
done

and then execute it as

Code:

asmfreespacecheck.sh 80 emailaddresslist

Finally create two text files boilerplate.txt and boilerplate2.txt, that contain a short story about the dangers of using too much disk space.

tspoon · #9 (**permalink**) 06-16-11, 08:28

OK,

I created 2 scripts: notify.sh and asmfreespacecheck.sh

Code:

notify.sh:

#!/bin/ksh
read heading
while read dgname totalmb usedmb freemb pctused pctfree status
do
#possible addtion
if [ "$status" != "OK" ]
then
  mail -s "Trouble $dgname $status" $mailinglist<boilerplate.txt
fi
#
if [ $pctused -gt 80 ]
then
   mail -s "$dgname is at $pctused" $mailinglist <boilerplate2.txt
fi
done

Code:

asmfreespacecheck.sh
#!/bin/bash

#asmfreespacecheck.sh

echo $1=80
echo $2=name@company.com

echo may need to cd to the location of your scripts here...
$ORACLE_HOME/bin/sqlplus -s -l /nolog <<EOF |notify.sh $1 $2
connect / AS SYSDBA
@asmfreespace.sql $1
exit

when I execute I receive this error:

Code:

./asmfreespacecheck.sh 80 name@company.com
80=80
name@company.com=name@company.com
may need to cd to the location of your scripts here...
./asmfreespacecheck.sh: line 9: notify.sh: command not found

I also have 2 boiler scripts

Code:

[oracle@ukedxdtmtdbs01a scripts]$ ls -ltr boiler*
-rw-r--r-- 1 oracle oinstall 36 Jun 16 13:15 boilerplate.txt
-rw-r--r-- 1 oracle oinstall 36 Jun 16 13:15 boilerplate2.txt

[oracle@ukedxdtmtdbs01a scripts]$ more  boilerplate.txt
#above threshold limit of 80%
exit

[oracle@ukedxdtmtdbs01a scripts]$ more boilerplate2.txt
# Under threshold limit of 80%
exit
[oracle@ukedxdtmtdbs01a scripts]$

All scripts are in the same folder. Is there something I am still doing incorrectly?

kitaman · #10 (**permalink**) 06-16-11, 08:48

Unix is different to DOS/Windows operating systems in that the current directory is not automatically searched.
Either add the current directory to your PATH, add ":." to the end of the PATH, or change the asmfreecheck script so that notify.sh can be found, either by using its absolute name /home/tspoon/notify.sh or adding ./ to the beginning of it.

tspoon · #11 (**permalink**) 06-16-11, 10:13

I've made the changes to include that notify.sh runs directly from the directory specified

Code:

$ORACLE_HOME/bin/sqlplus -s -l /nolog <<EOF |/u01/app/oracle/product/11.2.0/db_1/sysman/admin/scripts/notify.sh $1 $2

but will give following error:

[oracle@ukedxdtmtdbs01a scripts]$ ./asmfreespacecheck.sh 80 name@company.com
80=80
name@company.com=name@company.com
You must specify direct recipients with -s, -c, or -b.
/u01/app/oracle/product/11.2.0/db_1/sysman/admin/scripts/notify.sh[11]: [: argument expected
You must specify direct recipients with -s, -c, or -b.
/u01/app/oracle/product/11.2.0/db_1/sysman/admin/scripts/notify.sh[11]: [: argument expected
You must specify direct recipients with -s, -c, or -b.
You must specify direct recipients with -s, -c, or -b.
/u01/app/oracle/product/11.2.0/db_1/sysman/admin/scripts/notify.sh[11]: [: ---------------: more tokens expected
You must specify direct recipients with -s, -c, or -b.
You must specify direct recipients with -s, -c, or -b.
You must specify direct recipients with -s, -c, or -b.
You must specify direct recipients with -s, -c, or -b.
You must specify direct recipients with -s, -c, or -b.
You must specify direct recipients with -s, -c, or -b.
You must specify direct recipients with -s, -c, or -b.
You must specify direct recipients with -s, -c, or -b.
[oracle@ukedxdtmtdbs01a scripts]$

Although it has this piece of code in notify.sh

Code:

 mail -s "Trouble $dgname $status" $mailinglist < boilerplate.txt

Any yet if I do this from the command line it works - with no problem

Code:

[oracle@ukedxdtmtdbs01a scripts]$ mail -s "$dgname is at $pctused" name@company.com < boilerplate.txt
[oracle@ukedxdtmtdbs01a scripts]$

kitaman · #12 (**permalink**) 06-16-11, 10:40

/u01/app/oracle/product/11.2.0/db_1/sysman/admin/scripts/notify.sh[11]: [: argument expected

Indicates that the problem is on line eleven of notify.sh.
Check that $pctused has a value, and that it is numeric.
Spaces in if statements are important. There must be a space before and after each element (variable, operator).

You could change the line of code in post 11 from "|notify.sh......" to >/tmp/asmreport.rpt, and then run
"notify.sh $1 $2 </tmp/asmreport.rpt"
until each piece is fully debugged.

tspoon · #13 (**permalink**) 06-16-11, 11:01

Hello,

I am more confused now - not sure how pctused can have a value, as
it has not run the SQL script to obtain the value.

$1 is already defined at 80
$2 has the email address to send it to

kitaman · #14 (**permalink**) 06-16-11, 11:12

When you run asmfreespace.sh, the following happens in the following order.
sqlplus is loaded, and reads all the input down to the EOF.
The asmfreespace.sql script is run.
The output of this script is not saved, but is piped as input to notify.sh
notify.sh runs and may or may not create an email.
By changing the "|" to ">" in the line that has notify.sh, you can save the output of the sql script to a file, and then confirm that its format matches the format expected by notify.sh

kitaman · #15 (**permalink**) 06-16-11, 11:18

If the output of the sql script has blank lines, you would get that error message.

Internet Services

Web Development

Digital Marketing

Consumer Tech