ASM statistics script


#!/bin/ksh 
############################################################################ 
# Script Name..: 
# Description..: 
# Author.......: Michael Culp 
# Date.........: / /2012 
# Version......: 
# Modified By..: 
# Date Modified: 
# Comments.....: 
# Schema owner.: 
#    alter session set current 
# Login User...: 
# Run Order....: 
# Dependent on.: 
# Script type..: # 
############################################################################ 

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 

echo "ASM files stored in ASM instance (better)" 
echo ----------------------------------------------------- 
sqlplus -s "/ as sysdba" <<EOF 
SET LINESIZE  150 
SET PAGESIZE  9999 
SET VERIFY    off 
COLUMN full_alias_path        FORMAT a63                  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)
     , '&lt;DIRECTORY&gt;')  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; 


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 a20    

select path
       , state
       , total_mb
       , free_mb
  from v\$asm_disk; 

EOF 



echo "IO Stats ASM disks" 
echo ----------------------------------------------------------- 
sqlplus -s "/ as sysdba" <<EOF    
set pagesize 66    
set lines 120    
column path format a20    
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; 



echo "Amount of space left on individual ASM disk devices" 
echo ----------------------------------------------------------- 
sqlplus -s "/ as sysdba" &lt;&lt;EOF    
set pagesize 66    
set lines 100    
column path format a20    
select path
      ,state
      ,total_mb
      ,free_mb
 from v\$asm_disk; 

EOF 

echo "IO Stats ASM disks" 
echo ----------------------------------------------------------- 
sqlplus -s "/ as sysdba" <<EOF    

set pagesize 66    
set lines 120    
column path format a20    
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 

#!/bin/ksh 
############################################################################ # Script Name..: # Description..: # Author.......: Michael Culp # Date.........: / /2012 # Version......: # Modified By..: # Date Modified: # Comments.....: # Schema owner.: #    alter session set current # Login User...: # Run Order....: # Dependent on.: # Script type..: # ############################################################################ echo "ASM File types stored in this instance" echo ----------------------------------------------------- sqlplus -s "/ as sysdba" &lt;&lt;EOF    set lines 120    set pagesize 66    column name format a25    column type format a20    select distinct type      from v\$asm_file; EOF echo "ASM files stored in ASM instance (better)" echo ----------------------------------------------------- sqlplus -s "/ as sysdba" &lt;&lt;EOF SET LINESIZE  150 SET PAGESIZE  9999 SET VERIFY    off COLUMN full_alias_path        FORMAT a63                  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), '&lt;DIRECTORY&gt;')  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; echo "Amount of space left on individual ASM disk devices" echo ----------------------------------------------------------- sqlplus -s "/ as sysdba" &lt;&lt;EOF    set pagesize 66    set lines 100    column path format a20    select path,           state,           total_mb,           free_mb      from v\$asm_disk; EOF echo "IO Stats ASM disks" echo ----------------------------------------------------------- sqlplus -s "/ as sysdba" &lt;&lt;EOF    set pagesize 66    set lines 120    column path format a20    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                                                                                                123,1         Bot echo "Amount of space left on individual ASM disk devices" echo ----------------------------------------------------------- sqlplus -s "/ as sysdba" &lt;&lt;EOF    set pagesize 66    set lines 100    column path format a20    select path,           state,           total_mb,           free_mb      from v\$asm_disk; EOF echo "IO Stats ASM disks" echo ----------------------------------------------------------- sqlplus -s "/ as sysdba" &lt;&lt;EOF    set pagesize 66    set lines 120    column path format a20    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

Leave a Reply

Your email address will not be published. Required fields are marked *