asked on

alert log showing cannot allocate a new log

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

johnsone

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.

Pacita_Tibay

ASKER

Database is NO ARCHIVE LOG.

Pacita_Tibay

ASKER

Also, in the alert.log it says SMON: Parallel transaction recovery tried.

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pacita_Tibay

ASKER

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.

johnsone

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?

Pacita_Tibay

ASKER

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.

Pacita_Tibay

ASKER

I resolved our issue by dropping, creating the table that was causing the application to locked up.

johnsone

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.