Alert Log: “Private Strand Flush Not Complete” on Logfile Switch

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s