ASM queries

#### To know free space  in disk group ##

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'
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;


To know the disk info:
========================
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;


--- To check with single disk

SELECT dg.name,SUBSTR(d.name,1,16) AS asmdisk, d.mount_status,d.HEADER_STATUS,d.total_mb,d.free_mb
      AS diskgroup FROM V$ASM_DISKGROUP dg, V$ASM_DISK d
     WHERE dg.group_number = d.group_number and dg.name='<diskname>';

To find ASM file system:
==========================
SELECT fs_name, available_time, block_size, state, corrupt FROM V$ASM_FILESYSTEM;

To find Candidate/Formar disks:
============================
select name,path,lable,HEADER_STATUS from v$asm_disk where HEADER_STATUS not like 'MEMBER';

Adding disk to diskgroup:
=========================

ALTER DISKGROUP SKS0DB_DG ADD DISK 'ORCL:SKS0_DATA30' rebalance power 8;


For more queries Qeries LInk