Lesson 6	Archive log history
Objective	View a list of archived log files in Oracle

Archive log history

In Oracle (including Oracle 23ai), redo data is written to online redo log files and—when the database is in ARCHIVELOG mode, each filled redo log is copied to an archived redo log file. DBAs review redo and archive activity to:

verify log switching and archiving are healthy,
confirm archive destinations have capacity, and
estimate redo volume for recovery planning.

This lesson introduces three practical layers of visibility:

v$log for the current online redo log groups and their status
v$log_history for log switch history (which sequences were generated)
v$archived_log for the authoritative list of archived redo log files (filenames, times, sizes, destination)

Important: v$log and v$log_history help you understand log switching, but v$archived_log is the view you use to list archived log files.

Viewing the online redo log file status

Query v$log to get a snapshot of the current online redo log groups. This view shows which group is CURRENT, which groups are reusable (INACTIVE), and which group has been selected for the next switch (ACTIVE is still needed for instance recovery). The ARCHIVED column indicates whether the group’s contents have been archived for the most recent sequence written to that group.

Example:

SQL> SELECT group#, sequence#, archived, status
  2  FROM v$log
  3  ORDER BY group#;

GROUP#  SEQUENCE#  ARC  STATUS
------  ---------  ---  ----------------
1       16031      YES  INACTIVE
2       16034      NO   CURRENT
3       16029      YES  INACTIVE
4       16030      YES  INACTIVE
5       16032      NO   INACTIVE
6       16033      NO   INACTIVE

In this example, log file groups 2, 5, and 6 have not yet been archived. Group 2 is the CURRENT group and will be archived after it becomes full and Oracle switches to the next group. Groups 5 and 6 are reusable (INACTIVE) but still show ARCHIVED = NO, which may indicate they were created recently, the archive process is delayed, or the output shown is from a specific point in time during activity. Note:

Before adding or dropping members from a redo log group, verify the group is not CURRENT. In production, you typically modify a group that is INACTIVE. Also consider related dependencies:

RAC: use GV$LOG and watch THREAD# because each instance has its own thread of redo.
Data Guard: ensure standby transport/apply expectations are met before performing redo log maintenance.

Viewing the log switch history

The v$log_history view records redo log switch activity (the history of log sequence numbers generated). It is useful for understanding when log switches occurred and for estimating redo generation trends.

Example query:

SQL> SELECT sequence#, first_change#, first_time
  2  FROM v$log_history
  3  ORDER BY sequence#;

SEQUENCE#  FIRST_CHANGE#  FIRST_TIME
---------  -------------  --------------------
16017      118936         08-Nov-2025 07:16:01
16018      118941         08-Nov-2025 07:22:10
16019      138947         22-Nov-2025 07:07:48
16020      138953         22-Nov-2025 08:55:56
16021      138954         22-Nov-2025 08:55:57
16022      138955         22-Nov-2025 08:55:58
16023      138956         22-Nov-2025 08:55:59
16024      138957         22-Nov-2025 08:55:59
16025      138958         08-Nov-2025 08:56:04
16026      158960         23-Nov-2025 11:10:31
16027      178965         26-Nov-2025 02:35:35
16028      198970         26-Nov-2025 04:03:52
16029      198991         26-Nov-2025 10:40:36
16030      218998         28-Nov-2025 11:06:15
16031      219003         28-Nov-2025 04:08:50
16032      219004         28-Nov-2025 04:09:14
16033      219005         28-Nov-2025 04:17:04

The SEQUENCE# is the redo log sequence number. Archive log filenames commonly include this sequence value (exact naming depends on your archive destination configuration). The FIRST_CHANGE# column is the SCN of the first redo record in that sequence, and FIRST_TIME is when Oracle started writing that sequence.

Use v$log_history to track redo switch frequency, but use v$archived_log to list the archived log files themselves.

Viewing a list of archived log files

In Oracle 23ai, the most direct “inventory” view for archived redo logs is v$archived_log. It records the archived log’s sequence, thread (RAC), archive destination, completion time, and file name. This is the view you query when the lesson objective is to view a list of archived log files.

A practical starting query:

SQL> SELECT thread#, sequence#, resetlogs_id,
  2         first_time, next_time,
  3         completion_time,
  4         blocks, block_size,
  5         (blocks * block_size) bytes,
  6         name
  7  FROM   v$archived_log
  8  WHERE  name IS NOT NULL
  9  ORDER  BY completion_time DESC
 10  FETCH FIRST 20 ROWS ONLY;

Notes for real environments:

RAC: include THREAD# and aggregate by thread when estimating daily redo.
Multiple destinations: filter by DEST_ID if you have more than one archive destination.
Catalog vs control file: RMAN can list archived logs from the control file or recovery catalog. RMAN is often the best operational tool when you need “what exists and what is backed up.”
Multitenant: redo and archived redo are instance/CDB-level artifacts; run these queries with appropriate privileges, typically from the root container as a DBA user.

Monitoring the rate of redo log generation

If your database runs in ARCHIVELOG mode, you should understand how much redo is generated per day. This helps you size archive destinations (including the Fast Recovery Area) and estimate backup and recovery throughput requirements.

Older approaches estimated redo volume by counting logs per day and multiplying by a fixed log size. In modern systems, log sizes may vary (compression, destination differences, multiple threads in RAC). A better approach is to compute daily volume from archived logs:

SQL> SELECT TRUNC(completion_time) log_day,
  2         COUNT(*) archived_logs,
  3         ROUND(SUM(blocks * block_size) / 1024 / 1024) redo_mb
  4  FROM   v$archived_log
  5  WHERE  name IS NOT NULL
  6  GROUP  BY TRUNC(completion_time)
  7  ORDER  BY log_day;

Assumptions and interpretation

This daily summary is most accurate when:

You group by COMPLETION_TIME (when the archive finished writing) rather than FIRST_TIME (when the redo sequence began).
You include THREAD# for RAC (each instance generates its own stream of redo).
You understand destination behavior (multiple destinations, standby shipping, and deletion policies can affect what remains on disk).

If you instead use v$log_history, remember that FIRST_TIME is when a log sequence started—not when it was archived—and it does not provide the archived log filename.

Take time now that you have learned about v$log, v$log_history, and v$archived_log and run these queries in your own database. In the next lesson, you can review what you learned in this module and take a quiz to confirm mastery.