Find Oracle Archive Logs Older Than N Days with find -mtime

Find Oracle Archive Logs Older Than N Days with find -mtime

Purpose

Oracle databases in archivelog mode write a stream of .ARC (archived redo log) files to a destination directory. The directory fills up. When it fills up, the database hangs. The four find -mtime commands below let a DBA list, delete, compress, or move archive logs by age — the daily housekeeping that keeps the archive destination from filling.

These are the simplest possible archive log retention tools: no RMAN, no recovery catalog, just plain UNIX. Use them on hosts where RMAN is not yet configured, when you need a quick one-shot cleanup, or as the body of a cron job for very small databases.

Code

 1# List every archive log older than 1 day
 2find ./ -name "*.ARC" -mtime +1 -exec ls -l {} \;
 3
 4# Delete every archive log older than 1 day
 5find ./ -name "*.ARC" -mtime +1 -exec rm {} \;
 6
 7# Gzip every archive log older than 1 day
 8find ./ -name "*.ARC" -mtime +1 -exec gzip {} \;
 9
10# Move every archive log older than 1 day to another directory
11find ./ -name "*.arch" -mtime +1 -exec mv {} /u01/andy/ \;

Breakdown of Code

Every command shares the same skeleton. Only the action verb at the end changes.

• find ./ — start the search in the current directory. Run from $ORACLE_BASE/admin/<sid>/arch or wherever your LOG_ARCHIVE_DEST_1 parameter points.
• -name "*.ARC" — only match archived redo log files. Some Oracle versions write .arc (lowercase) or .arch; check your file extension and adjust. The pattern is case-sensitive on most UNIX filesystems.
• -mtime +1 — only match files whose modification time is more than 1 day old. Change +1 to +7 for a 7-day retention window, +30 for a 30-day window, and so on.
• -exec ls -l {} \; — for each match, run the action. The {} is the path of the matched file. The \; ends the action clause. The four lines above use ls -l, rm, gzip, and mv as their actions.

How It Works

find walks the directory tree from the starting point. For every file it visits, it tests the predicates: name pattern, modification age, size, type, and so on. When all predicates match, find runs the -exec action. UNIX runs one process per matched file, so on a directory with 10,000 archive logs the delete command spawns 10,000 short-lived rm processes. That is fine for daily cleanup; it would not be fine for a directory with millions of files (use find ... -delete or xargs -r0 in that case).

The four actions cover the four things a DBA does with an old archive log:

• List (ls -l) — confirm what would be deleted before deleting it. Always run the list version first as a dry run.
• Delete (rm) — free the disk. Only safe after the logs have been backed up by RMAN, copied to tape, or shipped to a Data Guard standby.
• Gzip (gzip) — keep the logs on disk but in a smaller form. Useful when you want a longer on-disk window for ad-hoc point-in-time recovery without buying more disk.
• Move (mv) — relocate to a slower or larger volume. Useful when archival policy says "keep 30 days on tier 2 storage."

Key Points

• Always dry-run first — the ls -l version of the command shows you exactly what the destructive version will touch.
• Extension matters — match the actual extension your database writes (*.ARC, *.arc, *.arch). Set it via the LOG_ARCHIVE_FORMAT init parameter; check the value with SHOW PARAMETER log_archive_format.
• Back up before deleting — never rm an archive log that is not yet on backup media or shipped to a standby. Lost archive logs break point-in-time recovery and Data Guard.
• +1 is the boundary, not the count — -mtime +1 means strictly more than 1 day. So a file modified 25 hours ago matches, a file modified 23 hours ago does not.

Common Variations

Wrap the delete command in a cron job for hands-off rotation:

1# /etc/cron.d/oracle-arch-cleanup — keep 7 days of archive logs
20 2 * * * oracle find /u01/app/oracle/arch -name "*.ARC" -mtime +7 -exec rm {} \;

Use find ... -delete instead of -exec rm {} \; for a faster single-process delete on large directories:

1find /u01/app/oracle/arch -name "*.ARC" -mtime +7 -delete

Combine with tar to roll old logs into a single archive before deletion:

1find . -name "*.ARC" -mtime +7 -print | tar -czvf arch_$(date +%F).tar.gz -T - && \
2  find . -name "*.ARC" -mtime +7 -delete

Important Considerations

The reliable tool for archive log lifecycle is RMAN, not find. RMAN's DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-7' updates the control file as it deletes, so the database knows the logs are gone. A find -exec rm does not update the control file — RMAN will still believe the logs exist until you run CROSSCHECK ARCHIVELOG ALL and DELETE EXPIRED ARCHIVELOG ALL. Run those two commands after any out-of-band file deletion to keep the control file honest.

For Data Guard environments do not delete primary-side archive logs that the standby has not yet applied. Use the V$ARCHIVE_DEST_STATUS view on the primary to confirm the standby is current before any aging-out script runs.

The original UNIX one-liners above were published in the shutdownabort.com DBA Quick Guides (Andrew Barry, 2007–2013), preserved via the Wayback Machine as the anchor source for this post.

References

• find(1) — UNIX find command manual page — full syntax for -mtime, -exec, and -delete
• Oracle Database Reference — LOG_ARCHIVE_FORMAT init parameter — controls the file extension and naming pattern of archived redo logs
• Oracle RMAN Reference — DELETE ARCHIVELOG — the RMAN-aware way to delete archive logs (preferred over rm)
• shutdownabort.com — Miscellaneous Useful UNIX (Wayback, 2013-01-15) — original source of the four one-liners above