Aychin's Oracle RDBMS Blog

Only for Advanced level Professionals

dfdg Script to display ASM disk usage information and files

This very simple script, can make life much easier. It simulates unix df command, dfdg (df disk group). Each time when You want to check diskgroup usage, or ASM in progress operations or list directories and files (formatted) You need to set ORACLE_SID and login to the sqlplus or asmcmd, write sqls or commands. Using this script will speed up this process.

Examples of dfdg usage:

When executed without any key, script will display diskgroup information:

bash$ dfdg

   ASM Instance name: +ASM5
             GI home: /u01/app/grid/product/11.2.0.3/grid01
GI installation type: CLUSTER

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE         TOTAL_MB    FREE_MB FREE_PCT
------------ ------------------------------ ----------- ---------- -------------------- ----------- ---------- ---------- --------
           1 DATA                                  1024       4096              1048576 MOUNTED        1099383     212474 19%
           2 FLASH                                 1024       4096              1048576 MOUNTED         317440      41522 13%
           3 REDO                                  1024       4096              1048576 MOUNTED          51200      39025 76%

Use -d key to display usage by disks
Use -o key to display asm operations in progress (disk rebalancing)
Use -f  to list files and directories of the disk group

As printed in the output, there is some other keys to display other information, for example usage by disks:

bash$ dfdg -d

   ASM Instance name: +ASM5
             GI home: /u01/app/grid/product/11.2.0.3/grid01
GI installation type: CLUSTER

GROUP_NUMBER NAME                           PATH                                               STATE         OS_MB   TOTAL_MB    FREE_MB FREE_PCT
------------ ------------------------------ -------------------------------------------------- -------- ---------- ---------- ---------- --------
           1 DATA_0000                      /dev/rdisk/disk48                                  NORMAL       824536     824536      22240 3%
           1 DATA_0001                      /dev/rdisk/disk49                                  NORMAL       824536     824536      22208 3%
           1 DATA_0002                      /dev/rdisk/disk50                                  NORMAL       824536     824536      22184 3%
           1 DATA_0003                      /dev/rdisk/disk51                                  NORMAL       824536     824536      22176 3%
           1 DATA_0004                      /dev/rdisk/disk52                                  NORMAL       824536     824536      22184 3%
           2 FLASH_0000                     /dev/rdisk/disk115                                 NORMAL      1923918    1923912     154176 8%
           2 FLASH_0001                     /dev/rdisk/disk90                                  NORMAL      1923918    1923912     154136 8%
           2 FLASH_0002                     /dev/rdisk/disk84                                  NORMAL      1923918    1923912     154104 8%
           3 REDO_0000                      /dev/rdisk/disk61                                  NORMAL        30720      30720       3619 12%

9 rows selected.

Use -d key to display usage by disks
Use -o key to display asm operations in progress (disk rebalancing)
Use -f  to list files and directories of the disk group

Or list of files on the specified diskgroup:

bash$ dfdg -f REDO

   ASM Instance name: +ASM5
             GI home: /u01/app/grid/product/11.2.0.3/grid01
GI installation type: CLUSTER

Session altered.

PL/SQL procedure successfully completed.

FILES                                                                            TYPE                BLOCKS         MB CREATION_DATE       MODIFICATION_DATE
-------------------------------------------------------------------------------- --------------- ---------- ---------- ------------------- -------------------
/ORCLDB

/ORCLDB/ONLINELOG

   group_1.257.748926943                                                         ONLINELOG          1024001       1000 20.04.2011 03:15:43 26.07.2011 05:00:00
   group_2.258.748926951                                                         ONLINELOG          1024001       1000 20.04.2011 03:15:50 26.07.2011 06:00:00
   group_3.259.748926957                                                         ONLINELOG          1024001       1000 20.04.2011 03:15:56 26.07.2011 06:00:00
   group_4.260.748926963                                                         ONLINELOG          1024001       1000 20.04.2011 03:16:03 26.07.2011 06:00:00
   group_5.261.748926969                                                         ONLINELOG          1024001       1000 20.04.2011 03:16:09 26.07.2011 06:00:00
   group_6.262.748926977                                                         ONLINELOG          1024001       1000 20.04.2011 03:16:16 26.07.2011 08:00:00
   group_7.263.748926983                                                         ONLINELOG          1024001       1000 20.04.2011 03:16:22 26.07.2011 09:00:00
   group_8.264.748926989                                                         ONLINELOG          1024001       1000 20.04.2011 03:16:29 26.07.2011 10:00:00
   group_9.265.748926995                                                         ONLINELOG          1024001       1000 20.04.2011 03:16:35 26.07.2011 05:00:00
   group_10.266.748927001                                                        ONLINELOG          1024001       1000 20.04.2011 03:16:41 26.07.2011 05:00:00
   group_11.267.748927009                                                        ONLINELOG          1024001       1000 20.04.2011 03:16:48 26.07.2011 05:00:00
   group_12.268.748927015                                                        ONLINELOG          1024001       1000 20.04.2011 03:16:54 26.07.2011 05:00:00

