Improve company productivity with a Business Account.Sign Up

x
?
Solved

alert log showing cannot allocate a new log

Posted on 2011-09-26
9
Medium Priority
?
669 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 36

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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 36

Accepted Solution

by:
johnsone earned 1000 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 36

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 36

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

584 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