Problem solve Get help with specific problems with your technologies, process and projects.

Shortage of disk for archive log

We're planning to upgrade to 8.1.7 from 8.03. At present we do not have archive turned on, due to archive files overflowing disk capacity (our participating private agency sites do not have administrators). Is there a way in 8.1.7 to manage the number of archive files saved when archive is turned on? Seems to me that this must be a common problem. Is there a shareware program that could be scheduled to deleted older archive files, say by creation date or other criteria?
You have a number of options to address a shortage of disk for your archive log destination. First, you can delete any archived redo logs that are older than your most recent backup. If you are running Unix or Linux, then the following command can be scheduled in cron to run on a periodic basis to remove your oldest files:

find /archive_log_dest_dir -type f -mtime +31 -exec rm -f {} ;

The above command will remove all files in the appropriate directory that are older than 31 days old. If you are not running Unix or Linux, then as long as you have Perl installed on your system, then you still have a solution. I wanted to perform an operation similar to the above on a Windows platform without the appropriate commands available to me. So I created a Perl script which does the same. You can find a copy of this Perl script on my Web site (http://www.peasland.net/delete_old.zip). Even in Windows, you can schedule this script to run on a periodic basis. The same script can, of course, be used on Unix platforms as well.

Many DBAs like to copy their archived redo logs to tape along with the backups. This is a fine idea, and one that I employ whenever possible. If I take a weekly backup and I do not have enough disk space to hold a week's worth of archived redo logs, then I make sure to copy the archived redo logs to tape more often, i.e. daily. In many cases, DBAs will set up jobs to copy archived redo logs to tape on an hourly basis. That way, if the server crashes and you lose the disk with your archived redo logs, you lose at most one hour's worth of transactions. The bottom line here is to copy your archived redo logs to a backup location on a frequent enough basis so that if the server and its disks are completely lost, you will not lose your company's valuable data. After your archived redo logs are copied to the backup location, you can remove them from disk.

Oracle's RMAN will help with this problem as well. You can have RMAN backup and delete archived redo logs all in one operation. This is the method that I use. A command similar to the following will backup archived redo logs and delete them in the same step:

backup format "gasp_archlogs_%U"
(archivelog from time 'SYSDATE-7' delete input);

In the above, "gasp" is my ORACLE_SID. I specify to backup and delete (DELETE INPUT) all archived redo logs created within the last seven days. Since I run this script many times a day, I will always have less than one day's worth of archived redo logs on disk. Once RMAN has backed up and deleted these archived redo logs, it does not attempt to back them up again since they don't exist on disk any more.

Hopefully I've given you a few ideas on how you can better manage your archive log destination device. You obviously don't want this device to become 100% full. Since the disk device has a finite size, it may take some additional tasks in your backup strategy to ensure that 100% of the total capacity is never reached. Additionally, I do not allow any other disk files on my archive log destination device. This way, I do not have the chance of any other process accidentally filling up this disk device. As always, with any backup strategy, you should test your various recovery scenarios.

Dig Deeper on Oracle database backup and recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.