Home » RDBMS Server » Server Administration » weird redo log behaviour?
weird redo log behaviour? [message #232356] Fri, 20 April 2007 04:36 Go to next message
meggens
Messages: 57
Registered: February 2007
Location: Netherlands
Member
Hi

I'm facing following problem on two oracle (version 10.2.0.2.0) databases.

It seems after a redo log switch the redo logs remain active for a long time. Futhermore when al the log files are active and i perform a logswitch I get this message in the alerlog:

Thread 1 cannot allocate new log, sequence 279
Private strand flush not complete

This is a small oracle database with only and it is not heavy loaded. The database is running in archivelog mode. During normal daytime V$log looks like this:

SQL> select group#, status, bytes from v$log;
GROUP# STATUS BYTES
---------- ---------------- ----------
1 CURRENT 157286400
2 INACTIVE 157286400
3 INACTIVE 157286400
4 INACTIVE 157286400

So this looks fine.

When I perform a switch logfile 3 times with a 5 minutes interval
it looks like this and the message ' 1 cannot allocate new log, sequence 279
Private strand flush not complete' appears in the alert log.

SQL> select group#, status, bytes from v$log;

GROUP# STATUS BYTES
---------- ---------------- ----------
1 ACTIVE 157286400
2 ACTIVE 157286400
3 ACTIVE 157286400
4 CURRENT 157286400

I don't think it is related to io, iowait is low and the system is idle most of the time.

I check on another system (oracle 10.2.0.1) and when i perform a alter system switch logfile on this system it only takes a second
to change the status from ACTIVE to INACTIVE.

Anybody seen this behaviour before?

regards,
Martin



Re: weird redo log behaviour? [message #232367 is a reply to message #232356] Fri, 20 April 2007 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How long a redo log remains active depends on how much job DBWR has to do and how long it takes to do it.
So check DBWR waits and average write time.
Maybe you have many active process and DBWR doesn't get sufficient CPU time slice to do his job in time.
In this case, add multiple dbwr.

In addition, 3 log switches in 5 minutes is too much, increase log file size.

Regards
Michel
Re: weird redo log behaviour? [message #232420 is a reply to message #232367] Fri, 20 April 2007 08:01 Go to previous messageGo to next message
meggens
Messages: 57
Registered: February 2007
Location: Netherlands
Member
Michel,

Thanks for your reply.
I made the logswitches myself with 'alter system switch logfile' just to demonstrate.

how should I check DBWR wait and average write time and what are resonable values?

The machine is idle most of the time so I think DBWR get enought cpu but i'm curious about the wait times.

thanks again.

regards,
martin


Re: weird redo log behaviour? [message #232445 is a reply to message #232420] Fri, 20 April 2007 09:54 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DBWR waits: select * from v$session_event where sid=<dbwr sid>
average write time: v$filestat

Regards
Michel
Previous Topic: about error ORA-01591
Next Topic: data buffer pool
Goto Forum:
  


Current Time: Mon Dec 05 06:42:43 CST 2016

Total time taken to generate the page: 0.14404 seconds