Oracle Flash_recovery_area -- ARCHIVELOG buildup ?

Posted on 2014-08-04
Last Modified: 2014-08-21
How can I remove the ARCHIVELOG buildup below ?

 1. ARCHIVELOG always cleaned itself nightly
 2. 2014_07_31 has 624 50MB files
 3. 2014_08_01 has 97 50MB files
 4. 2014_08_02 has 14 50MB files
 5. 2014_08_03 has 26 50MB files
Question by:finance_teacher
    LVL 76

    Accepted Solution

    It is likely the backups cleaning them out.    If they aren't being cleaned up, something is wrong.

    Verify backups have been running.
    Check the backup software log files.
    Check the alert log.

    Something isn't running like it used to be running.
    LVL 34

    Assisted Solution

    by:Mark Geerlings
    Do you use the Oracle Recovery Manager (RMAN) for your backups?  If yes, that is usually configured to remove the archived redo logs after they have been backed up once or twice.  If you use a different backup product, that may also be configured to remove these files after they are backed up.

    Author Comment

    Based on select name,floor(space_limit / 1024 / 1024) "Size MB",
    ceil(space_used / 1024 / 1024) "Used MB"from v$recovery_file_dest
    Oracle THINKS the "USED" amount is 129GB.
    How can I get Oracle to THINK the correct "USED"
    amount since I moved 37GB+ to E:\a_old_ArchiveLogs
    a few days ago and "E:\oracle\product\10.2.0\flash_recovery_area"
    really only has 90GB "USED" since then ?

    Maybe something like an
    "Alter Database open resetlogs;"
    command ?
      ** ARCHIVELOG = 5GB
           ** moved old 37GB to E:\a_old_ArchiveLogs
              a few days ago to see if that would fix the issue
           ** averages about 20 500MB O1.....ARC files per day
      ** AUTOBACKUP = 65MB
           ** one small 10MB O1.....BKP files per day
      ** BACKUPSET = 23GB
           ** one 10MB to 20GB O1.....BKP files per day
      ** CONTROLFILE = 0GB
           ** no files
      ** DATAFILE = 65GB
           ** different sized O1.....DBF files
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    >>"Alter Database open resetlogs;"

    NO!!!!!!  Do not do this!!!  You will mess up your system.

    We need information about your backups etc...

    If you are using RMAN, you need to tell RMAN that you moved the files.

    This is typically done with the CROSSCHECK command.

    The online docs have the information on this.
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    Moving files manually is a work-around.

    You need to dig into the main issue of why the 'system' stopped removing them automatically.

    This typically means that something somewhere is 'broken'.

    Author Comment

    Just trying to find the command for right now so I can prevent a false OUT_OF_SPACE error, will then fix the ROOT cause.

    Maybe do a "crosscheck archivelog all" (or something else) since my backups are via rman ?
    LVL 22

    Assisted Solution

    by:Steve Wales
    To clean up after the manual work around:

    Invoke RMAN.
    Connect to your target database
    crosscheck archive log all;
    delete expired archivelog all;  (or delete noprompt expired archivelog all)

    After a crosscheck, items found in the backup catalog that aren't on disk are marked as EXPIRED.

    In the normal processing of your backups, backups and archivelog backups that are outside of the recovery window you set in your backup configuration are marked as OBSOLETE.

    You should be managing your old backups as a part of your backup process.  For example, if you want to keep you last 7 days of backups on disk, you con configure rman to hold backup for 7 days, and on the 8th day the oldest backupset is marked as obsolete.

    At the end of your backup script you can DELETE NOPROMPT OBSOLETE and your backup files older than the retention period will be automatically removed.
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    >>delete expired archivelog all;  (or delete noprompt expired archivelog all)

    Before just running that command, you need to pay attention to your backup policies you have in place.

    Just because an archived redo log is 'expired' doesn't mean you "should" delete it.
    LVL 22

    Assisted Solution

    by:Steve Wales
    If the archived redo log backup is marked as expired - it has already been deleted from disk.  Expired means that the RMAN catalog has a pointer to a backup file that is no longer available on disk (or possibly tape, but I do my backups to disk and have a secondary archive to offsite, so I am not 100% certain on that one).

    All that is doing is removing data from the RMAN catalog because RMAN still thinks it has access to an archived redo log that is still on disk (and thus making Oracle think that the db_recovery_file_dest_size limit has been reached).

    I think that the real one that you need to make sure is configured correctly is DELETE OBSOLETE.

    You need to make sure that your recovery window configuration always maintains enough backup files on disk so that in the event of failure you can recover.  If you are taking a single full (or incremental 0) backup weekly, and filling the gaps with incremental level 1's and archive logs, but only have a recovery window of 5 days set, then you could be in very deep doo doo in the event of a crisis.
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    >Expired means that the RMAN catalog has a pointer to a backup file that is no longer available on disk

    Cool and thanks for clarifying.  I guess I was confusing it with obsolete.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now