* [[https://docs.oracle.com/en/database/oracle/oracle-database/19/axdbi/index.html|Database Installation Guide for IBM AIX on POWER Systems (64-bit)]]
* [[https://docs.oracle.com/en/database/oracle/oracle-database/19/axdbi/configuring-users-groups-and-environments-for-oracle-grid-infrastructure-and-oracle-database.html|Configuring Users, Groups and Environments for Oracle Grid Infrastructure and Oracle Database]]
==== 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 ====
* [[https://dbaprakash.wordpress.com/category/asm/|dbaprakash.wordpress.com]]
==== Migrate From Database File system to ASM ====
* [[https://dbaprakash.wordpress.com/2015/03/30/migrate-from-database-file-system-to-asm/|dbaprakash.wordpress.com]]
==== 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' <
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('----------------------------------------------------------------------------------------------------------------------------------------------------');
<>
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" < 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" <
==== 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" <>> 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" <) {
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 <
==== 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 () {
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\
";
}
}