Clear Flash Recovery Area

Scenerio:

If you see error in your alert log like below

ORA-19815: WARNING: db_recovery_file_dest_size of 6442450944 bytes is 100.00% used, and has 0 remaining bytes available.

Some times database hangs after these warnings.

and you  physically deleted all archived logfiles in flash recovery area due to space limitation as stated above. Even after deleting these archived logs database still will be showing insufficient space to archive online log, no matter what is set (size)  db_recovery_file_dest_size parameter.

Solution

If you physically delete archivelogs, Oracle isn't aware of this, until you instruct RMAN to validate the files. Therefore you should run these commands in RMAN :

CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;

The CrossCheck is where RMAN validates Archivelogs. It if finds an ArchiveLog in its repository but not present on disk, it marks the

file as "Expired" in it's repository. The next command then "deletes" the entries for these "Expired" file.