1

I'm using Oracle 11g database in archivelog mode. I'm getting the following error in my database logs for the last two days.

*** 2017-05-19 22:01:04.164 4515 kcrr.c
ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 100.00% used, and has 2340864 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.

When I went through some forums, one solution that I found was to increase the db_recovery space. So I checked the space and got this result.

enter image description here

Then, I checked the current rman retention policy which is as follows:

RMAN> SHOW ALL;

RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOA
D TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\PRODADMIN\PRODUCT\11.2.0\DBHOM
E_1\DATABASE\SNCFPROD.ORA'; # default

Also, the fra space :

enter image description here

Note : I've scheduled to delete archive log files every two days via Windows Task scheduler. The content of the batch file for deleting the archivelogs is as follows:

connect target /;
crosscheck archivelog all;
delete archivelog all completed before 'SYSDATE-1';

As far as I understood, FRA has enough space, but still getting the same error. I've no idea how to resolve this. Urgent help is needed.

  • Do you delete your archivelogfiles in RMAN with a command like DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-1' BACKED UP 1 TIMES TO DEVICE TYPE DISK;? – Marco May 22 '17 at 6:37
  • How did you understand that you have enough space on FRA? According to the space_used column, you have almost used all the allocated space. You already have number of options to free up space from FRA, listed after the warning message. As you have configure recovery window of 4 days, Oracle keeps all the required archivelogs for that time period for point-in-time recovery. You may need to increase your db_recovery_file_dest_size. – JSapkota May 22 '17 at 6:55
  • Okay..then I will change the retention policy to configure recovery window of 4 days to 2 days. Will it be useful? – akr May 22 '17 at 8:03
  • Can you be more specific on how you delete your archivelogs from Windows task scheduler. Do you use RMAN DELETE ARCHIVELOG ... or do you use the Windows command DEL ... If you use Windows commands to delete the archivelogs Oracle does not 'know' they have been removed and you need to 'tell' Oracle using RMAN CROSSCHECK and DELETE EXPIRED. – user125152 May 22 '17 at 8:26
  • The content of the batch file for deleting the archivelogs is as follows: connect target /; crosscheck archivelog all; delete archivelog all completed before 'SYSDATE-1'; – akr May 22 '17 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.