ASM Day to Day Life Scripts
5 min readTo identify the number of diskgroup ,status from below query.
select group_number,name from v$asm_diskgroup;
select group_number, name, state, type from v$asm_diskgroup;
Size of ASM Diskgroup
select NAME,TOTAL_MB/1024 "Total In GB", FREE_MB/1024 "FREE In GB", USABLE_FILE_MB/1024 "USABLE In GB", round((TOTAL_MB-free_mb)/TOTAL_MB*100,2) as "% USED" from v$asm_diskgroup;
Check Diskgroup online/offline Status from OS Prompt
$GRID_HOME/bin/crsctl stat res -t |grep -iE -A10 '_DG|DG_'
ora.orcl_DG_DATA.dg(ora.asmgroup)
1 ONLINE ONLINE localhost1 STABLE
2 ONLINE ONLINE localhost2 STABLE
ora.orcl_DG_FLASH.dg(ora.asmgroup)
1 ONLINE ONLINE localhost1 STABLE
2 ONLINE ONLINE localhost2 STABLE
Check Datafile, Controlfile, Logfile existence in respective Diskgroup.
select distinct regexp_substr(name,'+.*\/') from v$datafile;
select distinct regexp_substr(member,'+.*\/') from v$logfile;
select distinct regexp_substr(name,'+.*\/') from v$tempfile;
Check the compatibility version for below diskgroup.
SELECT name AS diskgroup, substr(compatibility,1,12) AS asm_compat,substr(database_compatibility,1,12) AS db_compat FROM V$ASM_DISKGROUP;
Change ASM Compatibility to 18.0.0.0.0.
select 'alter diskgroup '|| name ||' set attribute '||''''||'compatible.asm'||''''||'='||''''||'18.0.0.0.0'';' from v$asm_diskgroup where COMPATIBILITY not like '18.%' and COMPATIBILITY not like '19.%';
ASM_DISKSTRING
export ORACLE_HOME=/u01/app/grid/product/19/grid
export ORACLE_SID=+ASM1
$ORACLE_HOME/bin/sqlplus / as sysasm
show parameter asm_diskstring
Validate Unused Disk
set linesize 132;
set pages 500;
column path format a50;
column name format a20;
select a.inst_id, a.name, b.path, b.MOUNT_DATE,b.header_status,b.TOTAL_MB/1024 as Total_GB
from gv$asm_disk b, gv$asm_diskgroup a
where a.group_number(+)=b.group_number
and a.inst_id(+)=b.inst_id
order by path, inst_id;
To identify the free allocation units for each lun based on the diskgroup number run below query.
set head off
select 'Number of allocated (V) and free (F) Allocation Units' from dual;
col "VF" for a2
set head on
select GROUP_KFDAT "group#", number_kfdat "disk#", v_kfdat "VF", count(*)
from x$kfdat
where GROUP_KFDAT='&DiskGroupNo'
group by GROUP_KFDAT, number_kfdat, v_kfdat
ORDER BY 3 desc;
To determine the fail group names in use, run the following query:
set lines 160
set pages 500
col asm_disk_name format a20
col group_name format a16
col failgroup format a16
select substr(i.host_name,1,16) as node,
g.name as group_name,
d.name as asm_disk_name,
substr(d.path, 1, 32) as path,
d.header_status as header,
d.mode_status as Status,
d.state as State,
(case when d.FAILGROUP = d.name then 'NONE' else d.FAILGROUP end ) as failgroup,
round(d.os_mb/1024) as "LUN-Gbytes"
from gv$instance i, gv$asm_disk d, gv$asm_diskgroup g
where i.inst_id = d.inst_id
and d.inst_id = g.inst_id
and d.group_number = g.group_number
and d.header_status = 'MEMBER'
and g.name like '%ARC%'
order by i.inst_id, d.group_number, d.disk_number, d.failgroup, d.header_status, d.path;
Normally, an unused disk group can be identified by this SQL statement run in ASM:
select name from gv$asm_diskgroup
where NAME != 'GRID_DG' and GROUP_NUMBER in
(select GROUP_NUMBER from gv$asm_diskgroup
minus
select distinct GROUP_NUMBER
from gv$asm_client where DB_NAME not in ('%ASM%'));
Create ASM disk group:
CREATE DISKGROUP disk_group_1 NORMAL REDUNDANCY
FAILGROUP failure_group_1 DISK '/devices/diska1' NAME diska1, '/devices/diska2' NAME diska2, FAILGROUP failure_group_2 DISK '/devices/diskb1' NAME diskb1, '/devices/diskb2' NAME diskb2;
alter diskgroup <diskgroup_name> add disk
'<path>' name <disk_name>,
'<path>' name <disk_name>
rebalance power <p_level> <wait|nowait>;
Drop disk groups
DROP DISKGROUP DATA INCLUDING CONTENTS;
alter diskgroup <diskgroup_name> drop disk
<disk_name>,
<disk_name>,
<disk_name>
rebalance power <p_level> <wait|nowait>;
Add Disks in disk group
ALTER DISKGROUP DATA ADD DISK '/dev/sda3;
Drop a Disk
ALTER DISKGROUP DATA DROP DISK DATA_0001;
To rebalance diskgroup use below syntax.
ALTER DISKGROUP DATA REBALANCE POWER 6;
ALTER DISKGROUP <dg_name> rebalance power <p_level> <wait|nowait>;
Check Disk group
alter diskgroup data check;
alter diskgroup data check norepair;
ASMCMD Commands
cd Changes the current directory to the specified directory du Displays the total disk space occupied by ASM files find Lists the paths of all name under the specified directory ls +DATA/P9494 Lists the contents of an ASM directory lsct Lists information about current ASM clients lsdg Lists all disk groups and their attributes mkalias Creates an alias for a system generated filename mkdir Creates ASM directories pwd Displays the path of the current ASM directory rm or rm -f Deletes the specified ASM files or directories rmalias Deletes the specified alias lsdsk Lists disks visible to ASM md_backup Creates a backup of all of the mounted disk groups md_restore Restores disk groups from a backup mkalias Creates an alias for system generated filenames remap Repairs a range of physical blocks on a disk cp Copies files into and out of ASM
ASMLIB Commands
/etc/init.d/oracleasm start /etc/init.d/oracleasm stop /etc/init.d/oracleasm restart /etc/init.d/oracleasm configure /etc/init.d/oracleasm status /etc/init.d/oracleasm enable /etc/init.d/oracleasm disable /etc/init.d/oracleasm listdisks /etc/init.d/oracleasm deletedisk /etc/init.d/oracleasm scandisks /etc/init.d/oracleasm querydisk /dev/sdd1 /etc/init.d/oracleasm createdisk VOL1 /dev/sdb1 /etc/init.d/oracleasm renamedisk /dev/sdb1 VOL1
ASM Dictionary Views
v$asm_alias Lists all aliases in all currently mounted diskgroups v$asm_client Lists all the databases currently accessing the diskgroups v$asm_disk Lists all the disks discovered by the ASM instance v$asm_diskgroup Lists all the diskgroups discovered by the ASM instance v$asm_file Lists all files that belong to diskgroups mounted by the ASM instance. v$asm_operation Reports information & Rebalance activity about current active operations. v$asm_template Lists all the templates currently mounted by the ASM instance. v$asm_diskgroup_stat Same as v$asm_diskgroup but does discover new diskgroups. v$asm_disk_stat Same as v$asm_disk but does not discover new disks.
