ARCHIVE LOG LIST Command

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. Shared server 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 the SQL*Plus.

You can run the `ARCHIVE LOG LIST` command from SQL*Plus after starting SQL*Plus from Oracle Shared Server. The `ARCHIVE LOG LIST` command is a SQL statement that displays information about the database's archive logs, such as the oldest remaining log file group, the next log sequence to archive, and the current archive destination. To run the `ARCHIVE LOG LIST` command, you must be connected to an open Oracle database as SYSOPER or SYSDBA. For information about connecting to the database, see the `CONNECT` command.
Here are the steps on how to run the `ARCHIVE LOG LIST` command from SQL*Plus after starting SQL*Plus from Oracle Shared Server:

1. Start SQL*Plus from Oracle Shared Server.
2. Connect to the database as SYSOPER or SYSDBA.
3. Run the `ARCHIVE LOG LIST` command.

For example, the following command will display information about the archive logs for the database named `ORCL`:

SQL> ARCHIVE LOG LIST;

The output of the command will look something like this:

Database log mode Archive Mode Automatic archival Enabled
Archive destination /var/oracle/oracle/dbs/arch
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2

This output indicates that the database is in archive mode, automatic archiving is enabled, the archive destination is `/var/oracle/oracle/dbs/arch`, the oldest online log sequence is 1, the next log sequence to archive is 2, and the current log sequence is 2.

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

I need the commands to find the Oracle "archive logs" on my database.
Question: Is there a standard place to locate the archive redo logs directory?
Answer: The archived redo logs will be in the flash recovery area in the ARCHIVELOG directory.
The following commands can be used to locate the Oracle archive logs:

1. Issue the archive log list command:
   

   SQL> archive log list
   

2. Issue the show parameter command:
   

   SQL> show parameter db_recovery_file_dest
   

3. Query the v$archive_dest view:
   

   SQL> select dest_name, status, destination from v$archive_dest;  
   

Note In a RAC database, the settings must remain consistent across all instances. To enable archivelog mode:
1. Shut down the database first:

# srvctl stop database -d RONDB -o immediate

2. Mount the first instance either from the SQL*Plus prompt or by using the srvctl command, as follows:

SQL> startup mount;
# srvctl start database -d RONDB -o mount

Unlike pre-11gR2, you do not need to off/on the cluster_database parameter to switch between the archive/ noarchive modes.
1. Switch the database mode to archivelog and start the database using the following examples:

SQL> alter database archivelog;
SQL> alter database open;

2. To start the rest of the instance, use one of the following examples:

# srvctl start database -d RONDB
# srvctl start instance -d RONDB -I RONDB2

Once the preceding settings are made, connect to SQL*Plus and verify the database log mode and recovery parameters using the following commands:

SQL> SELECT log_mode FROM v$database;
SQL > archive log list

The outcome should be similar to the following:

SYS @RONDB1 >archive log list

Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12

From the preceding output, you can see that the database log mode is in Archive Mode now, and the archivelog location is set to Flash Recovery Area.

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.

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

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