3

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.

1

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.

1

I think 20 - 30 minutes switch is ok, unless you're seeing waitings caused for your log switches you should be ok with the current size. Check this post https://asktom.oracle.com/pls/apex/asktom.search?tag=size-redo-logs.

On the other side I'll strongly recommend to Multiplex your redo logs, BUT in separate devices. In your current situation if the Drive D becomes unusable you will loss all your changes which are not in archive logs, redo logs are one of the most important things for your recovery.

  • 1
    I would add my second to @dbamex emphasis to multiplex your online redo. It is often said that redo is the achilles heel of oracle. If you loose an online redo log through corruption or file failure (or human error!) and they are not properly multiplexed, you loose that data. As for human error, I twice had an SA delete redo "to recover disk space - they were only log files!" If I had not multiplexed them to seperate disks, I would have been a world of hurt. – EdStevens Dec 13 '18 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.