Archive Logs filling up Flash Recovery Area (FRA) space

Question

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?

Answer 1

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.

Answer 2

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!