asm_common.ksh


#!/bin/ksh
############################################################################
# Script Name..: asm_com.ksh
# Description..: Common library for ASM items
# Author.......: Michael Culp
# Date.........: 10/17/2008
# Version......: .08
# Modified By..:
# Date Modified:
# Comments.....: imported from asm_diskmnt.ksh
# Schema owner.:
#    alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
############################################################################

# set -vx

initialize()
{
hostnm=`hostname -s`
log_file=asm_db_diskspc.log
# This won't be the same on all clusters
crs_dir=/oracle_crs/product/11.2.0/crs_1/bin/
}

get_asm_info_txt()
############################################################################
# Start ASM procedure put a copy in asm_sum.txt
# All this does is create the text file
############################################################################
{

${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" <<EOF > asm_sum.txt

SET LINESIZE  175
SET PAGESIZE  9999
SET HEADING OFF

-- set verify off
-- COLUMN group_name             FORMAT a32           HEAD 'Disk Group Name'
-- COLUMN state                  FORMAT a11           HEAD 'State'
-- COLUMN type                   FORMAT a6            HEAD 'Type'
-- COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
-- COLUMN free_mb                FORMAT 999,999,999   HEAD 'Free Size (MB)'
-- COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
-- COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

select name, state, type, total_mb, free_mb, (total_mb - free_mb) used_mb, ROUND((1-(free_mb / total_mb))*100, 2) pct_used FROM v\$asm_diskgroup ORDER BY pct_used;


-- COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
-- COLUMN lun_size               FORMAT 9999          HEAD 'LUN Size|(GB)'
-- COLUMN total_luns             FORMAT 9999          HEAD 'Total|LUNs'

-- compute sum label "Grand Total: " of total_luns on report

-- SELECT vadg.name group_name,
--       ceil(round(vad.total_mb)/1024) lun_size,
--       count(*) total_luns
-- FROM v\\$asm_diskgroup vadg,
--     v\\$asm_disk vad
-- WHERE vad.group_number=vadg.group_number
-- GROUP BY vadg.name, vad.total_mb
-- ORDER BY vadg.name, vad.total_mb;

EOF

# cat asm_sum.txt
# testdisp=`cat asm_sum.txt`
# echo "this is the echo"
# echo "$testdisp"
# testdisp=$(<asm_sum.txt)
# echo "$testdisp"
#### End procedure
}

asm_file_type()
##################################################################
# Function name: asm_stat
# Description..: show file types in ASM
# Author.......: Michael Culp
##################################################################
{

echo "ASM File types stored in this instance"
echo -----------------------------------------------------

sqlplus -s "/ as sysdba" <<EOF
   set lines 120
   set pagesize 66
   column name format a25
   column type format a20
   select distinct type
     from v\$asm_file;

EOF

}

asm_files()
##################################################################
# Function name: asm_files
# Description..: show file types in ASM
# Author.......: Michael Culp
##################################################################
{

echo
echo
echo "ASM files stored in ASM instance (better)"
echo -----------------------------------------------------
echo
echo

sqlplus -s "/ as sysdba" <<EOF
SET LINESIZE  150
SET PAGESIZE  9999
SET VERIFY    off

COLUMN full_alias_path        FORMAT a80                  HEAD 'File Name'
COLUMN system_created         FORMAT a8                   HEAD 'System|Created?'
COLUMN bytes                  FORMAT 9,999,999,999,999    HEAD 'Bytes'
COLUMN blocks                 FORMAT 9,999,999,999,999    HEAD 'Blocks'
COLUMN space                  FORMAT 9,999,999,999,999    HEAD 'Space'
COLUMN type                   FORMAT a18                  HEAD 'File Type'
COLUMN redundancy             FORMAT a12                  HEAD 'Redundancy'
COLUMN striped                FORMAT a8                   HEAD 'Striped'
COLUMN creation_date          FORMAT a20                  HEAD 'Creation Date'
COLUMN disk_group_name        noprint

BREAK ON report ON disk_group_name SKIP 1

compute sum label ""              of bytes space on disk_group_name
compute sum label "Grand Total: " of bytes space on report

SELECT
    CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path
  , bytes
  , space
  , NVL(LPAD(type, 18), '<DIRECTORY>')  type
  , creation_date
  , disk_group_name
  , LPAD(system_created, 4) system_created
FROM
    ( SELECT g.name            disk_group_name
        , a.parent_index       pindex
        , a.name               alias_name
        , a.reference_index    rindex
        , a.system_created     system_created
        , f.bytes              bytes
        , f.blocks             blocks
        , f.space              space
        , f.type               type
        , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date
      FROM
          v\$asm_file f RIGHT OUTER JOIN v\$asm_alias     a USING (group_number, file_number)
                                    JOIN v\$asm_diskgroup g USING (group_number)
    )
WHERE type IS NOT NULL
START WITH (MOD(pindex, POWER(2, 24))) = 0
    CONNECT BY PRIOR rindex = pindex;

EOF
}

asm_spc_free()
{

echo "Amount of space left on individual ASM disk devices"
echo -----------------------------------------------------------

sqlplus -s "/ as sysdba" <<EOF
   set pagesize 66
   set lines 100
   column path format a25
   select path,
          state,
          total_mb,
          free_mb
     from v\$asm_disk;
EOF

}

asm_io_stat()
{

echo "IO Stats by ASM disk devices"
echo -----------------------------------------------------------

sqlplus -s "/ as sysdba" <<EOF
   set pagesize 66
   set lines 120
   column path format a25
   column read_time heading "Read Time" format 999,999.99
   column write_time                    format 999,999.99
   column writes                        format 999,999,999,999
   column reads                         format 999,999,999,999
   column bytes_written                 format 999,999,999,999
   column bytes_read                    format 999,999,999,999
   select path,
          reads,
          read_time,
          bytes_read,
          writes,
          write_time,
          bytes_written
     from v\$asm_disk;

EOF

}


asm_unbal()
{

echo "ASM Unbalanced report"
echo -----------------------------------------------------------
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
-- spool <some file name>

column "Diskgroup" format A30
column "Imbalance" format 99.9 Heading "Percent|Imbalance"
column "Variance"  format 99.9 Heading "Percent|Disk Size|Variance"
column "MinFree"   format 99.9 Heading "Minimum|Percent|Free"
column "DiskCnt"   format 9999 Heading "Disk|Count"
column "Type"      format A10  Heading "Diskgroup|Redundancy"

SELECT g.name "Diskgroup",
       100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance",
       100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance",
       100*(min(d.free_mb/d.total_mb)) "MinFree",
       count(*) "DiskCnt",
       g.type "Type"
  FROM v\$asm_disk d,
       v\$asm_diskgroup g
 WHERE d.group_number = g.group_number and
       d.group_number <> 0 and
       d.state = 'NORMAL' and
       d.mount_status = 'CACHED'
 GROUP BY g.name, g.type;

-- spool off
EOF


}

asm_stub()
{
sqlplus -s "/ as sysdba" <<EOF
-- spool <some file name>

-- spool off
EOF
}

asm_dg_size()
################################################################
# Script Name..: asm_disk_size.ksh
# Description..: ASM Disk Group sizing
# Author.......: Michael Culp
################################################################
{

echo -----------------------------------------------------------
echo "ASM Diskgroups"
echo -----------------------------------------------------------

sqlplus -s "/ as sysdba" <<EOF
spool asm_diskgroups.txt

SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off

COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

break on report on disk_group_name skip 1

compute sum label "Grand Total: " of total_mb used_mb on report

SELECT
    name                                     group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                     allocation_unit_size
  , state                                    state
  , type                                     type
  , total_mb                                 total_mb
  , (total_mb - free_mb)                     used_mb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
FROM
    v\$asm_diskgroup
ORDER BY
    name;


COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN lun_size               FORMAT 9999          HEAD 'LUN Size|(GB)'
COLUMN total_luns             FORMAT 9999          HEAD 'Total|LUNs'

compute sum label "Grand Total: " of total_luns on report

SELECT
   vadg.name                                   group_name
  ,ceil(round(vad.total_mb)/1024)              lun_size
  ,count(*)                                    total_luns
FROM
   v\$asm_diskgroup vadg,
   v\$asm_disk vad
WHERE
   vad.group_number=vadg.group_number
GROUP BY
   vadg.name
  ,vad.total_mb
ORDER BY
   vadg.name
  ,vad.total_mb;

spool off

EOF

}

asm_disk_2_dg()
######################################################################
# Disk device to diskgroup mapping
######################################################################
{

sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
column name format a20
column value format a20
-- spool <some file name>

SELECT SUBSTR(d.name,1,16) AS asmdisk,
       d.mount_status,
       d.state,
       dg.name AS diskgroup
  FROM V\$ASM_DISKGROUP dg,
       V\$ASM_DISK d
 WHERE dg.group_number = d.group_number
  order by dg.name;

SELECT dg.name AS diskgroup,
       SUBSTR(a.name,1,18) AS name,
       SUBSTR(a.value,1,24) AS value,
       read_only
  FROM V\$ASM_DISKGROUP dg,
       V\$ASM_ATTRIBUTE a WHERE dg.name = 'SHARED_DATA_DG01' AND
       dg.group_number = a.group_number;

SELECT dg.name AS diskgroup,
       SUBSTR(a.name,1,18) AS name,
       SUBSTR(a.value,1,24) AS value,
       read_only
  FROM V\$ASM_DISKGROUP dg,
       V\$ASM_ATTRIBUTE a WHERE dg.name = 'SHARED_FRA_DG01' AND
       dg.group_number = a.group_number;
-- spool off
EOF


}

asm_dsk_dev_lst()
########################################################
# Disk device listing
########################################################
{

echo
echo "ASM Disk devices....."
echo
ls -l /dev/mapper/asm*p1

echo
echo "ASM Disk devices (data)....."
echo
ls -l /dev/mapper/asm_d*p1

echo
echo "ASM Disk devices (fra)....."
echo
ls -l /dev/mapper/asm_f*p1

echo
echo "ASM Disk devices (system)....."
echo
ls -l /dev/mapper/asm_s*p1


# ls -l /dev/mapper/fra*p1
echo
echo "All devices from /dev/mapper directory ....."
echo
ls -l /dev/mapper
ls -l /dev/mapper/oraclevg*

}


asm_dsk_sze()
####################################################
# Shows the sizes of the diskgroups
# No prereq for ASM env
####################################################
{

clear

sqlplus -s "/ as sysdba" <<EOF
set wrap off
set lines 155 pages 9999
col "Group"      for 999
col "Group Name" for a25    Head "Group|Name"
col "Disk Name"  for a10
col "State"      for a10
col "Type"       for a10   Head "Diskgroup|Redundancy"
col "Total GB"   for 9,999,990 Head "Total|GB"
col "Free GB"    for 9,999,990 Head "Free|GB"
col "Imbalance"  for 99.9  Head "Percent|Imbalance"
col "Variance"   for 99.9  Head "Percent|Disk Size|Variance"
col "MinFree"    for 99.9  Head "Minimum|Percent|Free"
col "MaxFree"    for 99.9  Head "Maximum|Percent|Free"
col "DiskCnt"    for 9999  Head "Disk|Count"

prompt
prompt ASM Disk Groups
prompt ===============

SELECT g.group_number  "Group"
,      g.name          "Group Name"
,      g.state         "State"
,      g.type          "Type"
,      g.total_mb/1024 "Total GB"
,      g.free_mb/1024  "Free GB"
,      100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance"
,      100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance"
,      100*(min(d.free_mb/d.total_mb)) "MinFree"
,      100*(max(d.free_mb/d.total_mb)) "MaxFree"
,      count(*)        "DiskCnt"
FROM v\$asm_disk d, v\$asm_diskgroup g
WHERE d.group_number = g.group_number and
d.group_number <> 0 and
d.state = 'NORMAL' and
d.mount_status = 'CACHED'
GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb
ORDER BY 1;
EOF

}

asm_0002()
{
sqlplus -s "/ as sysdba" <<EOF

set wrap off
set lines 175 pages 9999

prompt ASM Disks In Use
prompt ================

col "Group"          for 999
col "Disk"           for 999
col "Header"         for a9
col "Mode"           for a8
col "State"          for a8
col "Created"        for a10          Head "Added To|Diskgroup"
--col "Redundancy"     for a10
--col "Failure Group"  for a10  Head "Failure|Group"
col "Path"           for a19
--col "ReadTime"       for 999999990    Head "Read Time|seconds"
--col "WriteTime"      for 999999990    Head "Write Time|seconds"
--col "BytesRead"      for 999990.00    Head "GigaBytes|Read"
--col "BytesWrite"     for 999990.00    Head "GigaBytes|Written"
col "SecsPerRead"    for 9.000        Head "Seconds|PerRead"
col "SecsPerWrite"   for 9.000        Head "Seconds|PerWrite"

select group_number  "Group"
,      disk_number   "Disk"
,      header_status "Header"
,      mode_status   "Mode"
,      state         "State"
,      create_date   "Created"
--,      redundancy    "Redundancy"
,      total_mb/1024 "Total GB"
,      free_mb/1024  "Free GB"
,      name          "Disk Name"
--,      failgroup     "Failure Group"
,      path          "Path"
--,      read_time     "ReadTime"
--,      write_time    "WriteTime"
--,      bytes_read/1073741824    "BytesRead"
--,      bytes_written/1073741824 "BytesWrite"
,      read_time/reads "SecsPerRead"
,      write_time/writes "SecsPerWrite"
from   v\$asm_disk_stat
where header_status not in ('FORMER','CANDIDATE')
order by group_number
,        disk_number;

EOF

}