ASM
From dbawiki
Contents
See what disk groups exist
select group_number , name from v$asm_diskgroup;
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.
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
