ORA-19815: WARNING: db_recovery_file_dest_size of xxxxxx bytes is 100.00% used

Check with alert Log
************************************************************************
ARC0: Error 19809 Creating archive log file to ‘/u01/app/oracle/product/11.2.0.4/fast_recovery_area/REDHAT/archivelog/2018_09_25/o1_mf_1_72_%u_.arc’
Errors in file /u01/app/oracle/product/11.2.0.4/diag/rdbms/redhat/REDHAT/trace/REDHAT_arc2_24458.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 99.97% used, and has 650752 remaining bytes available.
************************************************************************
You have following choices to free up space from 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.
************************************************************************
Cause:

Recovery Destination is full
Recovery Destination has reached the upper limit as defined by Size parameter.
Solution
Increase the Fast Recovery Area Size or Clean the destination using proper method like Using Rman, backup and delete the Archive logs.
 Then there is a scenario that as per Database Views/Metadata, Recovery Destination is full but when you check on the OS/storage level, there is plenty of space available.
SQL> select substr(name, 1, 30) name, space_limit AS quota,space_used as used,space_reclaimable as reclaimable,number_of_files as files from  v$recovery_file_dest ;
NAME          QUOTA    USED       RECLAIMABLE      FILES
—————————— ———-     ———–   —————   ——————      ———
/u01/app/oracle/product/11.2.0 4194304   3452050432  1767412224        27
 You can increase the Fast Recovery Area size as below 
SQL>alter system set db_recovery_file_dest_size=’4G’ scope=BOTH;
SQL> select substr(name, 1, 30) name, space_limit as quota,space_used as used,space_reclaimable as reclaimable,number_of_files as files from  v$recovery_file_dest ;
NAME          QUOTA       USED       RECLAIMABLE   FILES
—————————— ———-     ———–     —————    ——————   ———
/u01/app/oracle/product/11.2.0  4294967296   3452050432    1767412224     27
We can check Fast Recovery Area Size using Below parameter
SQL> show parameter db_recovery_file_dest_size;
NAME      TYPE   VALUE
————————————   ———–  —————-
db_recovery_file_dest_size  big integer    4G

(OR)

We can do that with RMAN using two commands, Crosscheck and Delete.


First, Run Crosscheck command for Archivelogs
crosscheck archivelog all;

Second, Delete the Expired Archive Logs
delete expired archivelog all;

Once, both of the above commands are completed, check the “v$flash_recovery_area_usage” to confirm that correct information is being reflected for used space for Archive logs.
  • October 2, 2018 | 19 views
  • Comments