ASM Day to Day Life Scripts

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

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;

 $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

select distinct regexp_substr(name,'+.*\/') from v$datafile;
select distinct regexp_substr(member,'+.*\/') from v$logfile;
select distinct regexp_substr(name,'+.*\/') from v$tempfile;

SELECT name AS diskgroup, substr(compatibility,1,12) AS asm_compat,substr(database_compatibility,1,12) AS db_compat FROM V$ASM_DISKGROUP; 

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.%';

export ORACLE_HOME=/u01/app/grid/product/19/grid
export ORACLE_SID=+ASM1
$ORACLE_HOME/bin/sqlplus / as sysasm
 
show parameter asm_diskstring

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;

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;

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;

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 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 DISKGROUP DATA INCLUDING CONTENTS;

alter diskgroup <diskgroup_name> drop disk
<disk_name>,
<disk_name>,
<disk_name>
rebalance power <p_level> <wait|nowait>;

ALTER DISKGROUP DATA ADD DISK '/dev/sda3;

ALTER DISKGROUP DATA DROP DISK DATA_0001;

ALTER DISKGROUP DATA REBALANCE POWER 6;

ALTER DISKGROUP <dg_name> rebalance power <p_level> <wait|nowait>;

alter diskgroup data check;

alter diskgroup data check norepair;