asm
Table of Contents
How to shutdown a standalone ASM instance in oracle
- First we need to bring down the databases that rely on it
[oracle@oracle ~]$ ps -ef|grep pmon grid 3135 1 0 20:03 ? 00:00:00 asm_pmon_+ASM oracle 3254 1 0 20:04 ? 00:00:00 ora_pmon_orcl oracle 3696 3203 0 20:10 pts/0 00:00:00 grep --color=auto pmon [oracle@oracle ~]$ sqlplus SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 9 20:10:36 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Enter user-name: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> exit; Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options [oracle@oracle ~]$ ps -ef|grep pmon grid 3135 1 0 20:03 ? 00:00:00 asm_pmon_+ASM oracle 3714 3203 0 20:11 pts/0 00:00:00 grep --color=auto pmon
- Shutdown the ASM instance
Now db has been down next switch to grid user and bring down the ASM instance with sysasm privilege.
[oracle@oracle ~]$ su - grid Password: Last login: Thu Feb 9 20:02:32 PST 2017 on pts/0 [grid@oracle ~]$ sqlplus SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 9 20:13:24 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Enter user-name: / as sysasm Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Automatic Storage Management option SQL> shut immediate; ASM diskgroups dismounted ASM instance shutdown SQL> exit; Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Automatic Storage Management option
- Now check the status of cluster resources then bring down the all cluster resources.
[grid@oracle ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
OFFLINE OFFLINE oracle STABLE
ora.DATA.dg
OFFLINE OFFLINE oracle STABLE
ora.FRA.dg
OFFLINE OFFLINE oracle STABLE
ora.LISTENER.lsnr
ONLINE ONLINE oracle STABLE
ora.ORA_LISTENER.lsnr
ONLINE OFFLINE oracle STABLE
ora.asm
OFFLINE OFFLINE oracle STABLE
ora.ons
OFFLINE OFFLINE oracle STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE oracle STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINE ONLINE oracle STABLE
ora.orcl.db
1 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
ora.orcl1.db
1 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
--------------------------------------------------------------------------------
[grid@oracle ~]$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'oracle'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'oracle'
CRS-2673: Attempting to stop 'ora.evmd' on 'oracle'
CRS-2677: Stop of 'ora.evmd' on 'oracle' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'oracle'
CRS-2677: Stop of 'ora.cssd' on 'oracle' succeeded
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'oracle' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'oracle' has completed
CRS-4133: Oracle High Availability Services has been stopped.
Useful ASM commands
Migrate From Database File system to ASM
See what disk groups exist
From Sql*Plus
select group_number , name from v$asm_diskgroup;
Check space available in disk groups
select name , state , type , round((total_mb/1024),1) total_gb , round(((total_mb-free_mb)/1024),1) used_gb , round((((total_mb-free_mb)/1024)/(total_mb/1024)*100),1) used_pct from v$asm_diskgroup order by used_pct /
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
Create a new disk group
List partitions that can be used for an ASM disk
Assumes Solaris, make allowances!
fdisk -l|grep '/dev/sd'|sort
Compare the first list (of used disks) with the second list (of unassiged disks)
On AIX, maybe
lsvg -o|lsvg -il
Pick an unused disk and feed it to fdisk
fdisk /dev/sde
Recheck disk now appears in the upper list
fdisk -l|grep '/dev/sd'|sort
List current diskgroups
oracleasm listdisks
Create an ASM disk from a device
oracleasm createdisk ASMDATA1_2 /dev/sde1
Recheck it appears in the list of ASM disks
oracleasm listdisks
Add new ASM disk to a diskgroup
alter diskgroup asmdata1 add disk '/dev/oracleasm/disks/ASMDATA1_2';
Add a disk to an existing disk group
See all previous steps for creating a new disk group and replace the last step (alter diskgroup) with
create diskgroup asmfra external redundancy disk '/dev/oracleasm/disks/ASMFRA';
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.
or, on AIX…
MOUNT_S HEADER_STATU MODE_ST STATE TOTAL_MB FREE_MB NAME PATH LABEL ------- ------------ ------- -------- ---------- ---------- ------------------------------ -------------------- -------------------- CACHED MEMBER ONLINE NORMAL 71680 57726 ARCH_0003 /dev/rhdisk10 CACHED MEMBER ONLINE NORMAL 430080 136756 DATA_0010 /dev/rhdisk16 CACHED MEMBER ONLINE NORMAL 430080 136786 DATA_0011 /dev/rhdisk17 CACHED MEMBER ONLINE NORMAL 430080 136773 DATA_0012 /dev/rhdisk18 CACHED MEMBER ONLINE NORMAL 430080 136769 DATA_0013 /dev/rhdisk19 CACHED MEMBER ONLINE NORMAL 430080 136780 DATA_0014 /dev/rhdisk20 CACHED MEMBER ONLINE NORMAL 430080 136770 DATA_0015 /dev/rhdisk21 CACHED MEMBER ONLINE NORMAL 430080 136781 DATA_0016 /dev/rhdisk22 CACHED MEMBER ONLINE NORMAL 430080 136777 DATA_0017 /dev/rhdisk23 CACHED MEMBER ONLINE NORMAL 430080 136757 DATA_0018 /dev/rhdisk24 CACHED MEMBER ONLINE NORMAL 430080 136768 DATA_0019 /dev/rhdisk25
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 as above 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 ORAENV_ASK=NO export ORACLE_SID=+ASM . oraenv $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;
Scripts
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
asmdisks.ksh
#!/usr/bin/env ksh
export ORACLE_SID=+ASM`$GRID_HOME/bin/olsnodes -l -n | cut -f2`
export ORACLE_HOME=`grep "^+ASM" /etc/oratab | cut -d: -f2`
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus -s "/ as sysasm" <<EOF
prompt
prompt
prompt =================== OVERVIEW OF ASM DISKGROUPS ==================================
set lines 150
set pages 50000
Col name format a15
Col nr_disks format 99
Col pctused format 9999 heading "%Usd"
Col used format 9999990 heading "Used"
select name, state, total_mb, (total_mb-free_mb) as used,
case when total_mb > 0 then trunc(100*(total_mb-free_mb)/total_mb) else null end as pctused,
(select count(*) from v\$asm_disk
where group_number = g.group_number) as nr_disks,
type
from v\$asm_diskgroup g
;
prompt
prompt ==================== OVERVIEW OF ASM DISKS ======================================
set feed off
set pages 50000
Set lines 150
Col path format a30
Col label format a10
Col failgroup format a11
Col diskgroup format a10
Col state format a10
Col total_mb format 9999990 heading "Total"
Col used format 9999990 heading "Used"
Col pctused format 9999 Heading "%Usd"
Col name format a15 heading "DiskName"
Col header_status format a15 heading "Header"
break on header_status skip 1 dup on diskgroup skip 0 nodup
select g.name as diskgroup, d.path, d.total_mb, (d.total_mb-d.free_mb) as used,
case when d.total_mb > 0 then decode(d.total_mb,0,0,trunc(100*(d.total_mb-d.free_mb)/d.total_mb)) else null end as pctused,
substr(d.mode_status,1,3) as st, header_status,
d.failgroup, d.state, /*substr(g.type,1,3) as red,*/ d.name, mount_status
from v\$asm_disk d, (select * from v\$asm_diskgroup where group_number <> 0) g
Where d.group_number = g.group_number(+)
Order by g.name, d.path;
prompt
prompt
prompt =================== OVERVIEW OF ASM OPERATIONS ==================================
Col name format a15
select g.name, o.operation, o.state, o.sofar, o.est_work, o.est_minutes, o.power
from v\$asm_diskgroup g, gv\$asm_operation o
where o.group_number = g.group_number
;
prompt
exit;
EOF
asmdu.ksh
#!/usr/bin/env ksh
export ORACLE_SID=+ASM`$GRID_HOME/bin/olsnodes -l -n | cut -f2`
export ORACLE_HOME=`grep "^+ASM" /etc/oratab | head -1 | cut -d: -f2`
export PATH=$ORACLE_HOME/bin:$PATH
- If Argument, then set bind variable ( filtering on file_path )
if [[ -z $1 ]]
then
filter=""
else
filter=$1
filter=`echo $filter|tr [[a-z]] [[A-Z]]`
fi
sqlplus -s "/ as sysasm" <<EOF
Set pages 100
set lines 100
Col diskgroup format a10
Col dbuniquename format a32
Col ftype format a16
break on diskgroup on dbuniquename on ftype
SELECT diskgroup, dbuniquename, ftype, round(sum(bytes)/(1024*1024),2) as total_MB, count(*) as number_of_files
FROM (
SELECT g.name diskgroup
, SUBSTR(a.fpath,2,INSTR(a.fpath,'/',1,2)-1-1) as dbuniquename
, SUBSTR(a.fpath,INSTR(a.fpath,'/',1,2)+1,INSTR(a.fpath,'/',1,3)-1-INSTR(a.fpath,'/',1,2)) as ftype
, f.bytes as bytes
FROM v\$asm_file f
, (
SELECT sys_connect_by_path(name, '/') as fpath, reference_index, parent_index, group_number, file_number
FROM v\$asm_alias
START WITH (mod(parent_index, power(2, 24))) = 0
CONNECT BY PRIOR reference_index = parent_index
) a
, v\$asm_diskgroup g
WHERE f.file_number = a.file_number
AND f.group_number = a.group_number
AND g.group_number = a.group_number
)
where dbuniquename like '%$filter%'
GROUP BY diskgroup, dbuniquename ,ftype
/
exit;
EOF
asmfiles.ksh
#!/usr/bin/env ksh
export ORACLE_SID=+ASM`$GRID_HOME/bin/olsnodes -l -n | cut -f2`
export ORACLE_HOME=`grep "^+ASM" /etc/oratab | cut -d: -f2`
export PATH=$ORACLE_HOME/bin:$PATH
- If Argument, then set bind variable ( filtering on file_path )
if [[ -z $1 ]]
then
filter=""
else
filter=$1
filter=`echo $filter|tr [[a-z]] [[A-Z]]`
fi
sqlplus -s "/ as sysasm" <<EOF
Set pages 100
--Col full_alias_path format a70
--SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path, system_created
-- FROM (SELECT g.name gname, a.parent_index pindex, a.name aname
-- ,a.reference_index rindex , a.system_created
-- FROM v\$asm_alias a, v\$asm_diskgroup g
-- WHERE a.group_number = g.group_number)
-- START WITH (mod(pindex, power(2, 24))) = 0
-- CONNECT BY PRIOR rindex = pindex;
set feed off
col max_width new_value maxwidth noprint
select to_char(max(length(file_path))) as max_width from
(select sys_connect_by_path(a.name, '/') as file_path
from v\$asm_alias a, v\$asm_diskgroup g
where a.group_number = g.group_number
START WITH (mod(parent_index, power(2, 24))) = 0
CONNECT BY PRIOR reference_index = parent_index)
;
alter session set nls_numeric_characters = ',.';
set lines 150
col disk_group format a11 heading "Disk Group"
col file_path format a&maxwidth heading "File [[ >>> alias ]]" word_wrapped
col bytes heading "Bytes" justify right format 9g999g999g999g999
col created format a20
break on report on disk_group skip 1
compute sum label "Group Total" of bytes on disk_group
compute sum label "Total" of bytes on report
SELECT
disk_group, file_path||
case when alias_path is not null then ' >>>'||alias_path else null end as file_path,
-- case when bytes < 1024 then lpad(to_char(bytes),8)
-- when bytes < 1024*1024 then lpad(round(bytes/1024),8)||' K'
-- when bytes < 1024*1024*1024 then lpad(round(bytes/1024/1024),8)||' M'
-- else lpad(round(bytes/1024/1024/1024),8)||' G'
-- end as bytes,
bytes,
to_char(creation_date,'dd-mon-yy hh24:mi') as created,
redundancy, striped
FROM (SELECT a.group_number, a.file_number, f.bytes, creation_date, redundancy, striped
FROM v\$asm_alias a, v\$asm_file f
WHERE a.group_number = f.group_number
and a.file_number = f.file_number
and a.system_created = 'Y'
) x1,
(select * from
(select '+'||g.name as disk_group, sys_connect_by_path(a.name, '/') as file_path, a.system_created,
a.group_number, a.file_number
from v\$asm_alias a, v\$asm_diskgroup g
where a.group_number = g.group_number
START WITH (mod(parent_index, power(2, 24))) = 0
CONNECT BY PRIOR reference_index = parent_index)
where system_created = 'Y'
) x2,
(select * from
(select concat('+'||g.name, sys_connect_by_path(a.name, '/')) as alias_path, a.system_created,
a.group_number, a.file_number
from v\$asm_alias a, v\$asm_diskgroup g
where a.group_number = g.group_number
START WITH (mod(parent_index, power(2, 24))) = 0
CONNECT BY PRIOR reference_index = parent_index)
where system_created = 'N'
) x3
where x1.group_number = x2.group_number
and x1.file_number = x2.file_number
and x1.group_number = x3.group_number(+)
and x1.file_number = x3.file_number(+)
AND file_path like '%$filter%';
exit;
EOF
asmlog.pl
#!/usr/bin/perl
-
use Env;
use Time::Local;
if ( !$ENV{"GRID_HOME"} ) {
print "Please set GRID_HOME for home dir of Oracle CRS\
";
exit 1;
}
use vars qw/ %opt /;
use Getopt::Std;
my $opt_string = 'f';
getopts( "$opt_string", \%opt ) or usage();
$retention=$ARGV[[0]];
$retention = 3600 if !defined $retention;
print "Retention: $retention\
";
$starttime=timegm(localtime());
print "Starttime: $starttime\
";
print "==========================================================================\
";
print "==========================================================================\
";
print "==========================================================================\
";
print "==========================================================================\
";
print "==========================================================================\
";
print "==========================================================================\
";
print "\
";
print "================= ASM LOG =================\
";
$printing=0;
$Tmp=`$GRID_HOME/bin/olsnodes -n -l`;
($NodeName,$NodeId)=split(/\\s+/,$Tmp);
print "NodeName=$NodeName, Id=$NodeId\
";
$temp=`$GRID_HOME/bin/srvctl config asm`;
$temp=~/ASM\\shome:\\s(.*)/;
$OraHome=$1;
$Instance=`cat /etc/oratab|grep "^+ASM"|cut -f1 -d":"`;
if (! $Instance )
{
$Instance=`cat /var/opt/oracle/oratab|grep "^+ASM"|cut -f1 -d":"`;
}
chomp($Instance);
print "ASM Instance: $Instance\
";
print "ASM Home: $OraHome\
";
$Bdump=`ORACLE_HOME=$GRID_HOME ORACLE_SID=$Instance sqlplus -s "/ as sysdba" <<EOF
set head off
set feed off
set pages 0
select value from v\\\\\\$parameter where name = 'background_dump_dest';
exit
EOF
`;
chop($Bdump);
print "Bdump=$Bdump\
";
if ($opt{f}) {
open(ALRT,"tail -f $Bdump/alert_$Instance.log|");
} else {
open(ALRT,"cat $Bdump/alert_$Instance.log|") || die "Cannot open $Bdump/alert_$Instance.log";
}
while(<ALRT>) {
chop;
#print "Processing: $_\
";
&pr("$_");
}
close ALRT;
print "\
\
";
sub date2secs {
my $date = shift;
my @date =(0,0,0,1,0,0);
@date = reverse (split /[[\\s:-]]/, $date);
- $date[[4]]=${{Jan => 0, Feb => 1, Mar => 2, Apr => 3, May => 4, Jun => 5, Jul => 6,
- Aug => 7, Sep => 8, Oct => 9, Nov => 10, Dec => 11, }}{$date[[4]]};
- $date[[4]]=${{"01" => 0, "02" => 1, "03" => 2, "04" => 3, "05" => 4, "06" => 5, "07" => 6,
- "08" => 7, "09" => 8, "10" => 9, "11" => 10, "12" => 11, }}{$date[[4]]};
$date[[4]]=${{"Jan" => 0, "Feb" => 1, "Mar" => 2, "Apr" => 3, "May" => 4, "Jun" => 5, "Jul" => 6,
"Aug" => 7, "Sep" => 8, "Oct" => 9, "Nov" => 10, "Dec" => 11,
"01" => 0, "02" => 1, "03" => 2, "04" => 3, "05" => 4, "06" => 5, "07" => 6,
"08" => 7, "09" => 8, "10" => 9, "11" => 10, "12" => 11, }}{$date[[4]]};
return timegm(@date);
}
- print &date2secs($time1)."\
";
- print timegm(localtime())."\
";
sub printwrap {
my $line=join " ",@_;
if (length($line) > 100) {
my $part1=substr($line,0,rindex($line,' ',80));
my $part2=substr($line,rindex($line,' ',80)+1);
print "$part1\
";
print " $part2\
";
} else {
print "$line\
";
}
}
sub pr {
# We need YYYY-MON-DD HH24:MI:SS
my $line=$_;
if (/^(...) (...) +(\\d+) (\\d\\d:\\d\\d:\\d\\d) (\\d\\d\\d\\d)/) {#Alert file
$datetime="$5-$2-$3 $4";
$lasttime="$4";
} elsif (/^(\\d\\d\\d\\d-\\d\\d-\\d\\d \\d\\d:\\d\\d:\\d\\d)/) {
$datetime=$1;
$lasttime="$1";
} elsif (/^(\\d\\d)-(...)-(\\d\\d\\d\\d) (\\d\\d:\\d\\d:\\d\\d)/) {
$datetime="$3-$2-$1 $4";
$lasttime="$4";
} else {
$datetime="";
}
if ($datetime) {
$linetime=&date2secs($datetime);
if ($linetime > $starttime-$retention) {
$printing=1;
} else {
}
} else {
if ($printing) {
printwrap "$lasttime\\+: $line";
}
}
}
crsstat.pl
unless ($#ARGV < 1) {
&usage;
}
- Usage
sub usage {
print <<ENDOFUSAGE;
crsstat {filter}
filter Filters the resources bases on NAME.
ENDOFUSAGE
exit(0);
}
if (@ARGV)
{
$filter='-w "NAME co '.$ARGV[[0]].'"';
}
@output=`crsctl status resource -t $filter`;
- --------------------------------------------------------------
- Rapport Status
format STDOUT_TOP ======
RESOURCE NAME TARGET STATE SERVER STATE_DETAIL
----------------------------------------------------------------------------------------------
.
format ======
@<<<<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<< @<<<<<<<<<<<< @* @*
$resource,$target,$status, $server, $state_details
.
write();
foreach (@output)
{
chomp($_);
if ( /^\\w+\\.\\w+/ )
{ # resource line
$resource=$_;
}
elsif (/^\\s+\\d+\\s+(\\w+)\\s+(\\w+)\\s+$/)
{
$target=$1;
$status=$2;
$server=$3;
write();
}
elsif ( /^\\s+\\d+\\s+(\\w+)\\s+(\\w+)\\s+(\\w+)\\s(.*)$/)
{ # status line Cluster Resources
$target=$1;
$status=$2;
$server=$3;
$state_details=$4;
$state_details=~ s/^\\s+//;
write();
$state_details="";
}
elsif ( /^\\s+(\\w+)\\s+(\\w+)\\s+(\\w+)\\s(.*)$/)
{ # status line Local Resources
$target=$1;
$status=$2;
$server=$3;
$state_details=$4;
$state_details=~ s/^\\s+//;
write();
$state_details="";
}
elsif (/NAME/ || /----/)
{ # Titles line
# skip
}
else
{ # Header Lines
print $_ . "\
";
$resource="";
$target="";
$server="";
$state_details="";
}
}
csslog.pl
- !/usr/bin/perl
-
use Env;
use Time::Local;
if ( !$ENV{"GRID_HOME"} ) {
print "Please set GRID_HOME for home dir of Oracle CRS\
";
exit 1;
}
use vars qw/ %opt /;
use Getopt::Std;
my $opt_string = 'f';
getopts( "$opt_string", \\%opt ) or usage();
sub usage {
print "Usage: csslog [[-f]]\
";
exit 0
}
$retention=$ARGV[[0]];
$retention = 3600 if !defined $retention;
print "Retention: $retention\
";
$starttime=timegm(localtime());
print "==========================================================================\
";
print "==========================================================================\
";
print "==========================================================================\
";
print "==========================================================================\
";
print "==========================================================================\
";
print "==========================================================================\
";
print "\
";
$Tmp=`$GRID_HOME/bin/olsnodes -n -l`;
($NodeName,$NodeId)=split(/\\s+/,$Tmp);
print "NodeName=$NodeName, Id=$NodeId\
";
print "================= CSS LOG =================\
";
$printing=0;
$NrIgnored=0;
$Hostname=`hostname -s`;
chomp($Hostname);
if ( ! $NodeName )
{
$NodeName=$Hostname;
}
$cssdfile="$GRID_HOME/log/$NodeName/cssd/ocssd.log";
print "Logfile: $cssdfile\
";
if ($opt{f}) {
open(CSS,"tail -f $cssdfile|");
} else {
open(CSS,"<$cssdfile");
}
while (<CSS>) {
chop;
if (!/clssgmDeleteClientListener: cleanup for proc/ &&
!/clssgmClientConnectMsg: Connect from con/ &&
!/clssnmSendingThread: sent \\d status msgs to all nodes/ &&
!/clssnmSendingThread: sending status msg to all nodes/ &&
!/clssgmClientConnectMsg: properties of cmProc/ &&
!/clsc_receive: .* Connection failed, transport error/ &&
!/clscreceive: .* Physical connection .* not active, rc 11$/ &&
!/clsc_receive: .* Remote disconnect$/ &&
!/^$/) {
&pr("$_");
} else {
$NrIgnored++;
}
}
close CSS;
print "Ignored $NrIgnored messages\
";
print "\
\
";
- grep -v "clssgmDeleteClientListener: cleanup for proc" | \\
- grep -v "clssgmClientConnectMsg: Connect from con" | \\
- grep -v "Connection failed, transport error" | \\
- grep -v "not active, rc 11" | \\
- grep -v "Remote disconnect$" | \\
- grep -v "^$"
- echo
- echo
sub date2secs {
my $date = shift;
my @date =(0,0,0,1,0,0);
@date = reverse (split /[[\\s:-]]/, $date);
- $date[[4]]=${{Jan => 0, Feb => 1, Mar => 2, Apr => 3, May => 4, Jun => 5, Jul => 6,
- Aug => 7, Sep => 8, Oct => 9, Nov => 10, Dec => 11, }}{$date[[4]]};
- $date[[4]]=${{"01" => 0, "02" => 1, "03" => 2, "04" => 3, "05" => 4, "06" => 5, "07" => 6,
- "08" => 7, "09" => 8, "10" => 9, "11" => 10, "12" => 11, }}{$date[[4]]};
$date[[4]]=${{"Jan" => 0, "Feb" => 1, "Mar" => 2, "Apr" => 3, "May" => 4, "Jun" => 5, "Jul" => 6,
"Aug" => 7, "Sep" => 8, "Oct" => 9, "Nov" => 10, "Dec" => 11,
"01" => 0, "02" => 1, "03" => 2, "04" => 3, "05" => 4, "06" => 5, "07" => 6,
"08" => 7, "09" => 8, "10" => 9, "11" => 10, "12" => 11, }}{$date[[4]]};
return timegm(@date);
}
- print &date2secs($time1)."\
";
- print timegm(localtime())."\
";
sub printwrap {
if (length($_) > 1000) {
$part1=substr($_,0,rindex($_,' ',80));
$part2=substr($_,rindex($_,' ',80)+1);
print "$part1\
";
print " $part2\
";
}
else {
print "$_\
";
}
}
sub pr {
my $line=$_;
if (/^\\[[ CSSD\\]](\\d\\d\\d\\d-\\d\\d-\\d\\d \\d\\d:\\d\\d:\\d\\d)/) {
$datetime=$1;
} elsif (/^\\[[ CSSD\\]](\\d\\d)-(...)-(\\d\\d\\d\\d) (\\d\\d:\\d\\d:\\d\\d)/) {
$datetime="$3-$2-$1 $4";
} else {
$datetime="";
}
if ($datetime) {
$linetime=&date2secs($datetime);
if ($linetime > $starttime-$retention) {
printwrap "$line\
";
}
}
else {
printwrap "$line\
";
}
}
asm.txt · Last modified: 2025/02/12 15:28 by 127.0.0.1