1 2 Previous Next 18 Replies Latest reply: Mar 7, 2013 2:23 PM by mseberg RSS

    Weird db_recovery_file_dest_size issue

    896971
      New DBA here, so my apologies if this is something obvious.

      *1)* I get this warning each day: db_recovery_file_dest_size of X is Y% used and has Z remaining bytes available.
      *2)* I check the metrics for it and it turns out that free space in db_recovery_file_dest has been steadily shrinking for the past 31 days. Right now, the amount of recovery file space remaining is approaching 100%, so I better act fast!
      *3)* The first and easiest solution is to increase the db_recovery_file_dest_size parameter (aka, give more hdd space to Oracle for logging purposes). I increase db_recovery_file_dest_size from 10G to 15G. That solves the immediate problem. Now my job is to remove some of the old logs.
      *4)* I run SELECT * FROM V$FLASHRECOVERY_AREA_USAGE;_ The results look like this:
      *FILE_TYPE-------------PERCENT_SPACE_USED-------------PERCENT_SPACE_RECLAIMABLE-------------NUMBER_OF_FILES*
      control file                 0                                              0                                                         0
      redo log                    0                                              0                                                         0
      archived log               .77                                            .01                                                      6
      backup piece              73.93                                        0                                                         8
      image copy                0                                              0                                                         0
      flashblack log             0                                              0                                                         0
      foreign archived log     0                                              0                                                         0
      This is confusing. There should be SOME space that can be reclaimed! Right?

      *5)* I go to RMAN and CONNECT TARGET sys@myDatabase; I change RETENTION POLICY to only save for 5 days (it was set to save for 7 days). I run CROSSCHECK BACKUP; It finds 8 available objects. OK great. I run CROSSCHECK ARCHIVELOG ALL; It finds 6 objects. I run BACKUP ARCHIVELOG ALL DELETE ALL INPUT;

      My recovery area file size shrinks again (aka, more hdd is being taken up).

      *6)* I run CROSSCHECKs again. I run LIST EXPIRED ARCHIVELOG ALL; RMAN says "specification does not match any archived log in the repository." I run LIST EXPIRED BACKUP; RMAN says "specification does not match any archived log in the repository."

      There must be something I'm not understanding here. I'm not able to free up any hdd space at all because, according to Oracle, there's nothing that can be deleted. Am I expected to go to Linux command line and remove all the files manually?

      Thanks so much for your time!

      Edited by: 893968 on Mar 1, 2013 7:03 AM
        • 1. Re: Weird db_recovery_file_dest_size issue
          Shivananda Rao
          Hello,

          One of the approaches in maintaining the Flash Recovery Area (db_recovery_file_dest) would be to delete the archives as and when they are backed up.
          RMAN>backup archivelog all not backedup up 1 times delete input; 
          If you are doing this, then did you check if there are any backups that are out of retention window / retention policy (obsolete)? Try to do this
          RMAN>report obsolete;
          RMAN>delete force noprompt obsolete;
          893968      
          Newbie
               
          Handle:      893968
          Status Level:      Newbie
          Registered:      Oct 31, 2011
          Total Posts:      17
          Total Questions:      10 (7 unresolved)
          Please consider closing your threads by providing appropriate points if you feel that they have been answered. Please keep the forum clean !

          Regards,
          Shivananda
          • 2. Re: Weird db_recovery_file_dest_size issue
            EdStevens
            Several concepts you need to get clear on ...

            First - regarding oracle's tracking of FRA usage. oracle's knowledge of the usage of the FRA is based on what oracle has put there and removed from there. The space is not reserved at the OS level, so there is nothing to prevent the disk from being consumed by things going on outside of the database. Conversly, if you delete say, a backupset or a bunch of archivelogs by using OS commands, Oracle will not know about it and will assume that those files are still there and still consuming space.

            Second - regarding 'expired' files. When a backupset or an archivlog file is created, a record of that file is recorded in the control file. If you use an os command to delete one of those files (let's say an archivelog), the record of that file still exists in the control file, and oracle still believes the file is there and consuming space. The only thing a 'crosscheck' command does is compare the records in the control file with reality. If a file is found to have gone missing, the records of that file is flagged as 'expired'. You then need to follow up with the rman commnd 'delete expired' to remove the record from the repository and 'free up' the space.

            Third, regarding obsolete backups. All recovery must begin with a full or incremental level 0 backup. Specifically the most recent one taken prior to the point in time to which you want to recover. To take an extreme example, let's suppose you take an incremental level 0 backup on 1 January, and incremental level backups once a day every day beginning on 2 Jan. But you never take another Incremental 0 backup. Even if your recovery window is only 1 day, three months later that lone incremental backup (and every incremental 1 backup since) is still needed to enforce the two-day recovery window. therefore, in spite of specifying a recovery window of 2 days, that 3-month old backup is still not considered obsolete.
            • 3. Re: Weird db_recovery_file_dest_size issue
              896971
              Thanks for the reply Shivananda!
              Shivananda Rao wrote:
              Hello,

              One of the approaches in maintaining the Flash Recovery Area (db_recovery_file_dest) would be to delete the archives as and when they are backed up.
              RMAN>backup archivelog all not backedup up 1 times delete input; 
              I just ran your code above. RMAN chugged along and finished backup normally.

              If you are doing this, then did you check if there are any backups that are out of retention window / retention policy (obsolete)? Try to do this
              RMAN>report obsolete;
              RMAN>delete force noprompt obsolete;
              I did both the commands above. In each case it said "no obsolete backups found." The mystery deepens!

              Please consider closing your threads by providing appropriate points if you feel that they have been answered. Please keep the forum clean !

              Regards,
              Shivananda
              I did so.
              • 4. Re: Weird db_recovery_file_dest_size issue
                896971
                EdStevens wrote:
                Several concepts you need to get clear on ...

                First - regarding oracle's tracking of FRA usage. oracle's knowledge of the usage of the FRA is based on what oracle has put there and removed from there. The space is not reserved at the OS level, so there is nothing to prevent the disk from being consumed by things going on outside of the database. Conversly, if you delete say, a backupset or a bunch of archivelogs by using OS commands, Oracle will not know about it and will assume that those files are still there and still consuming space.

                Second - regarding 'expired' files. When a backupset or an archivlog file is created, a record of that file is recorded in the control file. If you use an os command to delete one of those files (let's say an archivelog), the record of that file still exists in the control file, and oracle still believes the file is there and consuming space. The only thing a 'crosscheck' command does is compare the records in the control file with reality. If a file is found to have gone missing, the records of that file is flagged as 'expired'. You then need to follow up with the rman commnd 'delete expired' to remove the record from the repository and 'free up' the space.

                Third, regarding obsolete backups. All recovery must begin with a full or incremental level 0 backup. Specifically the most recent one taken prior to the point in time to which you want to recover. To take an extreme example, let's suppose you take an incremental level 0 backup on 1 January, and incremental level backups once a day every day beginning on 2 Jan. But you never take another Incremental 0 backup. Even if your recovery window is only 1 day, three months later that lone incremental backup (and every incremental 1 backup since) is still needed to enforce the two-day recovery window. therefore, in spite of specifying a recovery window of 2 days, that 3-month old backup is still not considered obsolete.
                The first 2 points I knew, the last I did not, however (or I knew it once, but forgot it). How would I discern if this is what is occurring in my database? Would it be some usage of the LIST command?

                Thanks
                • 5. Re: Weird db_recovery_file_dest_size issue
                  mseberg
                  Hello;

                  You can have RMAN backup in FRA that are not obsolete. What command have you run to make them obsolete?

                  Also what is the oldest folder date under backupset in FRA?

                  Best Regards

                  mseberg
                  • 6. Re: Weird db_recovery_file_dest_size issue
                    Shivananda Rao
                    Ok. Then may be as Ed said, though you brought down the recovery window from 7 days to 5 days, you might be having only one level 0 backup where oracle requires this backup for recovery purpose and thus not considering it to be obsolete.

                    Regards,
                    Shivananda
                    • 7. Re: Weird db_recovery_file_dest_size issue
                      896971
                      Shivananda Rao wrote:
                      Ok. Then may be as Ed said, though you brought down the recovery window from 7 days to 5 days, you might be having only one level 0 backup where oracle requires this backup for recovery purpose and thus not considering it to be obsolete.

                      Regards,
                      Shivananda
                      How would I know if this is the case?
                      • 8. Re: Weird db_recovery_file_dest_size issue
                        mseberg
                        Please consider reviewing this :

                        Understanding Obsolescence of RMAN Backups

                        http://hemantoracledba.blogspot.com/2011/09/understanding-obsolescence-of-rman.html


                        Best Regards

                        mseberg
                        • 9. Re: Weird db_recovery_file_dest_size issue
                          EdStevens
                          893968 wrote:
                          EdStevens wrote:
                          Several concepts you need to get clear on ...

                          First - regarding oracle's tracking of FRA usage. oracle's knowledge of the usage of the FRA is based on what oracle has put there and removed from there. The space is not reserved at the OS level, so there is nothing to prevent the disk from being consumed by things going on outside of the database. Conversly, if you delete say, a backupset or a bunch of archivelogs by using OS commands, Oracle will not know about it and will assume that those files are still there and still consuming space.

                          Second - regarding 'expired' files. When a backupset or an archivlog file is created, a record of that file is recorded in the control file. If you use an os command to delete one of those files (let's say an archivelog), the record of that file still exists in the control file, and oracle still believes the file is there and consuming space. The only thing a 'crosscheck' command does is compare the records in the control file with reality. If a file is found to have gone missing, the records of that file is flagged as 'expired'. You then need to follow up with the rman commnd 'delete expired' to remove the record from the repository and 'free up' the space.

                          Third, regarding obsolete backups. All recovery must begin with a full or incremental level 0 backup. Specifically the most recent one taken prior to the point in time to which you want to recover. To take an extreme example, let's suppose you take an incremental level 0 backup on 1 January, and incremental level backups once a day every day beginning on 2 Jan. But you never take another Incremental 0 backup. Even if your recovery window is only 1 day, three months later that lone incremental backup (and every incremental 1 backup since) is still needed to enforce the two-day recovery window. therefore, in spite of specifying a recovery window of 2 days, that 3-month old backup is still not considered obsolete.
                          The first 2 points I knew, the last I did not, however (or I knew it once, but forgot it). How would I discern if this is what is occurring in my database? Would it be some usage of the LIST command?

                          Thanks
                          Do a 'list backup'. Then review the output from the bottom up, looking for the most recent 'full' or 'Incr 0' backup of the datafiles. Careful that it is the backup of the datafiles you are considering. You may have 'full' backups of control files as well. When you find the most recent 'full' or 'incr 0' backup of data files, check the 'completion time'. Compare that date to what is implied by your retention rules.
                          • 10. Re: Weird db_recovery_file_dest_size issue
                            896971
                            EdStevens wrote:
                            Do a 'list backup'. Then review the output from the bottom up, looking for the most recent 'full' or 'Incr 0' backup of the datafiles. Careful that it is the backup of the datafiles you are considering. You may have 'full' backups of control files as well. When you find the most recent 'full' or 'incr 0' backup of data files, check the 'completion time'. Compare that date to what is implied by your retention rules.
                            This is the world according to LIST BACKUP. There are 9 backup sets, and it's obvious that the archived logs are the real heavy lift here. I've run DELETE expired/obsolete, but this doesn't work because they're "available." Is the best practice to set them as expired and then delete them from within RMAN? I'm new, but I'm learning! :)


                            BS1, 2.37G in size, 61 "Archived Logs" within. Next Times run from Dec 14, 2012 to Jan 2, 2013. Status is AVAILABLE.
                            BS2, 2.37G in size, 61 "Archived Logs" within. Next Times run from Jan 2, 2013 to Jan 20, 2013. Status is AVAILABLE.
                            BS3, 2.38G in size, 61 "Archived Logs" within. Next Times run from Jan 20, 2013 to Feb 10, 2013. Status is AVAILABLE.
                            BS4, 2.30G in size, 61 "Archived Logs" within. Next Times run from Feb 10, 2013 to Feb 28, 2013. Status is AVAILABLE.
                            BS5, 1.91M in size, 2 "Archived Logs" within. Next Times run from Feb 28, 2013 to Feb 28, 2013. Status is AVAILABLE

                            BS6, 1.40G in size, Type: FULL. It lists 8 datafiles, all with Ckp Times of Feb 28, 2013. Status is AVAILABLE.

                            BS7, 10.11M in size, Type: FULL. This is a control file backup. Ckp Time of Feb 28, 2013. Status is AVAILABLE.

                            BS8, 44.00K in size, 1 "Archived Logs" within. Next Time from Feb 28, 2013. Status is AVAILABLE.
                            BS9, 120.14M in size, 1 "Archived Logs" within. Next Times run from Feb 28, 2013 to present. Status is AVAILABLE.
                            • 11. Re: Weird db_recovery_file_dest_size issue
                              896971
                              As an addendum, I'm assuming BS8 was created by the BACKUP ARCHIVELOG ALL DELETE ALL INPUT; I ran yesterday (in an attempt to free up space).

                              My next step would be to go to the Linux prompt and manually delete BS1, BS2, and BS3, as they should not be needed for the last full backup. Does this seem reasonable, or is there a better method?

                              Thanks for everyone's help!
                              • 12. Re: Weird db_recovery_file_dest_size issue
                                Shivananda Rao
                                I wouldn't recommend to delete the backups as these backup pieces fall under the recovery window. What you could do is move the backups to a temporary location (and zip them if you are very much concerned about the disk space).

                                From the RMAN prompt, clear the FRA space by :
                                RMAN>crosscheck backup;
                                RMAN>delete expired backup;
                                Finally take a level 0 backup and if possible take a compressed backup so that you do run out of the FRA space in future.


                                Regards,
                                Shivananda
                                • 13. Re: Weird db_recovery_file_dest_size issue
                                  896971
                                  Shivananda Rao wrote:
                                  I wouldn't recommend to delete the backups as these backup pieces fall under the recovery window.
                                  Could you expand on this please? I thought that because BS1, BS2, and BS3 were before my last full backup (which occurred on Feb 28), that they weren't necessary anymore for restoring purposes.

                                  Thanks
                                  • 14. Re: Weird db_recovery_file_dest_size issue
                                    CKPT
                                    Could you expand on this please? I thought that because BS1, BS2, and BS3 were before my last full backup (which occurred on Feb 28), that they weren't necessary anymore for restoring purposes.
                                    As i see your previous post and it sense BS1, BS2 & BS3 belongs to the old backups.
                                    BS1, 2.37G in size, 61 "Archived Logs" within. Next Times run from Dec 14, 2012 to Jan 2, 2013. Status is AVAILABLE.
                                    BS2, 2.37G in size, 61 "Archived Logs" within. Next Times run from Jan 2, 2013 to Jan 20, 2013. Status is AVAILABLE.
                                    BS3, 2.38G in size, 61 "Archived Logs" within. Next Times run from Jan 20, 2013 to Feb 10, 2013. Status is AVAILABLE.
                                    However you just have to ensure, there is full backup recently. If you confused from RMAN prompt. You can use below SQL script to check when you took last INC0 backups.
                                    But why still the status is "AVIALBLE" for the old backups? what is the retention policy/window you set?
                                    alter session set nls_date_format = 'yyyy-MON-dd hh24:mi:ss';
                                    select backup_type,
                                    incremental_level,
                                    round(sum(original_input_bytes)/1024/1024,2) "MB in",
                                    round(sum(output_bytes)/1024/1024,2) "MB out",
                                    status,
                                    min(start_time),
                                    max(completion_time),
                                    round((sum(output_bytes)/1024/1024)/((max(completion_time)-min(start_time))*86400), 1) "MB/s"
                                    from v$backup_set_details
                                    group by backup_type, incremental_level, status, session_key, session_recid, session_stamp
                                    order by 6
                                    /
                                    1 2 Previous Next