Home » Oracle » Oracle Database » 15 Awesome Oracle ASM Queries every DBA must know

15 Awesome Oracle ASM Queries every DBA must know

Very Useful Oracle ASM queries for day to day activities for Oracle database administrator

HOW TO CLEAR ASM DISKS

The below dd command can be used to zero the disk

dd if=/dev/zero of= path of the disk bs= block size count=10000

QUERY TO FIND THE FILES IN USE BY AN ASM INSTANCE

col full_path format a50
 col full_alias_path format a50
 SELECT 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 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;

HOW TO ADD DISK

The below queries list down various ways to add the disks in the diskgroup

ALTER DISKGROUP DATA ADD DISK'/devices/hdisk'; 
ALTER DISKGROUP DATA ADD DISK'/devices/hdisk4';
ALTER DISKGROUP DATA ADD DISK'/devices/hdisk*'REBALANCE POWER 5 WAIT;
ALTER DISKGROUP DATA ADD DISK'/devices/hdisk5' NAME DATA5,'/devices/hdisk6' NAME DATA6,'/devices/hdisk7' NAME DATA7,'/devices/hdisk8' NAME DATA8,

ASM DISK INFORMATION

set pages 40000 lines 120
col NAME for a15
select GROUP_NUMBER DG#, name, ALLOCATION_UNIT_SIZE AU_SZ, STATE,TYPE, TOTAL_MB, FREE_MB, OFFLINE_DISKS from v$asm_diskgroup;

set pages 40000 lines 120
col PATH for a30
select DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,PATH FROM V$ASM_DISK;

col PATH for a15
col DG_NAME for a15
col DG_STATE for a10
col FAILGROUP for a10
select dg.name dg_name, dg.state dg_state, dg.type, d.disk_number dsk_no,d.path, d.mount_status, d.FAILGROUP, d.statefrom v$asm_diskgroup dg, v$asm_disk dwhere dg.group_number=d.group_numberorder by dg_name, dsk_no;

REBALANCE INFORMATION

select GROUP_NUMBER, OPERATION, STATE, ACTUAL, SOFAR, EST_MINUTES from v$asm_operation

QUERY TO DETECT FILES IN AN ASM DISKGROUP BEFORE DROPPING

col full_path format a50
col full_alias_path format a50
SELECT 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 
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number
AND gname = 'MDDX1')START WITH (mod(pindex, power(2, 24))) = 0 
CONNECT BY PRIOR rindex = pindex;

QUERY TO DETERMINE WHAT DISKGROUPS EXIST AND HOW FULL THEY ARE

SELECT NAME,TOTAL_MB,USABLE_FILE_MB FROM V$ASM_DISKGROUP;

QUERY TO DETERMINE THE STATE AND BALANCE OF DISKGROUPS

Starting in 10.2 this can be easily done with one query

SELECT NAME,STATE,UNBALANCED FROM V$ASM_DISKGROUP;

QUERY TO DETERMINE THE STATE OF THE DISKS WITHIN A DISKGROUP

col name format a12
col path format a25
col mount_status format a7
col header_status format a12
col mode_status format a7
col state format a8
SELECT D.NAME, D.PATH, D.MOUNT_STATUS, D.HEADER_STATUS, D.MODE_STATUS, D.STATE FROM V$ASM_DISK D, V$ASM_DISKGROUP G WHERE G.NAME = '&1'AND D.GROUP_NUMBER = G.GROUP_NUMBER;

Hope you like this article on Oracle ASM queries. Please do provide the feedback

See also  FND_FILE in oracle apps

All the Oracle ASM related articles

Oracle ASM : Learn about Oracle Automatic Storage Management Concepts and Implementation
Oracle ASM disks : Check out how to prepare the OS disks for ASM and how to add them to ASM
Oracle ASM Diskgroups : Oracle ASM uses disk groups to store data files; an Oracle ASM disk group is a collection of disks that Oracle ASM manages as a unit.Learn how to create the ASM diskgroups
Oracle ASM Failure Groups and CSS
Oracle ASM Initialization Parameters
Oracle ASM Rebalance
Oracle ASM Metadata
migrate Oracle database from Non ASM to ASM storage : Learn how to migrate the Oracle Database from filesytem or raw devices to the ASM disk with less downtime
Oracle ASM best practice to add disk
https://docs.oracle.com/cd/E11882_01/server.112/e18951/asmcon.htm

2 thoughts on “15 Awesome Oracle ASM Queries every DBA must know”

  1. I created ASM_DISKGROUP_USAGE.SQL. Here it is:
    SYS> !cat asm_diskgroup_usage.sql
    set pages 45 lines 200
    col compatibility for a15
    compute sum label “Total:” of total_tb total_gb total_mb free_mb used_mb free_tb used_tb on report
    break on report

    select name diskgroup,compatibility,
    round(total_mb/1024/1024,2) total_tb,
    round(free_mb/1024/1024,2) free_tb,
    round((total_mb/1024/1024)-(free_mb/1024/1024),2) used_tb,
    round(total_mb/1024,2) total_gb,total_mb,free_mb,
    (total_mb-free_mb) used_mb,round(((total_mb-free_mb)/total_mb)*100,2) pct_used
    from v$asm_diskgroup order by 1
    /

    clear computes
    clear breaks

    I also could not find a view/table that had ASM history info, so I first created table STORAGE_HISTORY:
    desc ivadmin.storage_history
    Name Null? Type
    —————– ——– ————
    DISKGROUP VARCHAR2(128
    )
    DATEID DATE
    MEGABYTES NUMBER
    FREE_MB NUMBER

    Then created a script that stores ASM info and put it into CRON:
    cat storage_history.sql
    alter session set container=;

    delete from ivadmin.storage_history where sample_date !cat storage_history_year_pivot.sql
    alter session set container=;

    set feedback off pause off verify off
    col dt for a20 noprint new_v dtv

    select to_char(sysdate,’Dl’) dt from dual;

    prompt Current usage for &dtv …
    prompt
    @asm_diskgroup_usage

    col January for 999,999,999
    col February for 999,999,999
    col March for 999,999,999
    col April for 999,999,999
    col May for 999,999,999
    col June for 999,999,999
    col July for 999,999,999
    col August for 999,999,999
    col September for 999,999,999
    col October for 999,999,999
    col November for 999,999,999
    col December for 999,999,999
    col diskgroup for a10

    prompt
    prompt Totals for the year
    select * from
    (select owner diskgroup,trim(to_char(sample_date,’Month’)) month,round(free_mb) free_mb
    from ivadmin.storage_history where owner not like ‘REDO%’ and sample_date >= ’01-JAN-‘||to_char(sysdate,’YY’))
    pivot (avg(free_mb) for month in (‘January’ as “January”,’February’ as “February”,’March’ as “March”,’April’ as “April”,’May’ as “May”,’June’ as “June”,
    ‘July’ as “July”,’August’ as “August”,’September’ as “September”,’October’ as “October”,’November’ as “November”,’December’ as “December”))
    order by 2
    /

    prompt
    prompt Note: Values are average free space in megabytes

    set feedback on head on
    clear columns

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top