Hi all!
Just a curiosity: Have you ever noticed in a database alert log the occourance of the following message for every logfile switch:
Thread 1 cannot allocate new log, sequence 9281 Private strand flush not complete Current log# 5 seq# 9280 mem# 0: /db/u5001/oradata/GREPORADB/redo05a.log Thread 1 advanced to log sequence 9281 (LGWR switch) Current log# 6 seq# 9281 mem# 0: /db/u5001/oradata/GREPORADB/redo06a.log
It happens because before every switch of logfile all private strands have to be flushed to current log.
It’s well described by the docs Alert Log Messages: Private Strand Flush Not Complete (Doc ID 372557.1) and Manual Log Switching Causing “Thread 1 Cannot Allocate New Log” Message in the Alert Log (Doc ID 435887.1).
The unpublished Bug 5241081 says:
“Technically all the strands need to be flushed when the log switch is being initiated and hence the messages for checkpoint not complete and private strand flush not complete are synonymous. The crux is that the strand still have transactions active which need to be flushed before this redo can be overwritten, would recommend letting Oracle perform the checkpointing by default and tune fast_start_mttr_target to achieve what one is looking for.”
So, it’s an expected behavior and normal to transactional environments, don’t worry! 🙂
It’s simple to be reproduced too… Take a look:
session1> update teste set a=5 where a=2; 1 row updated. session2> select 2 t1.sid, 3 t1.username, 4 t2.xidusn, 5 t2.used_urec, 6 t2.used_ublk 7 from 8 v$session t1, 9 v$transaction t2 10 where 11 t1.saddr = t2.ses_addr; SID USERNAME XIDUSN USED_UREC USED_UBLK ---------- ------------------------------ ---------- ---------- ---------- 304 MBOESING 4 1 1 session2> alter system switch logfile; System altered.
So, there is an uncommited session, how it looks in alert log?
Thread 1 cannot allocate new log, sequence 9289 Private strand flush not complete Current log# 4 seq# 9288 mem# 0: /db/u5001/oradata/GREPORADB/redo04a.log Thread 1 advanced to log sequence 9289 (LGWR switch) Current log# 5 seq# 9289 mem# 0: /db/u5001/oradata/GREPORADB/redo05a.log
Ok! The expected behavior. Now let’s commit the transaction and repeat the process:
session1> commit; Commit complete. session2> select 2 t1.sid, 3 t1.username, 4 t2.xidusn, 5 t2.used_urec, 6 t2.used_ublk 7 from 8 v$session t1, 9 v$transaction t2 10 where 11 t1.saddr = t2.ses_addr; no rows selected session2> alter system switch logfile; System altered.
And the alert log:
Thread 1 advanced to log sequence 9290 (LGWR switch) Current log# 6 seq# 9290 mem# 0: /db/u5001/oradata/GREPORADB/redo06a.log
Have a nice week!
Matheus.