If db_recovery_file_dest_size got full

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;