Oracle Useful Commands.....Unlimited Edition ;)

Error:

ORA-19815: WARNING: db_recovery_file_dest_size of 65779059917 bytes is 85.06% used

Oracle ASM server will naturally generate this error when the available space falls below 15% at db_recovery_file_dest Area

Please Check


SQL> SELECT object_type, message_type, message_level,reason, suggested_action FROM dba_outstanding_alerts;

db_recovery_file_dest_size of 65779059917 bytes is 85.06% used and has 9828093133 remaining bytes available.

Fix:

sql>SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .04 0 1
ONLINELOG .65 0 8
ARCHIVELOG 17.61 0 329
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 71.63 71.5 1180

This will tell us the next course of action instead of leading us to sheer increasing db_recovery_file_dest_size on FRA diskgroup

SQL> select NAME,STATE,TYPE ,TOTAL_MB,FREE_MB,USABLE_FILE_MB from v$asm_diskgroup;

NAME STATE TYPE TOTAL_MB FREE_MB USABLE_FILE_MB
------------------------------ ----------- ------ ---------- ---------- --------------
dbname_DATA1 CONNECTED EXTERN 243957 64945 64945
dbname_FRA1 CONNECTED EXTERN 69702 11218 11218
dbname_DATA1 MOUNTED EXTERN 522765 367338 367338
dbname_FRA1 MOUNTED EXTERN 34851 24176 24176

The main culprits of this issue are FLASHBACKLOG and ARCHIVELOG file types occupying 71 and 17% respectively
From the alertlog the flashback logs seems to be removed by oracle server itself whenever on demand...

Therefore I tried backing up archivelogs with delete input option and now the space released with no similar outstanding alerts in the ASM box

SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .04 0 1
ONLINELOG .65 0 8
ARCHIVELOG 3.48 0 65
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 70.39 70.27 1160