#!/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 }