ASM queries

To check ASM diskgroup usage details:

To check ASM diskgroup usage details:
-----------------------------------------------------

SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off
COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_gb               FORMAT 999,999,999   HEAD 'Total Size (GB)'
COLUMN free_gb                FORMAT 999,999,999   HEAD 'Free Size (GB)'
COLUMN used_gb                FORMAT 999,999,999   HEAD 'Used Size (GB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_gb used_gb on report
SELECT
 group_number                             group_number
 ,name                                     group_name
 , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                     allocation_unit_size
  , state                                    state
  , type                                     type
  , total_mb/1024                                 total_gb
  ,free_mb/1024                                   free_gb
 , (total_mb - free_mb)/1024                     used_gb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used FROM
    v$asm_diskgroup
where total_mb > 0
order by name;

To Check ASM disk details of a particular diskgroup:
------------------------------------------------------------------------

col path for a45
set lines 300 pages 500
col NAME for a30
col HEADER_STATUS for a15
col STATE for a10
col MODE_STATUS for a11
col FAILGROUP for a10
col MOUNT_STATUS for a11
select GROUP_NUMBER,DISK_NUMBER,NAME,path,HEADER_STATUS,MOUNT_STATUS,MODE_STATUS,STATE,READ_ERRS,WRITE_ERRS,FAILGROUP
from V$asm_disk where GROUP_NUMBER='&n';

 To check offline disk details:
----------------------------------------

SQL> select count(*) from v$asm_disk where MODE_STATUS='OFFLINE' ;

col path for a45
set lines 300 pages 500
col NAME for a30
col HEADER_STATUS for a15
col STATE for a10
col MODE_STATUS for a11
col FAILGROUP for a10
col MOUNT_STATUS for a11
select GROUP_NUMBER,DISK_NUMBER,NAME,path,HEADER_STATUS,MOUNT_STATUS,MODE_STATUS,STATE,READ_ERRS,WRITE_ERRS,FAILGROUP
from V$asm_disk where MODE_STATUS='OFFLINE';

 To check CANDIDATE & FORMER ASM disk details:
---------------------------------------------

col path for a50
set lines 300 pages 300
col NAME for a30
col HEADER_STATUS for a15
select GROUP_NUMBER,DISK_NUMBER,NAME,path,HEADER_STATUS,MOUNT_STATUS,MODE_STATUS,STATE from V$asm_disk where HEADER_STATUS in ('CANDIDATE','FORMER');

Note:
----

CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement

FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a
new disk group with the ALTER DISKGROUP statement.

* To drop a disk from a diskgroup:
    --------------------------------------------

alter diskgroup DATA_DG drop disk DATA_DG_0000  REBALANCE POWER 5;

(Or)

ALTER DISKGROUP DATA_DG DROP DISK DATA_DG_0000;

Note: REBALANCE POWER is optional here.if u omit REBALANCE POWER option then rebalance power defaults to the value of the ASM_POWER_LIMIT initialization parameter.We can check existing power limit by using below command.

show parameter asm_power_limit

 To add a disk to a diskgroup:
------------------------------

alter DISKGOUP DATA_DG add disk '/dev/asm-disk3' REBALANCE POWER 5;

or

ALTER DISKGROUP DATA_DG ADD DISK '/dev/asm-disk3';

Note:We can get disk path details from v$asm_disk view.

* To start rebalance for a diskgroup manually:
-----------------------------------------------------
$ sqlplus / as sysasm
ALTER DISKGROUP DATA_DG REBALANCE POWER 4;

or

ALTER DISKGROUP DATA_DG REBALANCE POWER 4 WAIT;

Note:
The ALTER DISKGROUP...REBALANCE command by default returns immediately so that you can issue other commands while the rebalance operation takes place asynchronously
in the background. You can query the V$ASM_OPERATION view for the status of the rebalance operation.
If you want the ALTER DISKGROUP...REBALANCE command to wait until the rebalance operation is complete before returning, you can add the WAIT keyword to the REBALANCE clause.

To check rebalance operation.
------------------------------------

SELECT group_number, operation, state, power, est_minutes FROM gv$asm_operation;

set lines 300 pages 300
select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM_OPERATION where
GROUP_NUMBER=(select GROUP_NUMBER from v$asm_diskgroup where NAME='<Disk group name>');



* To increase rebalance power for an ongoing rebalance operation
-----------------------------------------------------------------------------------

alter diskgroup DATA_DG rebalance power 6;

Note: if the COMPATIBLE.ASM disk group attribute is set to 11.2.0.2 or higher,
then the range of values of asm_power_limit is 0 to 1024..Default value is 1.

* To halt ongoing ASM rebalance operation:
------------------------------------------

alter diskgroup DATA_DG rebalance power 0;

* To check Disk Group Attributes details:
---------------------------------------

COLUMN name FORMAT A40
COLUMN value FORMAT A50
set lines 300 pages 300

SELECT group_number, name, value FROM v$asm_attribute ORDER BY group_number, name;

(ex:disk_repair_time,compatible.asm)

* To change diskgroup attributes:
---------------------------------

alter DISKGROUP DATA_DG set ATTRIBUTE 'disk_repair_time'='24h';

ALTER DISKGROUP DATA_DG SET ATTRIBUTE 'compatible.asm' = '11.1';

* ASM views:
----------------------
V$ASM_DISKGROUP
V$ASM_DISK
V$ASM_OPERATION
V$ASM_ALIAS
V$ASM_ATTRIBUTE
V$ASM_CLIENT
V$ASM_DISK_IOSTAT
V$ASM_DISK_STAT
V$ASM_DISKGROUP_STAT
V$ASM_FILE
V$ASM_FILESYSTEM
V$ASM_TEMPLATE
V$ASM_USER
V$ASM_USERGROUP
V$ASM_USERGROUP_MEMBER
V$ASM_VOLUME
V$ASM_VOLUME_STAT