Google+ Followers

Pages

Thursday, October 20, 2011


ORA-19809: limit exceeded for recovery files – db_recovery_file_dest_size and archiver error
As we  working on development systems  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 we needed to restart the database and hope that the problem resolves itself.

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’

connect from Rman

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.

1 comment:

  1. Powerful Oracle Database Recovery Tool utility scans the damaged or inaccessible DBF files and provides the list of all the scanned objects in three-pane structure. It allows you select the desired component so you can see the preview of these object on right size in main interface.

    Download now: http://www.mannatsoftware.com/stellar-phoenix-oracle-database-recovery.html

    ReplyDelete