Here is how to automatically delete archive logs every so many days, for Oracle databases running in archivelog mode. This was tested on a Windows 2000 Server running Oracle 8.1.7.x.
We start by creating a batch file, whose content is this:
sqlplus sys/sys02@db2 @run.sql remove_log.cmd
This batch file first calls the run.sql file, whose contents are these:
set head off set pagesize 0 set feedback off set linesize 100 spool g:oracleremove_log.cmd select 'del '||name from v$archived_log where trunc(completion_time) < trunc(sysdate-7) / spool off exit
Then it calls the remove_log.cmd file that we created in the first step, which in turn deletes the Archived log files from the disk.
That's it! Now all we have to do is schedule this batch file to run on a daily or weekly basis.
Colin M. writes: Deleting old archives is OK as far as it goes, but I prefer to check that they've made it onto a tape before I do -- this makes it slightly easier to do in a shell script (well, in Unix anyway), so you can check that each file has made it to tape before deleting them. This usually means you need a file somewhere listing what was taped. Then you use the Unix flavour of find to make a list of files that are more than N days old, and search the list to make sure they're taped before deleting them. I reckon the easiest way to do this on Windows machines is to use Unix-like tools and a shell like bash or Korn -- but maybe that's my preferences showing ;-) Does anyone know of a way of automating this using Windows-native tools?
Scott T. writes: The whole idea of generating archive logs is to use them for recovery purposes; therefore, these files should be backed up. This script doesn't check the status of archive logs and hence I consider it very dangerous. A better way would be to have a simple script check the "archive" flag on the files. Once the backup has completed its job you can *then* delete the files...
Yves M. writes: One way to be quite sure to have a backup before deleting the archivelogs is to use RMAN to make the backup of the database and the archivelogs with the "delete input" option when backuping archivelogs.
David G. writes: This is not a good tip if you plan to use RMAN to backup your archive logs. This generates a script to delete the archive logs at the OS level. It does not delete the log from Oracle, nor does it make sure you have backed up your archive log. If you delete from the OS level you will not be able to backup with RMAN as the file will no longer exist. RMAN backup will fail with errors. Until you restore the file, RMAN will not work.
Phil B. writes: Instead of running a script and possibly losing your recovery files, why not use RMAN or OEM Backup manager which does the following and provides safety? You could then write the output to tape using Veritas Net Nackup (the "delete input" drops the archivelogs after backing them up.):
allocate channel channel_id type disk format 'b_%u_%s_%p'; setlimit channel channel_id kbytes 1024000; backup ( database include current controlfile ); backup ( archivelog all delete input );
Ted H. writes: On Windows servers I use the command "echo y | del /a-a
For More Information
- What do you think about this tip? E-mail the editor at firstname.lastname@example.org with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
This was first published in June 2002