Oracle Flash_recovery_area -- ARCHIVELOG buildup ?

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
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.
finance_teacherAuthor Commented:
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 ?
       ** 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
       ** one small 10MB O1.....BKP files per day
       ** one 10MB to 20GB O1.....BKP files per day
       ** no files
  ** DATAFILE = 65GB
       ** different sized O1.....DBF files
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
>>"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.
slightwv (䄆 Netminder) Commented:
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'.
finance_teacherAuthor Commented:
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 ?
Steve WalesSenior Database AdministratorCommented:
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.
slightwv (䄆 Netminder) Commented:
>>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.
Steve WalesSenior Database AdministratorCommented:
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.
slightwv (䄆 Netminder) Commented:
>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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.