Oracle ASM diskgrop report ..
set feedback off pagesize 1000 head on underline _ linesize 170
var v number ;
begin :v := nvl('&v',95); end;
/
col total_gb format 99999.99 heading 'total gb '
col mb_avail format 999,999,999 heading 'mb avail'
col used format 999.99 heading '% used'
col volume_name format a10
col volume_device format a25
col mountpath format a15
col attention format a20
col name format a15
col COMPATIBILITY for a12
col DATABASE_COMPATIBILITY for a15
col total_gb format 99999.99 heading 'total gb '
col host_name for a15
set linesize 400
select
substr(max (SYS_CONTEXT('USERENV','HOST')),1,15) host_name
,g.group_number
, g.name
, g.state
,min(compatibility) compatibility, min(database_compatibility) database_compatibility
,max(Type) type
,max(allocation_unit_size/1024/1024) allocation_unit_size_MB
, g.total_mb/1024 total_gb
, trunc((g.free_mb/1024),2) free_gb
, usable_file_mb usable_file_mb
, count(DISK_NUMBER) Total_Disks
, trunc(max(d.TOTAL_MB/1024),2) disk_size
, (round((1- (g.free_mb / g.total_mb))*100, 2)) "TotalUsed%"
, case when (ROUND((1- (g.free_mb / g.total_mb))*100, 2)>:v) then :v||'% full ***'
else 'Good'
end as attention
from v$asm_diskgroup g,v$asm_disk d
where 1=1
and d.group_number = g.group_number
-- and g.Name not in ('GRID','ACFS' )
--and g.Name in ('RECO01')
--and g.group_number not in (select v1.group_number from v$asm_volume v1)
--and g.group_number in ( select g2.group_number from v$asm_diskgroup g2 where voting_files='N')
group by g.group_number, g.name,g.state, g.total_mb/1024, trunc((g.free_mb/1024),2), usable_file_mb,(round((1- (g.free_mb / g.total_mb))*100, 2))
--ORDER BY "TotalUsed%" desc
order by 2
;
set term on pages 200 lines 255 numf 999,999 COLUMN NAME HEAD "DiskGroup" FORMAT A15 COLUMN type HEAD "Type" FORMAT A6 COLUMN compatibility HEAD "ASM|Compat" FORMAT A10 COLUMN database_compatibility HEAD "RDBMS|Compat" FORMAT A10 COLUMN allocation_unit_size_MB HEAD "AU|Size|(MB)" FORMAT 99999999 COLUMN offline_disks HEAD "Offline|Disks" COLUMN TOTAL_GB HEAD "Total|(GB)" COLUMN FREE_GB HEAD "Free|(GB)" COLUMN used_GB HEAD "Used|(GB)" COLUMN hot_used_GB HEAD "Hot|Used|(GB)" COLUMN cold_used_GB HEAD "Cold|Used|(GB)" COLUMN REQUIRED_MIRROR_FREE_GB HEAD "Required|Free|Mirror|(GB)" JUSTIFY RIGHT COLUMN USABLE_GB HEAD "Usable|Free|(GB)" TTITLE LEFT "ASM DISKGROUP SPACE USAGE REPORT" BREAK ON REPORT COMPUTE SUM LABEL 'Total' OF TOTAL_GB FORMAT 99,999,999 ON REPORT COMPUTE SUM LABEL 'Total' OF FREE_GB FORMAT 99,999,999 ON REPORT COMPUTE SUM LABEL 'Total' OF USED_GB FORMAT 99,999,999 ON REPORT COMPUTE SUM LABEL 'Total' OF HOT_USED_GB FORMAT 99,999,999 ON REPORT COMPUTE SUM LABEL 'Total' OF COLD_USED_GB FORMAT 99,999,999 ON REPORT COMPUTE SUM LABEL 'Total' OF REQUIRED_MIRROR_FREE_GB FORMAT 99,999,999 ON REPORT COMPUTE SUM LABEL 'Total' OF USABLE_GB FORMAT 9,999,999 ON REPORT -- if disk not mounted with instance their values will be 0. SELECT NAME , state , type , compatibility , database_compatibility , allocation_unit_size/1024/1024 allocation_unit_size_MB , offline_disks , ROUND(TOTAL_MB/1024) TOTAL_GB , ROUND(FREE_MB/1024) FREE_GB , ROUND((hot_used_mb + cold_used_mb) /1024) USED_GB , ROUND(hot_used_mb/1024) HOT_USED_GB , ROUND(cold_used_mb/1024) COLD_USED_GB , ROUND(REQUIRED_MIRROR_FREE_MB/1024) REQUIRED_MIRROR_FREE_GB , ROUND(USABLE_FILE_MB /1024) USABLE_GB FROM v$asm_diskgroup;
===
var v number ;
begin :v := nvl('&v',90); end;
/
/
set term on pages 200 lines 255 numf 999,999
col name head "diskgroup" format a10
col type head "type" format a6
col compatibility head "asm|compat" format a10
col database_compatibility head "rdbms|compat" format a10
col allocation_unit_size_mb head "au|size|(mb)" format 999
col offline_disks head "offline|disks"
col total_gb head "total|(gb)"
col free_gb head "free|(gb)"
col used_gb head "used|(gb)"
col hot_used_gb head "hot|used|(gb)"
col cold_used_gb head "cold|used|(gb)"
col attention for a12
with disk as (select
d.group_number gp_num
, count(d.DISK_NUMBER) Total_Disks
, trunc(max(d.TOTAL_MB/1024),2) disk_size
from v$asm_disk d
group by d.group_number )
select
g.name
, di.total_disks
, di.disk_size
, g.state
, g.type
, g.compatibility
, g.database_compatibility
, g.allocation_unit_size/1024/1024 allocation_unit_size_MB
, g.offline_disks
, round(g.total_mb/1024) total_gb
, round(g.free_mb/1024) free_gb
, round((g.hot_used_mb + cold_used_mb) /1024) used_gb
, round(g.hot_used_mb/1024) hot_used_gb
, round(g.cold_used_mb/1024) cold_used_gb
, round(g.required_mirror_free_mb/1024) required_mirror_free_gb
, round(g.usable_file_mb /1024) usable_gb
, (round((1- (g.free_mb / g.total_mb))*100, 2)) "TotalUsed%"
, case when (ROUND((1- (g.free_mb / g.total_mb))*100, 2)>:v) then :v||'% full ***'
else 'Good'
end as attention
FROM v$asm_diskgroup g, disk di
where 1=1
and g.Name not in ('GRID' )
and g.group_number=di.gp_num ;
=====================
SET LINESIZE 200 PAGESIZE 9999
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 9999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "" of total_mb used_mb on disk_group_name
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / nullif(b.total_mb,0)))*100, 2) ||'%' pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY a.name
/
Disk Group Name Path File Name Fail Group File Size (MB) Used Size (MB) Pct. Used
-------------------- ----------------- -------------------- -------------------- -------------- -------------- -----------------------------------------
DATA ORCL:FLSH_DATA0 FLSH_DATA0 FLSH_DATA0 102,401 44,067 43.03%
ORCL:FLSH_DATA1 FLSH_DATA1 FLSH_DATA1 102,401 44,058 43.02%
ORCL:FLSH_DATA2 FLSH_DATA2 FLSH_DATA2 102,401 44,053 43.02%
ORCL:FLSH_DATA3 FLSH_DATA3 FLSH_DATA3 102,401 44,055 43.02%
******************** -------------- --------------
409,604 176,233
DUMPS ORCL:FLSH_DUMPS0 FLSH_DUMPS0 FLSH_DUMPS0 512,000 501,900 98.03%
******************** -------------- --------------
512,000 501,900
GRID ORCL:FLSH_GRID0 FLSH_GRID0 FLSH_GRID0 40,961 396 .97%
******************** -------------- --------------
40,961 396
LOGS ORCL:FLSH_LOGS0 FLSH_LOGS0 FLSH_LOGS0 102,401 38,704 37.8%
******************** -------------- --------------
102,401 38,704
MGMT ORCL:FLSH_MGMT0 FLSH_MGMT0 FLSH_MGMT0 102,400 55,080 53.79%
******************** -------------- --------------
102,400 55,080
[CANDIDATE] ORCL:FLSH_RECO0 0 0 %
******************** -------------- --------------
0 0
-------------- --------------
Grand Total: 1,167,366 772,313
9 rows selected.
============
COLUMN name NEW_VALUE _instname NOPRINT
select lower(instance_name) name from v$instance
set lines 600
col state format a9
col dgname format a15
col sector format 999990
col block format 999990
col label format a25
col path format a40
col redundancy format a25
col pct_used format 990
col pct_free format 990
col voting format a6
BREAK ON REPORT
COMPUTE SUM OF raw_gb ON REPORT
COMPUTE SUM OF usable_total_gb ON REPORT
COMPUTE SUM OF usable_used_gb ON REPORT
COMPUTE SUM OF usable_free_gb ON REPORT
COMPUTE SUM OF required_mirror_free_gb ON REPORT
COMPUTE SUM OF usable_file_gb ON REPORT
COL name NEW_V _hostname NOPRINT
select lower(host_name) name from v$instance;
select
trim('&_hostname') hostname,
name as dgname,
state,
type,
sector_size sector,
block_size block,
allocation_unit_size au,
round(total_mb/1024,2) raw_gb,
round((DECODE(TYPE, 'HIGH', 0.3333 * total_mb, 'NORMAL', .5 * total_mb, total_mb))/1024,2) usable_total_gb,
round((DECODE(TYPE, 'HIGH', 0.3333 * (total_mb - free_mb), 'NORMAL', .5 * (total_mb - free_mb), (total_mb - free_mb)))/1024,2) usable_used_gb,
round((DECODE(TYPE, 'HIGH', 0.3333 * free_mb, 'NORMAL', .5 * free_mb, free_mb))/1024,2) usable_free_gb,
round((DECODE(TYPE, 'HIGH', 0.3333 * required_mirror_free_mb, 'NORMAL', .5 * required_mirror_free_mb, required_mirror_free_mb))/1024,2) required_mirror_free_gb,
round(usable_file_mb/1024,2) usable_file_gb,
round((total_mb - free_mb)/total_mb,2)*100 as "PCT_USED",
round(free_mb/total_mb,2)*100 as "PCT_FREE",
offline_disks,
voting_files voting
from v$asm_diskgroup
where total_mb != 0
order by 1;
===
set numf 99999.99
col DiskCnt for 9999
col "Group" for 999
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;
=====
from web
set linesize 300
sset term on
SSET numf 999,999
DEFINE size_label=GB
DEFINE size_divider="1024/1024/1024"
PROMPT **********************************************************************
PROMPT * A S M D I S K G R O U P S P A C E U S A G E R E P O R T *
PROMPT **********************************************************************
COLUMN NAME HEAD "DiskGroup" FORMAT A16
COLUMN group_number HEAD "G#" FORMAT 99
COLUMN type HEAD "Redundancy|Type" FORMAT A6
COLUMN compatibility HEAD "ASM|Compat" FORMAT A10
COLUMN database_compatibility HEAD "RDBMS|Compat" FORMAT A10
COLUMN allocation_unit_size_MB HEAD "AU|Size|(MB)" FORMAT 999
COLUMN offline_disks HEAD "Offline|Disks"
COLUMN separator HEAD "!|!|!|!|!|!" FORMAT a1
COLUMN mirrored_total_GB HEAD "(E)||Mirrored|Total|(&size_label)"
COLUMN USABLE_TOTAL_GB HEAD "(A-D)|/REDUN||Safely|Usable|Total|(&size_label)"
COLUMN USABLE_USED_GB HEAD "(B-D)|/REDUN||Safely|Usable|Used|(&size_label)"
COLUMN USABLE_FREE_GB HEAD "(C-D)|/REDUN||Safely|Usable|Free|(&size_label)"
COLUMN USABLE_USED_PERCENT HEAD "Safely|Usable|Used|(%)" FORMAT 999.99
COLUMN USABLE_FREE_PERCENT HEAD "Safely|Usable|Free|(%)" FORMAT 999.99
COLUMN mirrored_free_GB HEAD "Mirrored|Free|(&size_label)"
COLUMN REQUIRED_MIRROR_FREE_GB HEAD "(D)|Required|Free|ForMirror|(&size_label)" JUSTIFY RIGHT
COLUMN TOTAL_GB HEAD "(A)||Total|Avail|(&size_label)"
COLUMN used_GB HEAD "(B)||Total|Used|(&size_label)"
COLUMN hot_used_GB HEAD "Hot|Used|(&size_label)"
COLUMN cold_used_GB HEAD "Cold|Used|(&size_label)"
COLUMN FREE_GB HEAD "(C)||Total|Free|(&size_label)"
BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF TOTAL_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF USED_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF HOT_USED_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF COLD_USED_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF FREE_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF REQUIRED_MIRROR_FREE_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF MIRRORED_TOTAL_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF MIRRORED_USED_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF MIRRORED_FREE_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF USABLE_GB FORMAT 9,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF USABLE_TOTAL_GB FORMAT 9,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF USABLE_USED_GB FORMAT 9,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF USABLE_FREE_GB FORMAT 9,999,999 ON REPORT
PROMPT
PROMPT . <----------------------- With Mirroring ----------------------> <-------------------- Without Mirroring ---------------->
-- Note:
-- The GROUP_NUMBER, TOTAL_MB, and FREE_MB columns are only
-- meaningful if the disk group is mounted by the instance. Otherwise, their values will be 0.
SELECT NAME
, group_number
, type
, '!' separator
, ROUND(TOTAL_MB/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)/1024) mirrored_TOTAL_GB
, ROUND((TOTAL_MB - REQUIRED_MIRROR_FREE_MB )/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)/1024) USABLE_TOTAL_GB
, ROUND((hot_used_mb + cold_used_mb)/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3) /1024) USABLE_USED_GB
, ROUND(USABLE_FILE_MB /1024) USABLE_FREE_GB
, ROUND((USABLE_FILE_MB + ROUND(REQUIRED_MIRROR_FREE_MB/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)) ) /1024) mirrored_free_GB
, ROUND( (hot_used_mb + cold_used_mb) / (TOTAL_MB - REQUIRED_MIRROR_FREE_MB ) * 100 , 2) USABLE_USED_PERCENT
, ROUND(USABLE_FILE_MB /((TOTAL_MB - REQUIRED_MIRROR_FREE_MB )/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)) * 100 ,2) USABLE_FREE_PERCENT
, '!' separator
, ROUND(REQUIRED_MIRROR_FREE_MB/1024) REQUIRED_MIRROR_FREE_GB
, ROUND(TOTAL_MB/1024) TOTAL_GB
, ROUND((hot_used_mb + cold_used_mb) /1024) USED_GB
, ROUND(hot_used_mb/1024) HOT_USED_GB
, ROUND(cold_used_mb/1024) COLD_USED_GB
, ROUND(FREE_MB/1024) FREE_GB
, '!' separator
FROM v$asm_diskgroup_stat;
===================
DiskGroup space info
set feedback off pagesize 1000 head on underline _ linesize 170
var v number ;
begin :v := nvl('&v',95); end;
/
set linesize 400
col mb_avail format 999,999,999 heading 'mb avail'
col used format 999.99 heading '% used'
col volume_name format a10
col volume_device format a25
col mountpath format a15
col attention format a20
col name format a15
col COMPATIBILITY for a12
col DATABASE_COMPATIBILITY for a15
--col total_gb format 99999.99 heading 'total gb '
col host_name for a16
col total_gb format 999,999,999,999 heading 'total gb '
col STATE for a12
col type for a12
select
substr(max (SYS_CONTEXT('USERENV','HOST')),1,16) host_name
,g.group_number
, g.name
, g.state
,min(compatibility) compatibility, min(database_compatibility) database_compatibility
,max(Type) type
, g.total_mb/1024 total_gb
, trunc((g.free_mb/1024),2) free_gb
, usable_file_mb usable_file_mb
, count(DISK_NUMBER) Total_Disks
, trunc(max(d.TOTAL_MB/1024),2) disk_size
, (round((1- (g.free_mb / g.total_mb))*100, 2)) "TotalUsed%"
, case when (ROUND((1- (g.free_mb / g.total_mb))*100, 2)>:v) then :v||'% full ***'
else 'Good'
end as attention
from v$asm_diskgroup g,v$asm_disk d
where 1=1
and d.group_number = g.group_number
-- and g.Name not in ('GRID','ACFS' )
--and g.Name in ('RECO01')
--and g.group_number not in (select v1.group_number from v$asm_volume v1)
--and g.group_number in ( select g2.group_number from v$asm_diskgroup g2 where voting_files='N')
group by g.group_number, g.name,g.state, g.total_mb/1024, trunc((g.free_mb/1024),2), usable_file_mb,(round((1- (g.free_mb / g.total_mb))*100, 2))
--ORDER BY "TotalUsed%" desc
order by 2
;
as grid [grid@ ~]$ sqlplus / as sysasm --- <<<<<<<<<<<<<< from grid var v number ; begin :v := nvl('&v',95); end; / set feedback off pagesize 1000 head off underline _ linesize 190 ttitle off column today noprint new_value strtoday select to_char( sysdate, 'dd mon yyyy hh24:mi:ss' ) today from dual; column database_name noprint new_value strdatabasename select 'Asm usage Statistics for ON '||HOST_NAME as database_name from v$instance ; TTITLE LEFT '______________________________________________________________________________________________________________________' - SKIP 2 CENTER strToday - SKIP CENTER 'ASM Usage' - SKIP CENTER strDatabaseName - SKIP LEFT '______________________________________________________________________________________________________________________' - SKIP LEFT '' set head on col total_gb format 999,999,999 heading 'total gb ' col mb_avail format 999,999,999 heading 'mb avail' col used format 999.99 heading '% used' col volume_name format a10 col volume_device format a25 col mountpath format a15 col attention format a20 col name format a15 col type format a11 heading 'Redundancy' col VOTING_FILES for A11 col COMPATIBILITY for a12 col DATABASE_COMP for a12 select g.group_number , g.name , g.state ,g.type ,g.VOTING_FILES ,max(COMPATIBILITY ) COMPATIBILITY ,max(DATABASE_COMPATIBILITY) DATABASE_COMP , g.total_mb/1024 total_gb , trunc((g.free_mb/1024),2) free_gb , usable_file_mb usable_file_mb , count(DISK_NUMBER) Total_Disks , trunc(max(d.TOTAL_MB/1024),2) disk_size , (round((1- (g.free_mb / nullif(g.total_mb, 0)))*100, 2)) "TotalUsed%" , case when (ROUND((1- (g.free_mb / nullif(g.total_mb, 0)))*100, 2)>:v) then :v||'% full ***' else 'Good' end as attention from v$asm_diskgroup g,v$asm_disk d where 1=1 and d.group_number = g.group_number -- and g.Name not in ('GRID','ACFS' ) --and g.Name in ('RECO01') and g.group_number not in (select v1.group_number from v$asm_volume v1) --- to avoid acfs !!! run as grid!! --and g.group_number in ( select g2.group_number from v$asm_diskgroup g2 where voting_files='N') group by g.group_number, g.name,g.state,g.type ,g.VOTING_FILES,g.total_mb/1024, trunc((g.free_mb/1024),2), usable_file_mb,(round((1- (g.free_mb / nullif(g.total_mb, 0)))*100, 2)) ORDER BY "TotalUsed%" desc ; ______________________________________________________________________________________________________________________ 21 apr 2024 09:14:19 ASM Usage Asm usage Statistics for ON rac01 ______________________________________________________________________________________________________________________ GROUP_NUMBER NAME STATE Redundancy VOTING_FILE COMPATIBILIT DATABASE_COM total gb FREE_GB USABLE_FILE_MB TOTAL_DISKS DISK_SIZE TotalUsed% ATTENTION ____________ _______________ ___________ ___________ ___________ ____________ ____________ ____________ __________ ______________ ___________ __________ __________ ____________________ 0 MGMT DISMOUNTED N 0.0.0.0.0 0.0.0.0.0 0 0 0 2 0 Good 0 RECO DISMOUNTED N 0.0.0.0.0 0.0.0.0.0 0 0 0 2 0 Good 3 LOGS MOUNTED EXTERN N 12.1.0.0.0 10.1.0.0.0 100 97.49 99835 1 100 2.51 Good 2 GRID MOUNTED EXTERN Y 12.1.0.0.0 10.1.0.0.0 40 39.57 40525 1 40 1.06 Good
var v number ; begin :v := nvl('&v',93); end; / set feedback off pagesize 1000 head off underline _ linesize 200 ttitle off column today noprint new_value strtoday select to_char( sysdate, 'dd mon yyyy hh24:mi:ss' ) today from dual; column database_name noprint new_value strdatabasename select 'Asm usage Statistics for ON '||HOST_NAME ||'-' || Db_unique_name as database_name from v$database,v$instance ; TTITLE LEFT '______________________________________________________________________________________________________________________' - SKIP 2 CENTER strToday - SKIP CENTER 'ASM Usage' - SKIP CENTER strDatabaseName - SKIP LEFT '______________________________________________________________________________________________________________________' - SKIP LEFT '' set head on col total_gb format 999,999,999 heading 'total gb ' col mb_avail format 999,999,999 heading 'mb avail' col used format 999.99 heading '% used' col volume_name format a10 col volume_device format a25 col mountpath format a15 col attention format a20 col name format a15 col Redundancy for a15 col free for a8 col disk_size for a10 col TOTAL for a8 col usable_file for a12 col COMPATIBILITY for a10 col DATABASE_COMP for a10 col VOTING_FILES for a12 select g.group_number , g.name , g.state ,g.type ,g.VOTING_FILES ,max(COMPATIBILITY ) COMPATIBILITY ,max(DATABASE_COMPATIBILITY) DATABASE_COMP , upper(dbms_xplan.format_size2(g.total_mb*1024*1024 )) total ,max(TYPE) Redundancy , dbms_xplan.format_size(g.free_mb*1024*1024) free , dbms_xplan.format_size(usable_file_mb* 1024*1024) usable_file , count(DISK_NUMBER) Total_Disks , dbms_xplan.format_size(max(d.TOTAL_MB*1024*1024)) disk_size , (round((1- (g.free_mb / g.total_mb))*100, 2)) "TotalUsed%" , case when (ROUND((1- (g.free_mb / g.total_mb))*100, 2)>:v) then :v||'% full ***' else 'Good' end as attention from v$asm_diskgroup g,v$asm_disk d where 1=1 and d.group_number = g.group_number and g.Name not in ('GRID','ACFS' ) --and g.Name in ('RECO01') and g.group_number not in (select v1.group_number from v$asm_volume v1) and g.group_number in ( select g2.group_number from v$asm_diskgroup g2 where voting_files='N') group by g.group_number, g.name,g.type ,g.VOTING_FILES,g.state, g.total_mb*1024*1024, (g.free_mb*1024*1024), usable_file_mb* 1024*1024 ,(round((1- (g.free_mb / g.total_mb))*100, 2)) ORDER BY "TotalUsed%" desc ;
from grid if proxy asm instance
export ORACLE_HOME=/u01/app/12.1.0/grid
export ORACLE_SID=+APX2
select * from v$asm_filesystem;
No comments:
Post a Comment