/ORCLDB/CONTROLFILE

   current.256.748926797                                                         CONTROLFILE           1831         14 20.04.2011 03:13:16 02.05.2011 17:00:00

/ORCLDB/TEMPFILE

   TEMP.301.748933567                                                            TEMPFILE           4096001      32000 20.04.2011 05:06:06 20.04.2011 05:00:00

/ORCLDB/PARAMETERFILE

   spfile.302.748933875                                                          PARAMETERFILE            7          0 20.04.2011 05:11:14 26.07.2011 10:00:00

                                                                                                            ----------
Total size of all files in MBytes on diskgroup REDO                                                              44014

20 rows selected.

Use -d key to display usage by disks
Use -o key to display asm operations in progress (disk rebalancing)
Use -f  to list files and directories of the disk group

You can also use -o key, to display the progress of ongoing rebalancing operation.

Put dfdg into the $ORACLE_HOME/bin directory, because this directory is already included into the PATH variable, then You can run dfdg from any location You want. Or, You can put it to any directory You want, then include this directory to the PATH environment variable. Make the script executable “chmod +x dfdg“. Also, to identify ASM instance, it must be included in to the /etc/oratab file for 10g version, it must be here by default, but if not, include it. For 11g version script will automatically identify all required variables no need to set them and no need in oratab file, just execute the script.

dfdg for Oracle 10g and HP-UX OS (Old version, use last version for 11g under):


#!/usr/local/bin/bash

# ------------------------------------------------------------------------------
# FUNCTION
#   Displays ASM diskgroup information, space usage. Displays usage by DISKS.
#   Displays ongoing operations and list of files on diskgroup.
# NOTES
#   Developed for 10g Oracle Version. The entry must be in the /etc/oratab
#   for ASM instance
# CREATED
#   Aychin Gasimov 03/2011 aychin.gasimov@gmail.com
# MODIFIED
#   Xavier Picamal 08/2012
#       Added -r key
# ------------------------------------------------------------------------------

TMP1=`grep -E '^\+' /etc/oratab`

if [ -z $TMP1 ]; then
  echo "Please check /etc/oratab file, there is no entry for ASM instance."
  exit 1
fi

ORACLE_HOME=`echo ${TMP1//\:/ } | awk {'print $2'}`
ORACLE_SID=`echo ${TMP1//\:/ } | awk {'print $1'}`

cd $ORACLE_HOME/bin

dispinfo () {
 echo "Use -d key to display usage by disks"
 echo "Use -o key to display asm operations in progress (disk rebalancing)"
 echo "Use -r key to display min, max and avergage free megabytes by diskgroups"
 echo "Use -f  to list files and directories of the disk group"
}

case "$1" in
 -d)
    sqlplus -S '/ as sysdba' << EOF
     set linesize 200
     set pagesize 1000
     col free_pct format a8
     col path format a50
     select group_number,name,path,state,total_mb,free_mb,round(free_mb*100/total_mb)||'%' free_pct from v\$asm_disk where header_status='MEMBER';
EOF
    dispinfo;
    ;;
 -o)
    sqlplus -S '/ as sysdba' << EOF
     set linesize 200
     select * from v\$asm_operation;
