Oracle DBA – A lifelong learning experience

ASM disks – lsdg compared with the v$asm_diskgroup view

Posted by John Hallas on July 8, 2016

What is the difference between the summaries of disk space on these two systems? Look at the free_usable_file_MB column and free space

System 1

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  NORMAL  N         512   4096  4194304   72769536   2031032           433152          798940              0             N  DATA_PEX0/
MOUNTED  NORMAL  N         512   4096  4194304  128286720  20004028          2672640         8665694              0             N  DATA_PEX1/
         Group                 Diskgroup     Total  Req'd     Free   Percent Disk Size Percent Percent  Disk
     Group Name       State      Redundancy       GB     GB       GB Imbalance  Variance    Free    Free Count
---------- ---------- ---------- ---------- -------- ------ -------- --------- --------- ------- ------- -----
         1 DATA_PEX0  MOUNTED    NORMAL       71,064    423    1,560       1.4        .0     2.2     3.5   168
         2 DATA_PEX1  MOUNTED    NORMAL      125,280  2,610   16,925        .1        .0    15.6    15.6    48

System 2

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   2621412   595744                0          595744              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576   1048568   700029                0          700029              0             N  FRA/

                                                     Mirror                      Percent Minimum Maximum
           Group                 Diskgroup     Total  Req'd     Free   Percent Disk Size Percent Percent  Disk
     Group Name       State      Redundancy       GB     GB       GB Imbalance  Variance    Free    Free Count
---------- ---------- ---------- ---------- -------- ------ -------- --------- --------- ------- ------- -----
         1 DATA       MOUNTED    EXTERN        2,560      0      582        .2        .0    22.7    22.8    20
         2 FRA        MOUNTED    EXTERN        1,024      0      684        .0        .0    66.8    66.8     8

System 1 is an Exadata stack with NORMAL redundancy whereas System 2 uses EXTERNAL redundancy. All of our systems bar Exadata are configured with dynamic multi-pathing with external redundancy to ensure high availability. – we allow the SAN to manage redundancy

In system 1 when using the second query to interrogate the diskgroups it would appear that we have 1.5Tb of free space in DATA_PEX0  and yet the lsdg command indicates we only have 800Gb free. Quite a significant difference when we have a weekly growth rate of ~250Gb in that diskgroup

SELECT g.group_number  "Group" ,      g.name          "Group Name" ,      g.state         "State" ,      g.type          "Type" ,      g.total_mb/1024 "Total GB" ,      g.free_mb/1024  "Free GB" ,      100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance" ,      100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance" ,      100*(min(d.free_mb/d.total_mb)) "MinFree" ,      100*(max(d.free_mb/d.total_mb)) "MaxFree" ,      count(*)        "DiskCnt" FROM v$asm_disk d, v$asm_diskgroup g WHERE d.group_number = g.group_number and d.group_number <> 0 and d.state = 'NORMAL' and d.mount_status = 'CACHED' GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb ORDER BY 1;

PS the code comes from a very good ASM script from this very blog

This blog by Harald van Breederode explains in a much better way than I could why the mirroring in normal redundancy uses additional space .

As we are quite space challenged on the Exadata storage at the moment I have been asked several times to explain the different values of free space that are being reported – now I can just point them to this blog entry which will re-direct them to Harald’s very good explanation.

Job done

 

 

 

 

