Changing Database To Archive Log Mode

To configure database to be run on Archive Mode is simply set following parametes

1. LOG_ARCHIVE_DEST
2. LOG_ARCHIVE_FORMAT
3. While database at mounted state issue command ALTER DATABASE ARCHIVELOG;

LOG_ARCHIVE_DEST is applicable only if you are running the database in ARCHIVELOG mode or are recovering a database from archived redo logs. LOG_ARCHIVE_DEST is incompatible with the LOG_ARCHIVE_DEST_n parameters, and must be defined as the null string ("") or (' ') when any LOG_ARCHIVE_DEST_n parameter has a value other than a null string. Use a text string to specify the default location and root of the disk file or tape device when archiving redo log files. (Archiving to tape is not supported onall operating systems.) The value cannot be a raw partition.

If LOG_ARCHIVE_DEST is not explicitly defined and all the LOG_ARCHIVE_DEST_n parameters have null string values, LOG_ARCHIVE_DEST is set to an operating system-specific default value on instance startup.

To override the destination that this parameter specifies, either specify a different destination for manual archiving or use the SQL*Plus statement ARCHIVE LOG START filespec for automatic archiving, where filespec is the new archive destination. To permanently change the destination, use the statement ALTER SYSTEM SET LOG_ARCHIVE_DEST = filespec, where filespec is the new archive destination.

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.

LOG_ARCHIVE_FORMAT is applicable only if you are using the redo log in ARCHIVELOG mode. Use a text string and variables to specify the default filename format when archiving redo log files. The string generated from this format is appended to the string specified in the LOG_ARCHIVE_DEST parameter.

The following variables can be used in the format:
%s log sequence number
%S log sequence number, zero filled
%t thread number
%T thread number, zero filled
%a activation ID
%d database ID
%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. An example of specifying the archive redo log filename format follows: LOG_ARCHIVE_FORMAT = 'log%t_%s_%r.arc'

Archive log file names must contain each of the elements %s (sequence), %t (thread), and %r (resetlogs ID) to ensure that all archive log file names are unique. If the LOG_ARCHIVE_FORMAT initialization parameter is set in the parameter file, then make sure the parameter value contains the %s, %t, and %r elements. Otherwise, the following error is displayed at the time of instance startup: ORA-19905: log_archive_format must contain %s, %t and %r

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.


1. To find out whether your database is archive mode or not archive you can issue following command

ARCHIVE LOG LIST;

Currently my database is No Archive Mode.

2. Set the value of LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT

ALTER SYSTEM SET log_archive_format = 'edba.blogspot_%t_%s_%r.arc' SCOPE = SPFILE;

ALTER SYSTEM SET log_archive_dest = '/datafiles/edba01/archived_log' SCOPE = SPFILE;

Again, please note that for the log_archive_format you must have at least %t, %s and %r in the file this is compulsory requirement.

After you have set the both parameter accordingly, you must start the database at mounted state.

3. Issue command ALTER DATABASE ARCHIVELOG; to enable the archiving.

After that you can open the database with ALTER DATABASE OPEN.

Note: To make the database no archive log the command is

ALTER DATABASE NOARCHIVELOG;

and to be executed at the database at mounted state.

4. Now we check the status with ARCHIVE LOG LIST.

The database is now in Archive Mode.


Addition Information.

The LOG_ARCHIVE_START parameter is old parameter that use to tell Oracle database to start the archival automatically each time database is started, however this parameter is no longer required, as you can see the LOG_ARCHIVE_START is FALSE but the information in ARCHIVE LOG LIST suggested that it is ENABLED.

To view the status of the archive log mode by querying dictionary view.

SELECT name, log_mode FROM v$database;