[Oracle] ASM Size check

 

 

set linesize 300
col name format a10
col USABLE_FILE_GB format 999,999.00
col TOTAL_GB       format 999,999.00
col FREE_GB        format 999,999.00
col USABLE_CALC_GB format 999,999.00

select group_number "Group#",
       name,
       total_mb/1024 TOTAL_GB,
   round((total_mb - USABLE_FILE_MB)/1024,2) USED_GB,
       USABLE_FILE_MB/1024 USABLE_FILE_GB,    
   free_mb/1024 FREE_GB,
       100-round(free_mb/total_mb*100) "usgae(%)",
        ((FREE_MB - REQUIRED_MIRROR_FREE_MB))/1024 USABLE_CALC_GB,
       type, state
from v$asm_diskgroup;


set linesize 300
col name format a10
col REAL_TOTAL_GB format 999,999.00
col REAL_FREE_GB       format 999,999.00
col USABLE_TOTAL_GB        format 999,999.00
col USABLE_FREE_GB format 999,999.00
col USABLE_USED_GB format 999,999.00

select to_char(sysdate,'yyyymmdd hh24:mi:ss') chdate,
       group_number "Group#",
       name,
       total_mb/1024 REAL_TOTAL_GB,
       ROUND(free_mb/1024,2) REAL_FREE_GB,
       ROUND((total_mb-REQUIRED_MIRROR_FREE_MB)/3/1024,2) USABLE_TOTAL_GB,
       ROUND(USABLE_FILE_MB/1024,2) USABLE_FREE_GB,
       ROUND(((total_mb-REQUIRED_MIRROR_FREE_MB)/3-USABLE_FILE_MB)/1024,2) USABLE_USED_GB,
       ROUND((((total_mb-REQUIRED_MIRROR_FREE_MB)/3)-USABLE_FILE_MB)/((total_mb-REQUIRED_MIRROR_FREE_MB)/3)*100,2) "USAGE(%)",
       type, state
from v$asm_diskgroup;



set linesize 300
col name format a10
col REAL_TOTAL_GB format 999,999.00
col REAL_FREE_GB       format 999,999.00
col USABLE_TOTAL_GB        format 999,999.00
col USABLE_FREE_GB format 999,999.00
col USABLE_USED_GB format 999,999.00
set head off
set feedback off
set time off
set timing off
set echo off
spool asm_status.txt

select to_char(sysdate,'yyyymmdd_hh24:mi:ss') chdate,
       group_number "Group#",
       name,
       total_mb/1024 REAL_TOTAL_GB,
       ROUND(free_mb/1024,2) REAL_FREE_GB,
       ROUND((total_mb-REQUIRED_MIRROR_FREE_MB)/2/1024,2) USABLE_TOTAL_GB,
       ROUND(USABLE_FILE_MB/1024,2) USABLE_FREE_GB,
       ROUND(((total_mb-REQUIRED_MIRROR_FREE_MB)/2-USABLE_FILE_MB)/1024,2) USABLE_USED_GB,
       ROUND((((total_mb-REQUIRED_MIRROR_FREE_MB)/2)-USABLE_FILE_MB)/((total_mb-REQUIRED_MIRROR_FREE_MB)/2)*100,2) "USAGE(%)",
       type, state
from v$asm_diskgroup;

spool off





/u01/app/19.0.0.0/grid/bin/sqlplus '/as sysdba'

col name format a10
col REAL_TOTAL_GB format 999,999.00
col REAL_FREE_GB       format 999,999.00
col USABLE_TOTAL_GB        format 999,999.00
col USABLE_FREE_GB format 999,999.00
col USABLE_USED_GB format 999,999.00
set linesize 300
set head off
set feedback off
set time off
set timing off
set echo off
spool /home/oracle/check/ASMCHECK/asm_status.tmp
select to_char(sysdate,'yyyymmdd_hh24:mi:ss') chdate,
       group_number "Group#",
       name,
       total_mb/1024 REAL_TOTAL_GB,
       ROUND(free_mb/1024,2) REAL_FREE_GB,
       ROUND((total_mb-REQUIRED_MIRROR_FREE_MB)/2/1024,2) USABLE_TOTAL_GB,
       ROUND(USABLE_FILE_MB/1024,2) USABLE_FREE_GB,
       ROUND(((total_mb-REQUIRED_MIRROR_FREE_MB)/2-USABLE_FILE_MB)/1024,2) USABLE_USED_GB,
       ROUND((((total_mb-REQUIRED_MIRROR_FREE_MB)/2)-USABLE_FILE_MB)/((total_mb-REQUIRED_MIRROR_FREE_MB)/2)*100,2) "USAGE(%)",
       type, state
from v$asm_diskgroup;
spool off
!cat /home/oracle/check/ASMCHECK/asm_status.tmp | egrep -v spool | egrep -v SQL  > /home/oracle/check/ASMCHECK/asm_status.txt
exit
EOF