#!/bin/ksh
############################################################################
# Script Name..: asm_com.ksh
# Description..: Common library for ASM items
# Author.......: Michael Culp
# Date.........: 10/17/2008
# Version......: .08
# Modified By..:
# Date Modified:
# Comments.....: imported from asm_diskmnt.ksh
# Schema owner.:
# alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
############################################################################
# set -vx
initialize()
{
hostnm=`hostname -s`
log_file=asm_db_diskspc.log
# This won't be the same on all clusters
crs_dir=/oracle_crs/product/11.2.0/crs_1/bin/
}
get_asm_info_txt()
############################################################################
# Start ASM procedure put a copy in asm_sum.txt
# All this does is create the text file
############################################################################
{
${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" <<EOF > asm_sum.txt
SET LINESIZE 175
SET PAGESIZE 9999
SET HEADING OFF
-- set verify off
-- COLUMN group_name FORMAT a32 HEAD 'Disk Group Name'
-- COLUMN state FORMAT a11 HEAD 'State'
-- COLUMN type FORMAT a6 HEAD 'Type'
-- COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
-- COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (MB)'
-- COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
-- COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
select name, state, type, total_mb, free_mb, (total_mb - free_mb) used_mb, ROUND((1-(free_mb / total_mb))*100, 2) pct_used FROM v\$asm_diskgroup ORDER BY pct_used;
-- COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
-- COLUMN lun_size FORMAT 9999 HEAD 'LUN Size|(GB)'
-- COLUMN total_luns FORMAT 9999 HEAD 'Total|LUNs'
-- compute sum label "Grand Total: " of total_luns on report
-- SELECT vadg.name group_name,
-- ceil(round(vad.total_mb)/1024) lun_size,
-- count(*) total_luns
-- FROM v\\$asm_diskgroup vadg,
-- v\\$asm_disk vad
-- WHERE vad.group_number=vadg.group_number
-- GROUP BY vadg.name, vad.total_mb
-- ORDER BY vadg.name, vad.total_mb;
EOF
# cat asm_sum.txt
# testdisp=`cat asm_sum.txt`
# echo "this is the echo"
# echo "$testdisp"
# testdisp=$(<asm_sum.txt)
# echo "$testdisp"
#### End procedure
}
asm_file_type()
##################################################################
# Function name: asm_stat
# Description..: show file types in ASM
# Author.......: Michael Culp
##################################################################
{
echo "ASM File types stored in this instance"
echo -----------------------------------------------------
sqlplus -s "/ as sysdba" <<EOF
set lines 120
set pagesize 66
column name format a25
column type format a20
select distinct type
from v\$asm_file;
EOF
}
asm_files()
##################################################################
# Function name: asm_files
# Description..: show file types in ASM
# Author.......: Michael Culp
##################################################################
{
echo
echo
echo "ASM files stored in ASM instance (better)"
echo -----------------------------------------------------
echo
echo
sqlplus -s "/ as sysdba" <<EOF
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN full_alias_path FORMAT a80 HEAD 'File Name'
COLUMN system_created FORMAT a8 HEAD 'System|Created?'
COLUMN bytes FORMAT 9,999,999,999,999 HEAD 'Bytes'
COLUMN blocks FORMAT 9,999,999,999,999 HEAD 'Blocks'
COLUMN space FORMAT 9,999,999,999,999 HEAD 'Space'
COLUMN type FORMAT a18 HEAD 'File Type'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN striped FORMAT a8 HEAD 'Striped'
COLUMN creation_date FORMAT a20 HEAD 'Creation Date'
COLUMN disk_group_name noprint
BREAK ON report ON disk_group_name SKIP 1
compute sum label "" of bytes space on disk_group_name
compute sum label "Grand Total: " of bytes space on report
SELECT
CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path
, bytes
, space
, NVL(LPAD(type, 18), '<DIRECTORY>') type
, creation_date
, disk_group_name
, LPAD(system_created, 4) system_created
FROM
( SELECT g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, a.system_created system_created
, f.bytes bytes
, f.blocks blocks
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v\$asm_file f RIGHT OUTER JOIN v\$asm_alias a USING (group_number, file_number)
JOIN v\$asm_diskgroup g USING (group_number)
)
WHERE type IS NOT NULL
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex;
EOF
}
asm_spc_free()
{
echo "Amount of space left on individual ASM disk devices"
echo -----------------------------------------------------------
sqlplus -s "/ as sysdba" <<EOF
set pagesize 66
set lines 100
column path format a25
select path,
state,
total_mb,
free_mb
from v\$asm_disk;
EOF
}
asm_io_stat()
{
echo "IO Stats by ASM disk devices"
echo -----------------------------------------------------------
sqlplus -s "/ as sysdba" <<EOF
set pagesize 66
set lines 120
column path format a25
column read_time heading "Read Time" format 999,999.99
column write_time format 999,999.99
column writes format 999,999,999,999
column reads format 999,999,999,999
column bytes_written format 999,999,999,999
column bytes_read format 999,999,999,999
select path,
reads,
read_time,
bytes_read,
writes,
write_time,
bytes_written
from v\$asm_disk;
EOF
}
asm_unbal()
{
echo "ASM Unbalanced report"
echo -----------------------------------------------------------
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
-- spool <some file name>
column "Diskgroup" format A30
column "Imbalance" format 99.9 Heading "Percent|Imbalance"
column "Variance" format 99.9 Heading "Percent|Disk Size|Variance"
column "MinFree" format 99.9 Heading "Minimum|Percent|Free"
column "DiskCnt" format 9999 Heading "Disk|Count"
column "Type" format A10 Heading "Diskgroup|Redundancy"
SELECT g.name "Diskgroup",
100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance",
100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance",
100*(min(d.free_mb/d.total_mb)) "MinFree",
count(*) "DiskCnt",
g.type "Type"
FROM v\$asm_disk d,
v\$asm_diskgroup g
WHERE d.group_number = g.group_number and
d.group_number <> 0 and
d.state = 'NORMAL' and
d.mount_status = 'CACHED'
GROUP BY g.name, g.type;
-- spool off
EOF
}
asm_stub()
{
sqlplus -s "/ as sysdba" <<EOF
-- spool <some file name>
-- spool off
EOF
}
asm_dg_size()
################################################################
# Script Name..: asm_disk_size.ksh
# Description..: ASM Disk Group sizing
# Author.......: Michael Culp
################################################################
{
echo -----------------------------------------------------------
echo "ASM Diskgroups"
echo -----------------------------------------------------------
sqlplus -s "/ as sysdba" <<EOF
spool asm_diskgroups.txt
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;
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN lun_size FORMAT 9999 HEAD 'LUN Size|(GB)'
COLUMN total_luns FORMAT 9999 HEAD 'Total|LUNs'
compute sum label "Grand Total: " of total_luns on report
SELECT
vadg.name group_name
,ceil(round(vad.total_mb)/1024) lun_size
,count(*) total_luns
FROM
v\$asm_diskgroup vadg,
v\$asm_disk vad
WHERE
vad.group_number=vadg.group_number
GROUP BY
vadg.name
,vad.total_mb
ORDER BY
vadg.name
,vad.total_mb;
spool off
EOF
}
asm_disk_2_dg()
######################################################################
# Disk device to diskgroup mapping
######################################################################
{
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
column name format a20
column value format a20
-- spool <some file name>
SELECT SUBSTR(d.name,1,16) AS asmdisk,
d.mount_status,
d.state,
dg.name AS diskgroup
FROM V\$ASM_DISKGROUP dg,
V\$ASM_DISK d
WHERE dg.group_number = d.group_number
order by dg.name;
SELECT dg.name AS diskgroup,
SUBSTR(a.name,1,18) AS name,
SUBSTR(a.value,1,24) AS value,
read_only
FROM V\$ASM_DISKGROUP dg,
V\$ASM_ATTRIBUTE a WHERE dg.name = 'SHARED_DATA_DG01' AND
dg.group_number = a.group_number;
SELECT dg.name AS diskgroup,
SUBSTR(a.name,1,18) AS name,
SUBSTR(a.value,1,24) AS value,
read_only
FROM V\$ASM_DISKGROUP dg,
V\$ASM_ATTRIBUTE a WHERE dg.name = 'SHARED_FRA_DG01' AND
dg.group_number = a.group_number;
-- spool off
EOF
}
asm_dsk_dev_lst()
########################################################
# Disk device listing
########################################################
{
echo
echo "ASM Disk devices....."
echo
ls -l /dev/mapper/asm*p1
echo
echo "ASM Disk devices (data)....."
echo
ls -l /dev/mapper/asm_d*p1
echo
echo "ASM Disk devices (fra)....."
echo
ls -l /dev/mapper/asm_f*p1
echo
echo "ASM Disk devices (system)....."
echo
ls -l /dev/mapper/asm_s*p1
# ls -l /dev/mapper/fra*p1
echo
echo "All devices from /dev/mapper directory ....."
echo
ls -l /dev/mapper
ls -l /dev/mapper/oraclevg*
}
asm_dsk_sze()
####################################################
# Shows the sizes of the diskgroups
# No prereq for ASM env
####################################################
{
clear
sqlplus -s "/ as sysdba" <<EOF
set wrap off
set lines 155 pages 9999
col "Group" for 999
col "Group Name" for a25 Head "Group|Name"
col "Disk Name" for a10
col "State" for a10
col "Type" for a10 Head "Diskgroup|Redundancy"
col "Total GB" for 9,999,990 Head "Total|GB"
col "Free GB" for 9,999,990 Head "Free|GB"
col "Imbalance" for 99.9 Head "Percent|Imbalance"
col "Variance" for 99.9 Head "Percent|Disk Size|Variance"
col "MinFree" for 99.9 Head "Minimum|Percent|Free"
col "MaxFree" for 99.9 Head "Maximum|Percent|Free"
col "DiskCnt" for 9999 Head "Disk|Count"
prompt
prompt ASM Disk Groups
prompt ===============
SELECT g.group_number "Group"
, g.name "Group Name"
, g.state "State"
, g.type "Type"
, g.total_mb/1024 "Total GB"
, g.free_mb/1024 "Free GB"
, 100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance"
, 100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance"
, 100*(min(d.free_mb/d.total_mb)) "MinFree"
, 100*(max(d.free_mb/d.total_mb)) "MaxFree"
, count(*) "DiskCnt"
FROM v\$asm_disk d, v\$asm_diskgroup g
WHERE d.group_number = g.group_number and
d.group_number <> 0 and
d.state = 'NORMAL' and
d.mount_status = 'CACHED'
GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb
ORDER BY 1;
EOF
}
asm_0002()
{
sqlplus -s "/ as sysdba" <<EOF
set wrap off
set lines 175 pages 9999
prompt ASM Disks In Use
prompt ================
col "Group" for 999
col "Disk" for 999
col "Header" for a9
col "Mode" for a8
col "State" for a8
col "Created" for a10 Head "Added To|Diskgroup"
--col "Redundancy" for a10
--col "Failure Group" for a10 Head "Failure|Group"
col "Path" for a19
--col "ReadTime" for 999999990 Head "Read Time|seconds"
--col "WriteTime" for 999999990 Head "Write Time|seconds"
--col "BytesRead" for 999990.00 Head "GigaBytes|Read"
--col "BytesWrite" for 999990.00 Head "GigaBytes|Written"
col "SecsPerRead" for 9.000 Head "Seconds|PerRead"
col "SecsPerWrite" for 9.000 Head "Seconds|PerWrite"
select group_number "Group"
, disk_number "Disk"
, header_status "Header"
, mode_status "Mode"
, state "State"
, create_date "Created"
--, redundancy "Redundancy"
, total_mb/1024 "Total GB"
, free_mb/1024 "Free GB"
, name "Disk Name"
--, failgroup "Failure Group"
, path "Path"
--, read_time "ReadTime"
--, write_time "WriteTime"
--, bytes_read/1073741824 "BytesRead"
--, bytes_written/1073741824 "BytesWrite"
, read_time/reads "SecsPerRead"
, write_time/writes "SecsPerWrite"
from v\$asm_disk_stat
where header_status not in ('FORMER','CANDIDATE')
order by group_number
, disk_number;
EOF
}