Oracle Archive Log Management Best Practices — A Practical DBA Guide

In most production environments, archive logs are something we don’t think much about — until storage fills up or backups start failing. Then suddenly, archive log management becomes critical.

I’ve seen multiple situations where archive logs kept growing silently, and before anyone noticed, the filesystem was full, RMAN backups started failing, and database operations were affected.

Proper archive log management is one of the basic but very important responsibilities for DBAs working with Oracle Database.

In this blog, I’ll walk through practical archive log management steps, useful queries, and best practices that can help avoid unexpected issues.

What Are Archive Logs

Archive logs are copies of redo logs created when the database runs in ARCHIVELOG mode. These logs are essential for backup and recovery operations.

Archive logs are mainly used for:

• Database recovery
• Point-in-time recovery
• Standby databases (Data Guard)
• RMAN backups

Because of their importance, archive logs must be managed carefully.

Check Archive Log Mode

First, check whether your database is running in ARCHIVELOG mode:

SELECT LOG_MODE FROM V$DATABASE;

If the output shows ARCHIVELOG, archive logging is enabled.

Check Archive Log Location

Next, check where archive logs are stored:

SHOW PARAMETER log_archive_dest_1;

or

SHOW PARAMETER db_recovery_file_dest;

Monitor Archive Log Generation

It’s important to monitor how frequently archive logs are generated:

SELECT 
SEQUENCE#,
FIRST_TIME,
NEXT_TIME
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE# DESC;

This helps understand:

• Log generation frequency
• Growth trend
• Database activity

Monitor Archive Log Growth

You can also check archive log generation per day:

SELECT 
TRUNC(FIRST_TIME) DAY,
COUNT(*) LOG_COUNT
FROM V$ARCHIVED_LOG
GROUP BY TRUNC(FIRST_TIME)
ORDER BY 1;

This helps estimate:

• Storage requirements
• Growth patterns
• Peak database activity

Backup Archive Logs Regularly

Backing up archive logs is one of the most important best practices:

RMAN> BACKUP ARCHIVELOG ALL;

Recommended approach:

RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;

This backs up archive logs and deletes them after backup, helping manage space efficiently.

Configure Retention Policy

Configure RMAN retention policy:

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

This keeps backups only for the required duration and helps avoid unnecessary storage usage

Monitor FRA Usage

If archive logs are stored in FRA, monitor usage regularly:

SELECT 
FILE_TYPE,
PERCENT_SPACE_USED
FROM V$RECOVERY_AREA_USAGE;

Common Archive Log Issues

Archive Destination Full

You may encounter:

ORA-00257: archiver error

This usually means archive destination is full.

To fix:

• Backup archive logs
• Delete old archive logs
• Increase storage

Useful Archive Log Queries

Last Archive Log

SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;

Archive Log Generation by Hour

SELECT 
TO_CHAR(FIRST_TIME,'HH24'),
COUNT(*)
FROM V$ARCHIVED_LOG
GROUP BY TO_CHAR(FIRST_TIME,'HH24')
ORDER BY 1;

Archive Log Size

SELECT 
SUM(BLOCKS*BLOCK_SIZE)/1024/1024 MB
FROM V$ARCHIVED_LOG;

Best Practices

• Backup archive logs regularly
• Configure retention policy
• Monitor archive log growth
• Setup alerts
• Automate cleanup jobs
• Monitor disk space

Conclusion

Archive logs are critical for database recovery, but if not managed properly, they can lead to storage issues and backup failures. Regular monitoring, proper retention policies, and scheduled backups can help prevent unexpected problems.

With proper archive log management, DBAs can ensure stable backup operations and maintain database availability.