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='E:\oracle';
System altered.
SQL> alter system set log_archive_format='test_%s_%t_%r.arch' scope=spfile;
System altered.
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');
NAME VALUE
------------------------------ ------------------------------
log_archive_dest E:\oracle
log_archive_format test_%s_%t_%r.arch
If we do above settings all our archive log files will go into directory E:\oracle and format will be test_%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.
