Useful ASM commands/scripts for DBA’s

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

8 replies

  1. Like!! I blog quite often and I genuinely thank you for your information. The article has truly peaked my interest.

  2. 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!

  3. This article offers clear idea in support of the new visitors
    of blogging, that actually how to do blogging and site-building.

  4. 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..

  5. 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!

  6. 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

Leave a Reply to Mayur Deshmukh Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s