Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people do not know how to deal with the undo global data problem in latch, so this paper summarizes the causes and solutions of the problem. I hope you can solve this problem through this article.

During the May Day holiday, I received a Wechat from a classmate of operation and maintenance, saying that the application had reported an error and that it was related to the database. I sent a screenshot to see that the error message was could not get next sequence value. Thinking that a certain sequence has reached the maximum value, let me find out which sequence it is.

So I checked the dba_sequences, and none of the sequence reached the maximum.

So look at the information of session, query the wait events in v$session, and find that a large number of wait events are "latch: undo global data". Judging from the name of the event, it should be the problem with undo.

Query the utilization of undo tablespaces, and sure enough, it reaches 100%. But undo can be reused, unless a very large transaction occupies the entire undo table space, and the undo table space has more than 460G, which is unlikely to be full.

I searched the Internet for articles related to latch: undo global data, and there was a reference to a document on MOS: "LATCH: UNDO GLOBAL DATA" In The Top Wait Events (document ID 1451536.1)

This wait event described in the document means that a large number of session are trying to find a new undo extent and steal an unexpired undo extents. This wait is related to insufficient UNDO space with the implicit parameter _ undo_autotune set to FALSE.

The _ undo_autotune of the current database is indeed FALSE, and undo_retention=259200, which translates to 72 hours.

First, take a look at the implicit parameter _ undo_autotune:

Starting from version 10.2, oracle automatically adjusts undo retention by default.

Automatically adjust the undo_retention parameters according to the size of your undo tablespace and the busy degree of the system (information in v$undostat), so in a 10g database you will often find that undo tablespace is always full, because when your undo tablespace has free space, the system automatically adjusts undo_retention to retain more undo blocks. This method is good for long-time queries, but it is not suitable for typical OLTP systems. Because it is unlikely to run such a long query on OLTP, and it will cause the problems encountered above on a very busy OLTP.

When _ undo_autotune=true, undo_retention is no longer applicable. In the case of _ undo_autotune=false, the undo_retention is retained for the set time.

Through the above explanation, coupled with the data cleaning work done during the May Day holiday, a large number of undo was retained for 72 hours, resulting in a full undo table space and no normal access to the application.

Solution:

1. Set _ undo_autotune=true, which can be modified online.

2. Increase the size of undo table space (resize existing data files or add data files)

3. Adjust the undo _ retention parameter

Finally, the undo_retention parameter is reduced to 43200 (12 hours), and the application returns to normal.

Reference: http://blog.itpub.net/4227/viewspace-1060723/

Http://blog.csdn.net/dba_waterbin/article/details/8646982

After reading the above, have you mastered the method of dealing with the undo global data problem in latch? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.