Re: Archive logs

> What do most folk do with the Archive Logs after a full backup? Delete
> most but the last few or what? I'd be very interested in what others
> have in way of a backup strategy as well? We're quite small for now
> with maybe 20 users tops of any Oracle database with way less than 1 gig
> of info so far.
>

The Archive / Backup cycle is vital to the integrity of your database. As your database grows this becomes increasingly difficult to manage as your archiving directly tends to fill up rather rapidly. Once full ORACLE will STOP until free space is created in the Archive directory. This was the problem we faced. To give a better picture first let me describe our system.

We run Oracle 7.2.3 on HPUX 10.10. The boxes are a Database Server K420 and an Application Server K400. There is also a D250 (function of which is explained later). The database size is currently 70 gigabytes on an EMC disk array. The application we run is BAAN/TRITON 3.1 with 250 concurrent users.

Right, now the archiving strategy :

With number of transaction we run country wide. the archive files are generated at quite a rapid rate. Obviously no matter how large you make the archive directory it is going to fill up in a given time. We actually use 2 directories /ARCHIVES and /EXPORTS to store these archives. The archives are also compressed by Unix compress command. Because compress puts a considerable overhead on the Database Server we use a remote machine (the D250 as mentioned earlier) to do the compression. The D250 has a crontab script which runs every 15 minutes. The sizes of the directories are /ARCHIVE = 3 gigabytes and /EXPORTS = 2.2 gigabytes.

The cycle goes likes this .......

1. D250 crontab kicks in. D250 does a remote copy of last archive file not compressed (BUT NOT THE CURRENT ARCHIVE) from the /ARCHIVES directory on the Database server.
2. File is then compressed on D250 and remote copied back to /ARCHIVES directory on the Database Server. The uncompressed version of the archive is then DELETED by the D250 from /ARCHIVES.
3. D250 then finds OLDEST file that has been compressed in the /ARCHIVES direcory and MOVES it to the /EXPORTS directory. (Thus effectivelly deleteing it from /ARCHIVES)
4. D250 then finds OLDEST compressed file in the /EXPORTS directory and DELETES it.

This method allows us to store approx 5 days worth of archives. The initial setup of the directories requires that the crontab on the D250 is not activated until you have enough files in /ARCHIVES (+- 60% full) and then it can be activated (MINUS the delete of file in /EXPORTS statement) until /EXPORTS becomes +- 85% full. Once the directories have the required amount of files the crontab can be activated in its intended state. (The reason for the 2 directories is a directory size limitation in Unix. If the system could create larger directories we would have used only 1).

The backup strategy used is a scheduled nightly online backup using OmniBack II.The Oracle database is backed up to 2 DLT tapes simultaneously and takes +- 6 hours. The /ARCHIVE, /EXPORTS and other system files are backed up to a DAT and takes +- 4 hours.

I realise you database is nowhere near this size yet but a similar principle can be applied just using 1 directory and ensuring that the oldest archives are periodically removed (with a method to ensure that they have been backed up first). The size of the archive directory and the period of removal are totally dependant on the rate at which your archive logs are created. A little trial and error is required but once stabilised you can sit back knowing that your archive cycle is trickling along nicely.

Good luck,
Mike Heydon Received on Tue Oct 07 1997 - 00:00:00 CDT