• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1038
  • Last Modified:

Oracle Flash_recovery_area -- ARCHIVELOG buildup ?

How can I remove the ARCHIVELOG buildup below ?

Timeline
 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
0
finance_teacher
Asked:
finance_teacher
  • 5
  • 2
  • 2
  • +1
8 Solutions
 
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.
0
 
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.
0
 
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 ?
--------------------------------------------------------------
E:\oracle\product\10.2.0\flash_recovery_area\....
  ** 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
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now