Solved

alert log showing cannot allocate a new log

Posted on 2011-09-26
9
653 Views
Last Modified: 2012-06-27
Hi,

Users are having problem running an application...too slow...In the alert.log file, it is showing Thread 1 cannot allocate a new log.  Initially, there are 5 redo logs group with 3 members each log with 100MB.  Then I increase the log size to 200MB; but did not help.  It is still showing cannot allocate a new log file.

I tried to add another new group but cannot create saying cannot allocate new group.  If I add another member it says, it has reached max.

We are on Oracle 9.2.0.6.

Any help is greatly appreciated.

Thanks.
Pacita
0
Comment
Question by:Pacita_Tibay
  • 5
  • 4
9 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 36601961
Is there more to the message in the alert log?  My guess is that your archive log destination is full.

Backup and remove some of the logs and the issue should clear itself.  Then you can go back and find out what caused the issue.

Typically, some unexpected major load or an unexpected file on the archive log file system is the culprit.
0
 

Author Comment

by:Pacita_Tibay
ID: 36602002
Database is NO ARCHIVE LOG.
0
 

Author Comment

by:Pacita_Tibay
ID: 36602048
Also, in the alert.log it says SMON: Parallel transaction recovery tried.
0
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 36602055
Full archive log destination is the most common cause of the problem.

Without archives, then you are waiting for a checkpoint.  I would have a look at this Ask Tom article:

http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:69012348056
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Pacita_Tibay
ID: 36602271
It looks like my redo log files are too big?  Can I add more groups than the 5 that I have? I'd like to decrease the sizes but I think I need to add more groups.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 36709550
If you are in noarchive log mode, then you really don't need a lot of groups.  2 or 3 small log groups should be all you need.  As long as the checkpoint on one group can complete before the second group fills and needs to switch, you should have no problems.  You do not need to worry about time to archive the log to another destination.

Are there very large (or a very large number) of write transactions going on?
0
 

Author Comment

by:Pacita_Tibay
ID: 36709597
One working table has about 4M rows which the application is trying the clean as part of the process that the user is doing.  So what I did is exported the table, drop it and re-created it.  Data are not needed anyway.  Even if I delete the records or truncate the table, I may encounter the same issue unable to create a new log.  Now the application is working fine for our users.

Thanks all for your input.
0
 

Author Closing Comment

by:Pacita_Tibay
ID: 36709611
I resolved our issue by dropping, creating the table that was causing the application to locked up.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 36709732
Truncate would not cause an issue with checkpoint.  Truncate moves the high water mark and does not actually delete the records individually so very little redo is generated.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now