Oracle DBA – A lifelong learning experience

Posts Tagged ‘ORa-19804’

ORA-19809: limit exceeded for recovery files – db_recovery_file_dest_size and archiver error

Posted by John Hallas on July 16, 2008

I have long been plagued by the parameter db_recovery_file_dest_size causing a problem when the size limit is exceeded.
As I have been working on development systems my normal work around was to free up some space in the db_recovery_file_dest and hope that
that the database would continue after the archiver logger error.
Invariably I needed to restart the database and hope that the problem resolves itself.
Today I decided to take some action and work out how to fix the problem.

I logged onto the ASM disk using asmcmd -p (the -p shows the current working directory) and moved to the archive log directory
ASCMD > cd FRA/SID/ARCHIVELOG/2008-07-16
ASCMD > rm thr* (I did not need to keep the archive logs as we had no need to backup/restore the database as it could be rebuilt if necessary)
ASCMD > YES (to confirm the prompt)

I then waited for the database to continue from the archiver error but this did not happen

The alert log typically shows an entry as follows :-

ORA-19815: WARNING: db_recovery_file_dest_size of 42949672960 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.
************************************************************************
Errors in file /u00/app/oracle/diag/rdbms/sid/SID/trace/SID_ora_20214.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 1063256064 bytes disk space from 42949672960 limit
ARCH: Error 19809 Creating archive log file to ‘+FRA’

I am sure that this message is either new in 11g or has been improved because I have never noticed the 4th option before.

I had previously configured RMAN on the database and I realised that I needed to ensure that the RMAN catalog was aware that the files had been removed manually and space was now available.

RMAN > CHANGE ARCHIVELOG ALL VALIDATE;
RMAN > DELETE EXPIRED ARCHIVELOG ALL;

Metalink Document https://metalink.oracle.com/metalink/plsql/f?p=200:27:9917209390401703684::::p27_id,p27_show_header,p27_show_help:621248.995,1,1 has an unpublished note on the subject

Cause
~~~~~~~

We register all the information about what we place in the flash recovery area in
the rman repository/controlfile. If we determine that there is not sufficient space
in the recovery file destination, as set by dest_size then we will fail.

Just deleting the old backups/archive logs from disk is not sufficient as it’s the rman repository/controlfile
that holds the space used information.

Fix
~~~~

There are a couple of possible options.

1) Increase the parameter db_recovery_file_dest_size
2) Stop using the db_recovery_file_dest by unsetting the parameter.
( This assumes you never really wanted to use this option )
3) Remove the Entries from the rman repository/Controlfile

The removal is desribed in the RMAN documentation but this is a quick and
dirty way if you don’t have an rman repository – but could endanger your ability
to recover – so be careful.

a) delete unwanted archive log files from disk ( rm /del )
b) connect to rman
c) rman crosscheck archivelog all – marks the controlfile that the archives have been deleted
d) rman delete expired archivelog all – deletes the log entries identified above.

Posted in Oracle | Tagged: , , , , | 8 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 184 other followers