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

Write before the case is shared

Thanks to everyone's love, we will continue to do it!

I also hope that friends who like the technology life series can help us forward it. Your retweet is the driving force for us to continue to share.

I remember when drinking with my brothers during the Dragon Boat Festival, a friend said, "Why don't you set up a user group, so that more friends can join the sharing team in the form of a public welfare, and can develop from online sharing to offline sharing?" and can go to various cities to do actual combat sharing, so that we can communicate face-to-face. "

That makes sense, so with CESOUG, that is, China Experience Sharing Oracle User Group, known in Chinese as the China experience sharing Oracle user Group, I hope that friends who are interested in different cities will join us to become co-founders (private chat with y Wechat shadow-huang-bj) to promote the atmosphere of operation and maintenance technology sharing!

And then, there is the planning of the first offline event, the theme of the event is Ode to Joy, that is, like you-ORACLE!

This will probably be the most exciting Oracle technology sharing conference you have ever attended!

Xiao y will invite the top Oracle experts in China to share it, which can be called the strongest lineup in history, and the content will definitely make you feel good. The sharing guests of the first ORACLE Ode to Joy Technology Conference in 2017 include:

Low-key walker little y Huang Yuanbang

Optimize the master old cat Chen Hongyi

Tech maniac old K × ×

Top GCS RAC and Exadata master Gao Bin

Song Rijie, former chief technical engineer of GCS

ACS mysterious master

Gold medal DBA Xu halberd (white eel)

Data recovery master Cheng Fei (pity to fly separately)

Zhang Haibin, an Oracle expert at the head office of the Bank of China.

Deng Qiang, an Oracle expert at the head office of ICBC.

And the Oracle experts of the head bank of China Construction Bank and the head bank of Agricultural Bank of China.

Why are you hesitating? quickly identify the QR code in the picture and sign up!

Editor's note: the difficulty of this problem lies in its concealment, and sometimes the fault phenomenon may not be obvious. For example, when the tablespace usage soared from 10% to 50% in an instant, you didn't notice it because you didn't reach the alarm threshold, but you were silently suffering from space leakage. Even if the alarm is given, customers for unexplained reasons just expand the table space to solve the problem simply and rudely. When this issue is re-raised by the people's Bank of China many years later, we should no longer turn a blind eye to it. It is worth our painstaking efforts to do a good job of monitoring and prevention for friends who match the version.

Preface

Recently, many of the xxx we maintain have received Oracle defect risk tips issued by the people's Bank of China on April 1st, but the article does not indicate which BUG it is or how to check whether its system has encountered a space leaking BUG. Everyone is worried that failure to prevent in time may lead to a surge in space and business disruption. Many customers called and asked which BUG it was and forwarded the documents released by the people's Bank of China on April 1st. After reading the documents issued by the people's Bank of China, the same fault that was dealt with seven years ago surfaced. We dealt with several of the same failures in 2010, and the tablespace suddenly rose to 100% for no reason, causing business disruption and shocking harm. Seven years later, there are still customers suffering from the problem of space leakage. Xiao Yi decided to open the dusty memories and work with you to recall the stories of seven years ago, hoping to help friends in need. There are ways of prevention and verification later.

Risk hint

In some older versions of Oracle databases, an inexplicable increase in tablespaces may occur. If you match all the points described in this article, you may have encountered Oracle Bug. If your database version is lower than 10.2.0.4.3, it is recommended that you check the risk as soon as possible. At the end of this article, I will show you how to confirm whether your system has encountered this bug.

Historical fault playback

At 1: 00 a.m. on December 2, 2010, the XXXIND utilization of the production environment index tablespace of the XX system rose to 100%, triggering a red alarm. Has caused business disruption. By checking the alarm results for two hours, the tablespace free is 70 at 1: 00 a.m. on December 1, with a utilization rate of 30%. By 2: 00 a.m. on December 1, the tablespace utilization rate free is 0, with a utilization rate of 100%, which is consistent with the alarm information.

Where's all the space?

As you can see from the following output, the tablespace size was only 4965m on December 1 and 16561m on December 2, using more than 10G in just one day. Because this tablespace is a business tablespace, and the application staff reported that there was no large data insertion during this period of time. So where's all the space?

A magical view

The ORACLE database provides a more obscure view, WRH$_TABLESPACE_SPACE_USAGE (available after the oracle 10g release), which records the use of tablespaces per hour. If the table space usage is full, the time period when the table space is full is recorded.

Based on the above query results, you can see that at 20:47 on December 1st, the tablespace suddenly grew from using 318064 BLOCK to 1059936 BLOCK. This point in time is the point in time when the table space is full.

Created a large object?

The check found that no new objects were created. Rule that out. Is an object suddenly getting bigger?

Check tablespace large objects

If there is a situation where a large amount of data is suddenly loaded in the table, the size of the table segment, index segment, and other objects in the tablespace will become larger. So you need to check which segment takes up the most space in the tablespace.

From the above query results, we can see that there is a segment larger than 1G in the table space, which is the XXX_ PK index segment.

The truth is clear here, although the analysis here knows who occupies the space, but this is far from enough, why is there such a large increment, why is the table not in the TOP segment and the index is really the largest in the tablespace? Are there many fields in the index? Let's continue to analyze the index. why?

What happened to the index?

As you can see, the size of the table is only 4G, but the index exceeds 12G. This is unusual, and it is not normally possible to achieve such a size ratio unless the index is created on all fields.

Space leak?

Checking the number of fields in the table, it is found that there are many fields in the table, and the average length of the table is much larger than that of the index field + rowid. The table actually has nearly 100 columns.

So we have reason to believe that there is a space leak.

How to check space allocation

Check the space allocation of the most space-consuming index through the dbms_space package that comes with oracle

As you can see, the Unformatted Blocks in the index reaches 740681, which is much larger than the actual footprint (5600-49427). That is, the index owns all the unformatted block in the tablespace, but does not use it. This is an obvious manifestation of space leakage.

Monitoring and judging method

By comparing the values of Full Blocks and FS2 and Unformatted Blocks, the two are very different, so you may encounter index space leaks or fragments.

Compare the size of the index and the table at the same time, if the index is much larger than the table. It's almost certain it's bug.

Monitoring method:

In addition to monitoring tablespace usage, it is also necessary to monitor whether the periodic increment of the tablespace is abnormal.

Confirm bug

With "Unformatted Blocks" as the keyword, search the ORACLE METALINK BUG library for the related BUG of space leakage, and you can find several similar BUG, all of which have a basic BUG of 5890312. The following are the details of the BUG. The BUG appears in versions 9.2.0.8, 10.2.0.3 and 10.2.0.4 and is confirmed by ORACLE. The BUG was fixed in PSU 10.2.0.4.3 and 10.2.0.5 PATSET.

Solution

Temporary solution: you can temporarily rebuild the index and reclaim space.

Fundamental solution:

Install the PSU patch to 10.2.0.4.3

Install 10.2.0.5 PATCHSET

Or upgrade to a later version.

If you want to hear more practical cases to share, come and sign up for the first Oracle Ode to Joy Technical Conference in 2017 ^ _ ^

Identify the QR code in the picture or read the full text to sign up.

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.