10 Replies Latest reply: Mar 7, 2010 5:28 PM by EdStevens RSS

    ORA-19815: WARNING: db_recovery_file_dest_size of x bytes is 100% used

    cayenne
      Hi all,

      Ok, I think I found the problem with my single node rac instance, and it isn't the CRS.

      I found in the logs:

      ORA-19815: WARNING: db_recovery_file_dest_size of 51539607552 bytes is 100.00% used, and has 0 remaining bytes available.
      Mon Jul 13 12:29:28 2009
      ************************************************************************
      You have following choices to free up space from flash 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.
      ************************************************************************
      Mon Jul 13 12:29:28 2009
      Errors in file /u01/app/oracle/admin/ISPGRID/bdump/ispgrid1_arc0_8368.trc:
      ORA-19809: limit exceeded for recovery files
      ORA-19804: cannot reclaim 266991616 bytes disk space from 51539607552 limit
      ARC0: Error 19809 Creating archive log file to '+RECOVERYDG'
      ARCH: Archival stopped, error occurred. Will continue retrying


      I've tried firing up RMAN to do a backup recovery area;

      However, I can't seem to get the instance to start up long enough for me to connect to via RMAN, and try to fix anything.

      Any suggestions?

      cayenne
        • 1. Re: ORA-19815: WARNING: db_recovery_file_dest_size of x bytes is 100% used
          Mohammed Taj
          Hi,

          You have following choices to free up space from flash 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.


          This is the warning message saying you don't have free space in FRA (parameter) either Increase the area or following the above mention solution.


          Regards,
          Taj
          • 2. Re: ORA-19815: WARNING: db_recovery_file_dest_size of x bytes is 100% used
            sb92075
            19815, 00000, "WARNING: %s of %s bytes is %s%% used, and has %s remaining bytes available."
            // *Cause: DB_RECOVERY_FILE_DEST is running out of disk space.
            // *Action: One of the following:
            //          1. Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
            //          2. Backup files to tertiary device using RMAN.
            //          3. Consider changing RMAN retention policy.
            //          4. Consider changing RMAN archivelog deletion policy.
            //          5. Delete files from recovery area using RMAN.
            • 3. Re: ORA-19815: WARNING: db_recovery_file_dest_size of x bytes is 100% used
              cayenne
              Mohammed Taj wrote:
              Hi,

              You have following choices to free up space from flash 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.


              This is the warning message saying you don't have free space in FRA (parameter) either Increase the area or following the above mention solution.


              Regards,
              Taj
              Ok, I've set the ORACLE_SID, and connected to the instance and catalog.

              I tried doing startup, but the database failed..log showed error above.

              I then tried a startup mount;

              This worked.

              I then tried the backup recovery area command. It looks like it started to run, then just hangs??
              I checked the tape and it seems to be sitting there waiting...

              I got this:

              RMAN> backup device type sbt filesperset = 1024 tag 'FRA_07_13_09' recovery area;

              Starting backup at 13-JUL-09
              allocated channel: ORA_SBT_TAPE_1
              channel ORA_SBT_TAPE_1: sid=415 instance=ISPGRID1 devtype=SBT_TAPE
              channel ORA_SBT_TAPE_1: NMO v4.2.0.0
              specification does not match any datafile copy in the repository
              specification does not match any backupset in the repository
              channel ORA_SBT_TAPE_1: starting archive log backupset
              channel ORA_SBT_TAPE_1: specifying archive log(s) in backup set
              input archive log thread=1 sequence=24500 recid=42738 stamp=691066528
              input archive log thread=1 sequence=24501 recid=42739 stamp=691071930
              input archive log thread=1 sequence=24502 recid=42740 stamp=691077721
              input archive log thread=1 sequence=24503 recid=42741 stamp=691082786
              input archive log thread=1 sequence=24504 recid=42742 stamp=691088555
              input archive log thread=1 sequence=24505 recid=42743 stamp=691094022
              input archive log thread=1 sequence=24506 recid=42744 stamp=691099846
              input archive log thread=1 sequence=24507 recid=42745 stamp=691105431
              input archive log thread=1 sequence=24508 recid=42746 stamp=691109885
              input archive log thread=1 sequence=24509 recid=42747 stamp=691113818
              input archive log thread=1 sequence=24510 recid=42748 stamp=691118818
              input archive log thread=1 sequence=24511 recid=42749 stamp=691124276
              input archive log thread=1 sequence=24512 recid=42750 stamp=691129618
              input archive log thread=1 sequence=24513 recid=42751 stamp=691135345
              input archive log thread=1 sequence=24514 recid=42752 stamp=691140779
              input archive log thread=1 sequence=24515 recid=42753 stamp=691146432
              <snip>
              input archive log thread=1 sequence=24692 recid=42930 stamp=692023659
              input archive log thread=1 sequence=24693 recid=42931 stamp=692029058
              channel ORA_SBT_TAPE_1: starting piece 1 at 13-JUL-09

              And it just hangs there. Can this be done with the instance in mount but not opened?
              I can't open the thing tho....so, quite stuck on this one.

              Am I working in the right direction?

              Thanks you,

              cayenne
              • 4. Re: ORA-19815: WARNING: db_recovery_file_dest_size of x bytes is 100% used
                baskar.l
                Hi all,

                The OP's question has raised in me one more questions...

                1.Does RMAN delete obsolete works when the DB is in mount state?

                thanks,
                baskar.l
                • 5. Re: ORA-19815: WARNING: db_recovery_file_dest_size of x bytes is 100% used
                  Mohammed Taj
                  @OP,
                  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.

                  1. Did you try to connect with SYS AS SYSDBA USER? and execute above command with RMAN prompt?
                  2. Increase the db_recovery_file_dest_size (check the parameter name again)

                  1.Does RMAN delete obsolete works when the DB is in mount state?
                  Didn't check but database should be open.

                  Regards,
                  Taj
                  • 6. Re: ORA-19815: WARNING: db_recovery_file_dest_size of x bytes is 100% used
                    669771
                    Hi,

                    1.Does RMAN delete obsolete works when the DB is in mount state?

                    answer: yes

                    Regards,
                    Tom
                    • 7. Re: ORA-19815: WARNING: db_recovery_file_dest_size of x bytes is 100% used
                      cayenne
                      It turns out there were also problems with the tape backup hardware.
                      ONce that was ironed out, I was able to run BACKUP RECOVERY AREA , and that freed up space for me to start things up again.

                      Thanks to all for all the help, advice and input!

                      C
                      • 8. Re: ORA-19815: WARNING: db_recovery_file_dest_size of x bytes is 100% used
                        758410
                        my data base hangs because of this issue.RMAN backup destination (local drive) was full.could you help me to modify my RMAN scripts so my drive will not get filled by having many backups.like to delete the previous days backup before taking a new backup.

                        Regrds,

                        raju
                        • 9. Re: ORA-19815: WARNING: db_recovery_file_dest_size of x bytes is 100% used
                          sb92075
                          .like to delete the previous days backup before taking a new backup.
                          & when current backup aborts, then you have NO backup; which could be a SERIOUS problem if you then need to restore DB
                          • 10. Re: ORA-19815: WARNING: db_recovery_file_dest_size of x bytes is 100% used
                            EdStevens
                            user4487322 wrote:
                            my data base hangs because of this issue.RMAN backup destination (local drive) was full.could you help me to modify my RMAN scripts so my drive will not get filled by having many backups.like to delete the previous days backup before taking a new backup.

                            Regrds,

                            raju
                            Please do not hijack someone else's thread, even if it looks like it's the same issue.

                            If you need to reduce the number of backups you retain, it's not a matter of adjusting the scripts, it's a matter of adjusting the retention policy. Check the CONFIGURE RETENTION POLICY in the fine Backup and Recovery Reference found, along with all the other documentation, at tahiti.oracle.com