In this Document

Applies to:

Purpose

This document is intended to provide a standard approach to resolve issues with Redo logs and Archiving 

Troubleshooting Steps

Concepts

Redo Logs consist of two or more pre-allocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has associated online redo logs to protect the database in case of an instance failure.
Redo log files are filled with redo records. A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.
Redo entries record data that you can use to re-construct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.

The Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log. The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving.
An archived redo log file is a copy of one of the filled members of a redo log group. It includes the redo entries and the unique log sequence number of the identical member of the redo log group.

Common Issues related to Redo logs and Archiving

Created Archived Logs are smaller in size than the related Redolog files

Excessive Generation of Archive Logs

Frequent Log Switches

These are the most relevant factors:

1. log_buffer size
If this is not explicitly set by the DBA then we use a default; at instance startup oracle calculates the number of shared redo strands as ncpus/16, and the size of each strand is 128Kb * ncpus (where ncpus is the number of CPUs in the system). The log buffersize is the number of stands multiplied by the strand size.

2. System load
Initially only one redo strand is used, ie the number of "active" redo strands is 1, and all the processes copy their redo into that one strand. When/if there is contention for that strand then the number of active redo strands is raised to 2. As contention for the active strands increases, the number of active strands increases. The maxmum possible number of active redo strands is the number of strands initially allocated in the log buffer.
(This feature is called "dynamic strands", and there is a hidden parameter to disable it which then allows processes to use all the strands from the outset).

3. Log file size
It may not always be possible to provide a specific size recommendation for redo log files, but redo log files in the range of a hundred megabytes to a few gigabytes are considered reasonable. Size your online redo log files according to the amount of redo your system generates. A rough guide is to switch logs at most once every twenty minutes."

4. the logfile space reservation algorithm
When oracle switches into a new online redo logfile, all the log buffer redo strand memory is "mapped" to the logfile space. If the logfile is larger than the log buffer then each strand will map/reserve its strand size worth of logfile space, and the remaining logfile space (the "log residue") is still available.
If the logfile is smaller than the log buffer, then the whole logfile space is divided/mapped/reserved equally among all the strands, and there is no unreserved space (ie no log residue).
When any process fills a strand such that all the reserved underlying logfile space for that strand is used, AND there is no log residue, then a log switch is scheduled.

Checkpoint Not Complete

This message indicates that Oracle wants to reuse a redo log file, but the current checkpoint position is still in that log. In this case, Oracle must wait until the checkpoint position passes that log. Because the incremental checkpoint target never lags the current log tail by more than 90% of the smallest log file size, this situation may be encountered if DBWR writes too slowly, or if a log switch happens before the log is completely full,or if log file sizes are too small.
When the database waits on checkpoints,redo generation is stopped until the log switch is done.

ORA- errors related to the Redo Logs and Archiving

ORA-336 log file size blocks is less than minimum blocks

Cause: The redo log file size specified in the CREATE DATABASE statement is too small.
Action: Increase the redo log file size and re-enter the statement.

ORA-337 log file does not exist and no size specified

Cause: The system could not add a redo log file because it could not find an existing file or a size for creating the file.
Action: Specify a size for the redo log file, then retry the operation.

ORA-1198 must specify size for log file if RESETLOGS

Cause: File sizes must be given for all redo log files if doing a CREATE CONTROLFILE with the RESETLOGS option.
Action: Retry the command with the appropriate redo log file size.

ORA-00338: log of thread is more recent than control file
ORA-00312: online log thread : 'redo.log'

Cause:  The control file change sequence number in the log file is greater than the number in the control file. This implies that the wrong control file is being used. Note that repeatedly causing this error can make it stop happening without correcting the real problem.
Action: Use the current control file or do backup control file recovery to make the control file current. Be sure to follow all restrictions on doing a backup control file recovery.

Further Diagnosis

If you were not able to resolve the issue with the details provided in this document, please raise a Service Request providing the following information:

1) Alert.log file

2) Output of the below queries:

SQL> SELECT distinct(to_char((bytes*0.000001),'9990.999')) size_mb FROM v$log;
SQL> Select * from v$logfile;
SQL> show parameter archive;
SQL> archive log list

References