This discussion is archived
10 Replies Latest reply: Feb 15, 2013 7:15 AM by user255401 RSS

Automatic Deletion of Files in FRA

user255401 Newbie
Currently Being Moderated
Ok, I am about fired up right now. I have read over and over how Oracle will reclaim space in the FRA if it is running low yet I can't reproduce this behavior to save my life.

- I have archive logging enabled.
- I am using the FRA.
- I have a recovery window of 1 day set.
- I have full incremental (level 0) backups for the past three days.
- I am also backing up archivelogs and have "all not backed up" specified as well.

"report obsolete" does indeed report the backups that fall outside of my recovery window correctly. When I run another backup I receive the following:

ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 9163505664 bytes disk space from 16106127360 limit

All I have at this moment is a strong desire to repeat the word "derp" over and over. Can someone explain what I am missing (or perhaps misconstruing) or point me at some docs that clearly spell this out for me?

Or is it simply that what is being stated implies that I run the following to cause Oracle to "automatically delete files" to create space for new FRA additions:

allocate channel for maintenance type disk;
delete noprompt obsolete device type disk;
release channel;

Cheers!

Edited by: 909736 on Feb 14, 2013 11:10 AM
  • 1. Re: Automatic Deletion of Files in FRA
    mseberg Guru
    Currently Being Moderated
    I like the fact you understand when you are fired up. That type of self feedback is priceless.

    Is this not working for any type of file?

    For example if a flashback log is old enough that it is no longer needed to satisfy the flashback retention target then it should get reused.

    Is this part working? If yes is it only some types of files this is not working for?

    Also can you supply your full Oracle version?

    Thanks for being human!

    Nice overview

    http://gjilevski.com/2008/10/04/file-deletion-policy-from-flash-recovery-area/

    Iordan also makes a good point. Oracle guess at what it needs sometimes is quite a ways off.

    Best Regards

    mseberg

    Edited by: mseberg on Feb 14, 2013 2:04 PM
  • 2. Re: Automatic Deletion of Files in FRA
    Iordan Iotzov Expert
    Currently Being Moderated
    It might be that it thinks that there would not be enough disk space even after obsolete backups are deleted. It says it needs 9G out of the 16G limit.

    Iordan Iotzov
    http://iiotzov.wordpress.com/
  • 3. Re: Automatic Deletion of Files in FRA
    user255401 Newbie
    Currently Being Moderated
    I am not working with flashback (yet).

    Oracle Database 11g Enterprise Edition
    11.2.0.1.0
    64bit Production
  • 4. Re: Automatic Deletion of Files in FRA
    user255401 Newbie
    Currently Being Moderated
    I think Iordan is on to something. Oh how embarrassing this will be if he is right. And what a waste of my own time. Ha!

    I am trying to dig up the sizes of each backupset to verify I missed something so simple. I am new to Oracle so this will take me a bit.

    Thank you very much for you input!
  • 5. Re: Automatic Deletion of Files in FRA
    user255401 Newbie
    Currently Being Moderated
    Alrighty then... I think this paints the appropriate picture:
    select file_type, space_used*percent_space_used/100/1024/1024 used, space_reclaimable*percent_space_reclaimable/10 /1024/1024 reclaimable, frau.number_of_files from v$recovery_file_dest rfd, v$flash_recovery_area_usage frau;
    
    FILE_TYPE                  USED RECLAIMABLE NUMBER_OF_FILES
    -------------------- ---------- ----------- ---------------
    CONTROL FILE                  0           0               0
    REDO LOG                      0           0               0
    ARCHIVED LOG          8277.5077           0              36
    BACKUP PIECE           3864.931    2938.875              13
    IMAGE COPY                    0           0               0
    FLASHBACK LOG                 0           0               0
    FOREIGN ARCHIVED LOG          0           0               0
    
    7 rows selected.
    So it appears that I cannot create new backups due to the Archived Logs being ineligible for space reclamation and NOT the backupsets.

    Edit: I shall now work on recreating my test scenario (appropriately).
  • 6. Re: Automatic Deletion of Files in FRA
    mseberg Guru
    Currently Being Moderated
    Or

    column FILE_TYPE format a20
    set linesize 132

    select * from v$flash_recovery_area_usage;

    or
    SELECT 
      ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB, 
      ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB, 
      ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
      SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED
    FROM 
      V$RECOVERY_FILE_DEST A,
      V$FLASH_RECOVERY_AREA_USAGE B
    GROUP BY
      SPACE_LIMIT, 
      SPACE_USED , 
      SPACE_RECLAIMABLE ;
    Would still read this :

    http://gjilevski.com/2008/10/04/file-deletion-policy-from-flash-recovery-area/


    Best Regards

    mseberg

    Edited by: mseberg on Feb 14, 2013 3:00 PM
  • 7. Re: Automatic Deletion of Files in FRA
    user255401 Newbie
    Currently Being Moderated
    Absolutely, I am planning on it when I get some more free time to put into Oracle. Been set back training Unix admins since my last post.

    Thanks again!

    Edit: Slick query. Thanks!

    Edited by: 909736 on Feb 14, 2013 1:56 PM
  • 8. Re: Automatic Deletion of Files in FRA
    user255401 Newbie
    Currently Being Moderated
    Sweet! That was exactly the breakdown I needed. I already understood most of that but the nitty gritty I was looking for was in bullet number 4.
    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
    A question I have from this excellent read is what does the following statement mean?

    Archived redo logs are not eligible for deletion until all the consumers of the logs have satisfied their requirements.

    Does this imply that archive logs must be backed up or made obsolete by a newer backup before they have satisfied their potential consumers?
  • 9. Re: Automatic Deletion of Files in FRA
    mseberg Guru
    Currently Being Moderated
    Hello again;

    Archived redo logs are not eligible for deletion until all the consumers of the logs have satisfied their requirements.

    A great example of this would be Data Guard. The Standby database would be a "consumer" of logs, so if you try to delete them Oracle will bark to protect you from yourself. Oracle will keep the logs for Data Guard, RMAN etc. Things that need the logs.

    In Data Guard its common to have a setting "Applied on Standby".

    Best Regards

    mseberg
  • 10. Re: Automatic Deletion of Files in FRA
    user255401 Newbie
    Currently Being Moderated
    Gotcha. Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points