How to justify making changes to the redo logs

oracleoracle-11gtransaction-log

I don't spend a lot of time with my Oracle environment. I'm more of a SQL Server guy. I need to look into the alert logs to check for correlation of an application error. What I found instead was collections of this:

Thread 1 cannot allocate new log, sequence 947
Checkpoint not complete
  Current log# 1 seq# 946 mem# 0: D:\ORACLE\DATA\TRAXTRAI\ONLINELOG\O1_MF_1_G0B8NV7H_.LOG
  Current log# 1 seq# 946 mem# 1: D:\ORACLE\RECOVERY\TRAXTRAI\ONLINELOG\O1_MF_1_G0B8NVLP_.LOG
Thread 1 cannot allocate new log, sequence 947
Private strand flush not complete
  Current log# 1 seq# 946 mem# 0: D:\ORACLE\DATA\TRAXTRAI\ONLINELOG\O1_MF_1_G0B8NV7H_.LOG

These are happening, in my test environment, rather frequently. At least once every 20 minutes for the last several years according to the log. I have read that I can ignore these but I was see more often is that I use this as motivation to play with my redo log settings/framework. It's just that I don't know exactly what I need to do with them. My test environment is not running archive logs which is supposed to play a factor in this. (My production environment is doing this more frequently so I am using this to try and get a handle on my solution approach.) I am sure this has a performance impact and should be looked at.

This is what I know so far…

SELECT name,value
  FROM v$sysstat 
  where name in ('redo entries','redo log space requests');

redo entries             30463497
redo log space requests  89

I also have this

select t1.group#, t1.bytes/1024/1024 as "Size(MB)", t2.member, t1.status 
from v$log t1, v$logfile t2 
where t1.group# = t2.group# ; 
GROUP SIZE  MEMBER                                                      STATUS 
  3   50    D:\ORACLE\DATA\TRAXTRAI\ONLINELOG\O1_MF_3_G0B8NW8F_.LOG     ACTIVE
  3   50    D:\ORACLE\RECOVERY\TRAXTRAI\ONLINELOG\O1_MF_3_G0B8NWFR_.LOG ACTIVE
  2   50    D:\ORACLE\DATA\TRAXTRAI\ONLINELOG\O1_MF_2_G0B8NVNN_.LOG     CURRENT
  2   50    D:\ORACLE\RECOVERY\TRAXTRAI\ONLINELOG\O1_MF_2_G0B8NVPM_.LOG CURRENT
  1   50    D:\ORACLE\DATA\TRAXTRAI\ONLINELOG\O1_MF_1_G0B8NV7H_.LOG     ACTIVE
  1   50    D:\ORACLE\RECOVERY\TRAXTRAI\ONLINELOG\O1_MF_1_G0B8NVLP_.LOG ACTIVE

50MB seems large enough based on other suggestions but I have also seen people suggest making them smaller or adding new ones. I am still making my way though Managing the Redo Log of the admin guide but I am not confident I will find the answers there.

How can I justify making changes to my redo tables? I know there is likely not enough information to make a suggestion from what little I provide so I basically need to know what else to get. I don't want to make guesses if there is something more I can do.

Best Answer

You don't necessarily need to duplex online redo. But if you have the disk space it should not hurt anything. You should probably add additional log groups. That will give your database more time to archive each online redo log before Oracle needs to use it again. Try adding 3 additional online redo log groups.