Unmanaged Oracle archivelogs are one of the most common causes of failed recoveries and sudden storage crises for database administrators (DBAs). If you don’t know exactly which archived redo logs exist or where they live, you risk losing recent transactions or running out of disk space at the worst moment. That’s why learning how to list archivelog files quickly—and interpret their status—is essential for every operations administrator.
In this guide, we break down what Oracle archivelogs are, why listing them matters so much in daily operations, and how you can use both SQL*Plus and RMAN tools to get complete visibility into your log files. We also explore advanced queries for troubleshooting gaps or lag in replication environments. Ready to master your archive log management?
What Are Archivelogs in Oracle?
Archivelogs are copies of filled redo log files that Oracle creates before overwriting them with new changes. When your database runs in ARCHIVELOG mode—a best practice for production systems—every committed transaction is preserved in these files until you back them up or delete them safely.
Why does this matter? Archivelogs let you restore your database to any exact point in time after hardware failure or user error by replaying all changes since the last backup. Without archivelogs, any work done since your last backup could be lost forever if disaster strikes.
Archivelogs also play key roles beyond recovery:
If you’re not managing these logs carefully—by tracking which exist, which have been backed up, and which can be deleted—you risk downtime or even permanent data loss.
Why List Archivelog Files Matters?
Listing archivelog files is not just a routine task—it’s central to healthy Oracle operations at every skill level. By regularly checking which archive logs exist on disk or tape (and their status), DBAs can:
Confirm that backups include all necessary logs
Monitor disk usage trends before storage fills up
Prepare old logs for safe deletion or offloading
Troubleshoot missing backups or recovery gaps before they become emergencies
For example: If you never check your archive log inventory but keep generating new transactions daily, it’s easy to fill up local storage without warning—which can halt database activity entirely until space is freed.
Listing archivelog files also helps spot problems early:
Gaps between sequence numbers may signal missing files
Logs marked as “not applied” on standby databases could indicate replication lag
Unexpectedly old completion times might reveal slow backup jobs
By making “list archivelog” checks part of your regular workflow—using both SQL*Plus views and RMAN commands—you gain confidence that no transaction slips through the cracks.
How to List Archivelog Using SQL*Plus?
SQL*Plus remains one of the most direct ways to interact with an Oracle database—from basic queries up through advanced administration tasks. Listing archive logs here gives immediate insight into what Oracle knows about its own history.
To start: Connect as a user with SYSDBA privileges using SQL*Plus.
First, check if ARCHIVELOG mode is enabled:
ARCHIVE LOG LIST
This command tells you whether ARCHIVELOG mode is active (a must-have for reliable backups), shows the current log sequence number being written now, and displays the main destination directory where new archive logs are stored.
However, this summary does not show each individual file present on disk—it only provides high-level information about logging status.
To see detailed information about every archived redo log known by Oracle’s control file (the internal record keeper), run:
SELECT SEQUENCE#, NAME, APPLIED, COMPLETION_TIME
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;
Here’s what each column means:
SEQUENCE#: Unique number assigned when each log is created; helps track continuity over time.
NAME: Full path showing exactly where each archived redo log file lives on disk.
APPLIED: Shows YES if this log has been applied on standby systems (for Data Guard); otherwise NO.
COMPLETION_TIME: Timestamp when writing finished; useful for finding old/unmoved logs.
You can filter results further based on operational needs:
If you want only those logs not yet applied on standby databases—for example during troubleshooting replication lag—try:
SELECT SEQUENCE#, NAME
FROM V$ARCHIVED_LOG
WHERE APPLIED = 'NO'
ORDER BY SEQUENCE#;
Or perhaps you need only those generated within the past day:
SELECT SEQUENCE#, NAME
FROM V$ARCHIVED_LOG
WHERE COMPLETION_TIME > SYSDATE - 1
ORDER BY SEQUENCE#;
Want more context? Add DEST_ID if using multiple destinations (such as remote archival):
SELECT SEQUENCE#, DEST_ID, NAME
FROM V$ARCHIVED_LOG
ORDER BY DEST_ID, SEQUENCE#;
Keep in mind: The V$ARCHIVED_LOG view depends on records stored inside the control file itself—not every physical file left behind on disk will always appear here if older entries have been overwritten due to limited control file size settings!
How to Query Archivelog Metadata for Advanced Operations?
Sometimes standard listings aren’t enough—you need deeper diagnostics across primary/standby setups or want quick health checks across many sequences at once.
For example: To find out whether there’s a gap between what has been generated versus what has actually reached your standby system (a common cause of failover delays), try:
SELECT MAX(SEQUENCE#) KEEP (DENSE_RANK LAST ORDER BY COMPLETION_TIME) AS LAST_GENERATED,
(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED='YES') AS LAST_APPLIED
FROM V$ARCHIVED_LOG;
This query compares the highest sequence generated against the highest one successfully applied elsewhere—a fast way to spot lags before they become serious issues.
You might also want counts per day:
SELECT TRUNC(COMPLETION_TIME), COUNT(*) AS LOGS_PER_DAY
FROM V$ARCHIVED_LOG
GROUP BY TRUNC(COMPLETION_TIME)
ORDER BY 1 DESC;
This helps forecast future storage needs based on historical activity patterns—a vital metric when planning retention policies!
Remember: Always cross-check these results against actual OS-level listings if discrepancies arise; sometimes manual deletions outside Oracle leave “ghost” entries behind until cleaned up by maintenance routines like CROSSCHECK commands in RMAN.
How to List Archivelog Using RMAN?
Recovery Manager (RMAN) is Oracle's main toolset for automating backup/recovery—including full control over listing and deleting archive logs safely according to policy rather than guesswork alone.
Start by connecting as target DBA user:
rman target /
To see every archived redo log currently recorded by either the control file or catalog repository:
LIST ARCHIVELOG ALL;
This command outputs sequence numbers (to track order), thread numbers (for RAC clusters), start/end times per file written out—and crucially—the physical location path where each archived redo log resides right now according to RMAN's knowledge base.
It’s important to note that this list reflects only those files still tracked within Oracle's metadata—not necessarily every single physical file left behind after manual deletions outside RMAN! Always use CROSSCHECK commands periodically so metadata stays accurate with reality.
If you're preparing cleanup jobs but want assurance nothing important gets deleted prematurely:
List all archive logs never backed up locally first—
LIST ARCHIVELOG ALL BACKED UP 0 TIMES TO DISK;
This shows exactly which archives remain unprotected so far—a perfect input set before running DELETE commands later.
Need details restricted by time window? For instance—to review everything generated yesterday—
LIST ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';
Or focus tightly between two known sequence numbers—
LIST ARCHIVELOG SEQUENCE BETWEEN 120316 AND 120320;
Prefer concise reporting instead of full detail? Add SUMMARY keyword—
LIST ARCHIVELOG ALL SUMMARY;
Want confirmation that specific archives have already made it into backup sets?
LIST BACKUP OF ARCHIVELOG ALL;
Missing sequences often mean trouble—compare output from LIST commands against expected ranges regularly so no gap goes unnoticed!
Using LIST Output for Proactive Maintenance
Proactive DBAs use LIST output as part of scheduled scripts—not just ad hoc checks—to automate healthy cleanup cycles while avoiding accidental data loss.
Here’s how such workflows typically unfold:
1. Use LIST ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE sbt;
This verifies all archives needed have reached tape-based media before considering deletion.
2. Run LIST ARCHIVELOG UNTIL TIME 'SYSDATE-7';
This identifies all archives older than seven days—matching typical retention windows—for targeted cleanup.
3. Execute CROSSCHECK ARCHIVELOG ALL;
This syncs RMAN's internal catalog with actual OS-level presence so stale records don't mislead future reports.
Once confident nothing critical remains unprotected—or needed soon—you can safely delete obsolete archives using:
DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-7' BACKED UP 1 TIMES TO DEVICE TYPE sbt;
By tying together regular listing with crosschecking/deletion steps like these,
you prevent silent buildup of unnecessary files while guaranteeing recoverability at all times.
Always remember: Never rely solely on OS-level scripts! Only coordinated use of LIST/CROSSCHECK/DELETE ensures both safety and efficiency across large enterprise deployments.
Oracle Enterprise Backup Solution: Vinchin Backup & Recovery
While mastering manual tools like SQL*Plus and RMAN strengthens operational reliability, comprehensive protection demands an enterprise-grade solution tailored for modern environments. Vinchin Backup & Recovery stands out as a professional platform supporting mainstream databases—including Oracle, MySQL, SQL Server, MariaDB, PostgreSQL, PostgresPro, and TiDB—with robust features designed specifically for complex workloads such as yours. For Oracle users especially concerned about archivelog management and data integrity, Vinchin Backup & Recovery delivers incremental backup capabilities alongside batch database backup options and flexible GFS retention policies. Combined with integrity check procedures and WORM protection technology—all managed through a unified interface—these features streamline compliance efforts while ensuring rapid recovery from ransomware attacks or accidental deletions.
The intuitive web console makes safeguarding your Oracle environment straightforward:
Step 1. Select the Oracle database to back up
Step 2. Choose backup storage
Step 3. Define your backup strategy
Step 4. Submit the job
Recognized globally among IT professionals—with top ratings from thousands of enterprises—Vinchin Backup & Recovery offers a fully featured free trial valid for 60 days; click below to experience leading-edge data protection firsthand.
List Archivelog FAQs
Q1: How do I automate daily listing of new archivelogs?
A1: Schedule an OS cron job running an RMAN script with LIST ARCHIVELOG FROM TIME 'SYSDATE-1' then email results 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 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 files needing attention.
Q4: What does it mean if LIST ARCHIVELOG shows a file but it's missing from my server?
A4: This usually means someone deleted it outside RMAN; run CROSSCHECK ARCHIVELOG ALL then DELETE EXPIRED ARCHIVELOG ALL if safe—but investigate root cause first!
Q5: How do I identify obsolete archivelogs matching my retention policy without deleting anything yet?
A5: Use LIST ARCHIVELOG UNTIL TIME 'SYSDATE-n'; replace n with days matching policy—for example seven days—to preview eligible candidates.
Conclusion
Systematic listing of archivelogs forms the backbone of reliable recovery planning, space management, and compliance monitoring in any serious Oracle deployment. With tools like SQL*Plus views plus automated RMAN scripts, you stay ahead of risks instead of reacting late. For seamless automation across complex environments, explore how Vinchin simplifies end-to-end protection today.
