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/

在歸檔模式下，這個(gè)等待事件發(fā)生在在線日志切換（log file switch）時(shí)，需要切換的在線日志還沒有被歸檔進(jìn)程（ARCH）歸檔完畢的時(shí)候。

當(dāng)在線日志文件切換到下一個(gè)日志時(shí)，需要確保下一個(gè)日志文件已經(jīng)被歸檔進(jìn)程歸檔完畢，否則不允許覆蓋那個(gè)在線日志信息（否則會(huì)導(dǎo)致歸檔日志信息不完整）。

出現(xiàn)這樣的等待事件，通常是由于某種原因?qū)е翧RCH 進(jìn)程緩慢或死掉。

日志組循環(huán)寫滿以后，前一個(gè)日志歸檔尚未完成，出現(xiàn)等待；

LGWR不能切換到下一個(gè)日志組。所有提交請(qǐng)求都等待此事件。

出現(xiàn)等待事件可能原因：

(1)歸檔所在存儲(chǔ)I/O性能差或出現(xiàn)故障

(2)歸檔進(jìn)程(log_archive_max_processes)不足

(3)歸檔所在磁盤空間不足

(4)如果是遠(yuǎn)程歸檔，需要檢查網(wǎng)絡(luò)

建議：

先確定是無法歸檔還是歸檔緩慢的問題？

無法歸檔：檢查告警日志，確定是什么原因?qū)е聼o法歸檔，例如 歸檔目錄空間不足等。

歸檔緩慢：分析歸檔緩慢的原因，可以考慮如下方式優(yōu)化歸檔速度

1 將歸檔移動(dòng)到更快的磁盤

2 增加歸檔進(jìn)程數(shù)量(log_archive_max_processes)

3 增大或增多redo log file，給予歸檔更多的時(shí)間

2 log file switch (checkpoint incomplete)

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

當(dāng)一個(gè)在線日志切換到下一個(gè)在線日志時(shí)，必須保證要切換到的在線日志上的記錄的信息（比如一些臟數(shù)據(jù)塊產(chǎn)生的 redo log）被寫到磁盤上（checkpoint），

也就是在日志切換時(shí)，會(huì)觸發(fā)檢查點(diǎn)操作，dbwr進(jìn)程會(huì)將內(nèi)存中臟數(shù)據(jù)寫入到磁盤。

這樣做的原因是，如果一個(gè)在線日志文件的信息被覆蓋，而依賴這些 redo 信息做恢復(fù)的數(shù)據(jù)塊尚未被寫到磁盤上（checkpoint），此時(shí)系統(tǒng) down 掉的話，Oracle 將沒有辦法進(jìn)行實(shí)例恢復(fù)。

在 v$log 視圖里記錄了在線日志的狀態(tài)。 通常來說，在線日志有三種狀態(tài)。

--Active: 這個(gè)日志上面保護(hù)的信息還沒有完成 checkpoint。

--Inactive： 這個(gè)日志上面保護(hù)的信息已完成 checkpoint。

--Current： 當(dāng)前的日志。

如果系統(tǒng)中出現(xiàn)大量的 log file switch（checkpoint incomplete）等待事件，原因可能是日志文件太小或者日志組太少，所以解決的方法是，增加日志文件的大小或者增加日志組的數(shù)量。

出現(xiàn)等待事件可能原因：

(1) DBWR進(jìn)程慢

增加DBWR進(jìn)程數(shù)，使用更快的磁盤，

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

增大或增加redo log

(3) 數(shù)據(jù)庫出現(xiàn)大數(shù)據(jù)量的DML或DDL操作，通過logminer或AWR中TOP SQL,Segments by DB Blocks Changes等信息定位是否有不正常的SQL在執(zhí)行。

3. log file switch (private strand flush incomplete)

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

log file switch(private strand flush incomplete)等待事件是10G后針對(duì) IMU特性新增的等待事件，如果你開啟了IMU，有時(shí)候就可能會(huì)遇到它。

DML執(zhí)行時(shí)，后映像數(shù)據(jù)是由Server Process產(chǎn)生的。在IMU方式下，后映像數(shù)據(jù)會(huì)先被Server Process放到Private Stand Area，提交時(shí)刷新到Public Log Buffer，在由LGWR寫進(jìn)磁盤中的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)

歡迎關(guān)注我的微信公眾號(hào)"IT小Chen"，共同學(xué)習(xí)，共同成長！?。?/span>