Some ASM Space tidbits in Exadata

Following are some of the queries, I frequently use to see the space status at the ASM diskgroups at the Exadata database machine, especially for the FLASH RECOVERY AREA diskgroup RECO.

Yes, they work the same on non-Exadata system, but you know Exadata is the buzzword :)

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               1
REDO LOG                          6                         0              3
ARCHIVED LOG                 3                         0              5
BACKUP PIECE                          0                         0               0
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

SQL> select sum(PERCENT_SPACE_USED) from v$flash_recovery_area_usage;

SUM(PERCENT_SPACE_USED)
-----------------------
                  9

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest               string                            +RECO
db_recovery_file_dest_size       big integer                     2000000M
recovery_parallelism                  integer                          0



[oracle@EXA1:~]$ export ORACLE_SID= ASMSID
[oracle@EXA1:~]$ export ORACLE_HOME= GRID HOME
[oracle@EXA1:~]$ asmcmd

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  4194304   7688704  3596368           771700         1412334              0             N  RECO/

 select name,total_mb/1024/1024,free_mb/1024/1024,usable_file_mb/1024/1024 from v$asm_diskgroup;




NAME     TOTAL_MB/1024/1024      FREE_MB/1024/1024     USABLE_FILE_MB/1024/1024
--------               ------------------      -----------------             ------------------------
DATA                    135.2734375        18.87905121                      12.55378914
RECO                    18.09545898        13.42976379                      11.34690666
SYSTEMDG          11.9909668          11.9886322                        1.903817177