15 Awesome Oracle ASM Queries every DBA must know



Last updated on August 25th, 2016 at 09:55 am

Very Useful Oracle ASM queries for day to day activities for Oracle database administrator
HOW TO CLEAR ASM DISKS
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;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;

HOW TO ADD DISK

ALTER DISKGROUP DATA ADD DISK
‘/devices/hdisk*’;

ALTER DISKGROUP DATA ADD DISK
‘/devices/hdisk*4’;

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.state
from v$asm_diskgroup dg, v$asm_disk d
where dg.group_number=d.group_number
order by dg_name, dsk_no;

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

All the Oracle ASM related articles

ASM: Lesson 1: Introduction

ASM : Lesson -2 ASM disks

Oracle ASM : Lesson 3 ASM Diskgroups

Oracle ASM : Lesson 4 Failure Groups and CSS

Oracle ASM Lesson 5 : ASM Parameters

Oracle ASM Lesson 6 : ASM Rebalance

Oracle ASM Lesson 7:ASM Metadata

Oracle ASM Lesson 8: How to move database to ASM storage

How to collect Oracle ASM metadata

Good ASM queries you should know

Oracle ASM best practice to add disk


Leave a Reply