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)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2155340/，如需轉載，請註明出處，否則將追究法律責任。