PROBLEM:
Observed below error in the alert log
Wed Nov 29 03:27:44 2017
Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prd_m000_102938.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 1717986918400 bytes is 85.02% used, and has 257437990912 remaining bytes available.
Wed Nov 29 03:27:44 2017
************************************************************************
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.
************************************************************************
RFS[2]: Selected log 8 for thread 2 sequence 774 dbid 2584717665 branch 950748698
Wed Nov 29 03:27:49 2017
CAUSE AND SOLUTION:
This warning shows that the specified db_recovery_file_dest is almost full and it need to be cleared.
Check the recovery usage using the query:
set lines 299 select * from v$flash_recovery_area_usage FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 87.27 0 518 0 ---- >>>> THIS ONE BACKUP PIECE 0 0 0 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 0 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0
We can see, the archived log has consumed 87 percent of recovery area.
To fix this, we will clean up some old archive logs using RMAN.
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-3';
Now recovery area space has been freed.
SQL> set lines 299 SQL> select * from v$flash_recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 19.2 0 107 0 BACKUP PIECE 0 0 0 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 0 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0 8 rows selected.
Check the alert log:
Archived Log entry 517 added for thread 2 sequence 782 ID 0x9b6e49b6 dest 1: Wed Nov 29 07:47:29 2017 Archived Log entry 518 added for thread 1 sequence 4415 ID 0x9b6e49b6 dest 1: Wed Nov 29 08:28:38 2017 db_recovery_file_dest_size of 1638400 MB is 19.20% used. This is a ------------ >>>>> user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup.
It shows recovery dest is only 20 percent used.
NOTE – If we remove the archive logs manually using os command rm, then post that we need to run below commands from rman. Else this warning won’t be resolved.
rman target / crosscheck archivelog all; delete expired archivelog all;
