log file switch

log file switch


一：
log file switch
说明

二：
log file switch
官方文档

一：
log file switch
说明
selectevent#,name,wait_class
fromv$event_name
wherenamelike’log file switch%’
orderby1;

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 aCLEAR LOGFILEcommand 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
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
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
Waiting for a log switch because the log is being cleared due to aCLEAR LOGFILEcommand or implicit clear logfile executed by recovery.
Wait Time:1 second
Parameters:None
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
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
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.
For thelogfileswitch(archivingneeded) 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 thelogfileswitch(checkpointincomplete) 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”，共同学习，共同成长！！！

相关推荐: RMAN-05001: auxiliary file name /u01/app/oracle/oradata/prima/system01.dbf confl

sql statement: alter database mount standby database RMAN-00571: =========================================================== RMAN-…