How to monitor space usage on ASM diskgroups

  • Last night the recovery area on one of our Oracle Database Appliances went full. This was reported in one of the database alert logs, and we were able to clear out some space before the next log switch, at which point the production would have come to a halt.

    It certainly would have been nice to have a little more warning, like when the disk group was 70% full.

    What options do we have for monitoring disk usage inside ASM?

    If you want monitoring and alerting of oracle components, I would really recommend setting up an Oracle Enterprise Manager installation. It will provide you with ASM monitoring, can send alerts via email, snmp, etc. https://oracle-base.com/articles/13c/cloud-control-13cr1-installation-on-oracle-linux-6-and-7

    @Patrick, interestingly we spent more time managing OEM than we did Oracle DB, so we discontinued our use of OEM some time back.

  • Philᵀᴹ

    Philᵀᴹ Correct answer

    7 years ago

    Just use asmcmd. For example:

    [[email protected] ~]$ . oraenv
    ORACLE_SID = [+ASM] ? +ASM
    The Oracle base remains unchanged with value /u01/app/oracle
    [[email protected] ~]$ asmcmd lsdg
    State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
    MOUNTED  EXTERN  N         512   4096  1048576     30708    28479                0           28479              0             N  DATA/
    [[email protected] ~]$ 
    

    Or get the info directly from the ASM Oracle instance:

    [[email protected] ~]$ . oraenv
    ORACLE_SID = [+ASM] ? +ASM
    The Oracle base remains unchanged with value /u01/app/oracle
    [[email protected] ~]$ sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 7 10:44:44 2013
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Automatic Storage Management option
    
    SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage 
         FROM v$asm_diskgroup;
    
    NAME                  FREE_MB   TOTAL_MB PERCENTAGE
    ------------------------------ ---------- ---------- ----------
    DATA                    28479      30708 92.7413052
    
    SQL> 
    

    OEM also has the info when you have that, and can probably be coerced into raising alerts. The `v$asm_diskgroup` view is also available on the "client" database(s) (might depend on exact versions), which is pretty practical if you already have custom monitoring scripts in there.

    The v$asm_diskgroup view is very convenient. Thanks! Although, the TOTAL_MB and FREE_MB columns appear to show raw capacity as opposed to usable space, is that right?

    See Managing Capacity in Disk Groups for the details on that view, @Roy.

  • Try this...

    col gname form a10
    col dbname form a10
    col file_type form a14
    
    SELECT
        gname,
        dbname,
        file_type,
        round(SUM(space)/1024/1024) mb,
        round(SUM(space)/1024/1024/1024) gb,
        COUNT(*) "#FILES"
    FROM
        (
            SELECT
                gname,
                regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname,
                file_type,
                space,
                aname,
                system_created,
                alias_directory
            FROM
                (
                    SELECT
                        concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
                        system_created,
                        alias_directory,
                        file_type,
                        space,
                        level,
                        gname,
                        aname
                    FROM
                        (
                            SELECT
                                b.name            gname,
                                a.parent_index    pindex,
                                a.name            aname,
                                a.reference_index rindex ,
                                a.system_created,
                                a.alias_directory,
                                c.type file_type,
                                c.space
                            FROM
                                v$asm_alias a,
                                v$asm_diskgroup b,
                                v$asm_file c
                            WHERE
                                a.group_number = b.group_number
                            AND a.group_number = c.group_number(+)
                            AND a.file_number = c.file_number(+)
                            AND a.file_incarnation = c.incarnation(+) ) START WITH (mod(pindex, power(2, 24))) = 0
                    AND rindex IN
                        (
                            SELECT
                                a.reference_index
                            FROM
                                v$asm_alias a,
                                v$asm_diskgroup b
                            WHERE
                                a.group_number = b.group_number
                            AND (
                                    mod(a.parent_index, power(2, 24))) = 0
                                and a.name like '&&db_name'
                        ) CONNECT BY prior rindex = pindex )
            WHERE
                NOT file_type IS NULL
                and system_created = 'Y' )
    WHERE
        dbname like '&db_name'
    GROUP BY
        gname,
        dbname,
        file_type
    ORDER BY
        gname,
        dbname,
        file_type
    /
    

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM