| Lesson 16 | The ARCHIVE LOG LIST command |
| Objective | Use ARCHIVE LOG LIST command to see current state |
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:
SVRMGR> 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:
- The database is currently operating in ARCHIVELOG mode.
- Automatic archiving is enabled.
- The archived redo log destination is D:\oracle\oradata\IDDB2\archive.
- The oldest filled redo log group has a sequence number of 11160.
- The next filled redo log group to archive has a sequence number of 11163.
- 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