LOG_ARCHIVE_FORMAT in Oracle

If you have enabled archive log mode in your database then LOG_ARCHIVE_FORMAT parameter will come into role. If your database is in archivelog mode then redo log files will be archived and the parameter LOG_ARCHIVE_FORMAT determines the name of the archived log files.

LOG_ARCHIVE_FORMAT uses a text string and variables to specify the format of the archived files.

The following variables can be used with the LOG_ARCHIVE_FORMAT

1) %s : log sequence number

2) %S : log sequence number, zero filled

3) %t : thread number

4) %T : thread number, zero filled

5) %a : activation ID

6) %d : database ID

7) %r : resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database

Using uppercase letters for the variables (for example, %S) causes the value to be fixed length and padded to the left with zeros.

Following is an example of how we can set LOG_ARCHIVE_FORMAT in a database.

SQL> ALTER SYSTEM SET log_archive_format=’VSPRODP_%s_%t_%r.arch’ SCOPE=spfile;

System altered.

Note that, neither LOG_ARCHIVE_DEST nor LOG_ARCHIVE_FORMAT have to be complete file or directory specifiers themselves; they only need to form a valid file path after the variables are substituted into LOG_ARCHIVE_FORMAT and the two parameters are concatenated together.

For example, we are setting the following values to log_archive_dest and log_archive_format parameters.

SQL> alter system set log_archive_dest='/u01/oradata/arch1';

SQL> alter system set log_archive_format='VSPRODP_%s_%t_%r.arch' scope=spfile;

SQL> col name for a30
SQL> col value for a30

SQL> select name, value from v$spparameter where name in 

     ('log_archive_dest','log_archive_format');

If we do above settings all our archive log files will go into directory

/u01/oradata/arch1  and format will be VSPRODP_%s_%t_%r.arch.

Note that, in the LOG_ARCHIVE_FORMAT %s, %t and %r are mandatory variables.

 If we dont specify anyone of them it while starting up oracle it will throw error.