We cannot tell from the apparent information presented HOW it got to 200. But it should not be able to extend beyond the set MAXBYTES. The behavior I would have built is to complain if you try to set maxbytes lower than an already existing size for a file. If you keep logs of the manual intervention sections (at least) or complete alert logs for at least the system, then you might discover how it got to 200.
Yes, you ARE within the limits of bigfile, presuming that more space is available on the storage volume. NO, it should not autoextend if it is at or beyond the MAXBYTES defined for the file, and tossing the error you are seeing seems correct behavior to me.
Sigh.
From: Lok P [mailto:loknath.73_at_gmail.com]
Sent: Wednesday, September 08, 2021 12:13 PM
To: Mark W. Farnham
Cc: Hemant K Chitale; John Piwowar; Oracle L
Subject: Re: Undo Tablespace Error
I see it's a bigfile tablespace. And if i am correct it will go beyond normal ~31Gb individual data file size limit. Correct me if I'm wrong.
And then in this case , can it go beyond defined max_bytes? Or is it that at the point when it reaches ~160GB size it throws an ora-30036 error and then someone has increased the size to ~200GB but it's not updated in dba_data_files? But in any scenario, is behaviour of UNDO different if we have AUTO EXTEND ON with defined max_size(i.e. our current setup) VS Autoextend OFF with the same max_size?
On Wed, Sep 8, 2021 at 7:39 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:
If you have a single file limited to 160 and it is already 200, it seems to me that file cannot extend. If you have reached 200, then I would expect it to go splat failing to extend with autoextend on, because it fails the maxbytes limit.
Since the file is already bigger than what someone probably set it down to later, the maxbytes seems like a red herring and autoextend could only come into play if you added a file.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lok P
Sent: Wednesday, September 08, 2021 9:00 AM
To: Hemant K Chitale
Cc: John Piwowar; Oracle L
Subject: Re: Undo Tablespace Error
Thank you Hemant.
to your point "Besides dba_hist_tbsp_space_usage you should also look at v$undostat"
This error occurred to us 4-5days ago. So from min(begin_time) , I am seeing it doesn't have those time data in v$undostat anymore. So is there another way to dig into history?
If i see current values in v$parameter, We have UNDO_MANGEMENT set as AUTO undo_retention set as 3600. If I see current values in dba_data_files we have only one data file for the UNDo tablespace. and it has BYTES as 200GB, MAXBYTES- 160GB, USER_BYTES- 200GB, Autoextensible- YES. So in this case do you suggest just by turning OFF the AUTOEXTEND , we should be good to avoid this issue in future?
But I had one question, even if we have AUTOEXTEND ON, as we have defined the MAXBYTES as ~160GB for that data file, so that is as good as AUTOEXTEND OFF having same maxBytes as ~160GB as because in both of the cases it can't go beyond 160GB. Is my understanding wrong here?
On Wed, Sep 8, 2021 at 8:14 AM Hemant K Chitale <hemantkchitale_at_gmail.com> wrote:
If you have AUTOEXTEND ON it is likely that the datafile had already hit the MAXSIZE.
So this would result in failure 6 in Oracle Support document " Troubleshooting ORA-30036 - Unable To Extend Undo Tablespace (Doc ID 460481.1)
Besides dba_hist_tbsp_space_usage you should also look at v$undostat
(also in earlier versions dba_hist_tbsp_space_usage had bugs in reporting Tablespace Usage for Undo tablespaces)
As John says, if you have AUTOEXTEND ON, Oracle will try to keep extending Undo retention. Setting a max size for the datafiles and then setting AUTOEXTEND OFF is better.
Hemant K Chitale
On Mon, Sep 6, 2021 at 2:59 AM Lok P <loknath.73_at_gmail.com> wrote:
Thank You John. I understand your point that with AUTOEXTENSIBLE YES oracle may tend to keep the expired UNDO longer to satisfy retention thereby increasing the UNDO tablespace to higher size. But , If we will talk specifically about the exact error here , isn't it true that with AUTOEXTENSIBLE YES, it should not have made it errored out with Ora-30036 rather it would have thrashed/increased UNDO tablespace size by its own?
Also as I see from dba_hist_tbspc_space_usage , the UNDO usage(tablespace_usedsize) was ~160GB which was equal to tablespace_size when it failed with ora-30036 , but then it increased to ~200GB after 4-5hrs, yet to see if somebody has increased it manually or it increased by itself.
On Sun, Sep 5, 2021 at 2:53 AM John Piwowar <jpiwowar_at_gmail.com> wrote:
The “autoextensible” setting on your undo data files is possibly the culprit here. Undo segments are a bit of a special case, and Oracle’s decision-making about when to reclaim expired undo can be thrown off when it thinks it has the option to extend a data file instead. It’s possible that you might still need to resize your undo to accommodate recent changes in your workload, but you’re better off with fixed-size undo datafiles, followed by an examination of your automatic undo configuration (undo_retention, etc).
Sorry about the vagueness of the response; I’m on my phone and can’t get decent references with my thumbs. :)
