Archiving Redo log   «Prev  Next»
Lesson 4Naming archive log files
ObjectiveSpecify the naming convention to use for archived log files.

Naming "archive log" Files

Just as you must specify a location for archived log files, you must also specify a "naming convention". The log_archive_format initialization parameter is used to do that. The four special character sequences listed here may be used as part of the filename:
%s The log sequence number. This continually increments as redo log files are produced.
%t The log thread number, which uniquely identifies the instance producing the log file. This is necessary when the Parallel Server option is being used because the sequence numbers used by multiple instances will overlap.
%S The log sequence number, but as a fixed-width field padded to the left with zeros.
%T The thread number as a fixed-width field padded to the left with zeros.

The following diagram illustrates a couple of typical settings and shows how the resulting filenames will look:

Naming Conventions for archive Log Files
Archive format
The above diagram provides a visual representation of how the `LOG_ARCHIVE_FORMAT` parameter in Oracle 19c, which determines the naming convention for archived redo log files.
1. First Example
  • The LOG_ARCHIVE_FORMAT is set to: 
          log_archive_format = 'log_%s.%t'
    • %s → Log sequence number
    • %t → Thread number
  • The resulting archived log filenames appear as: 
          log_387.1
      log_388.1
      log_389.1
      ...
    • "387", "388", "389" represent the log sequence numbers.
    • ".1" represents the thread number.

2. Second Example
  • The LOG_ARCHIVE_FORMAT is set to: 
          log_archive_format = 'log_%T%S'
    • %T → Thread number (padded to 3 digits)
    • %S → Log sequence number (padded to 6 digits)
  • The resulting archived log filenames appear as: 
          log_00100387
      log_00100388
      log_00100389
      ...

  • "001" represents the thread number (zero-padded to three digits).
  • "00387", "00388", "00389" represent the log sequence numbers (zero-padded to six digits).
Key Observations:
  • The first format (`log_%s.%t`) is simpler, displaying the log sequence number and thread number with minimal formatting.
  • The second format (`log_%T%S`) provides a more standardized, zero-padded structure, which may be useful for sorting and consistency.
Archive format

log_archive_format The initialization parameter that specifies the format of archived log filenames.
log_ Archived log filenames will start with these characters.
%s Places the log sequence number into the name.
%t Places the thread number into the name.
The numbers 387-389 The log sequence number as it will appear in the actual filename.
The three "1"s The thread number as it will appear in the actual filename.
%T The thread number in a fixed-width format.
%S The log sequence number in a fixed-width format.
The three 001s The thread number appears as 001.
00387 - 00389 The log sequence numbers also appear with leading zeros.

In Oracle 19c, the naming convention for archived log files is controlled by the `LOG_ARCHIVE_FORMAT` parameter. This parameter specifies the format of the archived redo log filenames.
Default Naming Convention
The default naming pattern for archived log files is: 
log_archive_format = 'ARC%S_%R.%T'

Where:
  • %S - Log sequence number (zero-filled)
  • %R - Resetlogs ID (ensures uniqueness after database resets)
  • %T - Thread number

Example File Name: For a database with sequence number 1234, resetlogs ID 987654321, and thread 1, the archived log filename would be: ARC01234_987654321.001
Customization: You can modify `LOG_ARCHIVE_FORMAT` to suit your requirements. A common format is: 
log_archive_format = 'arch_%t_%s_%r.log'
Where:
  • %t - Thread number
  • %s - Log sequence number
  • %r - Resetlogs ID
Example: 
arch_1_1234_987654321.log
### Setting the Parameter: Modify `LOG_ARCHIVE_FORMAT` in `sqlplus`: 
ALTER SYSTEM SET log_archive_format = 'arch_%t_%s_%r.log' SCOPE=SPFILE;
Then, restart the database for changes to take effect: 
SHUTDOWN IMMEDIATE;
STARTUP;

Additional Considerations:
  • The archive log destination is defined by LOG_ARCHIVE_DEST_n parameters.
  • Ensure that the format includes a unique component (e.g., %r for Resetlogs ID) to prevent overwriting files after a database reset.

In the next lesson, you will learn how to enable automatic archiving so that you don't have to archive each log file manually.

Naming Archive LogFiles - Exercise

Naming Archive LogFiles - Exercise
For an exercise, work through the process of placing the COIN database into archivelog mode.

SEMrush Software