ORA-19809: limit exceeded for recovery files

Use Case

After some database intensive operations following error occured:

ORA-16038: log 1 sequence# 572 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/orcl/redo01.log'

Solution

Quick googling gaves a tip: The flash recovery area is full (http://www.dba-oracle.com/t_ora_19809_limit_exceeded_for_recovery.htm)

To verify this, run following sql statement:

select * from v$recovery_file_dest;

In my case space_used was much greater than space_limit (that was self-efficient Oracle 10g VM).

To fix the problem, we can either increase flash recovery area size or clean / backup files from it.

If you have sufficient disk space available you can use following query (IMPORTANT! New flash recovery area size must be greater than space_used in previous query):

conn system/oracle@orcl
alter system set db_recovery_file_dest_size=10G scope=both
alter database open;

To remove files we have to use RMAN. If we will simply remove files from the host operating system, disk space will be emptied but Oracle won't be aware of it. So, something like that can be done:

rman target / catalog sys/oracle 

run { allocate channel t1 type disk; 

backup archivelog all delete input format '/<temp backup location>/arch_%d_%u_%s'; 

release channel t1; 

}

For more information about RMAN in Oracle 10g, follow
http://download.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmintro.htm
and
http://www.oracle-base.com/articles/10g/RMANEnhancements10g.php