How to Obtain Archive Log Information

In Oracle Database 23ai, it is still important to retrieve and monitor archive log information for most real-world environments. ARCHIVELOG mode is foundational for point-in-time recovery (PITR), Data Guard, and typical online backup strategies. Even when you use Flashback features, archived redo remains part of the broader recovery and availability story.

Is retrieving archive log information still necessary in Oracle 23ai?

Yes. The core ARCHIVELOG behavior remains consistent in 23ai: if you care about recovery, HA/DR, or operational correctness, you still need visibility into redo generation and archiving.

Feature / requirement	Requires ARCHIVELOG mode?	Changed in 23ai?	Notes
Point-in-time recovery (PITR)	Yes	No	Classic recovery still depends on archived redo.
Data Guard (physical standby)	Yes	No	Redo transport/apply depends on archived redo (and/or real-time redo).
Online backups and incremental backups	Yes (strongly recommended)	No	NOARCHIVELOG usually implies cold backups only.
Restore points / Flashback beyond short retention windows	Yes	No	Flashback logs help, but archived redo remains central for many recovery workflows.

What did improve in 23ai is flashback log placement flexibility: Oracle introduced parameters that let you separate Flashback logs from the FRA, reducing I/O contention. That is helpful, but it does not remove the need for archived redo.

• DB_FLASHBACK_LOG_DEST
• DB_FLASHBACK_LOG_DEST_SIZE

The simplest “at-a-glance” command is ARCHIVE LOG LIST. It shows whether you are in ARCHIVELOG mode, whether automatic archiving is enabled, the active archive destination, and key sequence numbers.

1. Connect with SYSDBA privileges:

   sqlplus / as sysdba

2. Run the command:

   ARCHIVE LOG LIST;

Example output (typical format)

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/fast_recovery_area/ORCL/archivelog
Oldest online log sequence     100
Next log sequence to archive   102
Current log sequence           102

The most important takeaways are:

• Database log mode: confirms ARCHIVELOG vs NOARCHIVELOG.
• Automatic archival: whether archiving is actively running.
• Archive destination: where archived redo is currently going (often FRA-based).
• Sequence fields: quick sense of redo progress and whether archiving is keeping up.

For operational monitoring (and for troubleshooting “archive destination full”, “cannot archive”, or Data Guard gaps), you typically query dynamic performance views. These are often more actionable than ARCHIVE LOG LIST alone.

Confirm log mode quickly

SELECT log_mode
FROM v$database;

Archive destinations and status

SELECT dest_id, status, destination, target, valid_now, error
FROM v$archive_dest
ORDER BY dest_id;

If you are diagnosing transport/apply issues (especially with Data Guard), V$ARCHIVE_DEST_STATUS is commonly used as well.

Archived log history recorded in the control file

SELECT thread#, sequence#, first_time, next_time, applied, name
FROM v$archived_log
ORDER BY thread#, sequence#;

Log switches captured in the control file

SELECT thread#, sequence#, first_time
FROM v$log_history
ORDER BY thread#, sequence#;

Rule of thumb: use V$ARCHIVED_LOG when you care about archived redo files (including names and applied status), and use V$LOG_HISTORY when you care about log switch history as recorded in the control file.

The diagrams below summarize three commonly referenced views for archived redo visibility and archiving state.

---

---

The next lesson introduces standby databases, where archived redo visibility becomes even more important for transport, apply, and gap detection.