8

I got hired to fill a position of DBA that I wasn't quite ready to fill, and while the training is getting me there, I am running close to a potential issue.

My oracle 11g database's Flash Recovery Area (FRA) is getting very close to being full(2.5% left). It is in ARCHIVELOG mode since we do live backups(at least that is the reason I was told). We have cleared up all unneeded backups through the enterprise manager, but the Archive logs are still taking up over 50% of the allocated FRA space. Is it possible to delete some of the older archive logs to free up space? The server holds logs from several months ago still, and we don't really need backups that far back.

If I can delete them, what would be the best way to do this?

3
  • @Jay We do a daily backup currently, but we would like to retain at least a weeks worth of logs. Oct 27, 2015 at 13:04
  • @Jay The Flashback Retention time is currently set to 24 Hours. Oct 27, 2015 at 13:08
  • "If you configure a fast recovery area, then the database automatically deletes unneeded files in this area automatically" Overview of RMAN Backup and Repository Maintenance
    – Jussi
    Oct 28, 2015 at 11:15

2 Answers 2

5

Oracle will automatically delete obsolete files. In general, you should not have to manually delete anything.

An important part of RMAN maintenance is deleting backups that are no longer needed. If you configure a fast recovery area, then the database automatically deletes unneeded files in this area automatically

From Overview of RMAN Backup and Repository Maintenance

First, determine if you are running out of space. For example using the following query (sample output included):

SELECT * FROM V$RECOVERY_FILE_DEST;

NAME            SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------  ----------- ---------- ----------------- ---------------
/mydisk/rcva     5368709120 109240320             256000              28

If you are running out of space you have to either decrease your retention policy or increase the size of your FRA.

3

I highly recommend writing a small RMAN script for this. Something like this should work:

run {
    allocate channel "dsk1" device type disk;
    allocate channel "dsk2" device type disk;
    crosscheck backup;
    crosscheck archivelog all;
    delete noprompt expired backup;
    delete noprompt expired archivelog all;
    report obsolete recovery window of 7 days;
    delete noprompt obsolete recovery window of 7 days;
    backup as compressed backupset archivelog all;
    delete noprompt archivelog until time 'sysdate-1' backed up 1 times to device type disk;
}

Explanation:

allocate channel <TAG> device type disk;

These lines are optional, but allows you to allocate several channels if you're connecting to storage through more than one physical channel

crosscheck backup;
crosscheck archivelog all;

These crosscheck lines will review the actual RMAN inventory (in the control file) against the contents on the storage. Those backups and/or archive logs that are no longer in storage will be marked as 'EXPIRED'

delete noprompt expired backup;
delete noprompt expired archivelog all;

These delete all inventory entries that have no match in the storage.

report obsolete recovery window of 7 days;

This line will mark any backup file that is no longer needed to restore the database to a consistent estate up to 7 days ago as 'OBSOLETE'

delete noprompt obsolete recovery window of 7 days;

This command delete all files marked as 'OBSOLETE'

backup as compressed backupset archivelog all;

This command backs up all current archives as a compressed backupset.

delete noprompt archivelog until time 'sysdate-1' backed up 1 times to device type disk;

This command deletes all archives older than 1 day that were backed up already.

Hope this helps you!

1
  • Most of that is automatically taken care by Oracle. It deletes obsolete files when more space is needed and FRA is full.
    – Jussi
    Oct 28, 2015 at 11:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.