Oracle ASM Monitoring

Space used in ASM Disk Groups


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 

/ 


Disks contained within Disk Groups 

SET ECHO        OFF 

SET FEEDBACK    6 

SET HEADING     ON 

SET LINESIZE    180 

SET PAGESIZE    50000 

SET TERMOUT     ON 

SET TIMING      OFF 

SET TRIMOUT     ON 

SET TRIMSPOOL   ON 

SET VERIFY      OFF 



CLEAR COLUMNS 

CLEAR BREAKS 

CLEAR COMPUTES 



COLUMN disk_group_name        FORMAT a25           HEAD 'Disk Group Name' 

COLUMN disk_file_path         FORMAT a25           HEAD 'Path' 

COLUMN disk_file_name         FORMAT a20           HEAD 'File Name' 

COLUMN disk_file_fail_group   FORMAT a20           HEAD 'Fail Group' 

COLUMN total_mb               FORMAT 999,999,999   HEAD 'File 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 ""              OF total_mb used_mb ON disk_group_name 

COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report 



SELECT 

    NVL(a.name, '[CANDIDATE]')                       disk_group_name 

  , b.path                                           disk_file_path 

  , b.name                                           disk_file_name 

  , b.failgroup                                      disk_file_fail_group 

  , b.total_mb                                       total_mb 

  , (b.total_mb - b.free_mb)                         used_mb 

  , ROUND((1- (b.free_mb / b.total_mb))*100, 2)      pct_used 

FROM 

    v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) 

ORDER BY 

    a.name 

/ 


List of ASM files as well as its volume information 
SET ECHO        OFF 

SET FEEDBACK    6 

SET HEADING     ON 

SET LINESIZE    180 

SET PAGESIZE    50000 

SET TERMOUT     ON 

SET TIMING      OFF 

SET TRIMOUT     ON 

SET TRIMSPOOL   ON 

SET VERIFY      OFF 



CLEAR COLUMNS 

CLEAR BREAKS 

CLEAR COMPUTES 



COLUMN full_path              FORMAT a75                  HEAD 'ASM File Name / Volume Name / Device Name' 

COLUMN system_created         FORMAT a8                   HEAD 'System|Created?' 

COLUMN bytes                  FORMAT 9,999,999,999,999    HEAD 'Bytes' 

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('+' || db_files.disk_group_name, SYS_CONNECT_BY_PATH(db_files.alias_name, '/')) full_path 

  , db_files.bytes 

  , db_files.space 

  , NVL(LPAD(db_files.type, 18), '')  type 

  , db_files.creation_date 

  , db_files.disk_group_name 

  , LPAD(db_files.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.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) 

    ) db_files 

WHERE db_files.type IS NOT NULL 

START WITH (MOD(db_files.pindex, POWER(2, 24))) = 0 

    CONNECT BY PRIOR db_files.rindex = db_files.pindex 

UNION 

SELECT 

    '+' || volume_files.disk_group_name ||  ' [' || volume_files.volume_name || '] ' ||  volume_files.volume_device full_path 

  , volume_files.bytes 

  , volume_files.space 

  , NVL(LPAD(volume_files.type, 18), '')  type 

  , volume_files.creation_date 

  , volume_files.disk_group_name 

  , null 

FROM 

    ( SELECT 

          g.name               disk_group_name 

        , v.volume_name        volume_name 

        , v.volume_device       volume_device 

        , f.bytes              bytes 

        , 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_volume    v USING (group_number, file_number) 

                                   JOIN v$asm_diskgroup g USING (group_number) 

    ) volume_files 

WHERE volume_files.type IS NOT NULL 

/

Disks performance metric 
SET ECHO        OFF 

SET FEEDBACK    6 

SET HEADING     ON 

SET LINESIZE    256 

SET PAGESIZE    50000 

SET TERMOUT     ON 

SET TIMING      OFF 

SET TRIMOUT     ON 

SET TRIMSPOOL   ON 

SET VERIFY      OFF 



CLEAR COLUMNS 

CLEAR BREAKS 

CLEAR COMPUTES 



COLUMN disk_group_name    FORMAT a20                    HEAD 'Disk Group Name' 

COLUMN disk_path          FORMAT a25                    HEAD 'Disk Path' 

COLUMN reads              FORMAT 999,999,999,999        HEAD 'Reads' 

COLUMN writes             FORMAT 999,999,999,999        HEAD 'Writes' 

COLUMN read_errs          FORMAT 999,999,999            HEAD 'Read|Errors' 

COLUMN write_errs         FORMAT 999,999,999            HEAD 'Write|Errors' 

COLUMN read_time          FORMAT 999,999,999,999        HEAD 'Read|Time' 

COLUMN write_time         FORMAT 999,999,999,999        HEAD 'Write|Time' 

COLUMN bytes_read         FORMAT 999,999,999,999,999    HEAD 'Bytes|Read' 

COLUMN bytes_written      FORMAT 999,999,999,999,999    HEAD 'Bytes|Written' 



BREAK ON report ON disk_group_name SKIP 2 



COMPUTE sum LABEL ""              OF reads writes read_errs write_errs read_time write_time bytes_read bytes_written ON disk_group_name 

COMPUTE sum LABEL "Grand Total: " OF reads writes read_errs write_errs read_time write_time bytes_read bytes_written ON report 



SELECT
    a.name                disk_group_name 

  , b.path                disk_path 

  , b.reads               reads 

  , b.writes              writes 

  , b.read_errs           read_errs  

  , b.write_errs          write_errs 

  , b.read_time           read_time 

  , b.write_time          write_time 

  , b.bytes_read          bytes_read 

  , b.bytes_written       bytes_written 

FROM 

    v$asm_diskgroup a JOIN v$asm_disk b USING (group_number) 

ORDER BY 

    a.name 

/ 

   
   

   HOME