Archived Redo Log Files

Filled redo log files can, optionally, be archived. One of the most important decisions that any DBA has to make is whether to configure the database to operate in ARCHIVELOG mode or NOARCHIVELOG mode.

NOARCHIVELOG Mode

If your database is running in NOARCHIVELOG mode, as the online redo log files are cycled, they are overwritten automatically. LGWR does not overwrite the redo log group until a checkpoint has been completed, but it does not have to incur the added overhead of having to allow the archiving of the redo log groups before the groups can be overwritten. This limits the amount of overhead that has to occur on a log switch.

Typically, you only want to run a database in NOARVHIVELOG mode if you are sure that you can recover the database in a reasonable amount of time from existing backups without significant data loss. Usually, this is in a data warehouse environment where there is little change other than bulk loads.

ARCHIVELOG Mode

ARCHIVELOG mode is when the database is configured to archive filled and inactive groups of online redo logs to an alternative location. There are two basic advantages to archiving redo log files and running your database in ARCHIVELOG mode: backup and recovery. All the changes made to the database are recorded in the online redo log files. This means that you can physically back up the database and all the archived redo logs and recover the database to the most recently committed transaction, or to any point in time without losing a single committed transaction.

There are two reasons that a database should be run in ARCHIVELOG mode:

• A full database backup can be performed while the database is operational if the database is in ARCHIVELOG mode.

• A database backup together with the online archived redo log files guarantee that you can recover your database to the last committed transaction.

You can archive redo log files either automatically or manually. For normal operation of the database, it is recommended that you allow the redo log files to be automatically archived and only manually archive a redo log file in special circumstances (for example, immediately before completing a hot backup).

LOG_ARCHIVE_START

The LOG_ARCHIVE_START initialization parameter controls whether archiving should be automatic when the instance starts up. Setting this parameter to trUE indicates that you want archiving to be automatic and causes the ARCn background process to initiate the archiving of filled online redo log groups every time there is a log switch. Setting LOG_ARCHIVE_START to FALSE (the default value) indicates that the DBA will archive the filled redo log files manually (in an active database, this can become a full time job). The DBA must manually execute a command each time he wants to archive an online redo log file and can archive all of them at once, or individually.

Archiving of redo log files is accomplished through the ARCn (Archiver) background process, or it is done manually through SQL statements.

To manually archive the redo log files, you would simply enter the following command:

 ALTER SYSTEM ARCHIVE LOG ALL; 

An entry is made in the control file every time an online redo log is archived. This entry contains the archive log name, the log sequence number, and the high and low System Change Number for the online redo log that has just been archived.

When the database is running in ARCHIVELOG mode, a filled online redo log file cannot be reused until a checkpoint has been taken and the redo log file has been backed up by the ARCn process. If the ARCn process cannot complete the archival process, and all the online redo log files fill up, the database will not crash but will not allow any more processing to be done until the ARCn process has started back up and cleared at least one online redo log file so that a log switch can occur.

Archived redo log files can be multiplexed, but all archived redo log files must be maintained by the DBA. This means that it is up to you to make sure that the directories don't fill up, that the files are protected from deliberate or accidental tampering, and that they are available whenever they are needed for instance and database recovery or restoration.

Archiving is covered in considerable detail in preparation for the OCP DBA Fundamentals II exam.

Archiver Information in the Data Dictionary

Information on the archive logs can be obtained by querying V$INSTANCE as shown in the following example:

 SELECT archiver FROM V$INSTANCE; 

Because we already know that we can get most any information from OEM, it seems logical to assume that we can get Archiver information from there, as well. Let's see how.

Obtaining Archiver Information from OEM

If you want to use OEM to obtain information about archived redo log files, you can open a launched console and expand your chosen database from the Databases folder. Expand the Instance in the navigator tree and click Configuration. Click on the Recovery tab in the detail side of the Console to obtain full information on the archived redo log files.