log file switch

log file switch

 

一： log file switch 说明

二： log file switch 官方文档

 

一： log file switch 说明

select event#, name, wait_class

  from v$event_name

 where name like 'log file switch%'

 order by 1;

log file switch (archiving needed)

log file switch (checkpoint incomplete)

log file switch (private strand flush incomplete)

log file switch completion

log file switch (clearing log file)

1 log file switch (archiving needed)

https://www.douban.com/group/topic/94934666/

在归档模式下，这个等待事件发生在在线日志切换（log file switch）时，需要切换的在线日志还没有被归档进程（ARCH）归档完毕的时候。

当在线日志文件切换到下一个日志时，需要确保下一个日志文件已经被归档进程归档完毕，否则不允许覆盖那个在线日志信息（否则会导致归档日志信息不完整）。

出现这样的等待事件，通常是由于某种原因导致ARCH 进程缓慢或死掉。

日志组循环写满以后，前一个日志归档尚未完成，出现等待；

LGWR不能切换到下一个日志组。所有提交请求都等待此事件。

出现等待事件可能原因：

(1)归档所在存储I/O性能差或出现故障

(2)归档进程(log_arcHive_max_processes)不足

(3)归档所在磁盘空间不足

(4)如果是远程归档，需要检查网络

建议：

先确定是无法归档还是归档缓慢的问题？

无法归档：检查告警日志，确定是什么原因导致无法归档，例如 归档目录空间不足等。

归档缓慢：分析归档缓慢的原因，可以考虑如下方式优化归档速度

1 将归档移动到更快的磁盘

2 增加归档进程数量(log_archive_max_processes)

3 增大或增多redo log file，给予归档更多的时间

2 log file switch (checkpoint incomplete)

Https://www.sohu.com/a/208336310_671058

当一个在线日志切换到下一个在线日志时，必须保证要切换到的在线日志上的记录的信息（比如一些脏数据块产生的 redo log）被写到磁盘上（checkpoint），

也就是在日志切换时，会触发检查点操作，dbwr进程会将内存中脏数据写入到磁盘。

这样做的原因是，如果一个在线日志文件的信息被覆盖，而依赖这些 redo 信息做恢复的数据块尚未被写到磁盘上（checkpoint），此时系统 down 掉的话，oracle 将没有办法进行实例恢复。

在 v$log 视图里记录了在线日志的状态。 通常来说，在线日志有三种状态。

--Active: 这个日志上面保护的信息还没有完成 checkpoint。

--Inactive： 这个日志上面保护的信息已完成 checkpoint。

--Current： 当前的日志。

如果系统中出现大量的 log file switch（checkpoint incomplete）等待事件，原因可能是日志文件太小或者日志组太少，所以解决的方法是，增加日志文件的大小或者增加日志组的数量。

出现等待事件可能原因：

(1) DBWR进程慢

增加DBWR进程数，使用更快的磁盘，

(2) REDO LOG日志太小或太少

增大或增加redo log

(3) 数据库出现大数据量的DML或DDL操作，通过logminer或AWR中TOP sql,Segments by DB Blocks Changes等信息定位是否有不正常的SQL在执行。

3. log file switch (private strand flush incomplete)

https://blog.csdn.net/ebay/article/details/43529149

log file switch(private strand flush incomplete)等待事件是10G后针对 IMU特性新增的等待事件，如果你开启了IMU，有时候就可能会遇到它。

DML执行时，后映像数据是由Server Process产生的。在IMU方式下，后映像数据会先被Server Process放到Private Stand Area，提交时刷新到Public Log Buffer，在由LGWR写进磁盘中的Redo File。

http://www.itpub.net/forum.PHP

https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=y4h9057u2_4&_afrLoop=333548650062841#SYMPTOM

Slow Running User Process And Top Database Wait Event Is 'log file switch (private strand flush incomplete)' (文档 ID 983473.1)

log file switch (private strand flush incomplete)等待事件可能是由BUG引起的

A user process is running very slow and the top database wait event is always 'log file switch (private strand flush incomplete)'.

AWR Report shows the following Top 5 Timed Events.

1. log file switch (private strand flush incomplete)

2. buffer busy waits

3. log file sync

4. db file sequential read

5. log file switch (checkpoint incomplete)

