select name
, group_number
, disk_number
, total_mb
, free_mb
from v$asm_disk
order by group_number
/
IN DETAIL
Back in 2009 I posted a script which I found very useful to review ASM disks. I gave that post the low-key title of
The ASM script of all ASM scripts.
Now that script has been improved I have to go a bit further with the
hyperbole and we have the The Mother of all ASM scripts. If it ever
gets improved then the next post will just be called ‘Who’s the Daddy’.
I have been using the current script across all our systems for the
last 3 years and I find it very useful, a colleague, Allan Webster, has
added a couple of improvements and it is now better than before.
The improvements show current disk I/O statistics and a breakdown of
the types of files in each disk group and the total sizes of that
filetype. The I/O statistics are useful when you have a lot of
databases, many of which are test and development and so you do not look
at them as that often. It just gives a quick overview that allows you
to get a feel if anything is wrong and to see what the system is
actually doing. There are also a few comments at the beginning defining
the various ASM views available.
REM VIEW |ASM INSTANCE |DB INSTANCE |
REM V$ASM_DISKGROUP |Describes a disk group (number, name, size |Contains one row for every open ASM |
REM |related info, state, and redundancy type) |disk in the DB instance. |
REM V$ASM_CLIENT |Identifies databases using disk groups |Contains no rows. |
REM |managed by the ASM instance. | |
REM V$ASM_DISK |Contains one row for every disk discovered |Contains rows only for disks in the |
REM |by the ASM instance, including disks that |disk groups in use by that DB instance. |
REM |are not part of any disk group. | |
REM V$ASM_FILE |Contains one row for every ASM file in every |Contains rows only for files that are |
REM |disk group mounted by the ASM instance. |currently open in the DB instance. |
REM V$ASM_TEMPLATE |Contains one row for every template present in |Contains no rows. |
REM |every disk group mounted by the ASM instance. | |
REM V$ASM_ALIAS |Contains one row for every alias present in |Contains no rows. |
REM |every disk group mounted by the ASM instance. | |
REM v$ASM_OPERATION |Contains one row for every active ASM long |Contains no rows. |
REM |running operation executing in the ASM instance. | |
col "Group Name" for a6 Head "Group|Name" |
col "Type" for a10 Head "Diskgroup|Redundancy" |
col "Total GB" for 9,990 Head "Total|GB" |
col "Free GB" for 9,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" |
SELECT g.group_number "Group" |
, 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" |
FROM v$asm_disk d, v$asm_diskgroup g |
WHERE d.group_number = g.group_number and |
d.mount_status = 'CACHED' |
GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb |
col "Created" for a10 Head "Added To|Diskgroup" |
col "SecsPerRead" for 9.000 Head "Seconds|PerRead" |
col "SecsPerWrite" for 9.000 Head "Seconds|PerWrite" |
select group_number "Group" |
, total_mb/1024 "Total GB" |
, read_time/reads "SecsPerRead" |
, write_time/writes "SecsPerWrite" |
where header_status not in ('FORMER','CANDIDATE') |
Prompt File Types in Diskgroups |
Prompt ======================== |
col "Block Size" for a5 Head "Block|Size" |
break on "Group Name" skip 1 nodup |
select g.name "Group Name" |
, f.BLOCK_SIZE/1024||'k' "Block Size" |
, 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 |
prompt Instances currently accessing these diskgroups |
prompt ============================================== |
select c.group_number "Group" |
, c.instance_name "Instance" |
where g.group_number=c.group_number |
prompt Free ASM disks and their paths |
prompt ============================== |
select header_status "Header" |
, lpad(round(os_mb/1024),7)||'Gb' "Disk Size" |
where header_status in ('FORMER','CANDIDATE') |
prompt Current ASM disk operations |
prompt =========================== |
This is how some of the changes look
Added To Total Free Seconds Seconds |
Group Disk Header Mode State Diskgroup GB GB Disk Name Path PerRead PerWrite |
1 0 MEMBER ONLINE NORMAL 20-FEB-09 89 88 FRA_0000 /dev/oracle/disk388 .004 .002 |
1 1 MEMBER ONLINE NORMAL 31-MAY-10 89 88 FRA_0001 /dev/oracle/disk260 .002 .002 |
1 2 MEMBER ONLINE NORMAL 31-MAY-10 89 88 FRA_0002 /dev/oracle/disk260 .007 .002 |
2 15 MEMBER ONLINE NORMAL 04-MAR-10 89 29 DATA_0015 /dev/oracle/disk203 .012 .023 |
2 16 MEMBER ONLINE NORMAL 04-MAR-10 89 29 DATA_0016 /dev/oracle/disk203 .012 .021 |
2 17 MEMBER ONLINE NORMAL 04-MAR-10 89 29 DATA_0017 /dev/oracle/disk203 .007 .026 |
2 27 MEMBER ONLINE NORMAL 31-MAY-10 89 29 DATA_0027 /dev/oracle/disk260 .011 .023 |
2 28 MEMBER ONLINE NORMAL 31-MAY-10 89 29 DATA_0028 /dev/oracle/disk259 .009 .020 |
2 38 MEMBER ONLINE NORMAL 31-MAY-10 89 29 DATA_0038 /dev/oracle/disk190 .012 .025 |
2 39 MEMBER ONLINE NORMAL 31-MAY-10 89 29 DATA_0039 /dev/oracle/disk189 .014 .015 |
2 40 MEMBER ONLINE NORMAL 31-MAY-10 89 30 DATA_0040 /dev/oracle/disk260 .011 .024 |
2 41 MEMBER ONLINE NORMAL 31-MAY-10 89 30 DATA_0041 /dev/oracle/disk260 .009 .022 |
2 42 MEMBER ONLINE NORMAL 31-MAY-10 89 29 DATA_0042 /dev/oracle/disk260 .011 .018 |
2 43 MEMBER ONLINE NORMAL 31-MAY-10 89 29 DATA_0043 /dev/oracle/disk260 .003 .026 |
2 44 MEMBER ONLINE NORMAL 31-MAY-10 89 29 DATA_0044 /dev/oracle/disk260 .008 .019 |
2 45 MEMBER ONLINE NORMAL 31-MAY-10 89 30 DATA_0045 /dev/oracle/disk193 .008 .018 |
2 46 MEMBER ONLINE NORMAL 31-MAY-10 89 30 DATA_0046 /dev/oracle/disk192 .007 .024 |
2 47 MEMBER ONLINE NORMAL 31-MAY-10 89 30 DATA_0047 /dev/oracle/disk191 .005 .022 |
2 48 MEMBER ONLINE NORMAL 31-MAY-10 89 29 DATA_0048 /dev/oracle/disk190 .008 .021 |
2 49 MEMBER ONLINE NORMAL 31-MAY-10 89 29 DATA_0049 /dev/oracle/disk189 .008 .026 |
2 50 MEMBER ONLINE NORMAL 31-MAY-10 89 29 DATA_0050 /dev/oracle/disk261 .009 .030 |
Name File Type Size STRIPE Files Gb |
DATA CONTROLFILE 16k FINE 1 0.01 |
DATAFILE 16k COARSE 404 2532.58 |
PARAMETERFILE 1k COARSE 1 0.00 |
TEMPFILE 16k COARSE 13 440.59 |
FRA AUTOBACKUP 16k COARSE 2 0.02 |
CONTROLFILE 16k FINE 1 0.01 |
No comments:
Post a Comment