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

Checking State of redo logs and archive logs

There are times as a DBA, when you need to know the current state of the database relative to the redo logs and archive logs. You might want to find out something as simple as whether the database is in archivelog mode, or you may want to find out which redo log file is currently in use. Server Manager provides a command for this purpose. The command is ARCHIVE LOG LIST, and with it you can find out whether your database is in archive log mode, which redo log file is currently being written to, and the archive log destination. You run the ARCHIVE LOG LIST command from Server Manager. Here is an example:

SQL> archive log list
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

In this example, the first line tells you that the database is in archive log mode. The second line tells you that the Archiver process is running, and automatically copying redo log files when they are filled. The third line tells you the directory to which those log files are being copied. The last three lines tell you that the oldest log file still online is #20, and that the one currently being used is #21.
Oracle archivelog list command

ARCHIVE LOG LIST Command

The SQL*Plus command ARCHIVE LOG LIST displays archiving information for the connected instance. For example:
SQL> ARCHIVE LOG LIST

Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\oracle\oradata\IDDB2\archive
Oldest online log sequence 11160
Next log sequence to archive 11163
Current log sequence 11163

This display tells you all the necessary information regarding the archived redo log settings for the current instance:
  1. The database is currently operating in ARCHIVELOG mode.
  2. Automatic archiving is enabled.
  3. The archived redo log destination is D:\oracle\oradata\IDDB2\archive.
  4. The oldest filled redo log group has a sequence number of 11160.
  5. The next filled redo log group to archive has a sequence number of 11163.
  6. The current redo log file has a sequence number of 11163.

ARCHIVE LOG

Syntax: ARCHIVE LOG LIST
Displays information about redo log files.

LIST

Requests a display that shows the range of redo log files to be archived, the current log file group's sequence number, and the current archive destination (specified by either the optional command text or by the initialization parameter LOG_ARCHIVE_DEST). If you are using both ARCHIVELOG mode and automatic archiving, the display might appear like:
ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vobs/oracle/dbs/arch
Oldest online log sequence 221
Next log sequence to archive 222
Current log sequence 222

Since the log sequence number of the current log group and the next log group to archive are the same, automatic archival has archived all log groups up to the current one.
If you are using ARCHIVELOG but have disabled automatic archiving, the last three lines might look like:
Oldest online log sequence 222
Next log sequence to archive 222
Current log sequence 225
If you are using NOARCHIVELOG mode, the "next log sequence to archive" line is suppressed. The log sequence increments every time the Log Writer begins to write to another redo log file group; it does not indicate the number of logs being used. Every time an online redo log file group is reused, the contents are assigned a new log sequence number. Usage: You must be connected to an open Oracle database as SYSOPER, or SYSDBA

Log List - Exercise

Click the Exercise link below to practice what you have learned.
Log List - Exercise

Oracle Database Administration
s