Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

alert log showing cannot allocate a new log

Posted on 2011-09-26
9
Medium Priority
?
665 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
Independent Software Vendors: 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 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 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

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!

Question has a verified solution.

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

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

636 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