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 184.108.40.206 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 220.127.116.11 (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.