6 Responses to “ASM disks – lsdg compared with the v$asm_diskgroup view”

  1. Enrico Sulich said

    Hi John,

    great blog you have here !

    Another good reading is the following MOS Document: Understanding ASM Capacity and Reservation of Free Space in Exadata (Doc ID 1551288.1)

    In short, the Usable_file_MB in lsdg takes redundancy factor into account.

    usable_total_mb = (total_mb – required_mirror_free_mb)/redundancy_factor (2 or 3)
    Usable_file_MB = (Free_MB – required_mirror_free_mb)/redundancy_factor

    In your case,
    usable_total_mb = (72769536 – 433152)/2 = 36168192
    Usable_file_MB = (2031032 – 433152)/2 = 798940

    As you can see, on top of accounting for the Redundancy, the formula also account for the rebalance following the loss of a disk.

    In Exadata, required_mirror_free_mb originally referred to the space required to rebalance after a cell failure but after 11.2.0.4 BP10 it was changed (by patch 14313306) to only account for a disk failure (or two for high redundancy). This resulted in a virtual ‘gain’ of Safely usable space.

    Hope this helps !

    Regards,
    Enrico

    • John Hallas said

      Thanks Enrico, valuable additional information. Andy C also mailed me about the bug you refer to – Bug 14313306 – included 11.2.0.4.10 (exadata)
      Before this the usable disk was massively lower

  2. Hi John,

    When you run your query on system 1 using normal redundancy, should you be including the failure group in the query. Have you got 800G free on each Failure group. Thereby only 800G free when using normal redundancy.

    • John Hallas said

      You are exactly right Scott. Glad to see your are still keeping yourself well educated by reading my blog 🙂
      If you take the System 1 lsdg example first line as a working example. if you multiply 798940 * 2 and then add 433152 you get 2031032 which is Free_Mb
      Free_MB Req_mir_free_MB Usable_file_MB
      2031032 433152 798940

      I could amend the v$asm_diskgroup query to show failgroups and really I should do – however it has never been a problem because 99% of our systems do not use NORMAL or HIGH levels of mirroring – it is all done at the SAN level

      John

  3. Vitaly Kaminsky said

    Hi John,

    I love the original asm_info.sql and thank you for updating it over the years.

    Below is another little thing from Oracle itself – I am using it when telling clients why they are really in trouble when disks begin to fail on Exadatas (after about 3 years).

    Vitaly

    SET SERVEROUTPUT ON
    SET LINES 155
    SET PAGES 0
    SET TRIMSPOOL ON

    DECLARE
    v_num_disks NUMBER;
    v_group_number NUMBER;
    v_max_total_mb NUMBER;

    v_required_free_mb NUMBER;
    v_usable_mb NUMBER;
    v_cell_usable_mb NUMBER;
    v_one_cell_usable_mb NUMBER;
    v_enuf_free BOOLEAN := FALSE;
    v_enuf_free_cell BOOLEAN := FALSE;

    v_req_mirror_free_adj_factor NUMBER := 1.10;
    v_req_mirror_free_adj NUMBER := 0;
    v_one_cell_req_mir_free_mb NUMBER := 0;

    v_disk_desc VARCHAR(10) := ‘SINGLE’;
    v_offset NUMBER := 50;

    v_db_version VARCHAR2(8);
    v_inst_name VARCHAR2(1);

    v_cfc_fail_msg VARCHAR2(500);

    BEGIN

    SELECT substr(version,1,8), substr(instance_name,1,1) INTO v_db_version, v_inst_name FROM v$instance;

    IF v_inst_name ‘+’ THEN
    DBMS_OUTPUT.PUT_LINE(‘ERROR: THIS IS NOT AN ASM INSTANCE! PLEASE LOG ON TO AN ASM INSTANCE AND RE-RUN THIS SCRIPT.’);
    GOTO the_end;
    END IF;

    DBMS_OUTPUT.PUT_LINE(‘—— DISK and CELL Failure Diskgroup Space Reserve Requirements ——‘);
    DBMS_OUTPUT.PUT_LINE(‘ This procedure determines how much space you need to survive a DISK or CELL failure. It also shows the usable space ‘);
    DBMS_OUTPUT.PUT_LINE(‘ available when reserving space for disk or cell failure. ‘);
    DBMS_OUTPUT.PUT_LINE(‘ Please see MOS note 1551288.1 for more information. ‘);
    DBMS_OUTPUT.PUT_LINE(‘. . .’);
    DBMS_OUTPUT.PUT_LINE(‘ Description of Derived Values:’);
    DBMS_OUTPUT.PUT_LINE(‘ One Cell Required Mirror Free MB : Required Mirror Free MB to permit successful rebalance after losing largest CELL regardless of redundancy type’);
    DBMS_OUTPUT.PUT_LINE(‘ Disk Required Mirror Free MB : Space needed to rebalance after loss of single or double disk failure (for normal or high redundancy)’);
    DBMS_OUTPUT.PUT_LINE(‘ Disk Usable File MB : Usable space available after reserving space for disk failure and accounting for mirroring’);
    DBMS_OUTPUT.PUT_LINE(‘ Cell Usable File MB : Usable space available after reserving space for SINGLE cell failure and accounting for mirroring’);
    DBMS_OUTPUT.PUT_LINE(‘. . .’);

    IF (v_db_version = ‘11.2.0.3’) OR (v_db_version = ‘11.2.0.4’) OR (v_db_version = ‘12.1.0.1’) OR (v_db_version = ‘12.1.0.2’) THEN
    v_req_mirror_free_adj_factor := 1.10;
    DBMS_OUTPUT.PUT_LINE(‘ASM Version: ‘||v_db_version);
    ELSE
    v_req_mirror_free_adj_factor := 1.5;
    DBMS_OUTPUT.PUT_LINE(‘ASM Version: ‘||v_db_version||’ – WARNING DISK FAILURE COVERAGE ESTIMATES HAVE NOT BEEN VERIFIED ON THIS VERSION!’);
    END IF;

    DBMS_OUTPUT.PUT_LINE(‘. . .’);
    — Set up headings
    DBMS_OUTPUT.PUT_LINE(‘—————————————————————————————————————————————————-‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘|Cell Req”d ‘);
    DBMS_OUTPUT.PUT(‘|Disk Req”d ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT_Line(‘|’);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘|DG ‘);
    DBMS_OUTPUT.PUT(‘|Num ‘);
    DBMS_OUTPUT.PUT(‘|Disk Size ‘);
    DBMS_OUTPUT.PUT(‘|DG Total ‘);
    DBMS_OUTPUT.PUT(‘|DG Used ‘);
    DBMS_OUTPUT.PUT(‘|DG Free ‘);
    DBMS_OUTPUT.PUT(‘|Mirror Free ‘);
    DBMS_OUTPUT.PUT(‘|Mirror Free ‘);
    DBMS_OUTPUT.PUT(‘|Disk Usable ‘);
    DBMS_OUTPUT.PUT(‘|Cell Usable ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT_LINE(‘|PCT |’);
    DBMS_OUTPUT.PUT(‘|DG Name ‘);
    DBMS_OUTPUT.PUT(‘|Type ‘);
    DBMS_OUTPUT.PUT(‘|Disks’);
    DBMS_OUTPUT.PUT(‘|MB ‘);
    DBMS_OUTPUT.PUT(‘|MB ‘);
    DBMS_OUTPUT.PUT(‘|MB ‘);
    DBMS_OUTPUT.PUT(‘|MB ‘);
    DBMS_OUTPUT.PUT(‘|MB ‘);
    DBMS_OUTPUT.PUT(‘|MB ‘);
    DBMS_OUTPUT.PUT(‘|File MB ‘);
    DBMS_OUTPUT.PUT(‘|File MB ‘);
    DBMS_OUTPUT.PUT(‘|DFC ‘);
    DBMS_OUTPUT.PUT(‘|CFC ‘);
    DBMS_OUTPUT.PUT_LINE(‘|Util |’);
    DBMS_OUTPUT.PUT_LINE(‘—————————————————————————————————————————————————-‘);

    FOR dg IN (SELECT name, type, group_number, total_mb, free_mb, required_mirror_free_mb FROM v$asm_diskgroup ORDER BY name) LOOP

    v_enuf_free := FALSE;

    v_req_mirror_free_adj := dg.required_mirror_free_mb * v_req_mirror_free_adj_factor;

    — Find largest amount of space allocated to a cell
    SELECT sum(disk_cnt), max(max_total_mb), max(sum_total_mb)*v_req_mirror_free_adj_factor
    INTO v_num_disks, v_max_total_mb, v_one_cell_req_mir_free_mb
    FROM (SELECT count(1) disk_cnt, max(total_mb) max_total_mb, sum(total_mb) sum_total_mb
    FROM v$asm_disk
    WHERE group_number = dg.group_number
    GROUP BY failgroup);

    — Eighth Rack
    IF dg.type = ‘NORMAL’ THEN

    — Eighth Rack
    IF (v_num_disks v_required_free_mb THEN v_enuf_free := TRUE; END IF;
    — Quarter Rack
    ELSIF (v_num_disks >= 36 AND v_num_disks v_required_free_mb THEN v_enuf_free := TRUE; END IF;
    — Half Rack
    ELSIF (v_num_disks >= 84 AND v_num_disks v_required_free_mb THEN v_enuf_free := TRUE; END IF;
    — Full rack is most conservative, it will be default
    ELSE
    — Use eqn: y = 1.33333 x+83220.
    v_required_free_mb := 1.33333 * v_max_total_mb + 83220;
    IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;

    END IF;

    — DISK usable file MB
    v_usable_mb := ROUND((dg.free_mb – v_required_free_mb)/2);
    v_disk_desc := ‘ONE disk’;

    — CELL usable file MB
    v_cell_usable_mb := ROUND( (dg.free_mb – v_one_cell_req_mir_free_mb)/2 );
    v_one_cell_usable_mb := v_cell_usable_mb;

    ELSE
    — HIGH redundancy

    — Eighth Rack
    IF (v_num_disks v_required_free_mb THEN v_enuf_free := TRUE; END IF;
    — Quarter Rack
    ELSIF (v_num_disks > 18 AND v_num_disks v_required_free_mb THEN v_enuf_free := TRUE; END IF;
    — Half Rack
    ELSIF (v_num_disks > 36 AND v_num_disks v_required_free_mb THEN v_enuf_free := TRUE; END IF;
    — Full rack is most conservative, it will be default
    ELSE
    — Use eqn: y = 2.14077 x+54276.4
    v_required_free_mb := 2.14077 * v_max_total_mb + 54276.4;
    IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;

    END IF;

    — DISK usable file MB
    v_usable_mb := ROUND((dg.free_mb – v_required_free_mb)/3);
    v_disk_desc := ‘TWO disks’;

    — CELL usable file MB
    v_one_cell_usable_mb := ROUND( (dg.free_mb – v_one_cell_req_mir_free_mb)/3 );

    END IF;
    DBMS_OUTPUT.PUT(‘|’||RPAD(dg.name,v_offset-40));
    DBMS_OUTPUT.PUT(‘|’||RPAD(nvl(dg.type,’ ‘),v_offset-41));
    DBMS_OUTPUT.PUT(‘|’||LPAD(TO_CHAR(v_num_disks),v_offset-45));
    DBMS_OUTPUT.PUT(‘|’||TO_CHAR(v_max_total_mb,’9,999,999’));
    DBMS_OUTPUT.PUT(‘|’||TO_CHAR(dg.total_mb,’999,999,999’));
    DBMS_OUTPUT.PUT(‘|’||TO_CHAR(dg.total_mb – dg.free_mb,’999,999,999’));
    DBMS_OUTPUT.PUT(‘|’||TO_CHAR(dg.free_mb,’999,999,999’));
    DBMS_OUTPUT.PUT(‘|’||TO_CHAR(ROUND(v_one_cell_req_mir_free_mb),’999,999,999’));
    DBMS_OUTPUT.PUT(‘|’||TO_CHAR(ROUND(v_required_free_mb),’999,999,999’));
    DBMS_OUTPUT.PUT(‘|’||TO_CHAR(ROUND(v_usable_mb),’999,999,999’));
    DBMS_OUTPUT.PUT(‘|’||TO_CHAR(ROUND(v_one_cell_usable_mb),’999,999,999’));

    IF v_enuf_free THEN
    DBMS_OUTPUT.PUT(‘|’||’PASS’);
    ELSE
    DBMS_OUTPUT.PUT(‘|’||’FAIL’);
    END IF;

    IF dg.type = ‘NORMAL’ THEN
    — Calc Free Space for Rebalance Due to Cell Failure
    IF v_req_mirror_free_adj < dg.free_mb THEN
    DBMS_OUTPUT.PUT('|'||'PASS');
    ELSE
    DBMS_OUTPUT.PUT('|'||'FAIL');
    v_cfc_fail_msg := 'WARNING: Not enough free space to rebalance after loss of ONE cell (however, cell failure is very rare)';
    END IF;
    ELSE
    — Calc Free Space for Rebalance Due to Single Cell Failure
    IF v_one_cell_req_mir_free_mb 0 THEN
    DBMS_OUTPUT.PUT_LINE(‘|’||TO_CHAR((((dg.total_mb – dg.free_mb)/dg.total_mb)*100),’999.9′)||CHR(37)||’|’);
    ELSE
    DBMS_OUTPUT.PUT_LINE(‘| |’);
    END IF;

    END LOOP;

    DBMS_OUTPUT.PUT_LINE(‘—————————————————————————————————————————————————-‘);
    <>

    IF v_cfc_fail_msg is not null THEN
    DBMS_OUTPUT.PUT_LINE(‘Cell Failure Coverage Freespace Failures Detected. Warning Message Follows.’);
    DBMS_OUTPUT.PUT_LINE(v_cfc_fail_msg);
    END IF;

    DBMS_OUTPUT.PUT_LINE(‘. . .’);
    DBMS_OUTPUT.PUT_LINE(‘Script completed.’);

    END;
    /

    WHENEVER SQLERROR EXIT FAILURE;

  4. Vitaly Kaminsky said

    Hi John,

    I love the original asm_info.sql and thank you for updating it over the years.

    Below is another little thing from Oracle itself – I am using it when telling clients why they are really in trouble when disks begin to fail on Exadatas (after about 3 years).

    I thing you can use some ideas from here as well.

    Vitaly

    SET SERVEROUTPUT ON
    SET LINES 155
    SET PAGES 0
    SET TRIMSPOOL ON

    DECLARE
    v_num_disks NUMBER;
    v_group_number NUMBER;
    v_max_total_mb NUMBER;

    v_required_free_mb NUMBER;
    v_usable_mb NUMBER;
    v_cell_usable_mb NUMBER;
    v_one_cell_usable_mb NUMBER;
    v_enuf_free BOOLEAN := FALSE;
    v_enuf_free_cell BOOLEAN := FALSE;

    v_req_mirror_free_adj_factor NUMBER := 1.10;
    v_req_mirror_free_adj NUMBER := 0;
    v_one_cell_req_mir_free_mb NUMBER := 0;

    v_disk_desc VARCHAR(10) := ‘SINGLE’;
    v_offset NUMBER := 50;

    v_db_version VARCHAR2(8);
    v_inst_name VARCHAR2(1);

    v_cfc_fail_msg VARCHAR2(500);

    BEGIN

    SELECT substr(version,1,8), substr(instance_name,1,1) INTO v_db_version, v_inst_name FROM v$instance;

    IF v_inst_name ‘+’ THEN
    DBMS_OUTPUT.PUT_LINE(‘ERROR: THIS IS NOT AN ASM INSTANCE! PLEASE LOG ON TO AN ASM INSTANCE AND RE-RUN THIS SCRIPT.’);
    GOTO the_end;
    END IF;

    DBMS_OUTPUT.PUT_LINE(‘—— DISK and CELL Failure Diskgroup Space Reserve Requirements ——‘);
    DBMS_OUTPUT.PUT_LINE(‘ This procedure determines how much space you need to survive a DISK or CELL failure. It also shows the usable space ‘);
    DBMS_OUTPUT.PUT_LINE(‘ available when reserving space for disk or cell failure. ‘);
    DBMS_OUTPUT.PUT_LINE(‘ Please see MOS note 1551288.1 for more information. ‘);
    DBMS_OUTPUT.PUT_LINE(‘. . .’);
    DBMS_OUTPUT.PUT_LINE(‘ Description of Derived Values:’);
    DBMS_OUTPUT.PUT_LINE(‘ One Cell Required Mirror Free MB : Required Mirror Free MB to permit successful rebalance after losing largest CELL regardless of redundancy type’);
    DBMS_OUTPUT.PUT_LINE(‘ Disk Required Mirror Free MB : Space needed to rebalance after loss of single or double disk failure (for normal or high redundancy)’);
    DBMS_OUTPUT.PUT_LINE(‘ Disk Usable File MB : Usable space available after reserving space for disk failure and accounting for mirroring’);
    DBMS_OUTPUT.PUT_LINE(‘ Cell Usable File MB : Usable space available after reserving space for SINGLE cell failure and accounting for mirroring’);
    DBMS_OUTPUT.PUT_LINE(‘. . .’);

    IF (v_db_version = ‘11.2.0.3’) OR (v_db_version = ‘11.2.0.4’) OR (v_db_version = ‘12.1.0.1’) OR (v_db_version = ‘12.1.0.2’) THEN
    v_req_mirror_free_adj_factor := 1.10;
    DBMS_OUTPUT.PUT_LINE(‘ASM Version: ‘||v_db_version);
    ELSE
    v_req_mirror_free_adj_factor := 1.5;
    DBMS_OUTPUT.PUT_LINE(‘ASM Version: ‘||v_db_version||’ – WARNING DISK FAILURE COVERAGE ESTIMATES HAVE NOT BEEN VERIFIED ON THIS VERSION!’);
    END IF;

    DBMS_OUTPUT.PUT_LINE(‘. . .’);
    — Set up headings
    DBMS_OUTPUT.PUT_LINE(‘—————————————————————————————————————————————————-‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘|Cell Req”d ‘);
    DBMS_OUTPUT.PUT(‘|Disk Req”d ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT_Line(‘|’);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘|DG ‘);
    DBMS_OUTPUT.PUT(‘|Num ‘);
    DBMS_OUTPUT.PUT(‘|Disk Size ‘);
    DBMS_OUTPUT.PUT(‘|DG Total ‘);
    DBMS_OUTPUT.PUT(‘|DG Used ‘);
    DBMS_OUTPUT.PUT(‘|DG Free ‘);
    DBMS_OUTPUT.PUT(‘|Mirror Free ‘);
    DBMS_OUTPUT.PUT(‘|Mirror Free ‘);
    DBMS_OUTPUT.PUT(‘|Disk Usable ‘);
    DBMS_OUTPUT.PUT(‘|Cell Usable ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT(‘| ‘);
    DBMS_OUTPUT.PUT_LINE(‘|PCT |’);
    DBMS_OUTPUT.PUT(‘|DG Name ‘);
    DBMS_OUTPUT.PUT(‘|Type ‘);
    DBMS_OUTPUT.PUT(‘|Disks’);
    DBMS_OUTPUT.PUT(‘|MB ‘);
    DBMS_OUTPUT.PUT(‘|MB ‘);
    DBMS_OUTPUT.PUT(‘|MB ‘);
    DBMS_OUTPUT.PUT(‘|MB ‘);
    DBMS_OUTPUT.PUT(‘|MB ‘);
    DBMS_OUTPUT.PUT(‘|MB ‘);
    DBMS_OUTPUT.PUT(‘|File MB ‘);
    DBMS_OUTPUT.PUT(‘|File MB ‘);
    DBMS_OUTPUT.PUT(‘|DFC ‘);
    DBMS_OUTPUT.PUT(‘|CFC ‘);
    DBMS_OUTPUT.PUT_LINE(‘|Util |’);
    DBMS_OUTPUT.PUT_LINE(‘—————————————————————————————————————————————————-‘);

    FOR dg IN (SELECT name, type, group_number, total_mb, free_mb, required_mirror_free_mb FROM v$asm_diskgroup ORDER BY name) LOOP

    v_enuf_free := FALSE;

    v_req_mirror_free_adj := dg.required_mirror_free_mb * v_req_mirror_free_adj_factor;

    — Find largest amount of space allocated to a cell
    SELECT sum(disk_cnt), max(max_total_mb), max(sum_total_mb)*v_req_mirror_free_adj_factor
    INTO v_num_disks, v_max_total_mb, v_one_cell_req_mir_free_mb
    FROM (SELECT count(1) disk_cnt, max(total_mb) max_total_mb, sum(total_mb) sum_total_mb
    FROM v$asm_disk
    WHERE group_number = dg.group_number
    GROUP BY failgroup);

    — Eighth Rack
    IF dg.type = ‘NORMAL’ THEN

    — Eighth Rack
    IF (v_num_disks v_required_free_mb THEN v_enuf_free := TRUE; END IF;
    — Quarter Rack
    ELSIF (v_num_disks >= 36 AND v_num_disks v_required_free_mb THEN v_enuf_free := TRUE; END IF;
    — Half Rack
    ELSIF (v_num_disks >= 84 AND v_num_disks v_required_free_mb THEN v_enuf_free := TRUE; END IF;
    — Full rack is most conservative, it will be default
    ELSE
    — Use eqn: y = 1.33333 x+83220.
    v_required_free_mb := 1.33333 * v_max_total_mb + 83220;
    IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;

    END IF;

    — DISK usable file MB
    v_usable_mb := ROUND((dg.free_mb – v_required_free_mb)/2);
    v_disk_desc := ‘ONE disk’;

    — CELL usable file MB
    v_cell_usable_mb := ROUND( (dg.free_mb – v_one_cell_req_mir_free_mb)/2 );
    v_one_cell_usable_mb := v_cell_usable_mb;

    ELSE
    — HIGH redundancy

    — Eighth Rack
    IF (v_num_disks v_required_free_mb THEN v_enuf_free := TRUE; END IF;
    — Quarter Rack
    ELSIF (v_num_disks > 18 AND v_num_disks v_required_free_mb THEN v_enuf_free := TRUE; END IF;
    — Half Rack
    ELSIF (v_num_disks > 36 AND v_num_disks v_required_free_mb THEN v_enuf_free := TRUE; END IF;
    — Full rack is most conservative, it will be default
    ELSE
    — Use eqn: y = 2.14077 x+54276.4
    v_required_free_mb := 2.14077 * v_max_total_mb + 54276.4;
    IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;

    END IF;

    — DISK usable file MB
    v_usable_mb := ROUND((dg.free_mb – v_required_free_mb)/3);
    v_disk_desc := ‘TWO disks’;

    — CELL usable file MB
    v_one_cell_usable_mb := ROUND( (dg.free_mb – v_one_cell_req_mir_free_mb)/3 );

    END IF;
    DBMS_OUTPUT.PUT(‘|’||RPAD(dg.name,v_offset-40));
    DBMS_OUTPUT.PUT(‘|’||RPAD(nvl(dg.type,’ ‘),v_offset-41));
    DBMS_OUTPUT.PUT(‘|’||LPAD(TO_CHAR(v_num_disks),v_offset-45));
    DBMS_OUTPUT.PUT(‘|’||TO_CHAR(v_max_total_mb,’9,999,999’));
    DBMS_OUTPUT.PUT(‘|’||TO_CHAR(dg.total_mb,’999,999,999’));
    DBMS_OUTPUT.PUT(‘|’||TO_CHAR(dg.total_mb – dg.free_mb,’999,999,999’));
    DBMS_OUTPUT.PUT(‘|’||TO_CHAR(dg.free_mb,’999,999,999’));
    DBMS_OUTPUT.PUT(‘|’||TO_CHAR(ROUND(v_one_cell_req_mir_free_mb),’999,999,999’));
    DBMS_OUTPUT.PUT(‘|’||TO_CHAR(ROUND(v_required_free_mb),’999,999,999’));
    DBMS_OUTPUT.PUT(‘|’||TO_CHAR(ROUND(v_usable_mb),’999,999,999’));
    DBMS_OUTPUT.PUT(‘|’||TO_CHAR(ROUND(v_one_cell_usable_mb),’999,999,999’));

    IF v_enuf_free THEN
    DBMS_OUTPUT.PUT(‘|’||’PASS’);
    ELSE
    DBMS_OUTPUT.PUT(‘|’||’FAIL’);
    END IF;

    IF dg.type = ‘NORMAL’ THEN
    — Calc Free Space for Rebalance Due to Cell Failure
    IF v_req_mirror_free_adj < dg.free_mb THEN
    DBMS_OUTPUT.PUT('|'||'PASS');
    ELSE
    DBMS_OUTPUT.PUT('|'||'FAIL');
    v_cfc_fail_msg := 'WARNING: Not enough free space to rebalance after loss of ONE cell (however, cell failure is very rare)';
    END IF;
    ELSE
    — Calc Free Space for Rebalance Due to Single Cell Failure
    IF v_one_cell_req_mir_free_mb 0 THEN
    DBMS_OUTPUT.PUT_LINE(‘|’||TO_CHAR((((dg.total_mb – dg.free_mb)/dg.total_mb)*100),’999.9′)||CHR(37)||’|’);
    ELSE
    DBMS_OUTPUT.PUT_LINE(‘| |’);
    END IF;

    END LOOP;

    DBMS_OUTPUT.PUT_LINE(‘—————————————————————————————————————————————————-‘);
    <>

    IF v_cfc_fail_msg is not null THEN
    DBMS_OUTPUT.PUT_LINE(‘Cell Failure Coverage Freespace Failures Detected. Warning Message Follows.’);
    DBMS_OUTPUT.PUT_LINE(v_cfc_fail_msg);
    END IF;

    DBMS_OUTPUT.PUT_LINE(‘. . .’);
    DBMS_OUTPUT.PUT_LINE(‘Script completed.’);

    END;
    /

    WHENEVER SQLERROR EXIT FAILURE;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: