Solved

alert log showing cannot allocate a new log

Posted on 2011-09-26
9
657 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
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.

 
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
 

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.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 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 configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

773 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