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
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
Leave a Reply