Automatic Storage Management (ASM)

Here are few SQLs which are used in day to day Database administration when dealing with ASM.



------- Find ASM available groups, disk Paths & it's state
set linesize 190
set pagesize 1000
col free_pct format a8
col path format a20
select group_number,name,state,header_status,path  from v$asm_disk ;


----- Find ASM diskgroup usage details
set linesize 190
set pagesize 1000
column pct_free format 99.99
select name, total_mb/1024 TotalGB, free_mb/1024 FreeeGB, (total_mb -free_mb)/1024 used_gb, free_mb/total_mb *100 pct_free from v$asm_diskgroup;

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;

select FS_NAME,VOL_DEVICE,PRIMARY_VOL,TOTAL_MB,FREE_MB from V$ASM_ACFSVOLUMES;

---- Find Candidate Disks
select header_status,name,path,state,os_mb,total_mb,free_mb from v$asm_disk where header_status ='CANDIDATE';

---- Allocation unit details.

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;

----- Find disks names with specific names/path
set pages 1200
set lines 180
col path for a30
select name, GROUP_NUMBER,DISK_NUMBER,HEADER_STATUS,STATE,TOTAL_MB,PATH from v$asm_disk where path like '%asmt%';

--- command to add the disk to diskgroup.

ALTER DISKGROUP DG1 ADD DISK '/dev/mapper/asmt2d7' , '/dev/mapper/asmt2d8' REBALANCE POWER 11;

-----Command to change the rebalance power of an ongoing operation:

alter diskgroup DATADG rebalance power 6;

----- SQL to check if the disks are properly Re-balanced or not
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;

---- SQL to check the expected time to complete the Rebalance operation.

 select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM_OPERATION where GROUP_NUMBER=&Group_no;

----- SQL to check if the disk rebalance is running on not.

SELECT group_number, operation, state, power, est_minutes FROM v$asm_operation;