| Init.ora Parameters Related To Archiving |
LOG_ARCHIVE_DEST_n, LOG_ARCHIVE_DEST, LOG_ARCHIVE_DUPLEX_DEST
You can specify a single destination or multiple destinations for the archived redo logs. Oracle recommends archiving your logs to different disks to guard against file corruption and media failure.
Specify the number of locations for your archived logs by setting either of two mutually exclusive sets of initialization parameters:
The first method is to use the LOG_ARCHIVE_DEST_n parameter to specify from one to five different destinations for archival. Each numerically-suffixed parameter uniquely identifies an individual destination, for example, LOG_ARCHIVE_DEST_1, LOG_ARCHIVE_DEST_2, etc. Use the LOCATION keyword to specify a pathname or the SERVICE keyword to specify a net service name (for use in conjunction with a standby database).
The second method, which allows you to specify a maximum of two locations, is to use the LOG_ARCHIVE_DEST parameter to specify a primary archive destination and the LOG_ARCHIVE_DUPLEX_DEST to determine an optional secondary location. Whenever Oracle archives a redo log, it archives it to every destination specified by either set of parameters.
To override the destination that this parameter specifies, either specify a different destination for manual archiving or use the Server Manager command ARCHIVE LOG START filespec for automatic archiving, where filespec is the new archive destination. To permanently change the destination, use the command ALTER SYSTEM SET LOG_ARCHIVE_DEST = filespec, where filespec is the new archive destination.
LOG_ARCHIVE_FORMATApplicable only if 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
%t thread number
Using uppercase letters (for example, %S) for the variables causes the value to be a fixed length padded to the left with zeros. The following is an example of specifying the archive redo log filename format:
LOG_ARCHIVE_FORMAT = "LOG%s_%t.ARC"
LOG_ARCHIVE_START
**NOTE:
It is a VERY common mistake to set this parameter to TRUE and assume that this puts the database in ARCHIVELOG mode.
This is NOT the case.
LOG_ARCHIVE_START is only applicable only when you use the database in ARCHIVELOG mode.
LOG_ARCHIVE_START indicates whether archiving should be automatic or manual when the instance starts up. TRUE indicates that archiving is automatic. FALSE indicates that the DBA will archive filled redo log files manually. (The Server Manager command ARCHIVE LOG START or STOP overrides this parameter.)
In ARCHIVELOG mode, if all online redo log files fill without being archived, an error message is issued, and instance operations are suspended until the necessary archiving is performed. This delay is more likely if you use manual archiving. You can reduce its likelihood by increasing the number of online redo log files.
To use ARCHIVELOG mode while creating a database, set this parameter to TRUE. Normally, a database is created in NOARCHIVELOG mode and then altered to ARCHIVELOG mode after creation.
Limits the number of redo records between the most recent redo record and the checkpoint.
Description:
The number of newly filled redo log file blocks needed to trigger a checkpoint. Regardless of this value, a checkpoint always occurs when switching from one online redo log file to another. If the value exceeds the actual redo log file size, checkpoints occur only when switching logs.
Extremely frequent checkpointing can cause excessive writes to disk, possibly impacting transaction performance. In addition, if the intervals are so close together that the interval checkpoint requests are arriving at a rate faster than the rate at which Oracle can satisfy these requests, Oracle can choose to ignore some of these requests in order to avoid excessive interval checkpointing activity.
The number of times DBWR has been notified to do a checkpoint for a given instance is shown in the <Statistic:DBWR_checkpoints>.
Note that specifying a value of 0 (zero) for the interval might cause interval checkpoints to be initiated very frequently since a new request will be started even if a single redo log buffer is written since the last request was initiated. Hence, setting the value to 0 is not recommended.
Example settings:
Assuming Redo_log_blocks are 512 bytes.
So:
LOG_CHECKPOINT_INTERVAL Approximate
Size in Megabytes
2000 1M
10000 5M
20000 10M
500000 250M
A Checkpoint is where we flush dirty (modified) buffers to disk from the buffer cache to ensure that blocks on disk match up with the change level in redo. A checkpoint ensures that all redo changes before the checkpoint no longer need to be applied. Processes have to wait for a checkpoint to complete before they can continue.
Set the initialization parameter
LOG_CHECKPOINT_INTERVAL to a value n (where n is an integer) to require that the checkpoint position never follow the most recent redo block by more than n blocks. In other words, at most n redo blocks can exist between the checkpoint position and the last block written to the redo log. In effect, you are limiting the amount of redo blocks that can exist between the checkpoint and the end of the log.
Oracle limits the maximum value of
LOG_CHECKPOINT_INTERVAL to 90% of the smallest log to ensure that the checkpoint advances into the current log before that log fills and a log switch is attempted.
LOG_CHECKPOINT_INTERVAL
LOG_CHECKPOINT_TIMEOUT
Set the initialization parameter
LOG_CHECKPOINT_TIMEOUT to a value n (where n is an integer) to require that the latest checkpoint position follow the most recent redo block by no more than n seconds. In other words, at most, n seconds worth of logging activity can occur between the most recent checkpoint position and the end of the redo log. This forces the checkpoint position to keep pace with the most recent redo block
You can also interpret
LOG_CHECKPOINT_TIMEOUT as specifying an upper bound on the time a buffer can be dirty in the cache before DBWn must write it to disk. For example, if you set LOG_CHECKPOINT_TIMEOUT to 60, then no buffers remain dirty in the cache for more than 60 seconds. The default value for LOG_CHECKPOINT_TIMEOUT is 1800.