Transient and permanent files
A file in the fast recovery area is either permanent or transient.
Files that are used by the
instance are
permanent. Those that are not used are
transient.
Size of the fast recovery area
The fast recovery area should be generously sized: the larger, the more useful it becomes (as is the case with everything, anyway).
The fast recovery area should have enough room to store a copy of all
data files of a
database as well as its incremental backups.
If available space is an issue, then at least the most important
tablespaces and
archived log files that are not yet saved on tape should be stored in the FRA.
Checking the space limit and the used space
The upper limit of the size of the fast recovery area and how much space is already used can be checked with the following query:
select
round(space_used /1024/1024/1024, 2) space_used_gb,
round(space_limit/1024/1024/1024, 2) space_limit_gb
from
v$recovery_file_dest;
Use v$recovery_area_usage to see how much space each file type (control file, redo log etc) uses
ORA-19809: limit exceeded for recovery files
This error message proposes 5 actions (solutions) to get rid of it:
- Take frequent backup of recovery area using RMAN.
- Consider changing RMAN retention policy.
- Consider changing RMAN archived log deletion policy.
- Add disk space and increase
db_recovery_file_dest_size.
- Delete files from recovery area using RMAN.
Deleting expired archived log files with RMAN:
$ rman target /
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
A more brute force way is to just delete all archived log files:
RMAN> delete archivelog all;
RMAN> crosscheck archivelog all;