I have an Oracle 9i which creates Archive Logs since the year 2007. Can I delete them without any concerns? Or do I need to tell Oracle something before I delete them?

(From time to time I do a cold backup of the database.)




Archived logs are used to do a point in time recovery. Say you have backups on Jan 1st, Jan 8th and Jan 15th. If you wanted to restore your database to the point it was on Jan 10th, you'd reload the Jan 8th backup, and use the archived logs from between the 8th and the 10th to bring the database up to that point in time.

If you never want to go earlier than your last cold backup, you could delete all archived log files from prior to when that cold backup was taken.

Archived logs are also used during a hot backup. Since the database itself is operation during the time of the backup, then additional information is written to the log files during the course of the backup, so you want to (at least) keep log files generated from when the backup started.


Archive logs are mainly used to recover changes that occurs since the last backup.

Instead of a cold backup, I suggest you to do an hot backup and delete the old archive logs right after. On 10g, this can be done by using the following command at an RMAN prompt:

backup database plus archive log delete all input;

It will perform the following:

  1. Switch current redo log and archive the last one used.
  2. Do a consistent backup of the database
  3. delete all archive logs that are included in the newly created backupset
  • 1
    Thanks. But we are not using RMAN at all. What I would like to understand is, how I can identify archive logs that can safely be deleted after a cold backup. Would this sequence be ok? 1. Use the SQL+ command ‘ARCHIVE LOG LIST’ to identify the ‘Oldest online log sequence’ 2. Do the cold backup 3. Delete all archive log files with a sequence number smaller than the ‘Oldest online log sequence’. Thanks, Tobias – cyntaxx Apr 22 '10 at 12:42
  • @cyntaxx That looks good but you should do a test. May I ask you why you don't use rman? – Benoit Apr 22 '10 at 17:15
  • This is just a test database our software developers are using from time to time - no high availability required. If worse comes to worse I could recreate the database and use the cold backup and archive logs to recover. I understand that RMAN creates a standby database that would probably require way more space than my exported files zipped away. Is this reason enough or would you recommend to use RMAN anyway - and if, in what way? Thanks again, Tobias – cyntaxx Apr 23 '10 at 8:54

I'd say, use RMAN to purge all your archivelogs after every succesfull cold backup. Romething in the line of

delete noprompt archivelog all completed before 'sysdate - (${KEEPTIME}/24)';

where ${KEEPTIME} is the amount of hours before 'NOW' you'd like to keep (Since you're doing cold backups, you could say like 2 or so, I'd use 24)

Deleting the archived logs with RMAN informs oracle that the files are deleted, whereas when you delete them manually, the files are still 'registered' in the controlfile (v$archived log etc. )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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