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"，共同學習，共同成長！！！