Unpublished Bug 5605290 which causes deadlocks between the CKPT and LGWR or DBWR processes, and can manifest itself in many ways.

Unpublished Bug 5605290 is fixed in the 10.2.0.4 patchset, so applying it will resolve this issue.  In addition there is a workaround available to prevent the problem until able to get the database patched up.

sqlplus / as sysdba
alter system set "_in_memory_undo" = FALSE scope=both;

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=345009836211265&id=372557.1&_adf.ctrl-state=s9gk1y6c4_77

Alert Log Messages: Private Strand Flush Not Complete (文档 ID 372557.1)

 

4.log file switch completion

Waiting for a log switch to complete.

Wait Time: 1 second

Parameters: None

 

5.log file switch (clearing log file)

Waiting for a log switch because the log is being cleared due to a CLEAR LOGFILE command or implicit clear logfile executed by recovery.

Wait Time: 1 second

Parameters: None

 

二： log file switch 官方文档

https://docs.oracle.com/cd/E11882_01/server.112/e40402/waitevents003.htm#REFRN00580

log file switch (archiving needed)

Waiting for a log switch because the log that the LGWR will be switching into has not been archived yet. Check the alert file to make sure that archiving has not stopped due to a failed archive write. To speed archiving, consider adding more archive processes or putting the archive files on striped disks.

Wait Time: 1 second

Parameters: None

log file switch (checkpoint incomplete)

Waiting for a log switch because the session cannot wrap into the next log. Wrapping cannot be perfORMed because the checkpoint for that log has not completed.

Wait Time: 1 second

Parameters: None

log file switch (clearing log file)

Waiting for a log switch because the log is being cleared due to a CLEAR LOGFILE command or implicit clear logfile executed by recovery.

Wait Time: 1 second

Parameters: None

log file switch (private strand flush incomplete)

User sessions trying to generate redo, wait on this event when LGWR waits for DBWR to complete flushing redo from IMU buffers into the log buffer; when DBWR is complete LGWR can then finish writing the current log, and then switch log files.

Wait Time: 1 second

Parameters: None

log file switch completion

Waiting for a log switch to complete.

Wait Time: 1 second

Parameters: None

 

https://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF94532

10.3.15  log file switch

There are two wait events commonly encountered:

log file switch (archiving needed)

log file switch (checkpoint incomplete)

In both of the events, the LGWR cannot switch into the next online redo log file. All the commit requests wait for this event.

10.3.15.1  Actions

For the log file switch (archiving needed) event, examine why the archiver cannot archive the logs in a timely fashion. It could be due to the following:

Archive destination is running out of free space.

Archiver is not able to read redo logs fast enough (contention with the LGWR).

Archiver is not able to write fast enough (contention on the archive destination, or not enough ARCH processes). If you have ruled out other possibilities (such as slow disks or a full archive destination) consider increasing the number of ARCn processes. The default is 2.

If you have mandatory remote shipped archive logs, check whether this process is slowing down because of network delays or the write is not completing because of errors.

Depending on the nature of bottleneck, you might need to Redistribute I/O or add more space to the archive destination to alleviate the problem.

For the log file switch (checkpoint incomplete) event:

Check if DBWR is slow, possibly due to an overloaded or slow I/O system. Check the DBWR write times, check the I/O system, and distribute I/O if necessary. See  Chapter 8, "I/O Configuration and Design".

Check if there are too few, or too small redo logs. If you have a few redo logs or small redo logs (for example, 2 x 100k logs), and your system produces enough redo to cycle through all of the logs before DBWR has been able to complete the checkpoint, then increase the size or number of redo logs. See  "Sizing Redo Log Files".

https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=328905377088011&id=1476444.1&_afrWindowMode=0&_adf.ctrl-state=qglcxccx7_153

Resolving Issues Where 'log file switch (archiving needed)' Waits Occur Because Log has not yet been Archived (文档 ID 1476444.1)

https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=y4h9057u2_4&_afrLoop=333548650062841#SYMPTOM

Slow Running User Process And Top Database Wait Event Is 'log file switch (private strand flush incomplete)' (文档 ID 983473.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=345009836211265&id=372557.1&_adf.ctrl-state=s9gk1y6c4_77

Alert Log Messages: Private Strand Flush Not Complete (文档 ID 372557.1)

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！