Solved

alert log showing cannot allocate a new log

Posted on 2011-09-26
9
660 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 35

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

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
 

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 35

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 35

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

732 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