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
I just like the helpful information you supply on your articles.
I’ll bookmark your weblog and take a look at once more right here regularly.
I am rather sure I will learn a lot of new stuff right right here!
Best of luck for the next!
This article offers clear idea in support of the new visitors
of blogging, that actually how to do blogging and site-building.
I am extremely impressed together with your writing skills and also with the
structure in your blog. Is this a paid subject or did
you modify it yourself? Anyway keep up the nice high quality writing,
it is rare to peer a great blog like this one today..
Howdy! This post could not be written any better! Reading through this post reminds me of my good old room mate! He always kept chatting about this. I will forward this article to him. Pretty sure he will have a good read. Many thanks for sharing!
I really appreciate this post. I have been looking everywhere for this! Thank goodness I found it on Bing. You’ve made my day! Thanks again
Oh my goodness! Awesome article dude! Thanks, However I am going through troubles with your RSS. I don’t understand why I am unable to join it. Is there anyone else getting the same RSS issues? Anyone who knows the answer can you kindly respond? Thanx!!
I’m really inspired together with your writing abilities and also with the structure to your blog. Is this a paid subject or did you customize it your self? Either way stay up the excellent quality writing, it抯 rare to look a nice blog like this one today..