Oracle DBA – A lifelong learning experience

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.

About these ads

8 Responses to “ORA-19809: limit exceeded for recovery files – db_recovery_file_dest_size and archiver error”

  1. phimic said

    Thank you for this nice post. Increasing the parameter db_recovery_file_dest_size solves my problem :)

  2. John Hallas said

    Thank you very much for taking the time to comment. It makes the effort worthwhile

    John

  3. Scott Danforth said

    I used DB_RECOVERY_FILE_DEST in the init file for a production db and manage the archive logs that are written there myself. I don’t use RMAN or Flash Recovery, but used this setting because I thought it would give me the option of enabling Flash Recovery in the future. Now, however, when restoring backups on a separate system, I ran into these db_recovery_file_dest_size warnings. V$flash_recovery_area_usage indicates I’m 44% full after having written 594 archive log files, and I’m quite worried about what would happen on my production system if I allowed the limit to be reached. I experimented on my backup system by running rman (%rman target /) and doing RMAN>CHANGE ARCHIVELOG ALL VALIDATE; and RMAN > DELETE EXPIRED ARCHIVELOG ALL; and this “fixed” my V$flash_recovery_area_usage. Thanks so much for your note explaining this!!!

    But, is this how I should proceed on my production system — by periodically running RMAN to fix things? This just doesn’t feel right. What is a non-RMAN archivelog database supposed to do???

    • John Hallas said

      Scott, thanks for the comment and I am glad my note was useful.
      Firstly the setting of db_recovery_file_dest_size is not dependant upon space available. If you have 5Gb in your backup filesystem then you can happily set the size to be 10gb as Oracle does not check. What the parameter is used for is when you have several databases all sharing the same db_recover_area then the setting is used to stop one database taking all the space from the others (which is what your error message was caused by)
      I assume that you normally manually delete the archivelogs via a script. When you ran the RMAN delete expired command it will have used the default retention period which I think is 7 days (need to check that) and tided up based on that.

      bear in mind that if you do a recovery then RMAN expects to backup the files again before deleting them even though they are already on tape.

      Cannot see why you do not use RMAN though

      John

  4. Bob Siegel said

    John,

    Excellent!!! I was having the same problem on a development Windows box after have the C: drive AND flash recovery area was filled. After I cleaned up old archive log files I could not get the DB to properly report the % free in the Flash Recovery Area.

    I wasn’t aware of the RMAN Change command. I always used the Crosscheck but that did not always give me the results I desired and properly clean things up if someone deleted backups or archivelogs at the OS level. RMAN always seems to be a bit twitchy to me. If you don’t so it exactly the way it expects it gets confused.

    Thanks,
    Bob

  5. [...] Posts ORA-19809: limit exceeded for recovery files – db_recovery_file_dest_size and archiver errorRMAN backup script – example – logging outputWhere has consistent=y gone when using Datapump [...]

  6. Pierre said

    Almost four years that you have posted this information and it is still useful. Thanks

  7. Ahmed said

    Almost 5 years now? and still information was helpful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 201 other followers

%d bloggers like this: