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

Most people do not understand the knowledge points of this article "how to solve the abnormal growth of Oracle sysaux tablespaces", so the editor summarizes the following, detailed contents, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "how to solve the abnormal growth of Oracle sysaux tablespaces" article.

Find a problem

For a database of a business system, the size of the sysaux table space reaches 24GB +.

Query the size of objects in the sysaux tablespace, exceeding the list of 1GB, and it is found that the ASH-related tables are too large.

SEGMENT_NAME OWNER SEGMENT_TYPE MB -WRH$_ACTIVE_SESSION_HISTORY SYS TABLE PARTITION 7151 WRH$_EVENT_HISTOGRAM_PK SYS INDEX PARTITION 1856 WRH$_EVENT_ HISTOGRAM SYS TABLE PARTITION 1777 WRH$_LATCH SYS TABLE PARTITION 1657 WRH$_SQLSTAT SYS TABLE PARTITION 1344

View how long snapshots are retained in the database

As you can see, the retention time set by AWR is 8 days, and WRH$_ACTIVE_SESSION_HISTORY ensures that 2140 days of data need to be cleaned up.

View the reasons why the AWR snapshot did not clean up

WRH$_ACTIVE_SESSION_HISTORY failed to delete expired snapshots due to partitioning failure, resulting in all data in the same tablespace.

Cause analysis of the problem:

Oracle determines which lines need to be cleared based on the retention policy. Using a special mechanism in the case of large AWR tables, we store snapshot data in partitions. One way to purge data from these tables is to delete partitions that contain only rows that exceed the retention condition. In the nightly cleanup task, we delete the partition only if all the data in the partition has expired. If the partition contains at least one row, which should not be deleted according to the retention policy, the partition will not be deleted, so the table will contain old data.

If there is no partition split (for whatever reason), we may end up in a situation where we have to wait for the latest entries to expire before we can delete the partition in which they are located. This may mean that some older entries can be significantly retained after the expiration date. The result is that the data is not cleared as expected.

Treatment method:

According to MOS:WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID 387914.1) processing steps

1.Check the partition details for the offending table before the split:

SELECT owner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GBFROM dba_segmentsWHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY'

2.Split the partitions so that there is more chance of the smaller partition being purged:

Alter session set "_ swrf_test_action" = 72

NOTE: This command will split partitions for ALL partitioned AWR objects. It also initiates a single split; it does not need to be disabled and will need to be repeated if multiple splits are required.

3.Check the partition details for the offending table after the split:

SELECT owner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GBFROM dba_segmentsWHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY'

After the above steps are completed, truncate the original partition, leaving only the new partition, freeing up the space

ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY TRUNCATE PARTITION WRH$_ACTIVE_xxxxx

Check whether the index is normal

Select index_name,partition_name,status from dba_ind_partitions where index_name='WRH$_ACTIVE_SESSION_HISTORY_PK'; above is about the content of this article on "how to solve the abnormal growth of Oracle sysaux tablespaces". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please follow the industry information channel.

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.