Dear Readers,
I thought of writing a post to include all ASM related scripts/commands. Its good to have everything you need in single place 🙂
1. Check file-type specific usage details all databases on the server. This is useful when we need to quickly check who occupied major space in a diskgroup.
col gname form a10 col dbname form a10 col file_type form a14 SELECT gname, dbname, file_type, round(SUM(space)/1024/1024) mb, round(SUM(space)/1024/1024/1024) gb, COUNT(*) "#FILES" FROM ( SELECT gname, regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname, file_type, space, aname, system_created, alias_directory FROM ( SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path, system_created, alias_directory, file_type, space, level, gname, aname FROM ( SELECT b.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex , a.system_created, a.alias_directory, c.type file_type, c.space FROM v$asm_alias a, v$asm_diskgroup b, v$asm_file c WHERE a.group_number = b.group_number AND a.group_number = c.group_number(+) AND a.file_number = c.file_number(+) AND a.file_incarnation = c.incarnation(+) ) START WITH (mod(pindex, power(2, 24))) = 0 AND rindex IN ( SELECT a.reference_index FROM v$asm_alias a, v$asm_diskgroup b WHERE a.group_number = b.group_number AND ( mod(a.parent_index, power(2, 24))) = 0 ) CONNECT BY prior rindex = pindex ) WHERE NOT file_type IS NULL and system_created = 'Y' ) GROUP BY gname, dbname, file_type ORDER BY gname, file_type, GB /
2. Check orphaned files in ASM diskgroups (Previous incarnation), which doesn’t belong to any of running databases on the server. This is helpful when we need to make a space in a ASM diskgroup. Make sure all databases are up & running before running a script. This scripts works on Oracle version 12 & above.
set pagesize 0 set linesize 200 col full_alias_path format a80 /*+ ---------------------------------------------------------------- Query will return all the files stored on ASM but not currenlty opened by any database client of the diskgroups ordered by group number, file type ---------------------------------------------------------------*/ select * from ( /*+ ----------------------------------------------------------------- 1st branch returns all the files stored on ASM -----------------------------------------------------------------*/ select x.gnum,x.filnum,x.full_alias_path,f.ftype from ( SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path FROM (SELECT g.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex,a.group_number gnum,a.file_number filnum FROM v$asm_alias a, v$asm_diskgroup g WHERE a.group_number = g.group_number) START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x, (select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f where x.filnum != 4294967295 and x.gnum=f.gnum and x.filnum=f.filnum MINUS /*+ -------------------------------------------------------------- 2nd branch returns all the files stored on ASM and currently opened by any database client of the diskgroups -----------------------------------------------------------------*/ select x.gnum,x.filnum,x.full_alias_path,f.ftype from ( select distinct GROUP_KFFOF gnum, NUMBER_KFFOF filnum from X$KFFOF where NUMBER_KFFOF >= 256) l, ( SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path FROM (SELECT g.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex,a.group_number gnum,a.file_number filnum FROM v$asm_alias a, v$asm_diskgroup g WHERE a.group_number = g.group_number) START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex ) x, (select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f where x.filnum != 4294967295 and x.gnum=l.gnum and x.filnum=l.filnum and x.gnum=f.gnum and x.filnum=f.filnum) q order by q.gnum,q.ftype ;
3. Create a ASM diskgroup:
CREATE DISKGROUP DATA1 DISK '/dev/rasm_hdisk001' name DATA1_001 '/dev/rasm_hdisk002' name DATA1_002;
4. Drop a ASM diskgroup:
DROP DISKGROUP DATA1 INCLUDING CONTENTS;
5. Check current space usage details in ASM diskgroup:
IN GB:
set lines 300 select group_number,name,total_mb/1024 TOTAL_GB,(total_mb-free_mb)/1024 USED_GB,free_mb/1024 FREE_GB,round(free_mb/total_mb*100,2) FREE_PERCENT from v$asm_diskgroup;
IN MB:
set lines 300 select group_number,name,total_mb,(total_mb-free_mb) USED_MB, free_mb,round(free_mb/total_mb*100,2) FREE_PERCENT from v$asm_diskgroup;
IN TB:
set lines 300 select group_number,name,total_mb/1024/1024 TOTAL_TB,(total_mb-free_mb)/1024/1024 USED_TB,free_mb/1024/1024 FREE_TB,round(free_mb/total_mb*100,2) FREE_PERCENT from v$asm_diskgroup;
USING ASMCMD:
asmcmd lsdg
6. Check current ASM disks for a diskgroup:
set lines 300 set pages 300 col path for a40 select name,path,OS_MB/1024 from v$asm_disk where group_number=1 order by 2;
7. Check candidate disk available on system:
set lines 300 set pages 300 col path for a40 select path,OS_MB/1024 from v$asm_disk where HEADER_STATUS in ('FORMER','CANDIDATE','PROVISIONED') order by 1;
8. Add a disk in ASM diskgroup:
alter diskgroup DATA1 add disk '/dev/rasm_hdisk001' name DATA1_001 rebalance power 20;
9. Add multiple disks in ASM diskgroup:
alter diskgroup DATA1 add disk '/dev/rasm_hdisk001' name DATA1_001, '/dev/rasm_hdisk002' name DATA1_002 rebalance power 20;
10. Drop a disk from ASM diskgroup:
alter diskgroup DATA1 drop disk DATA1_002 rebalance power 20;
11. Drop multiple disks from ASM diskgroup:
alter diskgroup DATA1 drop disk DATA1_001,DATA1_002 rebalance power 24;
12. Check ASM rebalance operation status:
set lines 300 select * from v$asm_operation;
13. Manually changing rebalancing operation speed for a diskgroup:
ALTER DISKGROUP DATA1 REBALANCE POWER 10;
Hope u will find this post very useful.
Cheers
Regards,
Adityanath
Categories: 12c, 19c, Administration, ASM, automation, Exadata, GRID patching/upgrade, Monitoring, OEM, Oracle 18c, Scripts
Like!! I blog quite often and I genuinely thank you for your information. The article has truly peaked my interest.
Very useful post! Thanks, Adityanath for sharing!!
Hello There,
Appreciate the feedback!!!
Regards,
Adi