Instance Architecture   «Prev  Next»

Lesson 16 The ARCHIVE LOG LIST command
Objective Use ARCHIVE LOG LIST command to see current state

ARCHIVE LOG LIST Command

Objective: Use the ARCHIVE LOG LIST command to view the current state of redo logs and archive logs in an Oracle database.

Introduction to the ARCHIVE LOG LIST Command

As a Database Administrator (DBA), you may need to check the status of your Oracle database's redo logs and archive logs. The ARCHIVE LOG LIST command provides essential information, such as whether the database is in ARCHIVELOG mode, the current redo log file in use, and the archive log destination. This command is executed from SQL*Plus when connected to an open Oracle database as SYSOPER or SYSDBA.

Using the ARCHIVE LOG LIST Command

To run the command, connect to SQL*Plus and execute:
SQL> ARCHIVE LOG LIST

Example Output:

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /m01/oracle/oradata/prod/archive
Oldest online log sequence     20
Next log sequence to archive   21
Current log sequence           21

Explanation of Output:
  • Database log mode: Indicates whether the database is in ARCHIVELOG or NOARCHIVELOG mode. ARCHIVELOG mode means redo logs are archived for recovery purposes.
  • Automatic archival: Shows if the Archiver process is automatically copying filled redo log files (Enabled/Disabled).
  • Archive destination: Specifies the directory where archived redo logs are stored.
  • Oldest online log sequence: The sequence number of the oldest redo log still online.
  • Next log sequence to archive: The sequence number of the next redo log to be archived.
  • Current log sequence: The sequence number of the redo log currently in use.


Finding the Archive Log Destination

In addition to ARCHIVE LOG LIST, you can use the following commands to locate the archive log directory:

  1. Show Parameter Command:
    SQL> show parameter db_recovery_file_dest

    This displays the Flash Recovery Area, where archived redo logs are typically stored.

  2. Query v$archive_dest View:
    SQL> select dest_name, status, destination from v$archive_dest;

    This provides details about archive log destinations and their status.


Usage Notes


Log List - Exercise

Practice using the ARCHIVE LOG LIST command and related queries to explore your database's redo log and archive log configuration. For a hands-on activity, refer to the Log List - Exercise to see what you have learned.

SEMrush Software