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

This article introduces what to do if UNDO rollback is not released for a long time. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Database environment: database version: 11.1.0.7.6 operating system: HPUX IA64 B.11.31 database operation mode: RAC,4 node UNDO tablespace size: each node 200G system type: OLAP

At more than 3: 00 in the morning, I received a call from a customer saying that the database had killed a session last night (the session had already reported an error), and then the UNDO data generated by this session had been rolling back, taking up a lot of UNDO table space from 9: 00 p. M., and the new session would report an error that the UNDO table space could not be allocated when performing SQL.

We know that operations such as Insert,Delete,Update allocate space in one of the rollback segments of the UNDO tablespace to generate UNDO rollback information. When we use UNDO information when we Rollback or use version queries or flashback tables, there is a contradiction. The longer the UNDO information is kept, the more strongly these features will be supported. This is what we would like to see, but there are two sides to everything. While supporting these features, there will also be corresponding consumption, and if the information is kept for too long. The cost will increase exponentially.

This means that we need to know a suitable balance that can not only maximize our needs for these features, but also reduce storage requirements.

On the other hand, if this time is set too small, then when one of our longer SQL execution times exceeds this parameter, we will get an ORA-01555 snapshot of the old error, not to mention our system is an OLAP system.

In fact, the best way is to create a new UNDO table space, and then assign the new UNDO table space to the current instance, let the old UNDO table space roll back slowly, or add data files to the original UNDO table space.

But both methods require new storage space support. And the second one is difficult to shrink the UNDO after it is added to the original UNDO tablespace.

For UNDO tablespaces, it seems that Oracle does not want to provide too many management tools to DBA, which is also a compromise made to ensure the integrity of the data.

In the field, we tried to reduce the parameter UNDO_retention from 20800 to 8000.

SQL > alter system set UNDO_RETENTION=8000 sid='dw2' scope=both

But after observation, the effect is not obvious. Later, we know that starting from Oracle 10g, there is a parameter of _ undo_autotune, which automatically controls the value of undo_retention according to the usage of undo tablespaces, that is, when the UNDO tablespaces are automatically expanded, ensure that the value set by undo_retention is the lowest threshold, and then expand the UNDO tablespaces as needed, if the UNDO tablespaces AutoExtend is OFF Then set the value of undo_retention dynamically according to the information of UNDO STATUS, then the problem is that the value of _ undo_autotune in our system is TURE, that is to say, the value of undo_retention is determined by the system, and the changes we have made have no effect at all.

According to the view of the query system, there are about 2.2 million blocks to be rolled back, or about 200000 per hour, which means it will take 11 to 12 hours to roll back.

Under the premise of not adding UNDO table space or switching UNDO table space, there is really no good way to automatically manage UNDO in the mode of releasing UNDO space quickly (Oracle should ensure that the rollback is completed to ensure data integrity. If the session is present, there is a session process to complete the rollback, otherwise the SMON process will complete the rollback.

We can only wait for the rollback to complete, shut down the node in time, and still have to complete the rollback of the database after restarting. If you have any good ideas, you are welcome to discuss them.

My suggestion is to change the _ undo_autotune parameter to False, and then reduce the value of undo_retention appropriately. If it is not as expected, you can solve the problem by creating a new UNDO tablespace and replacing it.

SQL > create undo tablespace UNDO005 datafile'.' Size 20G autoextend off

SQL > alter system set undo_tablespace=UNDO005

Wait until the rollback is complete before switching back to the original UNDO tablespace.

SQL > alter system set undo_tablespace=UNDO002

Recommendation: this type of large database, because each SQL execution time is relatively long, the amount of data is relatively large, some statements will parameter dozens of gigabytes of UNDO data, so it is recommended that at the beginning of the rollback, switch a spare UNDO table space, let it slowly roll back, wait until the rollback is complete, and then switch back, of course, if the rollback segment is relatively large, without affecting the use of the case can also let it slowly rollback.

This is the end of what to do if UNDO rollback is not released for a long time. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.