ASM

From dbawiki
Revision as of 16:03, 22 March 2018 by Stuart (talk | contribs)
Jump to: navigation, search

See what disk groups exist

select group_number
,      name
from   v$asm_diskgroup;

Check space available in disk groups using query below

Disk Group            Sector   Block   Allocation
Name                    Size    Size    Unit size State       Type   Total size (Mb) Used size (Mb) Pct. used
-------------------- ------- ------- ------------ ----------- ------ --------------- -------------- ---------
DATA                     512   4,096    1,048,576 CONNECTED   EXTERN          61,436         61,426     99.98
FRA                      512   4,096    1,048,576 CONNECTED   EXTERN           8,190          7,638     93.26
REDO                     512   4,096    1,048,576 CONNECTED   EXTERN           4,094          1,891     46.19
                                                                     --------------- --------------
Grand Total:                                                                  73,720         70,955

See how the disk groups are linked to their paths

set lines 2000
col label for a20
col path for a20
select mount_status
,      header_status
,      mode_status
,      state
,      total_mb
,      free_mb
,      name
,      path
,      label
from   v$asm_disk;

gives... 

MOUNT_S HEADER_STATU MODE_ST STATE      TOTAL_MB    FREE_MB NAME                           PATH                 LABEL
------- ------------ ------- -------- ---------- ---------- ------------------------------ -------------------- ----------
CLOSED  PROVISIONED  ONLINE  NORMAL            0          0                                ORCL:DATA04          DATA04
CLOSED  PROVISIONED  ONLINE  NORMAL            0          0                                ORCL:FRA03           FRA03
CLOSED  PROVISIONED  ONLINE  NORMAL            0          0                                ORCL:FRA04           FRA04
CACHED  MEMBER       ONLINE  NORMAL        20479          4 DATA01                         ORCL:DATA01          DATA01
CACHED  MEMBER       ONLINE  NORMAL        20479          0 DATA02                         ORCL:DATA02          DATA02
CACHED  MEMBER       ONLINE  NORMAL         4095        272 FRA01                          ORCL:FRA01           FRA01
CACHED  MEMBER       ONLINE  NORMAL         4095        280 FRA02                          ORCL:FRA02           FRA02
CACHED  MEMBER       ONLINE  NORMAL         2047       1103 REDO01                         ORCL:REDO01          REDO01
CACHED  MEMBER       ONLINE  NORMAL         2047       1100 REDO02                         ORCL:REDO02          REDO02
CACHED  MEMBER       ONLINE  NORMAL        20478          6 DATA03                         ORCL:DATA03          DATA03

10 rows selected.

Add disk to a disk group

alter diskgroup data add disk 'ORCL:DATA04' name data04;
alter diskgroup fra add disk 'ORCL:FRA03' name fra03;
alter diskgroup fra add disk 'ORCL:FRA04' name fra04;

Now the same disk query gives an updated view 

MOUNT_S HEADER_STATU MODE_ST STATE      TOTAL_MB    FREE_MB NAME                           PATH                 LABEL
------- ------------ ------- -------- ---------- ---------- ------------------------------ -------------------- ----------
CACHED  MEMBER       ONLINE  NORMAL        20479       5121 DATA01                         ORCL:DATA01          DATA01
CACHED  MEMBER       ONLINE  NORMAL        20479       5120 DATA02                         ORCL:DATA02          DATA02
CACHED  MEMBER       ONLINE  NORMAL         4095        903 FRA01                          ORCL:FRA01           FRA01
CACHED  MEMBER       ONLINE  NORMAL         4095        908 FRA02                          ORCL:FRA02           FRA02
CACHED  MEMBER       ONLINE  NORMAL         2047       1103 REDO01                         ORCL:REDO01          REDO01
CACHED  MEMBER       ONLINE  NORMAL         2047       1100 REDO02                         ORCL:REDO02          REDO02
CACHED  MEMBER       ONLINE  NORMAL        20478       5122 DATA03                         ORCL:DATA03          DATA03
CACHED  MEMBER       ONLINE  NORMAL        20479       5124 DATA04                         ORCL:DATA04          DATA04
CACHED  MEMBER       ONLINE  NORMAL         4093       3461 FRA03                          ORCL:FRA03           FRA03
CACHED  MEMBER       ONLINE  NORMAL         4094       3463 FRA04                          ORCL:FRA04           FRA04

10 rows selected.

Recheck space available in disk groups using query below

Disk Group            Sector   Block   Allocation
Name                    Size    Size    Unit size State       Type   Total size (Mb) Used size (Mb) Pct. used
-------------------- ------- ------- ------------ ----------- ------ --------------- -------------- ---------
DATA                     512   4,096    1,048,576 MOUNTED     EXTERN          81,915         61,478     75.05
FRA                      512   4,096    1,048,576 MOUNTED     EXTERN          16,377          7,829     47.80
REDO                     512   4,096    1,048,576 MOUNTED     EXTERN           4,094          1,891     46.19
                                                                     --------------- --------------
Grand Total:                                                                 102,386         71,198

