“ORA-1652: Unable To Extend Temp Segment” in RAC

Posted in: Technical Track

Today I’ve seen a question on OTN forum about ORA-1652 in alert.log in RAC environment. I immediately recalled the bug and thought that I should mention that here. I quickly found Metalink Note 258941.1 and was surprised to see that there are actually two bugs on that and I only was aware of one so I definitely have to blog about it now.

The issue that I hit in the past is that ORA-1652 can be dumped in alert.log without actually any queries failing. This was very confusing to find out as there was automated monitoring on alert.log errors and we had a lot of false positives resulting in escalations. In a nutshell, the problem is in the following. Every instance on Oracle RAC cluster maintains its own pool of free temporary extents and if there are no more free extents available in the current instance, ORA-1652 is generated but query doesn’t fail. Instead, RAC instance requests extents from another node and continues successfully. Bug 2858082 was filled for this issue. Oracle versions 9.2 and 10.1 are affected. Fixed in 9.2.0.7 and 10.1.0.4 and finally made it to 10.2.0.1 release.

Another bug (2934117) is related to DEFAULT TEMPORARY TABLESPACE feature. The situation is similar but in this case the query does fail with ORA-1652 as RAC instance is unable to acquire free temporary extents from another instance. There is a workaround and it’s fixed in 9.2.0.5 (you shouldn’t run a lower release these days) and 10.1.

I find the first bug very annoying as it causes problems for automated monitoring and proposed workaround (ignoring errors) is not feasible as the real issue can be missed.

Interested in working with Alex? Schedule a tech call.

About the Author

What does it take to be chief technology officer at a company of technology experts? Experience. Imagination. Passion. Alex Gorbachev has all three. He’s played a key role in taking the company global, having set up Pythian’s Asia Pacific operations. Today, the CTO office is an incubator of new services and technologies – a mini-startup inside Pythian. Most recently, Alex built a Big Data Engineering services team and established a Data Science practice. Highly sought after for his deep expertise and interest in emerging trends, Alex routinely speaks at industry events as a member of the OakTable.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *