ORA-00338, ORA-00312 Errors Reported In Alert Log

ORA-00338, ORA-00312 Errors Reported In Alert Log

ORA-00338, ORA-00312 Errors Reported In Alert Log

Titleimage

Posted by Patrick Hamou on 2016:04:19 20:00:10

Applies To

Oracle Server - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

Symptoms

Below errors are repeatedly reported in the alert log of one RAC node. 
Note that the actual issue applies to RAC and non RAC databases.

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

 

Cause

The ORA-00338 normally indicates an incorrect control file may be used:

00338, 00000, "log %s of thread %s is more recent than control file"
// *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. Every attempt to open the database will advance the
//         control file change sequence number until it is great enough.
// *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.

The error is reported when the log sequence number is greater than the sequence number in the control file. The process that detects this, posts the entries to the alert log.

Given the normal cause of the error, please verify first that the correct control file is being used.

In this particular case the control file updates could not keep up with the rate of log switches, e.g. 5 to 6 per minute. The difference in log sequence numbers came from the excessive redo log switches that occurred prior to the error. Note that also "Log file switch (checkpoint incomplete)" messages were reported.

In this case, the customer mentioned that fast log switches were forced by the 'storage side backup settings', meaning their backup implementation/application issued fast 'alter system switch log file' commands.

Solution

After having verified the correct control file is being used, the issue can be resolved by reducing the fast explicit redo log switches.

In case the log switches is not forced manually or by an application (like in this case) then below checks/actions can be performed:

Check the redo log file size, make sure it is adequate to the workload. 

Below document shows an example for how to re-size redo log file
Note 1035935.6 - Example of How To Resize the Online Redo Logfiles

After adjusting the redo log file size, monitor your database alert log to determine the time between log switches. Oracle recommends redo log switch each 20~30 minutes.

NOTE:
You can use the V$INSTANCE_RECOVERY view column OPTIMAL_LOGFILE_SIZE to determine a recommended size for your online redo logs. This field shows the redo log file size in megabytes that is considered optimal based on the current setting of FAST_START_MTTR_TARGET. If this field consistently shows a value greater than the size of your smallest online log, then you should configure all your online logs to be at least this size.

Check which session is generating a lot of redo.

Below document shows how to find which session generates lot of redo or archive data: 
Note 167492.1 - SQL: How to Find Sessions Generating Lots of Redo or Archive logs

Posted by Patrick Hamou on 2016:04:19 20:00:10

Return to Blog