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
