Managing Oracle databases means keeping a close eye on your archived redo logs—commonly called archivelogs. These files are essential for point-in-time recovery and disaster protection. But how do you list archivelog files quickly and accurately? In this article, we’ll walk through the main ways to list archivelogs in Oracle, explain why it matters, cover common use cases, show you how to manage old logs safely, and share best practices for deletion.
What Is Archivelog in Oracle?
Archivelog mode is a setting in Oracle Database that copies filled redo log files to a safe location before they are overwritten. This process creates archivelog files that store all changes made to the database since the last backup or log switch. With archivelog mode enabled, you can recover your database to any point in time—even after hardware failure or user error. Without it, recent transactions may be lost if something goes wrong.
Archivelogs are vital for business continuity because they let you restore data up to the moment of failure. They also support advanced features like Data Guard replication and help meet compliance requirements for data retention.
Why List Archivelog Files?
Listing archivelog files is a key task for database administrators at every skill level. It helps you track which logs exist on disk or tape, which have been backed up safely, and which are ready for deletion or offloading.
By listing archivelogs regularly:
You confirm backups are complete and current.
You monitor disk space so storage never runs out unexpectedly.
You prepare logs for deletion or transfer to long-term storage.
You troubleshoot backup gaps or recovery issues before they become critical.
If you don’t check your archivelogs often enough, you might miss a backup gap or fill up your storage—leading to possible downtime or even data loss.
Method 1: List Archivelog Using SQL*Plus
SQL*Plus is Oracle’s standard command-line tool for interacting with databases at all levels—from basic queries to advanced administration tasks.
To check if your database is running in ARCHIVELOG mode—and see some details about current logging—connect as a user with SYSDBA privileges using SQL*Plus:
ARCHIVE LOG LIST
This command displays:
Whether the database uses ARCHIVELOG or NOARCHIVELOG mode
The current log sequence number
The destination where new archivelogs are stored
However, ARCHIVE LOG LIST only shows summary information—it does not display every individual file present on disk.
For detailed listings of each archived log file known by Oracle’s control file (including name and status), run this SQL query:
SELECT SEQUENCE#, NAME, APPLIED, COMPLETION_TIME
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;
Here’s what these columns mean:
SEQUENCE#: Unique number assigned at creation; useful when tracking continuity
NAME: Full path of each archived log file
APPLIED: Shows YES if applied on standby systems (Data Guard); otherwise NO
COMPLETION_TIME: Timestamp when writing finished
You can filter results further—for example:
SELECT SEQUENCE#, NAME FROM V$ARCHIVED_LOG WHERE APPLIED='NO';
This lists only those logs not yet applied on standby databases—a common need during troubleshooting replication lag.
Method 2: List Archivelog Using RMAN
Recovery Manager (RMAN) is Oracle’s main tool for backup and recovery automation—including full control over listing and deleting archive logs.
To see every archived log recorded by the control file:
1. Start RMAN (rman target /)
2. Run this command:
LIST ARCHIVELOG ALL;
You’ll get output showing sequence numbers, thread numbers (in RAC), start/end times, sizes, and physical locations of each file known by RMAN’s catalog.
Want only those not yet backed up? Use:
LIST ARCHIVELOG ALL BACKED UP 0 TIMES TO DISK;
This lists all archive logs that have never been backed up locally—ideal before running cleanup jobs so nothing important gets deleted by mistake.
If using tape libraries via Media Management Layer (MML), swap TO DISK with TO SBT_TAPE.
Need specific ranges? Try these examples:
LIST ARCHIVELOG FROM TIME 'SYSDATE-1';
LIST ARCHIVELOG UNTIL SEQUENCE 1500;
LIST ARCHIVELOG FROM SEQUENCE 1000 UNTIL SEQUENCE 1500;
Adjust values based on your needs—such as reviewing only yesterday’s activity (SYSDATE - 1) or focusing on certain sequence intervals after failover events.
For an overview of existing backups rather than raw files themselves:
LIST BACKUP OF ARCHIVELOG ALL;
This summarizes which archive logs have been protected already—including device type (disk/tape/cloud) and copy counts per policy settings.
Protecting Your Oracle Database with Vinchin Backup & Recovery
Beyond manual management tasks like listing and deleting archivelogs, safeguarding your Oracle environment requires robust enterprise-grade protection. Vinchin Backup & Recovery delivers comprehensive database backup capabilities tailored specifically for platforms such as Oracle—the focus here—as well as MySQL, SQL Server, MariaDB, PostgreSQL, PostgresPro, and TiDB among others.
Key features include incremental backup support (for Oracle), batch database backup operations across instances, flexible GFS retention policies ensuring compliance-ready data lifecycle management, integrity checking routines that validate recoverability automatically via SQL scripts, plus WORM protection against accidental deletions or ransomware attacks—all designed to maximize reliability while minimizing administrative overheads.
The intuitive web console streamlines complex workflows into four simple steps:
Step 1: Select the Oracle database to back up
Step 2: Choose your preferred backup storage
Step 3: Define a tailored backup strategy
Step 4: Submit the job
Vinchin Backup & Recovery enjoys global recognition among IT professionals thanks to its strong customer base and top-rated performance—try its full-featured edition free for 60 days today by clicking below!
List Archivelog FAQs
Q1: How do I automate daily listing of new archivelogs?
A1: Schedule an OS cron job running an RMAN script that includes LIST ARCHIVELOG FROM TIME 'SYSDATE–1' then email results automatically each morning.
Q2: What should I do if my disk fills up due to too many unarchived redo logs?
A2: Free space by backing up then deleting old archived redo logs using DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DISK after confirming successful backups exist elsewhere.
Q3: How can I find missing sequences between two points?
A3: Query V$ARCHIVED_LOG filtering by sequence range then compare actual versus expected numbers; gaps indicate missing/moved/deleted files needing investigation.
Conclusion
Proactive archivelog management is key to Oracle health, enabling effective monitoring, disaster recovery, and compliance. With tools like SQL*Plus and RMAN, you have the essentials for success. To further simplify the protection of archives and live data, consider a solution like Vinchin—explore its features with a free trial.
