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 when connected to an Oracle 19c database using the Shared Server mode, but with some considerations:

1. Understanding ARCHIVE LOG LIST in SQL*Plus
   • The ARCHIVE LOG LIST command is used to check the current archiving mode of the database, along with the log archive destination and the current log sequence number.
2. Running ARCHIVE LOG LIST in Shared Server Mode
   • Shared Server Mode enables multiple user connections to share a pool of server processes, reducing memory usage in large-scale environments.
   • SQL*Plus can still execute the ARCHIVE LOG LIST command when running in Shared Server Mode, because it does not require a dedicated server process.
3. How to Check If You Are in Shared Server Mode
   • Before running the command, you can verify if your session is using a Shared Server connection by executing:

     SELECT username, server FROM v$session WHERE sid = SYS_CONTEXT('USERENV', 'SID');
     

     If the SERVER column returns SHARED, your session is running in Shared Server mode.
4. Running ARCHIVE LOG LIST
   • Once connected to SQL*Plus, execute:

     ARCHIVE LOG LIST;
     

     This will display:
     • Whether archiving is enabled (ARCHIVELOG or NOARCHIVELOG mode).
     • The log archive destination.
     • The current log sequence number.
5. Potential Issues in Shared Server Mode
   • Restricted Commands: Some archive log maintenance commands (like ALTER DATABASE ARCHIVELOG) require a Dedicated Server connection.
   • Dynamic Views May Differ: Certain v$ views may behave differently under Shared Server mode due to session multiplexing.
6. How to Switch to Dedicated Server Mode (If Needed)

If you encounter issues and need to switch to a "Dedicated Server session", use:

ALTER SYSTEM SET DISPATCHERS = '';

Then reconnect to SQL*Plus using a "dedicated connection":

sqlplus / as sysdba

Conclusion Yes, you can run `ARCHIVE LOG LIST` in SQL*Plus while using Shared Server Mode, but some archive log operations may require a Dedicated Server session. If you encounter restrictions, switching to a Dedicated Server session will resolve them.

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