Mostly this is applicable for 11g Instances when ever the db_recovery_file_dest_size got full then database will be in hang state and unable to operate anything.
SQL> show parameter recovery
NAME TYPE VALUE
db_recovery_file_dest string /u01/oradata/xxqa/arch
db_recovery_file_dest_size big integer 120G
recovery_parallelism integer 0
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 53.31 0 250
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
RMAN> crosscheck archivelog all;
RMAN> list expired archivelog all;
Note: Above command will give the list of expired archive logs.
RMAN> delete expired archivelog all;
Note: Above command will delete the expired archive logs.
Once after deleting the expired archivelogs again check usage from the below query and make sure space has been released.
SQL> select * from v$flash_recovery_area_usage;
