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 erro

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s