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='E:\oracle';

System altered.

SQL> alter system set log_archive_format='arju_%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             arju_%s_%t_%r.arch 

If we do above settings all our archive log files will go into directory E:\oracle and format will be arju_%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 http://arjudba.blogspot.com/2008/04/ora-32004-obsolete-andor-deprecated.html.
Related Documents
http://arjudba.blogspot.com/2010/01/ora-16014-ora-00312-ora-16038-ora-19809.html
http://arjudba.blogspot.com/2009/12/enable-archive-log-mode-for-rac.html
http://arjudba.blogspot.com/2009/12/database-archival-exercises.html
http://arjudba.blogspot.com/2008/07/archiving-not-possible-no-primary.html
http://arjudba.blogspot.com/2008/05/recovering-database-in-noarchivelog.html
http://arjudba.blogspot.com/2008/05/user-managed-consistent-backup-in.html
http://arjudba.blogspot.com/2008/05/user-managed-hot-backup-of-oracle.html
http://arjudba.blogspot.com/2008/05/what-will-be-archived-redo-log.html
http://arjudba.blogspot.com/2008/04/ora-16018-and-ora-16019-logarchivedest.html
http://arjudba.blogspot.com/2008/04/ora-00257-archiver-error-connect.html