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