spool asm_diag1.txt
set pagesize 1000
set lines 500
col "Group Name" form a25
col "Disk Name" form a30
col "State" form a15
col "Type" form a7
col "Free GB" form 9,999
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select sysdate "Date and Time" from dual;
select * from v$asm_diskgroup order by 1;
select * from v$asm_disk order by 1, 2, 3;
select * from gv$asm_operation order by 1;
select * from v$version where banner like '%Database%' order by 1;
select * from gv$asm_client order by 1;
prompt
prompt ASM Disk Groups
prompt ===============
select group_number "Group"
, name "Group Name"
, state "State"
, type "Type"
, total_mb/1024 "Total GB"
, free_mb/1024 "Free GB"
from v$asm_diskgroup
/
prompt
prompt ASM Disks
prompt ==============
col "Group" form 999
col "Disk" form 999
col "Header" form a9
col "Mode" form a8
col "Redundancy" form a10
col "Failure Group" form a10
col "Path" form a19
select group_number "Group"
, disk_number "Disk"
, header_status "Header"
, mode_status "Mode"
, state "State"
, redundancy "Redundancy"
, total_mb "Total MB"
, free_mb "Free MB"
, name "Disk Name"
, failgroup "Failure Group"
, path "Path"
from v$asm_disk
order by group_number
, disk_number
/
prompt
prompt Instances currently accessing these diskgroups
prompt ==============================================
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
/
prompt
prompt Report the Percentage of Imbalance in all Mounted Diskgroups
prompt ==============================================
select dfail, count(dfail) from
(
select disk, count(failgroup) as dfail
from x$kfdpartner, v$asm_disk where
number_kfdpartner=disk_number and grp=group_number
group by disk, failgroup
)
group by dfail;
select g.name as "GROUP", d.name as "DISK", d.failgroup, fcnt, pcnt,
decode(pcnt - fcnt, 0, 'MUST', 'SHOULD') as action from
(select gnum, DISK1, failgroup, count(failgroup) as fcnt from
(select gnum, DISK1
from
(
select d.group_number as gnum, disk as disk1,
count(distinct failgroup) as dfail
from x$kfdpartner, v$asm_disk_stat d where
number_kfdpartner=disk_number and grp=d.group_number
and active_kfdpartner=1
group by d.group_number, disk
), v$asm_disk_stat
where dfail < 3
and disk1=disk_number
and gnum=group_number),
x$kfdpartner, v$asm_disk_stat d where
number_kfdpartner=disk_number and grp=d.group_number and grp=gnum
and disk1=disk
and active_kfdpartner=1
group by gnum, disk1, failgroup),
(select grp, disk, count(disk) as pcnt from x$kfdpartner where
active_kfdpartner=1 group by grp, disk),
v$asm_diskgroup_stat g, v$asm_disk_stat d
where gnum=grp and gnum=g.group_number and gnum=d.group_number and
disk=disk1 and disk=disk_number and
((fcnt = 1 and (pcnt - fcnt) > 3) or ((pcnt - fcnt) = 0))
/
col TYPE form a15
col FILE_NUMBER form 9999 head FILE_NUM
col GROUP_NUMBER form 9999 head GR_NUM
col GB for 9999.99
select GROUP_NUMBER ,
FILE_NUMBER ,
COMPOUND_INDEX ,
INCARNATION ,
BLOCK_SIZE ,
BLOCKS ,
BYTES/1024/1024/1024 GB ,
TYPE ,
STRIPED ,
CREATION_DATE ,
MODIFICATION_DATE
from v$asm_file
where TYPE != 'ARCHIVELOG'
/
prompt
prompt free ASM disks and their paths
prompt ===========================
select header_status , mode_status, path from V$asm_disk
where header_status in ('FORMER','CANDIDATE')
/
show parameter asm
show parameter size
show parameter proc
show parameter cluster
show parameter instance_type
show parameter instance_name
show parameter pfile
show sga
spool off
Code to be run on the ASM instance. Use file asmdebug.sql -- Get the # of Allocation Units
本文转自maclean_007 51CTO博客，原文链接:
http://blog.51cto.com/maclean/1277810