Restart ASM

cat /opt/oracle/oak/onecmd/tmp/restartasm.sh 

/u01/app/11.2.0.4/grid/bin/crsctl stop res ora.crsd -init
/u01/app/11.2.0.4/grid/bin/crsctl stop res ora.asm -init
/u01/app/11.2.0.4/grid/bin/crsctl start res ora.asm -init
ps -ef | grep  -v grep | grep -q smon_+ASM
if [ $? -ne 0 ]
then
echo 'asm instance did not start in 1st attempt, sleeping for 30 secs and retrying'
sleep 30
/u01/app/11.2.0.4/grid/bin/crsctl start res ora.asm -init
fi
/u01/app/11.2.0.4/grid/bin/crsctl start res ora.crsd -init

Where are the real disk group devices?

#!/bin/ksh
for i in `/etc/init.d/oracleasm listdisks`
do
v_asmdisk=`/etc/init.d/oracleasm querydisk $i | awk  '{print $2}'`
v_minor=`/etc/init.d/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $1}'`
v_major=`/etc/init.d/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $2}'`
v_device=`ls -la /dev | grep $v_minor | grep $v_major | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/$v_device  [$v_minor $v_major]"
done

or check in 

ls -al /dev/oracleasm/disks

The major and minor numbers should match up.

Check free space in ASM diskgroups

cat /opt/oracle/oak/onecmd/tmp/chekDGSpaceSql.sh 

#! /bin/sh
export ORACLE_HOME=/u01/app/12.1.0.2/grid
export ORACLE_SID=+ASM1
$ORACLE_HOME/bin/sqlplus '/as sysasm' <<EOF
column path format a40
column name format a35
set linesize 200
select name, total_mb,free_mb from v\$asm_diskgroup;
quit
EOF

gives 

NAME                                  TOTAL_MB    FREE_MB
----------------------------------- ---------- ----------
RECO                                   2424000    2379084
REDO                                    763120     687700
DATA                                  14745600    2930908

or 

set linesize 145
set pagesize 9999
set verify off

column group_name format a20 head 'Disk Group|Name'
column sector_size format 99,999 head 'Sector|Size'
column block_size format 99,999 head 'Block|Size'
column allocation_unit_size format 999,999,999 head 'Allocation|Unit size'
column state format a11 head 'State'
column type format a6 head 'Type'
column total_mb format 999,999,999 head 'Total size (Mb)'
column used_mb format 999,999,999 head 'Used size (Mb)'
column pct_used format 999.99 head 'Pct. used'

break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report

select name                 group_name
,      sector_size          sector_size
,      block_size           block_size
,      allocation_unit_size allocation_unit_size
,      state                state
,      type                 type
,      total_mb             total_mb
,      (total_mb - free_mb) used_mb
,      round ( (1 -(free_mb / total_mb)) * 100, 2) pct_used
from   v$asm_diskgroup
order  by name;

asm_capacity.sql

Check resistance of an ASM cluster against a disk / cell failure 

SPOOL asm_disk_capacity.output

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(152);

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')  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 < 36) THEN
            -- Use eqn: y = 1.21344 x+ 17429.8
            v_required_free_mb :=  1.21344 * v_max_total_mb + 17429.8;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;
         -- Quarter Rack
         ELSIF (v_num_disks >= 36 AND v_num_disks < 84) THEN
            -- Use eqn: y = 1.07687 x+ 19699.3
                        -- Revised 2/21/14 for 11.2.0.4 to use eqn: y=0.803199x + 156867, more space but safer
            v_required_free_mb := 0.803199 * v_max_total_mb + 156867;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;
         -- Half Rack
         ELSIF (v_num_disks >= 84 AND v_num_disks < 168) THEN
            -- Use eqn: y = 1.02475 x+53731.3
            v_required_free_mb := 1.02475 * v_max_total_mb + 53731.3;
            IF dg.free_mb > 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 <= 18) THEN
            -- Use eqn: y = 4x + 0
                        -- Updated for 11.2.0.4 to higher value:  y = 3.84213x + 84466.4
            v_required_free_mb :=  3.84213 * v_max_total_mb + 84466.4;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;
         -- Quarter Rack
         ELSIF (v_num_disks > 18 AND v_num_disks <= 36) THEN
            -- Use eqn: y = 3.87356 x+417692.
            v_required_free_mb := 3.87356 * v_max_total_mb + 417692;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;
         -- Half Rack
         ELSIF (v_num_disks > 36 AND v_num_disks <= 84) THEN
            -- Use eqn: y = 2.02222 x+56441.6
            v_required_free_mb := 2.02222 * v_max_total_mb + 56441.6;
            IF dg.free_mb > 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 < 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 and high redundancy offers ample protection already)';
        END IF;

     END IF;

     -- Calc Disk Utilization Percentage
        IF dg.total_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('----------------------------------------------------------------------------------------------------------------------------------------------------');
   <<the_end>>

   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;

SPOOL OFF
Retrieved from "https://mailnest.com/dbawiki/index.php?title=ASM&oldid=2985"