EOF
    ;;
 -f)
    if [ -e $2 ]; then
     echo "Please specify diskgroup name after -f key"
    else
     sqlplus -S '/ as sysdba' << EOF
      alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
      set linesize 200
      set pagesize 50000
      variable pindx number;
      exec select group_number into :pindx from v\$asm_diskgroup where upper(name)=upper('$2');
      col reference_index noprint
      break on reference_index skip 1 on report
      compute sum label "Total size of all files in MBytes on diskgroup $2" of mb on report
      col type format a15
      col files format a80
      select decode(aa.alias_directory,'Y',sys_connect_by_path(aa.name,'/'),'N',lpad(' ',level)||aa.name) files,  aa.REFERENCE_INDEX,
             b.type, b.blocks, round(b.bytes/1024/1024,0) mb, b.creation_date, b.modification_date
         from v\$asm_alias aa,
              (select parent_index from v\$asm_alias where group_number = :pindx and alias_index=0) a,
              (select * from v\$asm_file where group_number = :pindx) b
         where aa.file_number=b.file_number(+)
         start with aa.PARENT_INDEX=a.parent_index
         connect by prior aa.REFERENCE_INDEX=aa.PARENT_INDEX;
EOF
     dispinfo;
    fi
    ;;
 -r)
     sqlplus -S '/ as sysasm' << EOF
     alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
     set linesize 200
     set pagesize 5000
     select dg.name,dg.allocation_unit_size/1024/1024 "AU(Mb)",min(d.free_mb) Min,
     max(d.free_mb) Max, round(avg(d.free_mb),2) as Avg
     from gv\$asm_disk d, gv\$asm_diskgroup dg
     where d.group_number = dg.group_number
     group by dg.name, dg.allocation_unit_size/1024/1024;
EOF
     dispinfo;
     ;;
 -h)
    dispinfo;
    ;;
  *)
    sqlplus -S '/ as sysdba' << EOF
     set linesize 200
     set pagesize 1000
     col free_pct format a8
     select group_number,name,sector_size,block_size,allocation_unit_size,state,total_mb,free_mb,round(free_mb*100/total_mb)||'%' free_pct from v\$asm_diskgroup;
EOF
    dispinfo;
esac

NEW! Latest version, updated 08/2014

dfdg for 11g Oracle:


#!/bin/bash

# ------------------------------------------------------------------------------
# FUNCTION
#   Displays ASM diskgroup information, space usage. Displays usage by DISKS.
#   Displays ongoing operations and list of files on diskgroup.
# NOTES
#   Developed for 11g Oracle Version. The entry must be in the /etc/oratab
#   for ASM instance
# CREATED
#   Aychin Gasimov 03/2011 aychin.gasimov@gmail.com
#
# MODIFIED
#   Xavier Picamal 08/2012
#       Added -r key
#   Xavier Picamal 09/2012 
#       NEW flag -p for Diskgroup Partners query.
#       NEW flag -fg for FailGroups, Diskgroups and Headers
#   Aychin Gasimov 08/2014
#       Removed reading ASM instance name and GI home info from oratab.
#       Now script automatically identifies all required variables.
#       Prints information about ASM and GI on execution
# ------------------------------------------------------------------------------

# set_crshome Author AG
#             Function for setting global variable CRS_HOME in the script, not in the environment.
#             It also set CRS_INSTALL_TYPE variable to RESTART or CLUSTER depending on installation type.
declare -r set_crshome_NO_OLRLOC=102
declare -r set_crshome_NO_HOMEINV=103
function set_crshome {
  local -i retCode=0
  # Get CRS_HOME from olr.loc
  local olrFile
  local PLATFORM=$(/bin/uname)
  case $PLATFORM in
      Linux) olrFile="/etc/oracle/olr.loc" ;;
      HP-UX) olrFile="/var/opt/oracle/olr.loc" ;;
      SunOS) olrFile="/var/opt/oracle/olr.loc" ;;
        AIX) olrFile="/etc/oracle/olr.loc" ;;
  esac
  if [[ -f $olrFile ]]; then
      CRS_HOME="$(grep -i crs_home $olrFile)" && CRS_HOME=${CRS_HOME#*=}
      [[ -z $CRS_HOME ]] && return $set_crshome_NO_OLRLOC
  else
      return $set_crshome_NO_OLRLOC
  fi
  # Now identify is it Cluster Installation or Oracle Restart
  local -i is_cluster
  if [[ -f $CRS_HOME/inventory/ContentsXML/oraclehomeproperties.xml ]]; then
      is_cluster=$(grep -i "" $CRS_HOME/inventory/ContentsXML/oraclehomeproperties.xml | wc -l)
      [[ $is_cluster -eq 0 ]] && CRS_INSTALL_TYPE="RESTART" || CRS_INSTALL_TYPE="CLUSTER"
  else
      return $set_crshome_NO_HOMEINV
  fi
  return 0 
}

# Setting environment for ASM instance
set_crshome
set_crshome_RES=$?
    
mess_no_olr="No olr.loc file on the system or corrupt entry! Grid Infrastructure not installed or have configuration problems!"
mess_no_homeinv="No oraclehomeproperties.xml in $CRS_HOME/inventory/ContentsXML folder, can not identify Cluster software installation type!"
[[ $set_crshome_RES -eq $set_crshome_NO_OLRLOC ]] && echo -e $mess_no_olr && exit $set_crshome_NO_OLRLOC 
[[ $set_crshome_RES -eq $set_crshome_NO_HOMEINV ]] && echo -e $mess_no_homeinv && exit $set_crshome_NO_HOMEINV 

t_sid=$( ps -eo args | grep -v grep | grep asm_pmon )
if [[ -z $t_sid ]]; then
     echo "ASM instance is not running, can not get pmon process of ASM instance!"
     exit 1
fi
t_sid=${t_sid##*_}

echo -e "\n   ASM Instance name: "$t_sid
echo "             GI home: "$CRS_HOME
echo "GI installation type: "$CRS_INSTALL_TYPE

export ORACLE_HOME=$CRS_HOME
export ORACLE_BASE="$($CRS_HOME/bin/orabase)"
export ORACLE_SID=$t_sid

# End setting environment

SQLPLUS=$ORACLE_HOME/bin/sqlplus

dispinfo () {
 echo "Use -d key to display usage by disks"
 echo "Use -o key to display asm operations in progress (disk rebalancing)"
 echo "Use -r key to display min, max and avergage free megabytes by diskgroups"
 echo "Use -f  to list files and directories of the disk group"
 echo "Use -fg to list failgroups, diskgroups and headers"
 echo "Use -p to list partner disks"
 }

case "$1" in
 -d)
     $SQLPLUS -S '/ as sysasm' << EOF
       set linesize 200
       set pagesize 50000
       col path format a50
       col free_pct format a8
       select group_number,name,path,state,os_mb,total_mb,free_mb,round(free_mb*100/total_mb)||'%' free_pct from v\$asm_disk where header_status='MEMBER';
EOF
     dispinfo;
     ;;
 -o)
     $SQLPLUS -S '/ as sysasm' << EOF
       set linesize 200
       select * from v\$asm_operation;
EOF
     dispinfo;
     ;;
 -f)
     if [ -e $2 ]; then
      echo "Please specify diskgroup name after -f key"
     else
      $SQLPLUS -S '/ as sysasm' << EOF
       alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
       set linesize 200
       set pagesize 50000
       variable pindx number;
       exec select group_number into :pindx from v\$asm_diskgroup where upper(name)=upper('$2');
       col reference_index noprint
       break on reference_index skip 1 on report
       compute sum label "Total size of all files in MBytes on diskgroup $2" of mb on report
       col type format a15
       col files format a80
       select decode(aa.alias_directory,'Y',sys_connect_by_path(aa.name,'/'),'N',lpad(' ',level)||aa.name) files,  aa.REFERENCE_INDEX,
              b.type, b.blocks, round(b.bytes/1024/1024,0) mb, b.creation_date, b.modification_date
         from (select * from v\$asm_alias order by name) aa,
              (select parent_index from v\$asm_alias where group_number = :pindx and alias_index=0) a,
              (select * from v\$asm_file where group_number = :pindx) b
         where aa.file_number=b.file_number(+)
         start with aa.PARENT_INDEX=a.parent_index
         connect by prior aa.REFERENCE_INDEX=aa.PARENT_INDEX;
EOF
     dispinfo;
     fi;
     ;;
 -r)
     $SQLPLUS -S '/ as sysasm' << EOF
     alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
     set linesize 200
     set pagesize 5000
     select dg.name,dg.allocation_unit_size/1024/1024 "AU(Mb)",min(d.free_mb) Min,
     max(d.free_mb) Max, round(avg(d.free_mb),2) as Avg
     from gv\$asm_disk d, gv\$asm_diskgroup dg
     where d.group_number = dg.group_number
     group by dg.name, dg.allocation_unit_size/1024/1024;
EOF
     dispinfo;
     ;;
 # XPA 2012-11-21 -fg flag BEGINS here
 -fg) 
     $SQLPLUS -S '/ as sysasm' << EOF
     set linesize 200
     set pagesize 300
     col path format a50
     select mount_status,header_status,state,redundancy,failgroup,path from v\$asm_disk order by failgroup;
EOF
     dispinfo;
     ;;
 # XPA 2012-11-21 -fg flag ENDS here
 # XPA 2012-09 -p flag begins here
 -p)
    $SQLPLUS -S '/ as sysasm' << EOF     
    alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';     
    set linesize 200     
    set pagesize 50000     
    col "Partner Disks" format a80     
    select d||' => '||listagg(p, ',') within group (order by p) "Partner Disks"
      from (
       select ad1.failgroup||'('||to_char(ad1.disk_number, 'fm00')||')' d,
              ad2.failgroup||'('||listagg(to_char(p.number_kfdpartner, 'fm00'), ',') within group (order by ad1.disk_number)||')' p
       from gv\$asm_disk ad1, x\$kfdpartner p, v\$asm_disk ad2
       where ad1.disk_number = p.disk
         and p.number_kfdpartner=ad2.disk_number
         and ad1.group_number = p.grp
         and ad2.group_number = p.grp
       group by ad1.failgroup, ad1.disk_number, ad2.failgroup
           )
    group by d
    order by d;
EOF
     dispinfo;
     ;;
 -h)
     dispinfo;
     ;;
  *)
     $SQLPLUS -S '/ as sysasm' << EOF
      set linesize 200
      set pagesize 50000
      col free_pct format a8
      select group_number,name,sector_size,block_size,allocation_unit_size,state,total_mb,free_mb,round(free_mb*100/total_mb)||'%' free_pct from v\$asm_diskgroup;
EOF
     dispinfo;
esac

exit 0

P.S.: You are welcome to make any changes, send Your modifications to me, and I will update this version and put Your name under the MODIFIED section.


(c) Aychin Gasimov, 07/2011, Azerbaijan Republic


Advertisements

21 responses to “dfdg Script to display ASM disk usage information and files

  1. Earl Bond August 23, 2012 at 07:58

    Hi Aychin

    Tnank your for sharing this tool. I added this flag and modified legacy queries replacing v$ views by gv$ views, and also included diskgroup name, and other littele details without importance:

    # XPA 2012-08 -r flag begins here
    -r)

    sqlplus -S ‘/ as sysasm’ << EOF
    alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
    set linesize 200
    set pagesize 5000
    select dg.name,dg.allocation_unit_size/1024/1024 "AU(Mb)",min(d.free_mb) Min,
    max(d.free_mb) Max, round(avg(d.free_mb),2) as Avg
    from gv\$asm_disk d, gv\$asm_diskgroup dg
    where d.group_number = dg.group_number
    group by dg.name, dg.allocation_unit_size/1024/1024;
    EOF
    dispinfo;
    ;;
    # XPA 2012-08 -r flag ends here

    I hope this will be useful
    Best Regards
    Xavier

  2. morad October 16, 2012 at 16:32

    can this script work on Linux platform ?
    sorry to ask and not try it myself as I don’t have a test environment and I am afraid to do it on production 😦

    • Earl Bond October 17, 2012 at 10:59

      Hi Morad
      Of course, the dfdg for 11g Oracle and HP-UX OS works fine in RHEL 5.2 / 5.7 and 5.8 platforms. This tool with a slight modification that I mentioned in my previous comment is running in our development, integration, test and production environments.
      Best Regards.
      Xavier

  3. Pingback: DFDG – ASM « Oracle Mine….

  4. mouse pads April 6, 2013 at 12:22

    Superb post but I was wondering if you could write a litte more on this subject?
    I’d be very thankful if you could elaborate a little bit more. Bless you!

  5. buy adipex April 10, 2013 at 04:16

    Great weblog here! Also your web site lots up very fast! What host are you the usage of? Can I am getting your affiliate hyperlink for your host? I want my web site loaded up as fast as yours lol.

  6. cafechitchat.mobi June 26, 2013 at 02:01

    Wow! I’m truly enjoying the layout of your web site. Are you using a custom made template or is this freely available to all individuals? If you don’t want to say the name of it out in the public, please be sure to email me at:
    irenebeavers@peacemail.com. I’d absolutely love to get my hands on this theme! Kudos.

    • Earl Bond June 26, 2013 at 07:35

      Hi friends
      since september last year I added new options to Aychin’s tool:

      # ——————————————————————————
      # FUNCTION
      # Displays ASM diskgroup information, space usage. Displays usage by DISKS.
      # Displays ongoing operations and list of files on diskgroup.
      # NOTES
      # Developed for 11g Oracle Version. The entry must be in the /etc/oratab
      # for ASM instance
      # CREATED
      # Aychin Gasimov 03/2011 aychin.gasimov@gmail.com
      # MODIFIED
      # Xavier Picamal 08/2012 ZERO DIVIDE EXCEPTION traitement in queries.
      # Added GROUP NAME in queries.
      # NEW flag -r SIZE and REBAL DISKGROUP NEEd query.
      # Xavier Picamal 09/2012 NEW flag -p for Diskgroup Partners query.
      # Xavier Picamal 09/2012 NEW flag -fg for FailGroups, Diskgroups and Headers
      # ——————————————————————————

      And here the two new options “-p” and “-fg” :

      case “$1” in
      # XPA 2012-11-21 -fg flag BEGINS here

      -fg) sqlplus -S ‘/ as sysasm’ << EOF
      set linesize 200
      set pagesize 300
      col path format a50
      select mount_status,header_status,state,redundancy,failgroup,path from v\$asm_disk order by failgroup;
      EOF
      dispinfo;
      ;;
      # XPA 2012-11-21 -fg flag ENDS here

      # XPA 2012-09 -p flag begins here
      -p)

      sqlplus -S '/ as sysasm' < ‘||listagg(p, ‘,’) within group (order by p) “Partner Disks”
      from (
      select ad1.failgroup||'(‘||to_char(ad1.disk_number, ‘fm00′)||’)’ d,
      ad2.failgroup||'(‘||listagg(to_char(p.number_kfdpartner, ‘fm00’), ‘,’) within group (order by ad1.disk_number)||’)’ p
      from gv\$asm_disk ad1, x\$kfdpartner p, v\$asm_disk ad2
      where ad1.disk_number = p.disk
      and p.number_kfdpartner=ad2.disk_number
      and ad1.group_number = p.grp
      and ad2.group_number = p.grp
      group by ad1.failgroup, ad1.disk_number, ad2.failgroup
      )
      group by d
      order by d;
      EOF
      dispinfo;
      ;;
      # XPA 2012-08 -r flag begins here

      I hope this is helpful to you
      Best Regards
      Xavier

      • Thomas August 27, 2014 at 09:16

        Thanks for those add ons, but ‘-p’ is somehow crippled.
        Instead of ‘<< EOF' for piping statements into sqlplus there is only a partial 'select' (without the 'select' itself).

        besides that, the use of ' ‘ ’ “ ” seems to make the statement invalid

        regards Thomas

      • Earl Bond August 27, 2014 at 09:37

        Hi Tomas
        Here my current version of -p ) flag
        # XPA 2012-09 -p flag begins here
        -p)

        sqlplus -S ‘/ as sysasm’ < ‘||listagg(p, ‘,’) within group (order by p) “Partner Disks”
        from (
        select ad1.failgroup||'(‘||to_char(ad1.disk_number, ‘fm00′)||’)’ d,
        ad2.failgroup||'(‘||listagg(to_char(p.number_kfdpartner, ‘fm00’), ‘,’) within group (order by ad1.disk_number)||’)’ p
        from gv\$asm_disk ad1, x\$kfdpartner p, v\$asm_disk ad2
        where ad1.disk_number = p.disk
        and p.number_kfdpartner=ad2.disk_number
        and ad1.group_number = p.grp
        and ad2.group_number = p.grp
        group by ad1.failgroup, ad1.disk_number, ad2.failgroup
        )
        group by d
        order by d;
        EOF
        dispinfo;
        ;;

        And the output look like this (RHEL 5.9 Grid 11.2.0.4 ) :
        GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE TOTAL_MB FREE_MB FREE_PCT
        ———— —————- ———– ———- ——————– ———– ———- ———- ——–
        1 DATA 512 4096 1048576 MOUNTED 153597 50747 33%
        2 FRA 512 4096 1048576 MOUNTED 20473 17301 85%
        3 CONFIG_01 512 4096 1048576 MOUNTED 6141 5215 85%
        4 CONFIG_02 512 4096 1048576 MOUNTED 10236 6828 67%
        5 ACFSFILES 512 4096 1048576 MOUNTED 25595 915 4%

        Use -d key to display usage by disks
        Use -o key to display asm operations in progress (disk rebalancing)
        Use -f to list files and directories of the disk group
        Use -r to list diskgroup sizes and rebal needs
        Use -fg to list failgroups, diskgroups and headers
        Use -p to list partner disks
        Use -c to list all ASM and underscore Parameters of the ASM Instance

        Regards.
        Xavier

      • Thomas August 27, 2014 at 10:02

        I wonder what am I missing here:

        every section besides ‘-p’ starts with
        sqlplus -S ‘/ as sysasm’ << EOF
        select

        section '-p' starts with
        sqlplus -S '/ as sysasm' < ‘||listagg(p, ‘,’) within group (order by p) “Partner Disks”
        from ( …

        – the <<EOF
        EOF

        loop is incomplete, there is no beginning

        Thomas

      • Earl Bond August 27, 2014 at 12:13

        Hi Thomas
        I think that copy and paste operation not recognized the < < characters and the rest of the line just to EOF is lost.
        ^XP

      • Earl Bond August 27, 2014 at 12:20

        Hi Thomas
        I post a snap with the -p option here:
        dfdg -p option
        Regards.
        Xavier

      • Thomas August 27, 2014 at 12:42

        my manual OCR has grabbed the relevant parts 😉

        Thanks
        Thomas

  7. AYEB July 1, 2014 at 08:50

    Hi,
    I realy enjoyed this script it work on Linux

  8. Earl Bond August 12, 2014 at 14:01

    Hi all
    I found a bug in -f flag when “alias of a datafiles like _01.dbf exists” the query duplicate values and returns an invalid “Total en MB Size”
    Regards.
    Xavier

  9. Kotti September 10, 2015 at 22:54

    If I want to get the du command directory wise…how is it possible

    DIRECTORY USED_MB MIRRORED_USED_MB

    RECO/abc/ 50G 100G
    RECO/cdc 25G 50G

    something like above.

  10. imerinor October 1, 2015 at 11:22

    Hello,
    I have a AIX machine where t_sid=$(ps -eo args | grep -v grep | grep asm_pmon) return an extra space after the SID, I have added “| xargs” to avoid this problem:

    t_sid=$( ps -eo args | grep -v grep | grep asm_pmon |xargs )

    With this modification works perfect. Thank you for the script!

  11. Thomas Dardenne April 19, 2016 at 17:38

    For the use of dfdg with -f argument, if you want the total size of each directory, you can replace the existing query by this one :
    SELECT
    decode(aa.alias_directory,’Y’,sys_connect_by_path(aa.name,’/’),’N’,lpadaa.aa.name) files,
    aa.REFERENCE_INDEX,
    af.type, af.blocks,
    (SELECT SUM(round(af2.bytes/1024/1024,0))
    FROM
    v\$asm_alias aa2,
    v\$asm_file af2
    WHERE aa2.file_number= af2.file_number (+)
    START WITH aa2.REFERENCE_INDEX||’_’||aa2.file_number = aa.REFERENCE_INDEX||’_’||aa.file_number
    CONNECT BY PRIOR aa2.REFERENCE_INDEX=aa2.PARENT_INDEX
    ) mb,
    af.creation_date, af.modification_date
    FROM
    v\$asm_alias aa,
    v\$asm_file af
    WHERE aa.file_number= af.file_number (+)
    START WITH aa.PARENT_INDEX = (select parent_index from v\$asm_alias where group_number = :pindx and alias_index=0)
    CONNECT BY PRIOR aa.REFERENCE_INDEX=aa.PARENT_INDEX;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: