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

This article introduces you how to deal with the WRH$_ACTIVE_SESSION_HISTORY problem, the content is very detailed, interested friends can use for reference, I hope it can be helpful to you.

A few days ago, I dealt with the problem that Oracle high availability becomes unavailable. The problem is this, WRH$_ACTIVE_SESSION_HISTORY.

The environment is a background with a RAC and a single instance database. First, the single-instance database was found to be very bad when I randomly checked AWR. (I am not the DBA of operation and maintenance. I am not in charge of these. I just come to me when I encounter problems.) there is a SQL execution that can not be finished in a day. I decided that there must be something wrong with the developer's writing.

The machine is very good. 96C 256g memory. And then someone came to me and told me that the RAC couldn't be connected. I'll connect and it will take a long time to enter the user name and password.

Check the latest AWR report and find that 4 am is the last one. And it's after 12:00. It's been eight hours.

Since there is no AWR, then AWR can not be saved. Take a look at the tablespace.

A look at the SYSAUX space is almost full, the size is 64G. This must not let people see this size a bit strange feeling. The operating system can only recognize a file of 32G, how can there be 64G. So that means there should be two files. Each file is 32G. At first glance, it was like this. It is inferred that the problems in the previous operation and maintenance have been directly covered up.

Let the file expand automatically, add another one to 32G, and then expand automatically. It doesn't matter why there's an exception. This leaves the hidden danger. If you continue the original train of thought, add another one, and then let him automatically to 32. Then it is getting bigger and bigger, and it is difficult to solve.

Take a look at the session and process views again. They are all nearly 4000. In the database, one of these two parameters is 4000 and the other is more than 6000. In other words, the operation and maintenance staff should have seen them increase before, but they did not feel abnormal, since the number of connections was not enough. As for these problems, none of them will be solved. I seem to think it's none of their business.

It is conceivable that if there are not enough connections and continue to expand the parameters, then this will become larger and larger. I can't help it after that.

Check that the largest table in SYSAUX space is about 70 million pieces of data in WRH$_ACTIVE_SESSION_HISTORY. As its name implies, this table is the active session history table. So this has something to do with the problem of development.

It is estimated that 26 gigabytes of space can be recycled with truncate. The process took about 20 minutes. The bigger it is, the harder it is to do it, and the longer it takes. This is the consequence of not paying attention to the problem at ordinary times.

Of course, before you do this again, check to see which day it starts to be big. Starting from last Friday, there are 3500 entries per second.

The radical cure is to develop and change, but only truncate, the WRH$_ACTIVE_SESSION_HISTORY, can free up space at present. Then create a profile for the single instance user to restrict the connection to the RAC. Because this is mainly caused by a single instance connecting to RAC. And this single instance is actually from dblink. There would have been no problem. Create a materialized view with a single instance. But the developer does not access the local materialized view or connects to the RAC remotely.

The original purpose of the separation is to keep the single instance machine from rebooting the RAC, but the result is still the same. In fact, if it is done well, it will be no problem to put it together. It's not much business. When it is impossible to do so, it is useless to separate. As far as the actual development situation is concerned, you can see the level and capability of development by looking at the single instance running at full load.

These machines are not a problem processing 30 to 500000 transactions a day, but it is now estimated that 3000 cannot be processed.

This is the end of the way to deal with WRH$_ACTIVE_SESSION_HISTORY problems. I hope the above content can be of some help 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.