An archived redo log file is a copy of a redo log file before it is overwritten by new redo information. Because the online redo log files are reused in a circular fashion, you have no way of bringing a backup of a datafile up to the latest committed transaction unless you configure the database in ARCHIVELOG mode.
The process of copying is called archiving. ARCn does this archiving. By archiving the redo log files, you can use them later to recover a database, update a standby database, or use the LogMiner utility to audit the database activities.
When an online redo log file is full, and LGWR starts writing to the next redo log file, ARCn copies the completed redo log file to the archive destination. It is possible to specify more than one archive destination. The LGWR process waits for the ARCn process to complete the copy operation before overwriting any online redo log file. As with LGWR, the failure of one of the ARCn backup processes will cause instance failure, but no committed transactions will be lost because the "Commit complete" message is not returned to the user or calling program until LGWR successfully records the transaction in the online redo log file group.
Archive Logging Space Issues After you configure the database for ARCHIVELOG mode, your job is only half complete. You need to continually make sure that there is enough room for the archived log files, otherwise the database will hang. At least once in every DBA’s career, he or she will get a phone call from some users saying that the database has "crashed," while other users are still using the database. It’s not until you check the alert log that you discover the archiving process cannot find disk space for a newly filled log file in the archiving destinations.
There should be enough space available for online archived redo log files to recover and roll forward from the last full backup of each datafile that is also online; the remaining archived logs and any previous datafile backups can be moved to another disk or to tape.
Remembering your zero transaction loss strategy (which should be every DBA’s strategy), make sure that you do not misplace or delete an archived log file before it is backed up to tape, otherwise you will not be able to perform a complete recovery due to a media failure.
If you use RMAN and the Flash Recovery area for all of your backup files, then you can further automate this process by directing RMAN to maintain enough backups to satisfy a recovery window policy (number of days) or a redundancy policy (multiple copies of each backup). Once an archived log or other backup file is no longer needed for the policy, the files are automatically deleted from the Flash Recovery area. When the archiver process is copying the redo log files to another destination, the database is said to be in ARCHIVELOG mode. If archiving is not enabled, the database is said to be in NOARCHIVELOG mode. In production systems, you cannot afford to lose data and should therefore run the database in ARCHIVELOG mode so that in the event of a failure, you can recover the database to the time of failure or to a point in time. You can achieve this ability to recover by restoring the database backup and applying the database changes by using the archived log files.
You specify the archive destination in the initialization parameter file. To change the archive destination parameters during normal database operation, you use the ALTER SYSTEM command. Here are some of the parameters associated with archive log destinations and the archiver process:
LOG_ARCHIVE_DEST_n Using this parameter, you can specify at most 10 archiving
destinations. These locations can be on the local machine or on a remote machine
where the standby database is located. The syntax for specifying this parameter in the
initialization file is as follows:
LOG_ARCHIVE_DEST_n = "null_string" | ((SERVICE = tnsnames_name |
LOCATION = ‘directory_name’)
[MANDATORY | OPTIONAL]
[REOPEN [= integer]])
LOG_ARCHIVE_DEST_1 = ((LOCATION=’/archive/MYDB01’) MANDATORY REOPEN = 60) specifies a location for the archive log files on the local machine at /archive/MYDB01.
The MANDATORY clause specifies that writing to this location must succeed. The REOPEN clause specifies when the next attempt to write to this location should be made, when the first attempt did not succeed. The default value is 300 seconds.
Here is another example, which applies the archive logs to a standby database on a remote computer.
LOG_ARCHIVE_DEST_2 = (SERVICE=STDBY01) OPTIONAL REOPEN;
Here STDBY01 is the Oracle Net connect string used to connect to the remote database. Because writing is optional, the database activity continues even if ARCn could not write the archive log file. It tries the writing operation again because the REOPEN clause is specified.
You can also use the EM Database Control web pages to configure the log filenaming and destinations by clicking Configure Recovery Settings in the Maintenance tab. The first destination is on the file system at /u09/oradata/arch01.
Destination number 10 is the Flash Recovery area using the string USE_DB_RECOVERY_FILE_DEST.
LOG_ARCHIVE_MIN_SUCCEED_DEST This parameter specifies the number of destinations that the ARCn process should successfully write at a minimum to proceed with overwriting the online redo log files. The default value of this parameter is 1. This parameter cannot exceed the total number of enabled destinations. If this parameter value is less than the number of MANDATORY destinations, the parameter is ignored.
LOG_ARCHIVE_FORMAT This parameter specifies the format in which to write the
filename of the archived redo log files. To ensure that the log files are not overwritten,
you use predefined substitution variables to construct the name of each archived redo
log file. You can provide a text string and any of the predefined substitution variables.
The variables are as follows:
%s Log sequence number
%t Thread number
%r Resetlogs ID: ensures uniqueness even after using advanced recovery techniques that resets the log sequence numbers
%d Database ID
The format you provide must include at least %s, %t, and %r. If you use the same archived redo log location for multiple databases, you must also use %d.
Specifying these parameters does not start writing the archive log files. To enable archiving of the redo log files, place the database in ARCHIVELOG mode. You can specify the ARCHIVELOG clause while creating the database. However, you might prefer to create the database first and then enable ARCHIVELOG mode. To enable ARCHIVELOG mode, follow these steps:
To disable ARCHIVELOG mode, follow these steps:
The dynamic performance view V$DATABASE tells you whether you are in ARCHIVELOG mode, as can be seen in this query:
SQL> select dbid, name, created, log_mode from v$database;
DBID NAME CREATED LOG_MODE
--------- -------- -------- ------------
1387044942 ORD 03-MAR-04 ARCHIVELOG
1 row selected.