ASM Day to Day Life Scripts

by

To identify the number of diskgroup ,status from below query.

1
2
select group_number,name from v$asm_diskgroup;
select group_number, name, state, type from v$asm_diskgroup;

Size of ASM Diskgroup

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    NAME,
    STATE,
    TYPE,
    ROUND(TOTAL_MB / 1024, 2) AS "SIZE_GB",
    ROUND(FREE_MB / 1024, 2) AS "AVAILABLE_GB",
    CASE
        WHEN TOTAL_MB = 0 THEN 0
        ELSE ROUND((TOTAL_MB - FREE_MB) / TOTAL_MB * 100, 2)
    END AS "USED%"
FROM
    v$asm_diskgroup;

Check Diskgroup online/offline Status from OS Prompt

1
2
3
4
5
6
7
8
$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.

1
2
3
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.

1
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.

1
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

1
2
3
4
5
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

1
2
3
4
5
6
7
8
9
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.

1
2
3
4
5
6
7
8
9
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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:

1
2
3
4
5
6
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:

1
2
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;
1
2
3
4
alter diskgroup <diskgroup_name> add disk
'<path>' name <disk_name>,
'<path>' name <disk_name>
rebalance power <p_level> <wait|nowait>;

Drop disk groups

1
DROP DISKGROUP DATA INCLUDING CONTENTS;
1
2
3
4
5
alter diskgroup <diskgroup_name> drop disk
<disk_name>,
<disk_name>,
<disk_name>
rebalance power <p_level> <wait|nowait>;

Add Disks in disk group

1
ALTER DISKGROUP DATA ADD DISK '/dev/sda3;

Drop a Disk

1
ALTER DISKGROUP DATA DROP DISK DATA_0001;

To rebalance diskgroup use below syntax.

1
2
3
ALTER DISKGROUP DATA REBALANCE POWER 6;
 
ALTER DISKGROUP <dg_name> rebalance power <p_level> <wait|nowait>;

Check Disk group

1
2
3
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.