Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2005
    Posts
    29

    Unanswered: script required for Flash back area

    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
    Last edited by tspoon; 06-08-11 at 11:07.

  2. #2
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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?

  3. #3
    Join Date
    Jun 2005
    Posts
    29

    please find enclosed script

    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

  4. #4
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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.
    Last edited by kitaman; 06-10-11 at 11:20.

  5. #5
    Join Date
    Jun 2005
    Posts
    29
    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

  6. #6
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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 ]
    Last edited by kitaman; 06-13-11 at 10:56.

  7. #7
    Join Date
    Jun 2005
    Posts
    29
    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

  8. #8
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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.

  9. #9
    Join Date
    Jun 2005
    Posts
    29
    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?

  10. #10
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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.

  11. #11
    Join Date
    Jun 2005
    Posts
    29
    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]$

  12. #12
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    /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.
    Last edited by kitaman; 06-16-11 at 11:44.

  13. #13
    Join Date
    Jun 2005
    Posts
    29
    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

  14. #14
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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

  15. #15
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    If the output of the sql script has blank lines, you would get that error message.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •