Shulou(Shulou.com)06/01 Report--

Log file switch

One: log file switch description

Two: log file switch official documents

One: log file switch description

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/

In archive mode, this wait event occurs when the online log switch (log file switch) is performed and the online log that needs to be switched has not been archived by the archiving process (ARCH).

When the online log file switches to the next log, you need to make sure that the next log file has been archived by the archiving process, otherwise that online log information is not allowed to be overwritten (otherwise the archived log information will be incomplete).

Such a wait event usually causes the ARCH process to slow or die for some reason.

After the log group cycle is full, the previous log archive has not been completed, and there is a wait.

LGWR cannot switch to the next log group. All submit requests wait for this event.

Possible reasons for waiting events:

(1) the storage in which the archive is located has poor performance or failure.

(2) insufficient archiving process (log_archive_max_processes)

(3) insufficient disk space for archiving

(4) if it is a remote archive, check the network.

Recommendations:

First determine whether it is unable to file or slow to file?

Unable to archive: check the alarm log to determine what causes it to fail to archive, such as insufficient space in the archive directory.

Slow archiving: to analyze the reasons for slow archiving, you can consider the following ways to optimize archiving speed

1 move the archive to a faster disk

2 increase the number of archiving processes (log_archive_max_processes)

3 increase or increase redo log file to allow more time for archiving

2 log file switch (checkpoint incomplete)

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

When one online log is switched to the next online log, you must ensure that the information recorded in the online log to be switched to (such as redo log generated by some dirty data blocks) is written to disk (checkpoint)

That is, when the log is switched, the checkpoint operation is triggered and the dbwr process writes dirty data in memory to disk.

The reason for this is that if the information of an online log file is overwritten and the data blocks that rely on this redo information for recovery have not been written to disk (checkpoint), if the system down is dropped, Oracle will have no way to restore the instance.

The status of the online log is recorded in the v$log view. Generally speaking, online logs have three states.

-- Active: the information protected in this log has not yet completed checkpoint.

-- Inactive: the information protected in this log has completed checkpoint.

-- Current: current log.

If there are a large number of log file switch (checkpoint incomplete) waiting events in the system, the reason may be that the log files are too small or there are too few log groups, so the solution is to increase the size of log files or the number of log groups.

Possible reasons for waiting events:

(1) DBWR process is slow

Increase the number of DBWR processes and use faster disks

(2) REDO LOG logs are too small or too few

Increase or increase redo log

(3) DML or DDL operations with a large amount of data occur in the database, and locate whether there is abnormal SQL through the information such as TOP SQL,Segments by DB Blocks Changes in logminer or AWR.

3. Log file switch (private strand flush incomplete)

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

The log file switch (private strand flush incomplete) wait event is a new wait event for the IMU feature after 10G, and you may encounter it sometimes if you turn on IMU.

When DML is executed, the post-image data is generated by Server Process. In IMU mode, the post-image data is first put into Private Stand Area by Server Process, refreshed to Public Log Buffer when submitted, and Redo File written to disk by LGWR.

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)'(document ID 983473.1)

The log file switch (private strand flush incomplete) wait event may be caused by 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 (document 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

Two: log file switch official documents

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 ram 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 (document 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)'(document 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 (document ID 372557.1)

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.