ASM Views And Scripts
Views:
V$ASM_DISKGROUP :- Describes a disk group (number, name, size related info, state, and redundancy type)
V$ASM_CLIENT:- Identifies databases using disk groups managed by the ASM instance.
V$ASM_DISK:- Contains one row for every disk discovered by the ASM instance, including disks that are not part of any disk group.
V$ASM_FILE:- Contains one row for every ASM file in every disk group mounted by the ASM instance.
V$ASM_TEMPLATE:- Contains one row for every template present in every disk group mounted by the ASM instance.
V$ASM_ALIAS:- Contains one row for every alias present in every disk group mounted by the ASM instance.
v$ASM_OPERATION:- Contains one row for every active ASM long running operation executing in the ASM instance.
Scripts:
ASM Disk Groups
==============
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;
ASM Disks In Use
==============
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;
File Types in Diskgroups
==================
select g.name "Group Name"
, f.TYPE "File Type"
, f.BLOCK_SIZE/1024||'k' "Block Size"
, f.STRIPED
, count(*) "Files"
, round(sum(f.BYTES)/(1024*1024*1024),2) "Gb"
from v$asm_file f,v$asm_diskgroup g
where f.group_number=g.group_number
group by g.name,f.TYPE,f.BLOCK_SIZE,f.STRIPED
order by 1,2;
clear break
Instances currently accessing these diskgroups
=================================
select c.group_number "Group"
, g.name "Group Name"
, c.instance_name "Instance"
from v$asm_client c
, v$asm_diskgroup g
where g.group_number=c.group_number;
Free ASM disks and their paths
=======================
select header_status "Header"
, mode_status "Mode"
, path "Path"
, lpad(round(os_mb/1024),7)||'Gb' "Disk Size"
from v$asm_disk
where header_status in ('FORMER','CANDIDATE')
order by path;
Current ASM disk operations
=====================
select * from v$asm_operation;
No comments:
Post a Comment