1 2 Previous Next 19 Replies Latest reply: Jan 7, 2010 6:14 PM by 367117 RSS

    Oracle 11g flash recovery keeps growing

    sky123
      Oracle 11g flash recovery (E:\ partition in a Windows 2003 server) keeps growing. It already used 200GB of disk space and only 46GB of disk space is left in that partition. Can we tell how often Oracle 11g delete the old backups?

      Thanks.
      Andy
        • 1. Re: Oracle 11g flash recovery keeps growing
          591186
          Can we tell how often Oracle 11g delete the old backups?
          Oracle does not delete eligible files from flash recovery area.
          [http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/setup005.htm]
          [http://download.oracle.com/docs/cd/B13789_01/server.101/b10735/maint.htm#1008961]

          You can do delete obsolete,expired backup using RMAN. Before doing so, report and cross check.

          HTH
          -Anantha
          • 2. Re: Oracle 11g flash recovery keeps growing
            367117
            What is the Flash Recovery Area Size that you have configured (assigned) ?

            What is the output of following queries ?

            SELECT * FROM V$RECOVERY_FILE_DEST;

            SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
            • 3. Re: Oracle 11g flash recovery keeps growing
              sky123
              We use Oracle 11g database control to initially set the flash recover area size to about 100GB. However, the backup sets have already occupied almost 200GB of disk space in the E:\ partition. Now, only 42GB of disk space is available in that partition. I have seen backupset folders since 12/01/2009. Before we migrated to Oracle 11g, in Oracle 10g, we used to have an option to indicate how far backward the backupsets needed to be kept in the filesystem...and it ran without any issue. This feature has disappeared in Oracle 11g. Any suggestion?

              Thanks.
              Andy
              • 4. Re: Oracle 11g flash recovery keeps growing
                sky123
                We set it to 100000 MB under E:\flash_recovery_area_11.

                Thanks.
                Andy
                • 5. Re: Oracle 11g flash recovery keeps growing
                  367117
                  Can you post the output for

                  SELECT * FROM V$RECOVERY_FILE_DEST;

                  SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
                  • 6. Re: Oracle 11g flash recovery keeps growing
                    sky123
                    <pre>
                    SQL> set linesize 1000
                    SQL> select substr(name,1,30) name,
                    2 space_limit,
                    3 space_used,
                    4 space_reclaimable,
                    5 number_of_files
                    6 from V$RECOVERY_FILE_DEST
                    7 ;

                    NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
                    ------------------------------ ----------- ---------- ----------------- ---------------
                    E:\flash_recovery_area_11 1.0486E+11 1.6752E+10 0 5

                    SQL> select *
                    2 from V$FLASH_RECOVERY_AREA_USAGE
                    3 ;

                    FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
                    -------------------- ------------------ ------------------------- ---------------
                    CONTROL FILE 0 0 0
                    REDO LOG 0 0 0
                    ARCHIVED LOG .09 0 2
                    BACKUP PIECE 15.88 0 3
                    IMAGE COPY 0 0 0
                    FLASHBACK LOG 0 0 0
                    FOREIGN ARCHIVED LOG 0 0 0

                    7 rows selected.

                    SQL>
                    </pre>
                    • 7. Re: Oracle 11g flash recovery keeps growing
                      591186
                      Take a look these metalink notes;

                      Note 833663.1 Flash Recovery Area - FAQ
                      Note 829755.1 : Space issue in Flash Recovery Area( FRA )
                      Note 305812.1 Flash Recovery area - Space management Warning & Alerts
                      Note 315098.1 How is the space pressure managed in the Flash Recovery Area - Example.
                      • 8. Re: Oracle 11g flash recovery keeps growing
                        367117
                        andychow wrote:
                        SQL> set linesize 1000
                        SQL> select substr(name,1,30) name,
                        2         space_limit,
                        3         space_used,
                        4         space_reclaimable,
                        5         number_of_files
                        6  from V$RECOVERY_FILE_DEST
                        7  ;
                        
                        NAME                           SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
                        ------------------------------ ----------- ---------- ----------------- ---------------
                        E:\flash_recovery_area_11       1.0486E+11 1.6752E+10                 0               5
                        
                        SQL> select *
                        2  from V$FLASH_RECOVERY_AREA_USAGE
                        3  ;
                        
                        FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
                        -------------------- ------------------ ------------------------- ---------------
                        CONTROL FILE                          0                         0               0
                        REDO LOG                              0                         0               0
                        ARCHIVED LOG                        .09                         0               2
                        BACKUP PIECE                      15.88                         0               3
                        IMAGE COPY                            0                         0               0
                        FLASHBACK LOG                         0                         0               0
                        FOREIGN ARCHIVED LOG                  0                         0               0
                        
                        7 rows selected.
                        
                        SQL> 
                        It shows only 5 files in E:\flash_recovery_area_11. Can you verify ?
                        • 9. Re: Oracle 11g flash recovery keeps growing
                          591186
                          Could be a bug 4911954.

                          HTH
                          -Anantha
                          • 10. Re: Oracle 11g flash recovery keeps growing
                            sky123
                            No, there are a total of 19 files under E:\flash_recovery_area_11\VRDS\BACKUPSET. The old ones are not deleted by Oracle 11g.

                            Thanks.
                            Andy
                            • 11. Re: Oracle 11g flash recovery keeps growing
                              367117
                              What is the DB version ?

                              and you don't have any Guaranteed restore point.
                              • 12. Re: Oracle 11g flash recovery keeps growing
                                367117
                                andychow wrote:
                                The old ones are not deleted by Oracle 11g.
                                Were they manually copied/moved or generated by 11g ?
                                • 13. Re: Oracle 11g flash recovery keeps growing
                                  sky123
                                  The backupsets were generated by Oracle 11g - not copied. We cleanly installed the 11g software in the server and imported the tables, one by one, into 11g.

                                  Thanks.
                                  Andy
                                  • 14. Re: Oracle 11g flash recovery keeps growing
                                    367117
                                    Do you have any Guaranteed restore point ?
                                    1 2 Previous Next