Have you seen the Oracle error messages ORA-19804, ORA-19809 or ORA-19815 a lot of times? Then you are not alone, welcome!

The reason for this message is that your database have reached the logical, or in worst case physical limit of «flash_recovery_area» in the Oracle-lingo. This is a side effect from the configuration assistant creating the database dbca, this is because you may have followed the instructions for a DEFAULT installation, which sets the database parameter DB_RECOVERY_FILE_DEST_SIZE to 2 GB.

What is the correct size for the Flash Recovery Area?

Most database administrators allocate a huge area for archive files and backup of the database. The configuration assistant should indicate the consequences having a too small Flash Recovery Area. You may end up with a hanging database, hanging until you either clean up some disk space or change this parameter to something more realistic. For instance, 90% of the space you have set aside for backup.
Things get more complicated if you have more databases using the same Flash Recovery Area, because they will compete for the same space…

First of all, check the alert-log.

The Alert log is usually a good starting point while in trouble. The message you should look for is easy to recognize:

ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
Creating archive destination file : /oradata/flash_recovery_area/PROD/archivelog/2008_09_26/o1_mf_1_10164_0_.arc (189364 blocks)
Fri Sep 26 18:06:36 2008
Errors in file /oracle/admin/PROD/udump/prod_rfs_17303.trc:
ORA-00270: error creating archive log /oradata/flash_recovery_area/PROD/archivelog/2008_09_26/o1_mf_1_10164_%u_.arc
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 96954368 bytes disk space from 53687091200 limit

You may also use a Oracle command oerr to check out what the error is all about. oerr is only available on the Linux and Unix platforms.

oracle@xps:~$ oerr ora 19809
19809, 00000, "limit exceeded for recovery files"
//*Cause: The limit for recovery files specified by the
//        DB_RECOVERY_FILE_DEST_SIZE was exceeded.
//*Action:The error is accompanied by 19804. See message 19804 for further
//        details.

oracle@xps:~$ oerr ora 19804
19804, 00000, "cannot reclaim %s bytes disk space from %s limit"
// *Cause: Oracle cannot reclaim disk space of specified bytes from the
//         DB_RECOVERY_FILE_DEST_SIZE limit.
// *Action: There are five possible solutions:
//          1) Take frequent backup of recovery area using RMAN.
//          2) Consider changing RMAN retention policy.
//          3) Consider changing RMAN archivelog deletion policy.
//          4) Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
//          5) Delete files from recovery area using RMAN.

SYS@PROD SQL> SELECT (100 - sum(percent_space_used))
                              + sum(percent_space_reclaimable)
                              "Percent free space"
                              FROM v$flash_recovery_area_usage;

Percent free space
------------------------------
    	             84,09

SYS@PROD SQL> ALTER SYSTEM SET
                              db_recovery_file_dest_size = 100G;